Datevector conversion from Excel to Matlab

I'm trying to convert an long Excel-format date array and the conversion I normally use is returning incorrect values. The date starts at 3/1/1942.

The format in excel is: 3/1/1942 1:00:00 AM

Below are my lines to import and convert to MATLAB Date Vector and Datenum. I've cleaned up the dataset to only include the date vector from excel. So, ignore the txt, and raw variables.

    %% Define file location, and load into the workspace.
    stafiles_WGA = 'datevec_raw.xlsx';

% Read the data.
    [num_WGA, txt_WGA, raw_WGA] = xlsread(stafiles_WGA); % Creates three variables. A number (double), raw (cell), and txt (cell).
% Note that the 'txt' and 'raw' variables contain the decriptions of the columns.    
    
% Rename the numerical matrix and remove the upper row that originally contained the data headers.
    sta_WGA = num_WGA;

    datevec_WGA = datetime(sta_WGA,'ConvertFrom','excel');
    date_num_WGA = datenum(datevec_WGA);

Any idea what I'm not catching?

Thanks

[Walter Roberson]

Is there a reason you are not using readtable() ?

[Eric Escoto]

Only that I’ve just used this method for all my scripts to date with no issues regarding the date conversions. If there’s a better way I’m fine to try it.

Answer 1

[Cris LaPierre]

I would read them in this way:

data = readtable('datevec_raw.xlsx','ReadVariableNames',false);

[Eric Escoto]

That works to read the table. I will attach a new file with all the variables I'm working with which may prove better to dissect.

%% Define file location, and load into the workspace.
    stafiles_WGA = 'datevec_raw_full.xlsx';
    
% Read the data.
    [num_WGA, txt_WGA, raw_WGA] = xlsread(stafiles_WGA); % Creates three variables. A number (double), raw (cell), and txt (cell).
% Note that the 'txt' and 'raw' variables contain the decriptions of the columns.    
    
% Rename the numerical matrix and remove the upper row that originally contained the data headers.
    sta_WGA = num_WGA(2:end, :);

% Change the fifth column (excel datenum value) to MATLAB datenum value.
    datevec_WGA = datetime(data,'ConvertFrom','excel');
    date_num_WGA = datenum(datevec_WGA);
    sta_WGA = [sta_WGA(:, 1:4) date_num_WGA sta_WGA(:,6:end)];

% Rename the 'txt' cell to 'header' and clear unused variables.
    header_WGA = txt_WGA(1,:);
    clearvars('num_WGA', 'txt_WGA', 'raw_WGA', 'date_num_WGA');
    
%% Use timetable for organizing records (of select variables).
    TT1_staWGA = timetable(datevec_WGA, sta_WGA(:,14), sta_WGA(:,17), sta_WGA(:,18), sta_WGA(:,15), sta_WGA(:,16), sta_WGA(:,7));  % Note: this is in hourly resolution.
    

Here's my script finishing off with a TT to organize and parse.

[Walter Roberson]

readtable() works fine on that file.

[Walter Roberson]

    datevec_WGA = datetime(data,'ConvertFrom','excel');

You have not assigned to data in your code.

    data = sta_WGA(:,4);
    

Why are you using datenum? You are using the (better) datevec in your timetable.

[Eric Escoto]

This is the error I get when trying to use this method.

    stafiles_WGA = readtable('datevec_raw_full.xlsx','ReadVariableNames',false);
    
% Read the data.
%    [num_WGA, txt_WGA, raw_WGA] = stafiles_WGA; % Creates three variables. A number (double), raw (cell), and txt (cell).
% Note that the 'txt' and 'raw' variables contain the decriptions of the columns.   
    
% Rename the numerical matrix and remove the upper row that originally contained the data headers.
    sta_WGA = stafiles_WGA(:, 5);

% Change the fifth column (excel datenum value) to MATLAB datenum value.
    datevec_WGA = datetime(sta_WGA,'ConvertFrom','excel');
    date_num_WGA = datenum(datevec_WGA);
    sta_WGA = [sta_WGA(:, 1:4) date_num_WGA sta_WGA(:,6:end)];

% Rename the 'txt' cell to 'header' and clear unused variables.
    header_WGA = txt_WGA(1,:);
    clearvars('num_WGA', 'txt_WGA', 'raw_WGA', 'date_num_WGA');
    
    
Error using datetime (line 586)
Input data must be one numeric matrix when converting from a different date/time representation.

[Walter Roberson]

When I insert

    data = sta_WGA(:,4);

before the assignment to datevec_WGA then I have no problem what that step; I do not have a problem until

        TT1_staWGA = timetable(datevec_WGA, sta_WGA(:,14), sta_WGA(:,17), sta_WGA(:,18), sta_WGA(:,15), sta_WGA(:,16), sta_WGA(:,7));  % Note: this is in hourly resolution.

which tries to use more than the 15 columns that exist in sta_WGA

