How to replace specific text in .csv file

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

Answer 1

[per isakson]

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 );

[Daniel Berveiller]

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]

In other word, how to get, after change of "NAN", the same structure as original file.

[Jan]

@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]

@per isakson: fwrite(fid, chr, 'char') would be faster than fprintf(fid, '%s', chr).

[per isakson]

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]

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]

"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]

@Jan, Thanks for your comment. Of course, however the problem is, fwrite isn't in my active repertoire.

[Daniel Berveiller]

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]

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]

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.

Answer 2

[Mathieu NOE]

hello

would this work for you ?

strArray = readlines('test.csv');
str = "NAN";
newString = '-9999';
newStr = strrep(strArray,str,newString);
writematrix(newStr,'test_out.csv');

[Daniel Berveiller]

Thank you but sorry, I forgot to say I'm working on R2017b version and readlines function seems to be since 2020b.