import large .csv file

3 次查看（过去 30 天）
Rose
Rose 2022-2-12
回答： Seth Furman 2022-2-28
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);

请先登录，再回答此问题。

回答（2 个）

Benjamin Thompson
Benjamin Thompson 2022-2-14
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 2022-2-28
Ran in:
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

类别

在 Help Center 和 File Exchange 中查找有关 Timetables 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by