You are now following this question
- You will see updates in your followed content feed.
- You may receive emails, depending on your communication preferences.
Datevector conversion from Excel to Matlab
2 views (last 30 days)
Show older comments
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
2 Comments
Walter Roberson
on 21 Sep 2020
Is there a reason you are not using readtable() ?
Eric Escoto
on 21 Sep 2020
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.
Accepted Answer
Cris LaPierre
on 21 Sep 2020
I would read them in this way:
data = readtable('datevec_raw.xlsx','ReadVariableNames',false);
14 Comments
Eric Escoto
on 21 Sep 2020
Edited: Eric Escoto
on 21 Sep 2020
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
on 21 Sep 2020
readtable() works fine on that file.
Walter Roberson
on 21 Sep 2020
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
on 21 Sep 2020
Edited: Eric Escoto
on 21 Sep 2020
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
on 21 Sep 2020
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
on 21 Sep 2020
Edited: Eric Escoto
on 21 Sep 2020
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
on 21 Sep 2020
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
on 21 Sep 2020
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
on 21 Sep 2020
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
on 21 Sep 2020
Edited: Cris LaPierre
on 21 Sep 2020
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
on 21 Sep 2020
Edited: Eric Escoto
on 21 Sep 2020
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
on 21 Sep 2020
Edited: Cris LaPierre
on 21 Sep 2020
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
on 21 Sep 2020
Edited: Eric Escoto
on 21 Sep 2020
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
on 21 Sep 2020
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).
More Answers (0)
See Also
Categories
Find more on Dates and Time in Help Center and File Exchange
Tags
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!An Error Occurred
Unable to complete the action because of changes made to the page. Reload the page to see its updated state.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list
How to Get Best Site Performance
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)
Asia Pacific
- Australia (English)
- India (English)
- New Zealand (English)
- 中国
- 日本Japanese (日本語)
- 한국Korean (한국어)