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

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

Answer 1

[Marc Servagent]

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.

[Dave B]

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'?

Answer 2

[Benjamin Kraus]

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?

Answer 3

[Dave B]

@Marc Servagent

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'

Answer 4

[Marc Servagent]

Yes you right understood my issue, I had not found the appropriate syntax. Thansk!