How to replace specific text in .csv file

31 views (last 30 days)
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

Accepted Answer

per isakson
per isakson on 27 Jan 2021
Edited: per isakson on 29 Jan 2021
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 Comments
Daniel Berveiller
Daniel Berveiller on 29 Jan 2021
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 on 29 Jan 2021
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.

Sign in to comment.

More Answers (1)

Mathieu NOE
Mathieu NOE on 27 Jan 2021
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 Comment
Daniel Berveiller
Daniel Berveiller on 27 Jan 2021
Edited: Daniel Berveiller on 27 Jan 2021
Thank you but sorry, I forgot to say I'm working on R2017b version and readlines function seems to be since 2020b.

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!