Excel to timetable, problem with datetime
显示 更早的评论
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);
采纳的回答
"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 个评论
OcDrive
2024-2-3
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
2024-2-3
"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
2024-2-3
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
2024-2-3
编辑：Cris LaPierre
2024-2-3
+1 to @Voss for figuring out what the right InputFormat syntax was. The one combination I didn't try!
OcDrive
2024-2-3
更多回答（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)
类别
在 帮助中心 和 File Exchange 中查找有关 Tables 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
Translated by 
