Readtable / writematrix / Excel file - how to control the format of variables in the table ?

20 views (last 30 days)
I am designing an app which read and/or update an excel file.
When I use the function readtable to import an Excel file, I can not control the format of the variables inside the table.
Indeed, my code to import the table is:
T = readtable("example.xls", "TextType","string")
Also, I use the function writematrix to update the same excel file:
writematrix(var, "example.xls", "Range","C6")
var is string or double types, however, the table shows sometimes string or double type, whereas there should be only string type
I also use actxserver to insert new lines in the excel file, and then fill the new lines with writematrix
e = actxserver('Excel.Application');
rg = ws.Range(ILine);
rg.Insert(-4121);
e.ActiveWorkBook.Close(true)
Quit(e);
delete(e);
I suspect actxserver or writematrix to be the cause of changing the format of the variables via readtable but I am not sure despite my investigations.
Thanks for your help

Accepted Answer

Marc Servagent
Marc Servagent on 3 Aug 2021
Thanks a lot, indeed detectImportOptions and then set the variable class is a solution.
I did this:
opts.VariableTypes{3} = 'double';
opts.VariableTypes{6} = 'double';
opts.VariableTypes{7} = 'double';
opts.VariableTypes{12} = 'datetime';
But how to set the remaining variable of the table to string class ?
Indeed, now the cell class is applied to the remaining variables which I dont want.
  1 Comment
Dave B
Dave B on 3 Aug 2021
I'm not sure I understand the question, do you mean how to set multiple types at once? A few options (I think I like the last one the best?):
opts.VariableTypes(:) = {'string'};
opts.VariableTypes{3} = 'double';
opts.VariableTypes{6} = 'double';
opts.VariableTypes{7} = 'double';
opts.VariableTypes{12} = 'datetime';
Or:
opts.VariableTypes{3} = 'double';
opts.VariableTypes{6} = 'double';
opts.VariableTypes{7} = 'double';
opts.VariableTypes{12} = 'datetime';
TheRemainingTypes=setdiff(1:numel(opts.VariableTypes),[3 6 7 12]);
opts.VariableTypes(TheRemainingTypes) = {'string'}
Or:
opts.VariableTypes{3} = 'double';
opts.VariableTypes{6} = 'double';
opts.VariableTypes{7} = 'double';
opts.VariableTypes{12} = 'datetime';
strrep(opts.VariableTypes, 'char', 'string'); % I'm guessing the ones that are coming in as cells are marked as 'char'?

Sign in to comment.

More Answers (3)

Benjamin Kraus
Benjamin Kraus on 3 Aug 2021
The readtable command takes an optional options input which can be used to control how the XLS file is imported.
T = readtable(filename,opts)
You create the options input using the spreadsheetImportOptions command.
I also recommend trying the Import Tool, as it can help you to customize the options used when importing data from a spreadsheet.
It is hard to be more specific without details about the format of the XLS file. Can you attach a copy of example.xls to this question?

Dave B
Dave B on 3 Aug 2021
For the reading part, there's an example that's fairly similar to this on the readtable documentation page entitled "Detect and Use Import Options for Spreadsheet Files"
The basic strategy is:
  1. Use detectImportOptions to create the default options object. This will include various options, including the variable types that MATLAB will import to (in VariableTypes)
  2. Change VariableTypes to the types to what you want.
  3. Use the modified options when you import.
I tested this with a little experiment xlsx file, attached, and the following code:
opts = detectImportOptions('example.xlsx');
opts.VariableTypes{1} = 'uint8';
opts.VariableTypes{2} = 'string';
data=readtable('example.xlsx', opts);
class(data.val1)
ans = 'uint8'
class(data.val2)
ans = 'string'

Products


Release

R2021a

Community Treasure Hunt

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

Start Hunting!

Translated by