Read in specific range of large .csv

I have very large .csv files that I am trying to work with, e.g. 7000 * 72000.

In each file the first column is a time vector. By saving these time vectors in separate files, I can load them in, get the row range of the dates of interest, and then use that to read in the rows of interest from the larger .csv?

However, I can't figure out how to apply this last step. Here is what I have so far...

%get time period of interest
startdate=datetime(2019,08,20); 
enddate=datetime(2019,09,10);
timeperiod=datenum(startdate:enddate); 
timeperiod=timeperiod';

%load in time vector
tvec_folder=('H:\SoundTrap\Boats\PSD Output\PSD_tvec');
tvecfile1=('TVEC_002Tiritiri_5280_PSD_1sHammingWindow_50%Overlap_2min_output.csv');
PSD_tvec=readtable(fullfile(tvec_folder,tvecfile1)); %read tvec and get times
PSD_tvec_t=PSD_tvec.Var1;

%get row range of interest
idx=PSD_tvec_t>timeperiod(1) & PSD_tvec_t<timeperiod(end); %find rows in tvec 
%which correspond to date range of interest
x=find(idx(:,1)>0); %get row numbers for reading in PSD

PSDfolder=('H:\SoundTrap\Boats\PSD Output\Duty cycle data'); %folder where PSD output files are
PSDfile1=('002Tiritiri_5280_PSD_1sHammingWindow_50%Overlap_2min_output.csv');
%PSDfile1=readtable(fullfile(PSDfolder,PSDfile1)); %read in PSD file

How can I select a range of interest as I read the .csv?

In addition to selecting specific rows, I could also cut the data down by selecting different columns. I have tried that this way:

opts.SelectedVariableNames(2:24000)
T=readtable(fullfile(PSDfolder,PSDfile1),opts);

...but for some reason, whilst this does select the desired column range, it doesn't read the full number of rows in the file and there are no error messages.

Alternative ways of solving the problem would be equally appreciated. I need to read in these large files but since it is time consuming and I don't always need all of the data, I am looking to be more efficient. Thanks

[Mathieu NOE]

hello welcome back Louise !

for the second csv file , have you tried with csvread and specifie the range ?

csvread Read a comma separated value file.

M = csvread('FILENAME') reads a comma separated value formatted file

FILENAME. The result is returned in M. The file can only contain

numeric values.

M = csvread('FILENAME',R,C) reads data from the comma separated value

formatted file starting at row R and column C. R and C are zero-

based so that R=0 and C=0 specifies the first value in the file.

M = csvread('FILENAME',R,C,RNG) reads only the range specified

by RNG = [R1 C1 R2 C2] where (R1,C1) is the upper-left corner of

the data to be read and (R2,C2) is the lower-right corner. RNG

can also be specified using spreadsheet notation as in RNG = 'A1..B7'.

[Louise Wilson]

Hi Mathieu,

No I hadn't tried this. What I want to do is read specific rows, not necessarily a range of rows as there could be rows inside the range that are not of interest, is that possible?

%Input variables
startdate=datetime(2019,08,20);
enddate=datetime(2019,09,10);
tvec_folder=('H:\SoundTrap\Boats\PSD Output\PSD_tvec');
tvecfile1=('TVEC_002Tiritiri_5280_PSD_1sHammingWindow_50%Overlap_2min_output.csv');
PSDfolder=('H:\SoundTrap\Boats\PSD Output\Duty cycle data'); %folder where PSD output files are
PSDfile1=('002Tiritiri_5280_PSD_1sHammingWindow_50%Overlap_2min_output.csv');


timeperiod=datenum(startdate:enddate); %get time period of interest
timeperiod=timeperiod';

PSD_tvec=readtable(fullfile(tvec_folder,tvecfile1)); %read tvec 
PSD_tvec_t=PSD_tvec.Var1; %get times
idx=PSD_tvec_t>timeperiod(1) & PSD_tvec_t<timeperiod(end); %find rows in tvec which correspond to date range of interest
x=find(idx(:,1)>0); %get row numbers for reading in PSD

PSD=csvread(fullfile(PSDfolder,PSDfile1),x); %to specify rows, doesn't work

[Louise Wilson]

Hi again Mathieu,

I can get these to work:

%works to select first row number 
PSD=csvread(fullfile(PSDfolder,PSDfile1),4681,1);

%works to select specific frequency range
opts=detectImportOptions(fullfile(PSDfolder,PSDfile1));
opts.SelectedVariableNames=opts.SelectedVariableNames(1:24000); %only read freq range of interest
PSDfile1=readtable(fullfile(PSDfolder,PSDfile1),opts); %read in PSD file

but I would like to be able to specify the start and end of the columns and rows in one command.

I can try this (which would be perfect if it worked):

PSD=readmatrix(fullfile(PSDfolder,PSDfile1),'Range',[4681 1 5760 24000]);

but I get the error:

Error using readmatrix (line 149)

Unable to determine range. Range must be a named range in the sheet or a single cell within

'XFD1048576'.

[Louise Wilson]

It doesn't matter what range I try, even if I just try to get the first row it doesn't work.

Answer 1

[Raunak Gupta]

Hi,

From the question I understand that you want to import only a chunk of ‘.csv’ file for analyzing. The readmatrix with ‘Range’ should return the mentioned range of values for you. The only crux here is the number of columns that you want to import is greater than what a normal csv file can be displayed with in Excel, which is XFD and that corresponds to 16384 columns. Since you are using columns from 1 to 24000 the specified error is thrown.

So, if you can somehow store the transpose of the original matrix to the csv file that will resolve the current error as ranges for row and columns will reverse and fall into the limits.

If that is not possible you can use tall array to import the whole file but not in memory. And then you can choose the size using logical indexing and then finally get the desired matrix using gather.

Hope this helps!

[Louise Wilson]

Thank you Raunak. This helps a lot. I am using this successfully at the moment:

tic
PSD1=readmatrix(fullfile(PSDfolder,PSDfile1),'Range',rowrange1); %works
PSD1_t=datetime(PSD1(:,1),'ConvertFrom','datenum');
toc

I can transpose the file after reading it in as above, and this seems to work fine? Is it the case that the issue you mention is purely a display issue, that the data is still there?

[Raunak Gupta]

Hi,

This the limit on column number is basically due to display limit in Excel, since .csv files follows the same methodology it is expected that number of columns to not exceed 16384. Number of rows can be a big number since usually it represent the observations so it can go upto 1048576, whereas column represent each feature for a specific observation.