Excel to timetable, problem with datetime
Show older comments
Hello
The code below doesn't seem to turn my dates into timetable times (it returns NaT). Can anyone see the reason? I'm attaching the data.
Is there an option to get rid of NaN lines and simply leave them empty in the timetable? As a line of space.
Thanks for any assistance.
sheetnames = {'189x', '190x', '191x', '192x', '193x', '194x', '195x', '196x', '197x', '198x', '199x', '200x'};
allTablesCombined = {};
for i = 1:numel(sheetnames)
filename = sheetnames{i};
[~, sheetNames] = xlsfinfo(filename);
% Read each sheet into a cell array of tables
allTables = cell(1, numel(sheetNames));
for j = 1:numel(sheetNames)
currentSheet = sheetNames{j};
allTables{j} = readtable(filename, 'Sheet', currentSheet);
end
% Vertically combine
allTablesCombined = [allTablesCombined, allTables];
end
% Combine all tables into a single table
finalTable = vertcat(allTablesCombined{:});
dateColumn = datetime(finalTable{:, 1}, 'InputFormat', '""dd-MMM-yyyy""');
timeTable = table2timetable(finalTable(:, 2:end), 'RowTimes', dateColumn);
Accepted Answer
"Can anyone see the reason?"
The date format you are attempting to use does not match the dates given in the file text: there are no double quotes in the date text. It appears that the existing single quotes (which are in the date text) are not liked by DATETIME, so we can trim them before converting.
unzip("Excel Data.zip")
P = "."; % absolute or relative path to where the files are saved.
S = dir(fullfile(P,"*x.xlsx"));
for ii = 1:numel(S)
F = fullfile(P,S(ii).name);
N = sheetnames(F);
C = cell(size(N));
for jj = 1:numel(N)
C{jj} = readtable(F, 'Sheet',N(jj));
end
S(ii).data = vertcat(C{:});
end
T = vertcat(S.data);
T = rmmissing(T)
D = datetime(regexprep(T.Var1,"'",""), "InputFormat","dd-MMM-yyyy");
TT = table2timetable(T(:,2:end), 'RowTimes',D)
7 Comments
OcDrive
on 3 Feb 2024
Cris LaPierre
on 3 Feb 2024
Edited: Cris LaPierre
on 3 Feb 2024
Our approaches were so similar, I'm adding mine as a comment instead of a separate answer. The main difference is I used a spreadsheetDatastore to avoid looping through the files and sheets manually.
unzip("Excel Data.zip")
ssds = spreadsheetDatastore("*x.xlsx","NumHeaderLines",0,"ReadVariableNames",false);
finalTable = readall(ssds);
% remove empty lines
finalTable = rmmissing(finalTable);
% Convert date strings to datetimes
finalTable.Var1 = datetime(finalTable.Var1,"InputFormat","''dd-MMM-yyyy''");
% convert finalTable to a timeTable
TT = table2timetable(finalTable)
OcDrive
on 3 Feb 2024
"It shows up as "" in my matlab, I'm not sure why."
I am taking a closer look at those spreadsheets, perhaps something in there.
"How did the below work for you? Unrecognized field name "data"."
You will get that error when DIR does not find any files matching the provided name (because then the FOR loop iterates zero times and so the DATA field is never defined).
This will occur when the files are saved in another location than the one you gave DIR, or the names are different to the name you provided, etc. The solution is to provide the correct path and the correct name to DIR.
@Cris LaPierre: should the TIMETABLE rows be headed "Time" ? The header "Var1" seems odd.
Cris LaPierre
on 3 Feb 2024
Just a function of how you create the timetable. The first column of a timetable is always the Time column, but when converting a table, it preserves the time variable name if the time information is already in your table (Var1 here). You can see some more examples of this here.
More to the point, it would be a good idea to give each column a meaningful variable name. Not knowing what those would be, here's at least the syntax to use. For simplicity, this is best done on the table instead of the timetable.
finalTable.Properties.VariableNames = ["Date","Val1","Val2","Val3"];
Cris LaPierre
on 3 Feb 2024
Edited: Cris LaPierre
on 3 Feb 2024
+1 to @Voss for figuring out what the right InputFormat syntax was. The one combination I didn't try!
OcDrive
on 3 Feb 2024
More Answers (1)
Change the InputFormat to "''dd-MMM-yyyy''" to match what's in the files.
unzip('Excel Data.zip')
sheetnames = {'189x', '190x', '191x', '192x', '193x', '194x', '195x', '196x', '197x', '198x', '199x', '200x'};
allTablesCombined = {};
for i = 1:numel(sheetnames)
filename = sheetnames{i};
[~, sheetNames] = xlsfinfo(filename);
% Read each sheet into a cell array of tables
allTables = cell(1, numel(sheetNames));
for j = 1:numel(sheetNames)
currentSheet = sheetNames{j};
allTables{j} = readtable(filename, 'Sheet', currentSheet);
end
% Vertically combine
allTablesCombined = [allTablesCombined, allTables];
end
% Combine all tables into a single table
finalTable = vertcat(allTablesCombined{:});
% dateColumn = datetime(finalTable{:, 1}, 'InputFormat', '""dd-MMM-yyyy""');
dateColumn = datetime(finalTable{:, 1}, 'InputFormat', "''dd-MMM-yyyy''");
timeTable = table2timetable(finalTable(:, 2:end), 'RowTimes', dateColumn)
Categories
Find more on Tables in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
