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?

Answer 1

[Eric]

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

[Per]

Thanks, Eric!

With these two outputs I get at least what I want from xlsread, which works well with my big file. Then I have to combine the results and convert the date and time to datenum with datenum(timestamps{i,1},'yyyy-mm-dd HH:MM:SS').