MATLAB: Using spreadsheet column headers as variable

column headers

I am writing a function to import an Excel table. I am using 'opts' commands in order to add some versatility to the function. However, when I use readtable(… , opts),the variables for my imported table say 'Var1, Var2, Var3..'. I want my variable names to be the variables contained in the first row of my spreadsheet. How am I able to fix this? Obviously, I don't want to use opts.'VariableNames' and type out each variable that I have. That would take way too long.

P.S. this does not happen when I use juse readtable(..), it only occurs when I have the opts with it.

Best Answer

To read the tabular data while preserving variable names, set the 'PreserveVariableNames' parameter to true.

T_preserve = readtable('sampletable.txt','PreserveVariableNames',true)

You must be using MATLAB R2019b or newer to use this.

If you are using opts, try this.

opts = detectImportOptions;
...
opts.ReadVariableNames = true;
opts.PreserveVariableNames=true;
T_preserve = readtable('sampletable.txt',opts);

Related Solutions

MATLAB: How to convert all string cells in a table to numerical values

If you use the import data tool, you can chose to ouput the data in matrix instead of a table.

With this tool you can also generate a script to import your data:

opts = delimitedTextImportOptions("NumVariables", 9);
% Specify range and delimiter
opts.DataLines = [7, Inf];
opts.Delimiter = " ";
% Specify column names and types
opts.VariableNames = ["Agilent", "TechnologiesE8362BMY43020256A060432", "VarName3", "VarName4", "VarName5", "VarName6", "VarName7", "VarName8", "VarName9"];
opts.VariableTypes = ["double", "double", "double", "double", "double", "double", "double", "double", "double"];
% Specify file level properties
opts.ExtraColumnsRule = "ignore";
opts.EmptyLineRule = "read";
opts.ConsecutiveDelimitersRule = "join";
opts.LeadingDelimitersRule = "ignore";
% Import the data
test = readtable("D:\thuis\test.txt", opts);
%% Convert to output type
test = table2array(test);
%% Clear temporary variables
clear opts

MATLAB: How to Read and plot CSV file and delete infinity values from CSV file

Matlab provide many ways to read your csv-files. Here are two variants based on textscan, Read formatted data from text file or string .

sensor.csv contains "???", which I assume is your "infinity value". AFAIK, there is no way to make textscan() or any other reading function directly convert "???" to the numerical value, Inf.

In the script, R1, textscan() converts "???" to NaN and in a second step replaces NaN by Inf. That's fine if the file doesn't have missing values, since they also would be converted to Inf.

In the script, R2, the entire file is read to a character array, chr. Next all '???' are replaced by 'Inf' and finally textscan() parses chr. R2 requires more memory to store chr.

%% R1 
fid = fopen( 'sensor.csv', 'r' );
cac = textscan( fid, '%f%f%f%f%f'                       ...
            ,   'HeaderLines',3, 'CollectOutput',true   ...
            ,   'Delimiter',',', 'TreatAsEmpty','???'   );
[~] = fclose( fid );  
num = cac{1};
% num(isnan(num)) = inf;    % why not just keep the NaNs
num(isnan(num)) = 0;        % or replace NaNs by zero
display( num )
%% R2
chr = fileread( 'sensor.csv' );
% chr = strrep( chr, '???', 'inf' );
chr = strrep( chr, '???', '0' );    % or replace '???' by '0'
cac = textscan( chr, '%f%f%f%f%f'                       ...
            ,   'HeaderLines',3, 'CollectOutput',true   ...
            ,   'Delimiter',','                         );
num = cac{1};
display( num )

Both outputs

num =
            0          Inf       1.8947      0.19107    -0.015871
        4e-06       1.9911       1.9105      0.19107            0
        8e-06       1.9911       1.8947      0.19107            0
      1.2e-05       1.9751       1.9105      0.19107            0
      1.6e-05       1.9911       1.9421      0.17514            0
        2e-05       1.9751       1.9262      0.17514            0
      2.4e-05       1.9911       1.8947      0.17514            0
      2.8e-05       1.9911       1.8947      0.17514            0
      3.2e-05          Inf       1.9105      0.19107     0.015871
      3.6e-05       1.9911       1.9262      0.19107            0
        4e-05       1.9911       1.9105      0.19107            0
      4.4e-05       1.9911       1.9262      0.19107            0
      4.8e-05       1.9911       1.8947      0.19107            0
>>

That's before I realised that you don't want Inf in your matrix.

Best Answer

Related Solutions

MATLAB: How to convert all string cells in a table to numerical values

MATLAB: How to Read and plot CSV file and delete infinity values from CSV file

Related Question