I have created a program that reads tables, both CSV and excel. The tables come in different formats, and the program helps the user reformat them to match the formatting of our database. However, I am having trouble reading excel files which have dates and times.
If I open the table in excel, it looks like this.
DATE EVENTNO TIME
30-Apr-2017 84 140158
30-Apr-2017 85 140201
30-Apr-2017 86 140206
30-Apr-2017 87 140211
30-Apr-2017 88 140216
30-Apr-2017 89 140221
30-Apr-2017 90 140226
30-Apr-2017 91 140231
I can look at the format of cells in excel and see that they are the time represents hmmss.
However, when I try to open it in MATLAB, it looks like this
>> opts = detectImportOptions(fname);>> preview(fname,opt)>> opts.VariableTypesans = 1×3 cell array {'datetime'} {'double'} {'double'}
'30-Apr-2017 10:01:57' 84 0.584694444444445
'30-Apr-2017 10:02:00' 85 0.584731481481482
'30-Apr-2017 10:02:05' 86 0.584789351851852
'30-Apr-2017 10:02:10' 87 0.584847222222222
'30-Apr-2017 10:02:15' 88 0.584906250000000
'30-Apr-2017 10:02:20' 89 0.584964120370370
'30-Apr-2017 10:02:25' 90 0.585021990740741
'30-Apr-2017 10:02:30' 91 0.585079861111111
The dates have some unexpected times added to them, and the times have values that only come into range if multiplied by 24. Even then it is not a proper time, and I'm not sure how to reconcile it with the date time.
There are actually a lot more columns in my actual tables, and my program does not know what order they will be in or what they will be named, so I cannot hard code a solution.
Is there anyway to automatically detect and fix excel datetime import errors? I prefer to have a single column labeled TIME containing a datetime object.
EDIT: sample file attached
Best Answer