importing large amounts of text and numerical data from csv or txt files

Hi everybody

I have a very large csv or txt file with numerical as well as text data. I am looking for a way to import this data, beginning from the third row. The text strings in the data ("NA") should be represented as NaN.

The original csv data represents approx. 1000 columns and 500 rows, so specifying the handling of each column is not feasible.

Any ideas?

Thank you, best,

Chris

Answer 1

[Walter Roberson]

numcols = 1000;
fmt = repmat('%f',1,numcols);

fid = fopen('YourFile.csv', 'rt');
indata = textscan(fid, fmt, 'Headerlines', 2, 'Delimiter', ',', 'TreatAsEmpty', 'NA', 'CollectOutput', 1);
fclose(indata);

indata = indata{1};

[Christian]

Again: awesome, thanks a lot!

There is still one problem:

How can I find out in advance how many columns are in that csv? The csv-structure is always one column of dd.mm.yyyy, no data strings yet, only numbers, followed by numerical data and NA's.

Have a great day, and thanks a lot.

Best,

Chris

[Sven]

Read one line using fgetl(), and count how many commas are in that line:

str = fgetl(fid);

numcols = nnz(str==',') + 1;

You'll need to make sure your fgetl() is run on one of your data lines (not your header), and take care that textscan() works from the top of the file again.

[Christian]

Works like a charm, thank you Sven.

And thank you for the remark to take care that textscan() works from the top again.

But why doesn't it do that by default?

[Sven]

When working with reading/writing files, by default the "pointer" to the current location in the file is always at the end of the last piece that was read. This means that you can do things like reading a file line-by-line without explicitly needing to move the pointer of the current location to "current location plus whatever-the-length-of-the-last-line-was". In other words, it's easier under normal operation, but needs an extra piece of code for your case where you want to go back and re-read that portion.

[Walter Roberson]

fseek(fid, 0, 'bof')

will move the file pointer back to the beginning of the file.