import large .csv file
3 views (last 30 days)
Show older comments
I have a large csv file I would like to import. It uses the "," as a delimiter, and the data is a mixed "datetime", "text" and "numerical". The complete file represents approx 1366 colums, and 4600 rows, of which the first row are the variable names.
For a smaller subset, I have created a script, which is able to read it correctly. However, I cannot specify all these properties for the entire file, as it is too big.
Any ideas on how to optimise this script to import the large .csv file?
for illustration, this is how my small subset looks:
Fault Code_date_time,Fault Code,Alert Code_date_time,Alert Code,position_date_time, Position(degrees)
2004-05-04 12:48:40.560000,02069 INPUT FAIL[1],2004-05-04 12:48:26.000000,0238 DETECTED[1],2004-05-04 12:48:35.440000,1.2307692307692308
opts = delimitedTextImportOptions("NumVariables", 6);
% Specify range and delimiter
opts.DataLines = [2, Inf];
opts.Delimiter = ",";
% Specify column names and types
opts.VariableNames = ["FaultCode_date_time", "FaultCode", "AlertCode_date_time", "AlertCode", "position_date_time", "Positiondegrees"];
opts.VariableTypes = ["datetime", "string", "datetime", "string", "datetime", "double"];
% Specify file level properties
opts.ExtraColumnsRule = "ignore";
opts.EmptyLineRule = "read";
% Specify variable properties
opts = setvaropts(opts, ["FaultCode", "AlertCode"], "WhitespaceRule", "preserve");
opts = setvaropts(opts, ["FaultCode", "AlertCode"], "EmptyFieldRule", "auto");
opts = setvaropts(opts, "FaultCode_date_time", "InputFormat", "yyyy-MM-dd HH:mm:ss.SSS");
opts = setvaropts(opts, "AlertCode_date_time", "InputFormat", "yyyy-MM-dd HH:mm:ss.SSS");
opts = setvaropts(opts, "position_date_time", "InputFormat", "yyyy-MM-dd HH:mm:ss.SSS");
% Import the data
trial = readtable("trial.csv", opts);
0 Comments
Answers (2)
Benjamin Thompson
on 14 Feb 2022
If there is some kind of pattern to the content of 1366 columns, you could write a loop to add information to opts.VariableNames and opts.VariableTypes until you have everything. I don't know if any built in limit on the number of columns that readtable can read in for you except for the memory on your system.
0 Comments
Seth Furman
on 28 Feb 2022
Take a look at detectImportOptions. This function will infer the import options, which can be then overwritten for individual variables.
opts = detectImportOptions("example.csv", "TextType", "string", "ExtraColumnsRule", "ignore", "EmptyLineRule", "read")
0 Comments
See Also
Categories
Find more on Text Files in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!