Excel to timetable, problem with datetime

Question

OcDrive 2024-2-3

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2077866-excel-to-timetable-problem-with-datetime

编辑： Cris LaPierre 2024-2-3

采纳的回答： Stephen23

Excel Data.zip

在 MATLAB Online 中打开

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 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Stephen23 2024-2-3

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2077866-excel-to-timetable-problem-with-datetime#answer_1402301

编辑：Stephen23 2024-2-3

在 MATLAB Online 中打开

Excel Data.zip

"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)
T = 5806×4 table
          Var1           Var2    Var3    Var4 
    _________________    ____    ____    _____

    {''14-Jan-1882''}     2       3      -3001
    {''14-Jan-1882''}     2       4      -4401
    {''22-Jan-1882''}     2       4      -3102
    {''27-Jan-1882''}     2       4      -2502
    {''01-Feb-1882''}     2       4      -2402
    {''06-Feb-1882''}     2       1      -2596
    {''04-Mar-1882''}     2       4      -2701
    {''10-Mar-1882''}     2       4      -2401
    {''20-Mar-1882''}     2       3      -2501
    {''02-Jun-1882''}     2       3      -3002
    {''07-Oct-1882''}     2       2      -2401
    {''09-Dec-1882''}     2       1      -2802
    {''15-Jan-1882''}     4       3      -2500
    {''15-Jan-1882''}     4       4      -2499
    {''28-Jan-1882''}     4       3      -2498
    {''28-Jan-1882''}     4       4      -3198
D = datetime(regexprep(T.Var1,"'",""), "InputFormat","dd-MMM-yyyy");
TT = table2timetable(T(:,2:end), 'RowTimes',D)
TT = 5806×3 timetable
       Time        Var2    Var3    Var4 
    ___________    ____    ____    _____

    14-Jan-1882     2       3      -3001
    14-Jan-1882     2       4      -4401
    22-Jan-1882     2       4      -3102
    27-Jan-1882     2       4      -2502
    01-Feb-1882     2       4      -2402
    06-Feb-1882     2       1      -2596
    04-Mar-1882     2       4      -2701
    10-Mar-1882     2       4      -2401
    20-Mar-1882     2       3      -2501
    02-Jun-1882     2       3      -3002
    07-Oct-1882     2       2      -2401
    09-Dec-1882     2       1      -2802
    15-Jan-1882     4       3      -2500
    15-Jan-1882     4       4      -2499
    28-Jan-1882     4       3      -2498
    28-Jan-1882     4       4      -3198

7 个评论
显示 5更早的评论隐藏 5更早的评论

Cris LaPierre 2024-2-3

编辑：Cris LaPierre 2024-2-3

在 MATLAB Online 中打开

Excel Data.zip

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)
TT = 5806×3 timetable
       Var1        Var2    Var3    Var4 
    ___________    ____    ____    _____

    14-Jan-1882     2       3      -3001
    14-Jan-1882     2       4      -4401
    22-Jan-1882     2       4      -3102
    27-Jan-1882     2       4      -2502
    01-Feb-1882     2       4      -2402
    06-Feb-1882     2       1      -2596
    04-Mar-1882     2       4      -2701
    10-Mar-1882     2       4      -2401
    20-Mar-1882     2       3      -2501
    02-Jun-1882     2       3      -3002
    07-Oct-1882     2       2      -2401
    09-Dec-1882     2       1      -2802
    15-Jan-1882     4       3      -2500
    15-Jan-1882     4       4      -2499
    28-Jan-1882     4       3      -2498
    28-Jan-1882     4       4      -3198

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

Thanks for all the input, it's been really helpful

请先登录，再进行评论。

Answer 2

Voss 2024-2-3

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2077866-excel-to-timetable-problem-with-datetime#answer_1402321

在 MATLAB Online 中打开

Excel Data.zip

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)
timeTable = 6143×3 timetable
       Time        Var2    Var3    Var4 
    ___________    ____    ____    _____

    14-Jan-1882      2       3     -3001
    14-Jan-1882      2       4     -4401
    22-Jan-1882      2       4     -3102
    27-Jan-1882      2       4     -2502
    01-Feb-1882      2       4     -2402
    06-Feb-1882      2       1     -2596
    04-Mar-1882      2       4     -2701
    10-Mar-1882      2       4     -2401
    20-Mar-1882      2       3     -2501
    02-Jun-1882      2       3     -3002
    07-Oct-1882      2       2     -2401
    09-Dec-1882      2       1     -2802
    NaT            NaN     NaN       NaN
    15-Jan-1882      4       3     -2500
    15-Jan-1882      4       4     -2499
    28-Jan-1882      4       3     -2498