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!