MATLAB Answers

using spreadsheet column headers as variable

9 views (last 30 days)
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.

  2 Comments

Sai Gudlur
Sai Gudlur on 20 May 2020
Try this or maybe you sharing your code might give a little more information.
[filename,pathname] = uigetfile('*.xlsx');
fullfilename = fullfile(pathname,filename);
A = importdata(fullfilename);
headers = A.textdata(1,end);
Forrest Ward
Forrest Ward on 20 May 2020
I'll try that! And here is what my code currently looks like.
function DataTable = ReadDataTable(filename)
opts = spreadsheetImportOptions('NumVariables',6,'PreserveVariableNames',true);
opts = setvaropts(opts,'FillValue','0');
DataTable = readtable(filename,opts,'ReadRowNames',true);
end

Sign in to comment.

Accepted Answer

Cris LaPierre
Cris LaPierre on 20 May 2020
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);

  17 Comments

Cris LaPierre
Cris LaPierre on 21 May 2020
To answer your last question, if you want to specify the fill value, then yes, your fill value must much the variable data type. If you want the default 'missing' value, then specifying the varible datatype will suffice.
Walter Roberson
Walter Roberson on 21 May 2020
fillmissing can fill '' to a preferred text.
Forrest Ward
Forrest Ward on 22 May 2020
Thank you guys very much for the help! I think I'm getting it now!!

Sign in to comment.

More Answers (1)

Jeremy Hughes
Jeremy Hughes on 21 May 2020
When you create import options from scratch, you need to specify all the information, including where the variablenames and data are.
function DataTable = ReadDataTable(filename)
opts = spreadsheetImportOptions('NumVariables',6);
opts = setvaropts(opts,'FillValue','0');
opts.PreserveVariableNames = true;
% You need to specify where to read the variable names from.
opts.VariableNameRange = 'B1';
opts.DataRange = 'B2';
opts.RowNamesRange = 'A2'
DataTable = readtable(filename,opts,'ReadVariableNames',true,'ReadRowNames',true);
end

  0 Comments

Sign in to comment.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!