How to get same xlsread "raw" output whithout using xlsread?

Hello,

xlsread is no more recommanded. So what is the correct solution to get exactly the same variable "raw" I was getting when using the following command :

[~, ~, raw] = xlsread('MyExcelFile.xls');

I have tried to use readcell function like this :

raw = readcell('MyExcelFile.xls', 'DateTimeType', 'text');

but that doesn't return me the same result :

• When I was using xlsread, date was returned as text with format 'dd/mm/yyyy', example : '01/01/2014'
• When I use readcell, date is returned as text with the exact same format as the cell in Excel 'dd-mmm-yyyy', example : '01-janv-2014'

I have to get the same result I got with xlsread, regardless of Excel language, regardless of Excel cells date format...

What is/are the command(s) I should use?

Other constraint : The command(s) do(es) not have to use/launch Excel.

Thanks!

Answer 1

[Walter Roberson]

mask = cellfun(@isdatetime, YourCell);
YourCell(mask) = cellfun(@(DT) datetime(DT, 'format', 'dd/MM/yyyy'), 'uniform', 0);

Now the datetime objects will be reformatted to the style you want.

If you want text, then

mask = cellfun(@isdatetime, YourCell);
YourCell(mask) = cellfun(@(DT) char(datetime(DT, 'format', 'dd/MM/yyyy')), 'uniform', 0);

[langrg]

Hi Walter,

raw = readcell('MyExcelFile.xls', 'DateTimeType', 'text');

returns text dates.

To have datetime with readcell, as you suggest, I need to use the following command :

raw = readcell('MyExcelFile.xls', 'DateTimeType', 'datetime');

But this command return the following error :

So your solution depends of "Excel language", I would like a solution regardless of Excel language, regardless of Excel cells date format.

For information, when I call :

raw = readtable('MyExcelFile.xls', 'DateTimeType', 'datetime');

I don't have any error message... I don't understand why the timeformat seems to be a problem for readcell but not for readtable...

Maybe there is a solution using table2cell and readtable, but I don't find how to use readtable so that all Excel cells are returned. It's seems that readtable systematically consider first row or column as header and never return them inside table (only header)...

[Walter Roberson]

Use the 'readvariablenames', false option to stop readtable() from reading the first row as a header.

[langrg]

But if I set this option to false, I won't have the first line of my excel sheet in the table.

I'm looking for command(s) that return(s) me all the cells, like [~, ~, raw] = xlsread('MyExcelFile.xls') was doing.

[langrg]

With

xlsread('MyExcelFile.xls')

I get :

With

table2cell(readtable('MyExcelFile.xls', 'DateTimeType', 'text', 'ReadVariableNames', false))

I get :

So the first line is not available with readtable...

[Walter Roberson]

You might be able to use a 'Range' option to specifically read that line. If not then use

filename = 'MyExcelFile.xls';
opt = detectImportOptions(filename, 'DataTimeType', 'text', 'ReadVariableNames', false);
opt.DataLines = [1 inf];
table2cell(readtable(filename, opt))

[langrg]

I don't have the option 'DataLines" available.

I think readtable is not the good solution because each column of a table must content same class data. My Excel file contains text and double in column 1 and 2.

readcell should be the right function, but I don't know why

readcell('MyExcelFile.xls', 'DateTimeType', 'datetime')

return an error while

readtable('MyExcelFile.xls', 'DateTimeType', 'datetime')

does not...