Time info is not read correctly by readtable

Question

Leon 2020-4-8

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/516436-time-info-is-not-read-correctly-by-readtable

回答： Divya Gaddipati 2020-4-13

test.xlsx

在 MATLAB Online 中打开

I'm using the below command to read my Excel files (see attached for an example):

A = readtable('test.xlsx');

I notice one interesting thing:

My time column is read as NaN, when they are formatted as 'time' (like 02:45:31 PM)within Excel.

The fix is pretty simple, I open the Excel file, right click and choose "Format cell" and choose "Number" and select their decimal places. Then, everything will be read properly.

Here is my question. I have a lot of such Excel files, is there a way I can fix my Matlab code to address this issue, instead of manually going through my time columns?

Thanks!

6 个评论
显示 4更早的评论隐藏 4更早的评论

J. Alex Lee 2020-4-9

test_.xlsx

Odd, readtable cannot read your 4th column at all, no matter what datatype is specified (inc. string and char). detectImportOptions decided it was a char, for me, and returned {0x0 char}s. Couldn't read in 2019b either.

But, when I copy-pasted the rows of your excel into a new excel file, readtable worked on the new file; detectImportOptions decided TIME_UTC was of type double.

I'm at my wit's end, maybe someone else can shed light on this situation. I attached the copy-pasted excel (matched the sheet name for good measure), and noticed the file sizes are different.

Walter Roberson 2020-4-9

My Excel 2011 for Mac says that the original file is invalid and will not open it.

请先登录，再进行评论。

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

Answer 1

Divya Gaddipati 2020-4-13

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/516436-time-info-is-not-read-correctly-by-readtable#answer_425767

在 MATLAB Online 中打开

Hi,

This is a known issue and our development team is working on it.

Until this issue is resolved, please use Microsoft Excel to save this file as an "Excel Workbook (.XLS)" instead. The readtable function should work as expected on the .XLS workbook.

>> A = readtable('test.xls');
>> opts = detectImportOptions('test.xls');
>> preview('test.xls',opts)
ans =
  8×4 table
    Year    Month    Day               TIME_UTC           
    ____    _____    ___    ______________________________
    2017      9      18     {'20:32:53.99999999999522700'}
    2017      9      18     {'20:35:53.99999999999458800'}
    2017      9      18     {'20:37:14.00000000000069775'}
    2017      9      18     {'20:38:40.99999999999510950'}
    2017      9      18     {'20:40:04.00000000000168875'}
    2017      9      18     {'20:41:28.00000000000522950'}
    2017      9      18     {'20:42:39.00000000000033900'}
    2017      9      18     {'20:43:45.00000000000106275'}
    