At the point that you have the error, what shows up for class(data) and size(data) ?

[Eric Escoto]

I load the file

    stafiles_WGA = readtable('datevec_raw_full.xlsx','ReadVariableNames',false);
    

pull that column with the dates

    sta_WGA = stafiles_WGA(:, 5);

and try to convert

    datevec_WGA = datetime(sta_WGA,'ConvertFrom','excel');

I still produce this error message

    Error using datetime (line 586)
    Input data must be one numeric matrix when converting from a different date/time representation.

I see wha the issue for @Walter Roberson is. There is a new file attached above that includes more than only the first file with rows of dates.

Oh also I see that you are pulling from the 4th column. the dates are in the 5th column.

[Walter Roberson]

When you readtable(), the stafiles-WGA.DATE column (5th column) is already in datetime format and does not need to be converted.

There is a new file attached above that includes more than only the first file with rows of dates.

I used that file in my testing. It has 48 data rows in it, plus a header row.

Oh also I see that you are pulling from the 4th column. the dates are in the 5th column.

No, after you do the xlsread() you are pulling the numeric values from the first output of xlsread(), num_WGA . The first column of input is not numeric, so attempting to convert it to numeric internally gives a column of all nan. Then, when xlsread() is producing the num (numeric) output, it searches for the first and last row and columns that have something that is not a NaN, and it trims out anything outside of that. The first row of the input file is all text, so str2double() of it converts to all NaN, so the first row of the file is trimmed out of the num results. The first column of the file is all non-numeric, so str2double() of it converts to all NaN, so the first column of the file is trimmed out of the num results.

As a result, what was column 5 of the input shows up as column 4 of num_WGA, with the original column 1 having been removed as all nan.

Removing a row or column as all-NaN is only done around the edges, never in the middle.

Because of the way this happens, in order to be sure you are getting the columns you expect, if there is any possibility that a leading column might be all text but it just might happen to have an entry that looks like a number, then you cannot be sure that the entire column was removed or not. You have to refer back to the "raw" output to be sure you get the correct column, if a leading column might contain user input that might look like a number. Or consider for example that most hexadecimal would have characters that cannot form decimal numbers, but there might happen to be a leading row that contains '0000' and that looks like a decimal number to xlsread() so suddenly and accidentally that column gets retained as being numeric...

These kinds of problems are why I avoid xlsread().

readtable() does not have these issues. It can have different issues, such as if users might write in notes in numeric columns, like "power failure" where a number was expected, but readtable() will not chop out leading rows or columns.

[Eric Escoto]

Hmm, this is not making sense for me. I cannot see where the lines are wrong. I keep getting an error after the steps taken above (in my last post).

[Eric Escoto]

Ah, looks like I just got it with the following lines...

%% Define file location, and load into the workspace.
    stafiles_WGA = readtable('datevec_raw_full.xlsx','ReadVariableNames',false);
    
% Pulled the datetime to convert Excel format to MATLAB format.
    sta_WGA = stafiles_WGA(:, 5);

% Change the excel datenum value to MATLAB datenum value.
    datevec_WGA = datevec(sta_WGA.Var5);    % Create a datevector.
    date_num_WGA = datenum(datevec_WGA);    % Create the datenum (MATLAB format).
    date_num_WGA = array2table(date_num_WGA);   % Convert array to table.
    sta_WGA = [stafiles_WGA(:, 1:4) date_num_WGA stafiles_WGA(:,6:end)];    % Combine matrices to one final table.

This created a final table for me with all the original columns present. However, the header is still gone. How can I retireve that?

Thanks!

[Cris LaPierre]

The original file you shared didn't have headers. Now that you do, you can simply use:

stafiles_WGA = readtable('datevec_raw_full.xlsx');

The variable names will the the column headers.

Incidentally, you can accomplish all this with 2 lines of code:

%% Define file location, and load into the workspace.
stafiles_WGA = readtable('datevec_raw_full.xlsx');
stafiles_WGA.DATE = datenum(stafiles_WGA.DATE);

I do not understand why you'd want to convert back to a serial date, but I don't have to :)

[Eric Escoto]

Hmm, so I'm doing that and the variable names do not populate as the column headers?

>> stafiles_WGA.Properties

ans = 

  TableProperties with properties:

             Description: ''
                UserData: []
          DimensionNames: {'Row'  'Variables'}
           VariableNames: {1×19 cell}
    VariableDescriptions: {}
           VariableUnits: {}
      VariableContinuity: []
                RowNames: {}
        CustomProperties: No custom properties are set.
      Use addprop and rmprop to modify CustomProperties.

Ignore the name of the file, its just the complete file I have versus the shortened one I've provided. The variable names are still located in the first row of the table.

BTW, I've started a new question that is linked to this data parsing, https://www.mathworks.com/matlabcentral/answers/597400-convert-variable-to-numeric-in-timetable

and regarding the TT that was created. Perhaps this issue is another simple repair.

[Cris LaPierre]

