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

4 次查看（过去 30 天）
Marc Servagent
Marc Servagent 2021-8-3
回答： Marc Servagent 2021-8-4
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

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

采纳的回答

Marc Servagent
Marc Servagent 2021-8-3
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 个评论
Dave B
Dave B 2021-8-3
编辑：Dave B 2021-8-3
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'?

请先登录，再进行评论。

更多回答（3 个）

Benjamin Kraus
Benjamin Kraus 2021-8-3
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 2021-8-3
Ran in:
@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'
Marc Servagent
Marc Servagent 2021-8-4
Yes you right understood my issue, I had not found the appropriate syntax. Thansk!

类别

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

标签

产品


版本

R2021a

Community Treasure Hunt

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

Start Hunting!

Translated by