Problem to import data from Excel, using "Import Data" tool

Hello,

I'm want to import into MATLAB some data from an Excel file, but I have some problems. In order to simplify the issue I've created a Libro2.xlsx file with the first 6000 lines and the problematic date column. This file contains dates in A2:A6001, and "Import Data" tool works perfectly for it (using "Import Data" option of this tool to directly import the file Libro2.xlsx), but when I generate a script or a function and then I run it whith the same Excel file, it doesnt work. Obviously I need a script or a function to integrate this data load inside my actual code/app.

Using direct import data, I get this variable:

In order to check this variable:

>> datestr(Libro2.FECHA(2))

ans =

01-Oct-2015

>> datestr(Libro2.FECHA(20))

ans =

01-Oct-2015 00:43:38

When using an automaticly generated scrip, I get this code (with a diferent table name Libro2_):

%%Import data from spreadsheet
% Script for importing data from the following spreadsheet:
%
%    Workbook: Libro2.xlsx
%    Worksheet: eventosPCL
%
% To extend the code for use with different selected data or a different
% spreadsheet, generate a function instead of a script.

% Auto-generated by MATLAB on 2015/12/14 11:16:39

%%Import the data, extracting spreadsheet dates in Excel serial date format
[~, ~, raw, dates] = xlsread('Libro2.xlsx','eventosPCL','','',@convertSpreadsheetExcelDates);
raw(cellfun(@(x) ~isempty(x) && isnumeric(x) && isnan(x),raw)) = {''};
dates = dates(:,1);

%%Replace non-numeric cells with NaN
R = cellfun(@(x) ~isnumeric(x) && ~islogical(x),raw); % Find non-numeric cells
raw(R) = {NaN}; % Replace non-numeric cells
R = cellfun(@(x) ~isnumeric(x) && ~islogical(x),dates); % Find non-numeric cells
dates(R) = {NaN}; % Replace non-numeric Excel dates with NaN

%%Create table
Libro2_ = table;

%%Allocate imported array to column variable names
Libro2_.FECHA = datetime([dates{:,1}].', 'ConvertFrom', 'Excel', 'Format', 'MM/dd/yyyy');

% For code requiring serial dates (datenum) instead of datetime, uncomment
% the following line(s) below to return the imported dates as datenum(s).

% Libro2_.FECHA=datenum(Libro2.FECHA);

%%Clear temporary variables
clearvars FECHA raw dates R;

I want to highlight something. Date format in Excel file is not MM/dd/yyyy but dd/MM/yyyy. When a direct import is done, MATLAB recognize it perfectly (you can see 1/10/2015 in Excel file (1st October) and 10/01/2015 in MATLAB Workspace). I'm assuming last code line is only for establishing this format for MATLAB dates.

This is the variable Libro2_ I get when using generated script:

I have just discovered that when I import the first 1-5002 lines of Libro2.xlxs the script works perfectly!! Any idea?

Answer 1

[Walter Roberson]

"Date format in Excel file is not MM/dd/yyyy but dd/MM/yyyy."

That turns out not to be. The file has numeric data in that column, which you have formatted with a Custom format in Excel to show up as a date in dd-MM-yyyy h:mm format. The 'Format' parameter in the datetime() call has to do with the default format to display the converted data in MATLAB after converting it from Excel numeric date format (which is slightly different than MATLAB date format but close.)

I looked through your data in Excel and as reported by xlsread() on OS-X (which does not talk to Excel to do the reading), and I do not see anything odd. I do not happen to have a version of MATLAB new enough to use datetime() so I cannot test that import script with your data.