Read CSV into table but not all of the columns

I have a large CSV of ~200 MB with about 1 milion lines and 50 columns.

I need all of the lines, but only like 4-5 columns.

When I read the entire table with 'readtable' and then filter only the colums I need it takes about 10 seconds and more than 1GB of memory.

Is there a more efficient way to read only the columns I need?

Thanks!

[Stephen23]

Perhaps https://www.mathworks.com/help/matlab/ref/fscanf.html

Answer 1

[Star Strider]

Without at least an example of your .csv file it is difficult to determine that. One option (for both readtable and readmatrix) is the 'Range' name-value pair. There is no direct link, however you can search for it in the Spreadsheet Files section of the documentation. It is more fully explained in the DataRange documentation section of SpreadsheetImportOptions.

I am not sure if this is more efficient (I never timed it), however it is the only option that appears to exist for what you want to do.

[Stephen23]

"it is the only option that appears to exist for what you want to do. "

For text files the FORMAT option can be specified to read/ignore any columns.

[Ran Peiser]

The CSV has headers in the first line and then lines of data, it looks something like:

Param1,Param2, Param3

0,0,0

1,2,3

100,200,400

7,8,9

[Sarah Gilmore]

Hi @Ran Peiser,

If you use an DelimitedTextImportOptions object, you can specify which columns you want to read in via SelectedVariableNames. To create this object, first call detectImportOptions on the file. This is what readtable is actually doing under the hood. Once this object is created, you can set the SelectedVariableNames property and then pass this object to readtable as the second input argument.

Best,

Sarah

[Star Strider]

If the columns are consecutive, 'Range' works —

T = array2table([0,0,0
    1,2,3
    100,200,400
    7,8,9], 'VariableNames',["Param1","Param2", "Param3"])

T = 4×3 table
    Param1    Param2    Param3
    ______    ______    ______

       0         0         0  
       1         2         3  
     100       200       400  
       7         8         9  

writetable(T,'F.csv')

T = readtable('F.csv', 'Range','B:C')

T = 4×2 table
    Param2    Param3
    ______    ______

       0         0  
       2         3  
     200       400  
       8         9  

T = readtable('F.csv', 'Format','%f%*f%f')

T = 4×2 table
    Param1    Param3
    ______    ______

       0         0  
       1         3  
     100       400  
       7         9  

If the columns are not consecutive, and the file is a text file, then 'Format' is the only option to read non-consecutive columns. Otherwise, separate readtable calls for non-consecutive columns would be necessary. See the textscan section on formatSpec for details.

.

[Sarah Gilmore]

Hi @Star Strider,

You can can actually read non-consecutive columns with SelectedVariableNames. For example, here's how you could read Param1 and Param3 from the example file:

opts = detectImportOptions("F.csv");
opts.SelectedVariableNames = ["Param1" "Param3"];
T = readtable("F.csv", opts)

T = 4×2 table
    Param1    Param3
    ______    ______

       0         0  
       1         3  
     100       400  
       7         9  

I hope this helps.

Best,

Sarah

[Star Strider]

@Sarah Gilmore — It does.

Thank you!

Answer 2

[Stephen23]

First lets create a fake data file:

writetable(array2table(rand(1e5,50),'Variablenames',"V"+(1:50)),"test.csv")

Now lets try some different ways to import that data:

X = [5,23,42]; % the columns you want
tic
T0 = readtable("test.csv");
T0 = T0(:,X);
toc

Elapsed time is 1.741005 seconds.

tic
C  = cell(1,50);
C(:) = {'%*f'};
C(X) = {'%f'};
T1 = readtable("test.csv", "Format",[C{:}]);
toc

Elapsed time is 0.750706 seconds.

tic
opts = detectImportOptions("test.csv", "filetype","delimitedtext");
opts.SelectedVariableNames = "V"+X;
T2 = readtable("test.csv", opts);
toc

Elapsed time is 0.846019 seconds.

Checking:

isequal(T0,T1,T2)

ans = logical
   1