datevec text format problems

2016 6 24
1 个回答
回答已采纳
更新时间：2016 8 3
3 次查看（30 天）

0 个投票

I am trying to read in time series from an excel spreadsheet. This contains UK format date & time in the first column.
I load this using the command
[data,txt,raw] = xlsread(filename, 'Data', 'A12:AU1451');
The date column appears as a cell array in the txt cell array, which shows the following values. Note that the midnight value doesn't contain a time part:
My problem is this - if I then convert this array using datevec:
Tm = datevec(txt);
The datevectors swap day and month around, because they assume US-style dates. If I use the format string:
Tm = datevec(txt,'dd/mm/yyyy HH:MM:SS');
then datevec throws an error, because the midnight date doesn't have its time values.
Error using dtstr2dtvecmx
Failed on converting date string to date number.
Error in datevec (line 117)
y = dtstr2dtvecmx(t,icu_dtformat);
So how can I get a correct datevec conversion in this situation? I do need both the date and time values. I'm loading several hundred spreadsheets, and it's not practical to modify these at source.
Thanks, John

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

 采纳的回答

dpb
dpb 2016-6-24

1 个投票

Not sure if there's a way to force the read or not but a fixup could be
L=length('01/01/0000 00:00:00'); % saves me counting chars.. :)
ix=cellfun(@length,txt)<L;
ds(ix)=strcat(ds(ix),' 00:00:00')
then do the conversion.
Alternatively, try the '%D' format string and the new(er) datetime object instead of date numbers; maybe it's more forgiving; I don't know and don't have the release to be able to test.

3 个评论
显示 1更早的评论 隐藏 1更早的评论

John Wood
John Wood 2016-7-1
编辑：John Wood 2016-7-1
Thanks dpb I had been hoping there might be a rather less fussy way of importing date & time strings from Excel, but in the end I've had to go with your solution and go through correcting every incorrectly formatted entry. A cleaner way of doing this would be to force xlsread() to return excel serial numbers, but that option doesn't seem to be on offer. Cheers, John
dpb
dpb 2016-7-1
That's getting into the Excel side more than Matlab methinks, but if you change the format of the value to numeric instead of date it doesn't "take" and read as numeric? Of course, that's munging on the spreadsheet and is also a kluge as well...
Peter Perkins
Peter Perkins 2016-8-3
John, both points well-taken. If you have access to R2016b, you'll find better support for importing dates from Excel, via readtable. Prior to R2016b, you should be able to use basic mode to read excel serial date numbers. You can convert those to datetime using
datetime(dates,'ConvertFrom','Excel')
Hope this helps.

请先登录，再进行评论。

更多回答（0 个）

类别

在 帮助中心 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by