MATLAB Answers

Leon
0

Problems when using textscan to read csv files

Asked by Leon
on 27 Nov 2013
Latest activity Commented on by dpb
on 14 Aug 2014
Accepted Answer by dpb
I have multiple csv files that needs to be loaded into Matlab. Below is my script. Weird enough, it works flawlessly for some of them, but for others, it can only read the very first row of the data, and refuse to move on to read the rest of the data. I tried to open the csv file using xlsx format and re-save them using tab-delimited txt files, then the script always works. But this is not the best option, as I have too many of the csv files. Anyone has any clue?
file1 = ['sko', name, '_final.csv']
fid = fopen(file1, 'rt');
% skip the headerlines:
line1=fgetl(fid)
fmt = [repmat('%s', 1, 25) '%*[^\n]'];
nn = 0;
while ~feof(fid);
nn=nn+1;
C = textscan(fid, fmt, 1, 'delimiter', ',', 'collectoutput', true);
S{nn}.group_ship = C{1}{2};
S{nn}.cruiseid = C{1}{3};
S{nn}.jday = str2num(C{1}{5});
end;
fclose(fid);

  7 Comments

dpb
on 28 Nov 2013
OK, iff'en it'll make you feel better I'll move the comment to Answers. :)
BTW, since you're doing it the way you are (altho I wondered why not just read the whole file then select the columns wanted instead of line-by-line), why not count the number of commas in the header line and make the format field fit the actual data dynamically?
Think I'll leave this here for my fellow Googlers: I found that using
C= textscan(fid,fmt,'Delimiter',',','CollectOutput',true);
with no loop to check for the end of the file created a cell containing a correct 2D array. Specifying any sort of new line or whitespace screwed it up; it's up to smarter people to say why.
dpb
on 14 Aug 2014
Indeed, default behavior is for the format string to be applied repetitively to end of file; combining that default behavior with trying to help too much trying to account for records manually does make for problems because it then tries to do what was asked for--even when not needed.
Only time things like skipping rest of record and the like are needed/useful is when, for example, one is ignoring one or more fields in each record--then it needs the extra help to be told to do that.

Sign in to comment.

Tags

2 Answers

Answer by dpb
on 28 Nov 2013
Edited by dpb
on 28 Nov 2013
 Accepted Answer

1) They're only 22 columns of data for a fmt string of 25...can't help altho by itself it doesn't seem to confuse. (ADDENDUM: But does return 3 empty cells at end which seems pointless?)
2) The killer seems to be the attempt to skip end of line -- not surprising to me that cornfoozed textscan since already past eol trying to satisfy the format string.
Killing the trailing '%*[^\n]' seems to work irrespective of correct count or no.
BTW, since you're doing it the way you are (altho I wondered why not just read the whole file then select the columns wanted instead of line-by-line), why not count the number of commas in the header line and make the format field fit the actual data dynamically?
Or, since you seem to be picking a fixed set of columns, why not read those only and use the skipping facility to not return the undesired ones? This then would be a place where the "skip to end of line" would come into play after the last desired column is scanned in the format string.

  3 Comments

"I wondered why not just read the whole file then select the columns wanted instead of line-by-line), why not count the number of commas in the header line and make the format field fit the actual data dynamically?"
I'd love to do what you suggested, but do not know how. Do you have an example script to share? Thanks. I do need all the columns, I did not spell out my whole script here.
Instead of
fmt = [repmat('%s', 1, 25) '%*[^\n]'];
try
fmt = [repmat('%s', 1, numberOfCommasYouCounted) '%*[^\n]'];
where the number of commas you counted is stored in variable called numberOfCommasYouCounted (or whatever you want).
dpb
on 28 Nov 2013
There is one more field in the line than number of columns, though, so
fmt = [repmat('%s', 1, numberCommasCounted+1) '%*[^\n]'];

Sign in to comment.


Answer by Simon
on 28 Nov 2013
Edited by Simon
on 28 Nov 2013

Hi!
Even if the question is already answered, my suggestion is
% read in file
fid = fopen('myfile.csv');
FC = textscan(fid, '%s', 'delimiter', '\n');
fclose(fid);
FC = FC{1};
% prepend separator ';'
FC = strcat(';', FC);
% read all columns
CSV = regexp(FC, ';([^;]*)', 'tokens');
It is quite fast, needs no loops and you can read in any file regardless of the number of columns.

  1 Comment

Awesome!
Thank you so much for sharing.

Sign in to comment.