How to replace specific text in .csv file
31 views (last 30 days)
Show older comments
Daniel Berveiller
on 27 Jan 2021
Commented: per isakson
on 29 Jan 2021
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
0 Comments
Accepted Answer
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
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.
More Answers (1)
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
See Also
Categories
Find more on Import, Export, and Conversion in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!