writetable/readtable with multi-line headers

How can I use writetable to make a table to excel sheet and .csv file which has multi-line headers. Headers can include both per-variable headers like units and description, but also per-table headers like description or comments.

For example, take a table like below.

T = table(["Blue";"Red";"Green"],[50;100;75],[0.1;0.5;0.9],...
    VariableNames={'Color','Weight','Temp'});
T.Properties.VariableUnits = ["[-]";"[kg]";"°C"];
T.Properties.VariableDescriptions = ["Outside Color";"Gross Weight";"Melting Point"];
T.Properties.Description = "Playdough Properties Chart";

Now I want to make it look like this in excel and .csv files

I know to do this by other means, but I would like to be able to do it with writetable, and also to read it back in with readtable.

Is there a straight-forward way of doing this?

Answer 1

[Voss]

Here's one way:

% specify the file to write to
output_file = 'test.csv';

% construct the table
T = table(["Blue";"Red";"Green"],[50;100;75],[0.1;0.5;0.9],...
  VariableNames={'Color','Weight','Temp'});
T.Properties.VariableUnits = ["[-]";"[kg]";"[°C]"]; % added [] around °C
T.Properties.VariableDescriptions = ["Outside Color";"Gross Weight";"Melting Point"];
T.Properties.Description = "Playdough Properties Chart";

T

T = 3x3 table
     Color     Weight    Temp
    _______    ______    ____

    "Blue"       50      0.1 
    "Red"       100      0.5 
    "Green"      75      0.9 

T.Properties

ans = 
  TableProperties with properties:

             Description: 'Playdough Properties Chart'
                UserData: []
          DimensionNames: {'Row'  'Variables'}
           VariableNames: {'Color'  'Weight'  'Temp'}
           VariableTypes: ["string"    "double"    "double"]
    VariableDescriptions: {'Outside Color'  'Gross Weight'  'Melting Point'}
           VariableUnits: {'[-]'  '[kg]'  '[°C]'}
      VariableContinuity: []
                RowNames: {}
        CustomProperties: No custom properties are set.
      Use addprop and rmprop to modify CustomProperties.

% write the table to the output file
fid = fopen(output_file,'w');
fprintf(fid,'%s\n', ...
    T.Properties.Description, ...
    strjoin(T.Properties.VariableNames,','), ...
    strjoin(T.Properties.VariableDescriptions,","), ...
    strjoin(T.Properties.VariableUnits,","));
fclose(fid);
writetable(T,output_file,'WriteMode','append','WriteVariableNames',false);

% check the contents of the output file
type(output_file)

Playdough Properties Chart
Color,Weight,Temp
Outside Color,Gross Weight,Melting Point
[-],[kg],[°C]
Blue,50,0.1
Red,100,0.5
Green,75,0.9

% read the output file
opts = detectImportOptions(output_file);
opts.VariableNamesLine = 2;
opts.VariableDescriptionsLine = 3;
opts.VariableUnitsLine = 4;
opts.DataLines = [5 Inf];
opts = setvartype(opts,1,'string');
Tnew = readtable(output_file,opts);
tmp = readlines(output_file);
Tnew.Properties.Description = tmp(1);
Tnew.Properties.VariableDescriptions = string(Tnew.Properties.VariableDescriptions);
Tnew.Properties.VariableUnits = string(Tnew.Properties.VariableUnits);

Tnew

Tnew = 3x3 table
     Color     Weight    Temp
    _______    ______    ____

    "Blue"       50      0.1 
    "Red"       100      0.5 
    "Green"      75      0.9 

Tnew.Properties

ans = 
  TableProperties with properties:

             Description: 'Playdough Properties Chart'
                UserData: []
          DimensionNames: {'Row'  'Variables'}
           VariableNames: {'Color'  'Weight'  'Temp'}
           VariableTypes: ["string"    "double"    "double"]
    VariableDescriptions: {'Outside Color'  'Gross Weight'  'Melting Point'}
           VariableUnits: {'[-]'  '[kg]'  '[°C]'}
      VariableContinuity: []
                RowNames: {}
        CustomProperties: No custom properties are set.
      Use addprop and rmprop to modify CustomProperties.

% the table read from file is equivalent to the original table
isequal(Tnew,T)

ans = logical
   1

[ET]

Thank you for this answer, and this does indeed work for .csv case, though I'm not sure of how to use fprintf to also print to an excel sheet. I guess I was hoping there was a way to tell writetable to write the variableunits and variabledescriptions in the same way that readtable allows you to read them.

[Walter Roberson]

There is not support in writetable() for writing the units or variable descriptions.

Answer 2

[Walter Roberson]

writetable() first just the header lines. Then writetable() the data with WriteMode='append'

When you readtable() you can specify VariableNamesRange, VariableUnitsRange, VariableDescriptionsRange

[ET]

How do you write just the header lines with writetable?

[Walter Roberson]

Actually, writematrix() or writecell() would be easiest for the headers. Construct either an array of string() objects or a cell array of character vectors and writematrix() or writecell() as appropriate.