Where are you expecting to see them? As I said, the coumn headers become the VariableNames (e.g. stafiles_WGA.DATE).

stafiles_WGA = 
     Station_Name     ELEVATION    LATITUDE    LONGITUDE       DATE       HOURLYVISIBILITY    AIRTEMPC    HOURLYDRYBULBTEMPF    HOURLYDRYBULBTEMPC    HOURLYWETBULBTEMPF    HOURLYWETBULBTEMPC    HOURLYDewPointTempF    HOURLYDewPointTempC    HOURLYRelativeHumidity    HOURLYWindSpeed    HOURLYWindDirection    HOURLYPrecip_in    HOURLYPrecip_mm    HOURLYAltimeterSetting
    ______________    _________    ________    _________    __________    ________________    ________    __________________    __________________    __________________    __________________    ___________________    ___________________    ______________________    _______________    ___________________    _______________    _______________    ______________________

    {'WBAN:23104'}      421.2        33.3       -111.67     7.0936e+05         99.42          {'NAN'}             39                    4.1                {'NAN'}               {'NAN'}                  37                       3                      93                     1                   360                {'NAN'}            {'NAN'}               {'NAN'}        
    {'WBAN:23104'}      421.2        33.3       -111.67     7.0936e+05         99.42          {'NAN'}             37                      3                {'NAN'}               {'NAN'}                  35                     1.9                      93                     0                     0                {'NAN'}            {'NAN'}               {'NAN'}        
    {'WBAN:23104'}      421.2        33.3       -111.67     7.0936e+05         99.42          {'NAN'}             36                    2.4                {'NAN'}               {'NAN'}                  34                     1.3                      93                     0                     0                {'NAN'}            {'NAN'}               {'NAN'}        
    {'WBAN:23104'}      421.2        33.3       -111.67     7.0936e+05         99.42          {'NAN'}             36                    2.4                {'NAN'}               {'NAN'}                  34                     1.3                      93                     0                     0                {'NAN'}            {'NAN'}               {'NAN'}        
    {'WBAN:23104'}      421.2        33.3       -111.67     7.0936e+05         99.42          {'NAN'}             35                    1.9                {'NAN'}               {'NAN'}                  33                     0.8                      93                     0                     0                {'NAN'}            {'NAN'}               {'NAN'}        
    {'WBAN:23104'}      421.2        33.3       -111.67     7.0936e+05         99.42          {'NAN'}             34                    1.3                {'NAN'}               {'NAN'}                  32                     0.2                      92                     0                     0                {'NAN'}            {'NAN'}               {'NAN'}        
    {'WBAN:23104'}      421.2        33.3       -111.67     7.0936e+05         99.42          {'NAN'}             34                    1.3                {'NAN'}               {'NAN'}                  32                     0.2                      92                     0                     0                {'NAN'}            {'NAN'}               {'NAN'}        
    {'WBAN:23104'}      421.2        33.3       -111.67     7.0936e+05         99.42          {'NAN'}             42                    5.8                {'NAN'}               {'NAN'}                  35                     1.9                      76                     0                     0                {'NAN'}            {'NAN'}               {'NAN'}        
    {'WBAN:23104'}      421.2        33.3       -111.67     7.0936e+05         99.42          {'NAN'}             51                   10.7                {'NAN'}               {'NAN'}                  39                     4.1                      64                     0                     0                {'NAN'}            {'NAN'}               {'NAN'}        
    {'WBAN:23104'}      421.2        33.3       -111.67     7.0936e+05         99.42          {'NAN'}             54                   12.4                {'NAN'}               {'NAN'}                  38                     3.5                      55                     0                     0                {'NAN'}            {'NAN'}               {'NAN'}        
...

To see the property values, use the following code:

stafiles_WGA.Properties.VariableNames

ans = 1×19 cell    
'Station_Name'    'ELEVATION'    'LATITUDE'    'LONGITUDE'    'DATE'       'HOURLYVISIBILITY'    'AIRTEMPC'    'HOURLYDRYBULBTEMPF'    'HOURLYDRYBULBTEMPC'    'HOURLYWETBULBTEMPF'    'HOURLYWETBULBTEMPC'    'HOURLYDewPointTempF'    'HOURLYDewPointTempC'    'HOURLYRelativeHumidity'    'HOURLYWindSpeed'    'HOURLYWindDirection'    'HOURLYPrecip_in'    'HOURLYPrecip_mm'    'HOURLYAltimeterSetting'    

[Eric Escoto]

Oh, I found the issue way back when I used this line that was provided.

data = readtable('datevec_raw.xlsx','ReadVariableNames',false);

I just changed it and all is well.

stafiles_WGA = readtable('WGA_stationdata_corrected.xlsx','ReadVariableNames',true);

Thanks!

[Cris LaPierre]

Yup, or drop the "ReadVariableNames" setting completely, like I showed in my previous response (true is the default setting, so doesn't need to be set explicitely).