Clear Filters
Clear Filters

How can I read rows from a CSV outside of datalines using readtable

27 views (last 30 days)
I am trying to read a CSV with a specific format that I can't change. I can read most of the data, but I lose some data from above start of the data lines (in the header that also has a units line). Below I have a section of the data I want to read:
; Bat_MaximumVoltage_x; Bat_MaximumVoltage_y; Bat_MinimumVoltage_x; Bat_MinimumVoltage_y
0; XY ; ; XY ;
1; ; ; ;
2; s ; V ; s ; V
3; 02.06.2022 05:03:33 ; ; 02.06.2022 05:03:33 ;
4; 02.06.2022 15:00:22 ; ; 02.06.2022 15:00:22 ;
5; X-Values ; Y-Values ; X-Values ; Y-Values
6; -2,51E-7 ; 3954 ; 1,11E-7 ; 3933
7; 0,240157917 ; 3953 ; 0,240157889 ; 3933
8; 0,478257917 ; 3953 ; 0,478257555 ; 3933
9; 0,71209725 ; 3953 ; 0,712096888 ; 3933
10; 1,020145332 ; 3953 ; 1,020144999 ; 3933
11; 1,258192585 ; 3953 ; 1,258192221 ; 3933
12; 1,491101584 ; 3953 ; 1,491101222 ; 3933
The csv file I am reading has 40 signal (_y) columns and 39 time (_x) columns. I have manually added the spacing you see above to improve readability.
I can import the data into a table using the following code:
opts = detectImportOptions(filename);
opts.DataLines = [8, Inf];
opts.Delimiter = ";";
opts.VariableUnitsLine = 4;
opts.VariableNames{1} = 'RowNum';
idxTimeVars = find(endsWith(opts.VariableNames,"_x"));
idxSignalVars = find(endsWith(opts.VariableNames,"_y"));
opts = setvaropts(opts, idxTimeVars, "Type","double");
opts = setvaropts(opts, idxTimeVars, "DecimalSeparator", ",");
opts = setvaropts(opts, idxSignalVars, "Type","double");
opts = setvaropts(opts, idxSignalVars, "DecimalSeparator", ",");
T = readtable(filename, opts);
But I want to create a timetable with this data, where the time vector is made of the start time (row number 3) added to the 'X-values', for example:
startTime = 02.06.2022 05:03:33; %datetime variable when read from data
timeVec = startTime + seconds(table.Bat_MaximumVoltage_x);
Then replace the double (type) '_x' columns with datetime (type) columns.
How can I store or access this data from the header of the file (above DataLines) while using readtable to import data below DataLines?
Do I just have to run textscan or readtable again to read those two lines separately?

Accepted Answer

dpb
dpb on 23 Sep 2022
"Do I just have to run textscan or readtable again to read those two lines separately?"
Yes, unfortunately readtable doesn't have the feature of textscan to make multiple reads within the same file without closing and reopening it. textscan does, of course; one can fixup another import object to cull out the header with something like
opt=detectImportOptions('ben.csv',"Range",[4 6],'ReadVariableNames',1,'Delimiter',';')
opt.DataLines=5:6;
opt=setvaropts(opt,{'s','s_1'},'Type','datetime');
tHdr=readtable('ben.csv',opt);
By default the datetime with ambiguous month/day format will interpret the above as 02-June so if these dates are actually February 6, then will need to set the import format option 'DateTimeFormat' to match.
  7 Comments
Ben
Ben on 26 Sep 2022
I might send a feature request too! It's unfortunate, but I will survive xD
Thank you both for your answers and support.
Ben
Ben on 26 Sep 2022
I was having some trouble getting the data into the table, as the data table has the time column as a duration and I can't replace duration data with datetime due to time mismatch. I was trying
T(:, idxTimeVars(ii)) = {timeVec};
where timeVec is a datetime vector. But MATLAB says timeVec needs to be a duration array.
I found that I could pass an anonymous function to convertvars which allows me to do what I want. See code below.
One thing not shown by the sample data is that not all columns have the same start time, which is why this is a loop that goes through each time column.
T = convertvars(T, idxTimeVars, "seconds");
for ii = 1:length(idxTimeVars)
startTime = tableStartEndTimes(1, idxTimeVars(ii)).Variables;
startTime.Format = 'dd.MM.yyyy HH:mm:ss.SSS';
addDatetime = @(x)(x + startTime);
T = convertvars(T, idxTimeVars(ii), addDatetime);
end
Mainly wanted to close this out with a working solution to the end. Also curious if I can do this without a loop, but it's not slow so unimportant.

Sign in to comment.

More Answers (0)

Products


Release

R2021b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!