import large .csv file

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);

Answer 1

[Benjamin Thompson]

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.

Answer 2

[Seth Furman]

Take a look at detectImportOptions. This function will infer the import options, which can be then overwritten for individual variables.

Create import options based on file content - MATLAB detectImportOptions (mathworks.com)

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