How to replace specific text in .csv file

2021 1 27
2 个回答
回答已采纳
更新时间：2021 1 29
7 次查看（30 天）

0 个投票

I have a lot of "NAN" in a .csv as following :
"2020-01-03 05:00:00",7094,-0.0333308,-0.5147614,-0.8675244,38.42,0,6.799,0.508,-1,0,-1
"2020-01-03 05:30:00",7095,"NAN","NAN","NAN",38.42,1.216,6.799,0.508,-1,0,-1
"2020-01-03 06:00:00",7096,0,0,0,38.61,0,6.833,0.505,-1,0,-1
How can I replace "NAN" by -9999. I tried using eg Notepad++ but it does not recognize the quotes ! It would be nice to have a matlab code to do that automaticaly cause I have a lot of simlar files.
Thanks

请先登录，再回答此问题。

 采纳的回答

per isakson
per isakson 2021-1-27
编辑：per isakson 2021-1-29

1 个投票

Another approach, which avoids conversion to numerical and back to text. (And, I think, works with R2006 and later releases.)
%%
chr = fileread('test.csv');
chr = strrep( chr, '"NAN"', '-9999' );
fid = fopen( 'test.csv', 'w' );
fprintf( fid, '%s', chr );
fclose( fid );
type test.csv
outputs
"2020-01-03 05:00:00",7094,-0.0333308,-0.5147614,-0.8675244,38.42,0,6.799,0.508,-1,0,-1
"2020-01-03 05:30:00",7095,-9999,-9999,-9999,38.42,1.216,6.799,0.508,-1,0,-1
"2020-01-03 06:00:00",7096,0,0,0,38.61,0,6.833,0.505,-1,0,-1
In response to comment
% ** Datafile to work on
ficin='file.dat';
% Noun of output file
idot=strfind(ficinOK,'.');
ficout=strcat(ficin(1:(idot-1)),'_OK',ficin(idot:end)); %fichier produit par le script
% Find any "NAN" and replace by -9999
chr = fileread(ficin); % read from "Datafile to work on"
chr = strrep( chr,'"NAN"','-9999'); % replace any "NAN" by -9999
fid = fopen(ficout,'w');
fprintf(fid,'%s',chr); % write to output file
fclose(fid);
% Import data % read from output file
data = importdata( ficout );

11 个评论
显示 9更早的评论 隐藏 9更早的评论

Daniel Berveiller
Daniel Berveiller 2021-1-29
Nice it works fine.
How can I work on the output? I was using importdata function to open and read the original csv file (containing header lines), but how can I manage this please to I have?
Here is the first part of my code with the adpatation. The importdata function does not work
% ** Datafile to work on
ficin='file.dat';
%Find any "NAN" and replace by -9999
chr = fileread(ficin);
chr = strrep( chr,'"NAN"','-9999');
fid = fopen(ficin,'w');
fprintf(fid,'%s',chr);
fclose(fid);
% Noun of output file
idot=strfind(ficinOK,'.');
ficout=strcat(ficin(1:(idot-1)),'_OK',ficin(idot:end)); %fichier produit par le script
% Import data
data = importdata(ficin);
Daniel Berveiller
Daniel Berveiller 2021-1-29
In other word, how to get, after change of "NAN", the same structure as original file.
Jan
Jan 2021-1-29
@Daniel Berveiller : What exactly does this mean: "The importdata function does not work" ? Do you get an error message or does the result differ from your expectations?
In your code example, "icinOK" is not defined.
Jan
Jan 2021-1-29
@per isakson: fwrite(fid, chr, 'char') would be faster than fprintf(fid, '%s', chr).
per isakson
per isakson 2021-1-29
编辑：per isakson 2021-1-29
My first script overwrites the data file with "NAN" replaced by -9999. I've added a modified version, which is based on your comment.
"The importdata function does not work" I cannot see any reason why importdata should fail with the modified data file when it worked with the original one. Does "NAN" appear in the header? There can be one of many reasons.
Did you try to import it interactively . That may provide a hint. What was the full error message?
Daniel Berveiller
Daniel Berveiller 2021-1-29
Sorry there were errors. I get this error
Error using importdata (line 137)
Unable to open file.
% ** Datafile to work on
ficin='file.dat';
% Noun of output file
idot=strfind(ficin,'.');
ficout=strcat(ficin(1:(idot-1)),'_OK',ficin(idot:end)); %fichier produit par le script
% Find any "NAN" and replace by -9999
chr = fileread(ficin); % read from "Datafile to work on"
chr = strrep( chr,'"NAN"','-9999'); % replace any "NAN" by -9999
fid = fopen(ficin,'w');
fwrite(fid,chr,'char'); % write to output file
fclose(fid);
% Import data % read from output file
data = importdata(chr);
per isakson
per isakson 2021-1-29
"the same structure as original file" The sample file I work with doesn't change structure.
Open and inspect the file in a text editor. What has changed?
per isakson
per isakson 2021-1-29
@Jan, Thanks for your comment. Of course, however the problem is, fwrite isn't in my active repertoire.
Daniel Berveiller
Daniel Berveiller 2021-1-29
编辑：Daniel Berveiller 2021-1-29
I tried with a small test file I can open in the Matlab editor. The file is composed of 4 header lines. I attached the example file.
Daniel Berveiller
Daniel Berveiller 2021-1-29
I tried again and it works fine now. Sorry.
So the following works fine :
% ** Datafile to work on
ficin='file.dat';
chr = fileread(ficin); % read from "Datafile to work on"
chr = strrep( chr,'"NAN"','-9999'); % replace any "NAN" by -9999
fid = fopen(ficin,'w');
fwrite(fid,'%s',chr); % write to output file
fclose(fid);
per isakson
per isakson 2021-1-29
Importing data from files is a pain. That's the reason why The MathWorks continuously adds new data reading functions/features. importdata is supposed to be smart, but with your sample file it only produces a mess (when used by me). It has something to do with the lines 2,3,4. I cannot care. This old time function does the job.
%%
ffs = 'D:\m\cssm\2020_B_HTGSoil-2_Soil_GfluxP5_test.txt';
fid = fopen( ffs );
cac = textscan( fid, '%q%f%f%f%f%f%f%f%f%f%f%f', 'CollectOutput',true ...
, 'HeaderLines',4, 'Delimiter',',', 'TreatAsEmpty','"NAN"' ...
, 'EmptyValue', -9999 );
[~] = fclose( fid );
readtable can read your file, but it also requires the "extra" info about the file. It outputs a nice table.

请先登录，再进行评论。

更多回答（1 个）

Mathieu NOE
Mathieu NOE 2021-1-27

0 个投票

hello
would this work for you ?
strArray = readlines('test.csv');
str = "NAN";
newString = '-9999';
newStr = strrep(strArray,str,newString);
writematrix(newStr,'test_out.csv');

1 个评论
显示 -1更早的评论 隐藏 -1更早的评论

Daniel Berveiller
Daniel Berveiller 2021-1-27
编辑：Daniel Berveiller 2021-1-27
Thank you but sorry, I forgot to say I'm working on R2017b version and readlines function seems to be since 2020b.

请先登录，再进行评论。

类别

在 帮助中心 和 File Exchange 中查找有关 Text Files 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by