Clear Filters
Clear Filters

import large .csv file

7 views (last 30 days)
Rose on 12 Feb 2022
Answered: Seth Furman on 28 Feb 2022
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);

Answers (2)

Benjamin Thompson
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.

Seth Furman
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")
opts =
DelimitedTextImportOptions with properties: Format Properties: Delimiter: {','} Whitespace: '\b\t ' LineEnding: {'\n' '\r' '\r\n'} CommentStyle: {} ConsecutiveDelimitersRule: 'split' LeadingDelimitersRule: 'keep' TrailingDelimitersRule: 'ignore' EmptyLineRule: 'read' Encoding: 'UTF-8' Replacement Properties: MissingRule: 'fill' ImportErrorRule: 'fill' ExtraColumnsRule: 'ignore' Variable Import Properties: Set types by name using setvartype VariableNames: {'FaultCode_date_time', 'FaultCode', 'AlertCode_date_time' ... and 3 more} VariableTypes: {'datetime', 'string', 'datetime' ... and 3 more} SelectedVariableNames: {'FaultCode_date_time', 'FaultCode', 'AlertCode_date_time' ... and 3 more} VariableOptions: Show all 6 VariableOptions Access VariableOptions sub-properties using setvaropts/getvaropts VariableNamingRule: 'modify' Location Properties: DataLines: [2 Inf] VariableNamesLine: 1 RowNamesColumn: 0 VariableUnitsLine: 0 VariableDescriptionsLine: 0 To display a preview of the table, use preview

Community Treasure Hunt

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

Start Hunting!