Importing Dates as variable names in an table from Excel

Hello,

I'm trying to import data from Excel into MATLAB as a table, but I'm running into issues regarding variable labels.

The excel sheet is an automatically generated report with n rows representing various sites, and m columns. The first four columns give details of the site (location, site ID number, etc.) and then the rest of the columns represent dates from the time the tracking began to the day of the report. These columns are filled with 1s and 0s, ie 1 if I pinged that site that day and 0 if I didn't. I'd like to write this script in such a way that I can run the report and import it regardless of the date range I select for the report, so it needs to be very flexible.

Unfortunately, due to the variable difference between my pseudoboolean 1s and 0s and the date headers, when I import the data as a table, all my date columns assume a VarName5, VarName6, ...etc. variable name instead of the proper dates. This date information is important for the data manipulation I'm looking for. Does anyone know how to get the dates to come across as headers even if the variable type of the rest of each column is different?

Thanks!

Visual example of excel sheet:

Location IDNumber 3/1/2023 3/2/2023 3/3/2023 ...

LocationA ID_A 0 0 1

LocationB ID_B 1 1 1

LocationC ID_C 0 0 0

...

[Walter Roberson]

Are you using 'VariableNamingRule', 'preserve' ?

[Josh Halff]

I've tried that, and had little luck. I've also realized that I made some significant mistakes that will prevent this from working as the dataset expands and I re-run...

I greatly appreciate your response. I'm going to try to rework, will update.

Answer 1

[Sulaymon Eshkabilov]

Here is one option that partially solves your exercise and explains it can't be solved completely:

T_VARS = readtable('VAR_SAVE.xlsx', ReadVariableNames=true,Range='A1:D5')

Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.

T_VARS = 4×4 table
      LOC        IDNum      x03_Mar_2023    x04_Mar_2023
    ________    ________    ____________    ____________

    {'LOCA'}    {'LD_A'}         1                  3   
    {'LOCB'}    {'LD_C'}         2                  5   
    {'LOCA'}    {'LD_B'}         3             3.1322   
    {'LOCB'}    {'CL_K'}         4                  7   

[Stephen23]

"...and explains it can't be solved completely"

Reading and following the advice in the warning message:

T = readtable('VAR_SAVE.xlsx', 'ReadVariableNames',true, 'VariableNamingRule','preserve')

T = 4×7 table
      LOC        IDNum      03-Mar-2023    04-Mar-2023    05-Mar-2023    05-Mar-2023_1    07-Mar-2023
    ________    ________    ___________    ___________    ___________    _____________    ___________

    {'LOCA'}    {'LD_A'}         1                3           10               0               1     
    {'LOCB'}    {'LD_C'}         2                5           20               1               1     
    {'LOCA'}    {'LD_B'}         3           3.1322           30               0               0     
    {'LOCB'}    {'CL_K'}         4                7           40               1               0     

Note that those dates are store in the excel as Excel serial date numbers (days since 1 Jan 1900), apparently the formatting is not stored for each cell.

Also note that columns E and F contain identical headers, which READTABLE must modify to be unique.