readtable reads numbers as dates

I am using the command:

 tbl1 = readtable(tmpNm,'sheet',sNameField,'ReadVariableNames',true,'ReadRowNames',false)

to read a table from a specific sheet inside an excel (.xlsx) workbook.

The sheet contains a first row of headers. Then 255 rows, each with a date in the first columns and 10,000 numbers in the remaining 10,000 columns.

readtable reads (more or less) correctly the first column as dates. Then it reads all the other columns as doubles (desired) except one: there is one column for which readtable decides the data must be dates and converts them to dates. Since I need to read multiple files and I have no way of knowing if readtable will read some column as datenum, I have no way of turning the column back to numbers.

I looked at the 'Format' parameters but (a) it is not supported when reading excel, and (b) how can I possibly specify the format 10,000 times in my function?

Thank you for reading and for your help!

[Walter Roberson]

Could you attach an excerpt of the .xlsx for testing?

Also are you using MS Windows with Excel installed?

[Thanassis Tjavaras]

Thank you, Walter. Yes, I am using MS windows with Excel installed. I am attaching one file that I am trying to read. I had to save the file as .xls. THis resulted in removal of the vast majority of columns. I do not know if the problem I was seeing still occurs...

[Walter Roberson]

I am not observing the described problem with that .xls .

You can zip a .xlsx file and attach that (unless it is more than 5 megabytes compressed.)

[Peter Perkins]

Thanassis, like Walter, I can't reproduce what you are seeing with that xls. Also, it will help to know what version of MATLAB you are using, because time/date handling for spreadsheets in readtable has changed since it first shipped, to address ambiguities such as this. Ideally you would have access to R2016b or later, and if the problem still happens, use detectImportOptions .

Without seeing a file where the problem occurs, is it possible that the problem is in the Excel file itself, i.e. that that column is formatted as a date?

One last thing: I think you have a two-digit year issue in your file - dates that look like they ought to be 2030 are 1930. That appears to be in the xls, not an issue with readtable.

[Thanassis Tjavaras]

Thank you Peter, I will try to find a column in my original file that had the issue and upload it. Late last night I found a workaround (a bit ugly but works): after I read the file, I issue tbl = varfun(@datenum,tbl) this takes care of anything that might had been read in as a datenum

To answer your questions: I use 2017a; the format on all columns in my workbook is numeric (except for the 1st column).

Finally, you are right: my workbook has the wrong dates towards the end of column 1. It is due to the source of the data and excel. readtable reads correctly what it sees.

I will look use at detectImportOptions next...