Problems with importing date and time from Excel to Matlab
显示 更早的评论
I have an Excel-sheet (xlsx) with the following as the first column:
Timestamp
2011-09-30 08:15
2011-09-30 08:23
2011-09-30 08:47 and so on
I have tried the three ways to import it into Matlab by using the functions xlsread and importdata and opening it with Ctrl-O in Matlab. The functions do not import the date and time column. With the last alternative Matlab opens an import windows, where it proposes the conversion of Excel dates to datenum and non-numeric to 0.0. It works well for all files except for the biggest one, about 180,000x18. It warns that it can be slow when importing more than 2 million cells. It takes a while and then the first column contains just 0. When I cut off the columns that I do not use by now (to 11 or less), I managed to get the file into the variable untitled.
How can I use the function xlsread to import the data? How can I import bigger files without having to cut them?
采纳的回答
Eric
2013-1-21
xlsread seems to work fine, at least with small files. That seems to contradict your problem description. I created a simple three-row XLSX file with your strings as cells A1:A3. The following works fine:
[temp, timestamps] = xlsread(filename, 'Sheet1', 'A1:A3');
You can then access elements of the cell array timestamps via
>> timestamps{1}
Alternatively, here's some COM code that will work at a lower level than xlsread(). This might help the problem with large files in case xlsread() indeed has some error.
%%Start COM interface
fname = 'c:\users\eashiel\documents\temp\datetest.xlsx';%Specify full path
assert(exist(fname,'file')==2,'%s does not exist.', fname)
xlsobj = actxserver('Excel.Application');
xlsobj.Visible = 1;
fileobj = xlsobj.Workbooks.Open(fname);
Sheet = fileobj.Worksheets.Item(1);%Assume data exist on the first worksheet
%%Specify first row of column
FirstRow = 'A1';%Enter cell coordinates of the top of the column of data
Range_FirstRow = Sheet.Range(FirstRow);
%%Find last row of column
xlDown = -4121;
Range_LastRow = Range_FirstRow.End(xlDown);
%%Create Range object for the entire range
Range_Full = Sheet.Range([Range_FirstRow.Address ':' Range_LastRow.Address]);
assert(Range_Full.Columns.Count == 1, '%d columns were provided. Only one is supported.', Range_Full.Columns.Count);
%%Read data
%Preallocate cell array of strings with a length of 14. See
% http://www.mathworks.com/matlabcentral/newsreader/view_thread/278968
data = cell(Range_Full.Rows.Count,1);
data(:) = {'12345678901234'};
%Loop over all cells of data
for ctr = 1:Range_Full.Rows.Count
data{ctr,1} = Range_Full.Cells.Item(ctr).Text;
end
%%Close Excel and release COM objects
xlsobj.Quit();
release(xlsobj);
release(fileobj);
release(Sheet);
release(Range_FirstRow);
release(Range_LastRow);
release(Range_Full);
Good luck,
Eric
更多回答（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 
