readtable, excel, date base
4 visualizzazioni (ultimi 30 giorni)
Mostra commenti meno recenti
How can I tell readtable what the appropriate excel base date is?
3 Commenti
Risposte (1)
Cris LaPierre
il 3 Mar 2021
Modificato: Cris LaPierre
il 3 Mar 2021
Perhaps with the DatetimeType name value pair. You can find details in the Name-Value Pair Arguments section of the documentation under Text and Spreadsheet files.
One of the options is exceldatenum.
Otherwise, you might consider using import options. You can use setvarops to set parameters for how datetimes are interpretted. See this example. The dateType property might provide you the setting you need.
3 Commenti
Cris LaPierre
il 3 Mar 2021
If the original question is still how to tell readtable what base date to use, then look into the dateType property I linked to above.
If the question is instead how to determine what date system the workbook is using, I'm afraid I don't know of a way to do that programmatically.
Cris LaPierre
il 3 Mar 2021
Modificato: Cris LaPierre
il 3 Mar 2021
Ok, I may have found a way (idea source). It relies on creating an activex connection to the workbook. Once you've done that, you can query the property. I first recorded a macro in Excel of me toggling the 1904 date system setting on then off. The macro contained the following
ActiveWorkbook.Date1904 = True
ActiveWorkbook.Date1904 = False
That gives me a hint at what property name I should try to query.
Looking on the forum, I found this post about how to create an ActiveX connection to an Excel Workbook in MATLAB. I modified the code from that post to the following.
% Specify file name
file = 'D:\Applications\MATLAB701\work\example.xls'; % This must be full path name
% Open Excel Automation server
Excel = actxserver('Excel.Application');
Workbooks = Excel.Workbooks;
% Open Excel file
Workbook=Workbooks.Open(file);
% Determine if 1904 date system was used
is1904 = Workbook.Date1904
% Close Excel and clean up
invoke(Excel,'Quit');
delete(Excel);
clear Excel;
The result is a logical value (1 or 0) for is1904 indicating whether the 1904 date system is used in the indicated workbook or not. That can be used to select which dateType is used for importing the data.
Just note that the workbook must not be open in Excel when this code runs.
Vedere anche
Categorie
Scopri di più su Spreadsheets in Help Center e File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!