MATLAB: How to read a CSV file with multiple datatype using a “readtable” function

MATLABnumericreadtablestring

I have a CSV file with multiple datatypes that I was trying to import using the "readtable" function. I have tried different options and ran into issues. For example, I have tried the two commands that they tried:

The below command replaces all string to 'NaN'

>> readtable('test.csv');

The following command converts all the numeric to string

>> readtable('test.csv','Format','auto');

How can I read this file correctly?

How to read a CSV file with multiple datatype using a "readtable" function?

Best Answer

The mixture of the numeric and string values cannot be parsed into 2 different data types when contained within the same variable. The best we can do is to import everything as string. That way none of the data is lost. Post-process the imported data into numeric and string arrays.

>> opts = detectImportOptions("test.xlsx");
>> opts.VariableTypes = "string";
>> R = readtable("test.xlsx", opts);

All the data is now imported as 'string' type. You can now run a 'for' loop over each individual value in the table and check whether it can be converted to 'double'. If yes, do the conversion and save to a numeric array. If not, save to a string array. This can also be optimized for space to perform an in-place replacement of the doubles.

numericVals = [];
stringVals = strings.empty(0,1);
for ii = 1 : size(R,1)
   if ~isnan(double(R.sedol(ii,1)))
      numericVals = [numericVals; double(R.sedol(ii,1))];
   else
      stringVals = [stringVals; R.sedol(ii,1)];
   end
end