Read a very large .csv file, split into parts and save each part into a smaller .csv file

Deat Matlabers,

I need to read a very large .csv file with about 15.000 columns and 500.000 rows. I need to split it into chunks of rows (i.e. 20.000 rows and all 15.000 columns), and save each chunk into a separate .csv file.

1. I have tried to use textscan, but I am not sure that this can work, as I have not only numerics, but also non-numerics and dates across separate columns. I would ideally aim to get all this information, as I will need it for different parts of my project.

2. I also attempted tabularTextDatastore, but I get an error:

Unable to determine the format of the DATETIME data.

Try adding a format to the DATETIME specifier. e.g. '%{MM/dd/uuuu}D'.

Is there any way I could provide a DATETIME specifier (this is not explained in the relevant documentation)?

Memory is not a problem here, as I currently use a supercomputer in terms of RAM.

I would be grateful for any ideas.

George

Answer 1

[Jeremy Hughes]

If your plan is to write all the small CSV files out, and do nothing with them in MATLAB, I'd say just use tabularTextDatastore, and set all of the ds.TextscanFormats(:) = {'%q'}, There should never be any errors with '%q'

Then use writetable.

ds = tabularTextDatastore(filename,'ReadSize',myReadSize);
ds.TextscanFormats(:) = {'%q'};
while hasdata(ds)
    % Need to figure out the file names but other than that, this should work.
    writetable(read(ds),output_filename);
end

[GioPapas81]

Hi Jeremy,

1. Many thanks for your answer. Sorry, as I am not very familiar with the TextscanFormats yet, what is the term (:) specifying here? Is it the number of the column (e.g. column no 1000 should be denoted as (1000))?

I have dates across many columns, but I could find a way to specify them. If let's say I have dates in columns number 100 and 1000, would the following command be correct (?):

ds.TextscanFormats(100,1000) = {'%q','%q'};

2. On a separate but similar note, I also tried the following way:

ds.SelectedVariableNames = {'eid','20201-2.0'};

where eid, and 20210-2.0 are column variables. This way would also work for me, as I could extract specific columns to work with for my data analysis. However, I get an error:

Error using matlab.io.datastore.TabularTextDatastore/set.SelectedVariableNames (line 619)

SelectedVariableNames must be a unique subset of VariableNames.

I don't have other variable with that name, but the same name repeats in the same column across multiple rows.

If I could get either of 1 or 2 to work, that would be so helpful.

Thank you again,

George

[Jeremy Hughes]

':' is a MATLAB syntax meaning "all".

x(:) = -1,

would set all the values in x to -1. I meant literally that code. =)

[GioPapas81]

Thank you Jeremy, I will try this out.

George

Answer 2

[Sulaymon Eshkabilov]

Hi,

The answer is rather simple. You can take out all dates with string specifier: %s. E.g. file called: DATA_date.txt

DATE Row1 Row2 Row3 Row5

11/11//2019 1 1.13 2 3.33

11/11//2019 2 0.13 3.12 3.33

11/11//2019 3 2.13 -2 -5.33

11/11//2019 4 4.13 -3 -7.33

11/11//2019 5 3.13 5.5 -8.33

11/11//2019 6 2.13 2.6 -13.33

Can be imported into matlab workspace with:

FileName = 'DATA_date.txt';
FID = fopen(FileName, 'r');
SPECs = '%s%d%f%f%f';
N_header = 1;
DATA = textscan(FID, SPECs, 'headerlines', N_header);
fclose(FID);

Now all imported data will be inside a cell array DATA. DATA{1,1} contains DATE values; DATA{1,2} contains data of Row1; ... DATA{1,5} contains data of Row5.

Good luck.

[GioPapas81]

Thank you Sulaymon. When I tried your example, I get an error and can't see the final DATA output variable:

Error using textscan

Invalid file identifier. Use fopen to generate a valid file identifier

Is there any chance I can order to extract specific columns (e.g. in your example Row1, etc.)?

I have thousands of columns and one way I figure to overcome this issue is:

a) select specific columns (without dates) which are useful for my data analysis, whilst,

b) specify columns with dates to apply your %s approach.

Thank you again.

[Sulaymon Eshkabilov]

Of course, in this case as it appears does not work due to empty rows created in-between data. I should have given to you the original file (my dummy data file. DATA_date.txt) that is now attached. Test it.

While importing you can't skip some data and select others. Instead, you import/read all data and take out whichever is necessary from your imported/matlab read data and export to an external file.

Only after importing/reading the whole data, you omit the columns which are not of your interest.

Good luck

[Sulaymon Eshkabilov]

Carefully pay attention how your data is formatted such as data type, viz. integer, floating point, dates, texts, etc. Number of columns in each row has to match with the subsequent row. That means your data need to be very well neatly formatted. If you have one data point missing somewhere in your large data that would create a problem.

Good luck.

[GioPapas81]

Unfortunately I do have lots of missing data in my file,randomly distributed. I also don't know which columns have dates (there are tousands of columns, across houndreds of thousans of rows).

I hoped that the tabularTextDatastore option would be possible, but I think it is not possible to account for dates via that route (according to the errors I get above).

But, thank you for your responses Sulaymon.