Step-by-Step Guide to Import CSV Files with Mixed Data Types in MATLAB

Importing CSV files containing mixed data types (e.g., numbers, text, dates) requires careful handling to ensure data integrity. Below are three robust methods to achieve this, along with code examples and best practices.

Method 1: Use readtable (Simplest Approach)

The readtable function automatically detects data types and imports the CSV as a table.

Example CSV Structure (data.csv):

ID,Name,Age,JoinDate,Score
1,Alice,25,2023-01-15,89.5
2,Bob,NA,2022-11-30,92.3
3,Charlie,30,2021-05-10,NA

Code:

data = readtable('data.csv', 'TextType', 'string');

Key Features:

• Automatically parses numbers, strings, and dates.
• Converts missing values (NA) to NaN for numeric columns and <missing> for strings.
• Use 'TextType', 'string' to import text as strings instead of character arrays.

Limitations:

• May misclassify columns with mixed numeric/text data (e.g., 'Age' with NA).

Method 2: Use detectImportOptions for Custom Import

Refine the import process by specifying column data types and handling missing values.

Code:

% Create import options
opts = detectImportOptions('data.csv');

% Specify data types for columns
opts = setvartype(opts, {'ID', 'Age', 'Score'}, 'double');
opts = setvartype(opts, {'Name'}, 'string');
opts = setvartype(opts, {'JoinDate'}, 'datetime');

% Handle missing values (e.g., 'NA')
opts = setvaropts(opts, 'Age', 'TreatAsMissing', {'NA'});
opts = setvaropts(opts, 'Score', 'TreatAsMissing', {'NA'});

% Import the data
data = readtable('data.csv', opts);

Output:

ID    Name      Age    JoinDate     Score
___   ______    ___    _________    _____
1     "Alice"   25     15-Jan-2023  89.5
2     "Bob"     NaN    30-Nov-2022  92.3
3     "Charlie" 30     10-May-2021  NaN

Advantages:

• Explicit control over data types and missing values.
• Supports datetime parsing with format specifiers:
  matlab

  Copy

  opts = setvaropts(opts, 'JoinDate', 'InputFormat', 'yyyy-MM-dd');

Method 3: Use textscan for Low-Level Control

For non-standard CSV formats, use textscan with explicit format specifiers.

Code:

% Open the file
fid = fopen('data.csv', 'r');
header = fgetl(fid); % Skip header

% Define column formats (e.g., %f for double, %q for quoted strings)
formatSpec = '%f %q %f %{yyyy-MM-dd}D %f';
data = textscan(fid, formatSpec, 'Delimiter', ',', 'TreatAsEmpty', {'NA'}, 'HeaderLines', 0);

% Close the file
fclose(fid);

% Convert to a table
dataTable = table(data{1}, data{2}, data{3}, data{4}, data{5}, ...
    'VariableNames', {'ID', 'Name', 'Age', 'JoinDate', 'Score'});

Notes:

• %q handles quoted strings (e.g., "Name").
• %{yyyy-MM-dd}D parses dates in a specific format.
• Use 'TreatAsEmpty' to mark missing values.

Handling Large Files with datastore

For CSV files too large to load into memory, use datastore to process data in chunks:

ds = datastore('data.csv', 'TextType', 'string');
ds.SelectedVariableNames = {'ID', 'Name', 'Age', 'JoinDate', 'Score'};
ds.TreatAsMissing = {'NA'};

while hasdata(ds)
    chunk = read(ds);
    % Process each chunk
end

Troubleshooting Common Issues

1. Mixed Numeric/Text Columns:
   • Use readtable with 'TextType', 'string' and manually convert columns:
     matlab

     Copy

     data.Age = str2double(data.Age);

2. Date Parsing Errors:
   • Specify date formats explicitly:
     matlab

     Copy

     opts = setvaropts(opts, 'JoinDate', 'InputFormat', 'yyyy-MM-dd');

3. Headers with Special Characters:
   • Rename variables after import:
     matlab

     Copy

     data.Properties.VariableNames = {'ID', 'Full_Name', 'Age', 'Date', 'Score'};

Summary of Methods

By selecting the appropriate method, you can efficiently import CSV files with mixed data types while ensuring accuracy and performance.