Excel to timetable, problem with datetime
3 ビュー (過去 30 日間)
古いコメントを表示
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 件のコメント
採用された回答
Stephen23
2024 年 2 月 3 日
編集済み: Stephen23
2024 年 2 月 3 日
"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 件のコメント
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!
その他の回答 (1 件)
Voss
2024 年 2 月 3 日
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 件のコメント
参考
カテゴリ
Help Center および File Exchange で Data Type Conversion についてさらに検索
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!