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);

Answer 1

[Stephen23]

"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

[OcDrive]

Thanks Stephen

It shows up as "" in my matlab, I'm not sure why.

How did the below work for you?

Unrecognized field name "data".

Error in LoadExcel (line 14)

T = vertcat(S.data);

[Cris LaPierre]

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

[OcDrive]

Thanks Cris, that's quite straightforward!

[Stephen23]

"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]

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]

+1 to @Voss for figuring out what the right InputFormat syntax was. The one combination I didn't try!

[OcDrive]

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

Answer 2

[Voss]

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