Excel to timetable, problem with datetime
5 views (last 30 days)
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);
0 Comments
Accepted Answer
Stephen23
on 3 Feb 2024
Edited: Stephen23
on 3 Feb 2024
"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
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!
More Answers (1)
Voss
on 3 Feb 2024
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)
0 Comments
See Also
Categories
Find more on Timetables 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!