using spreadsheet column headers as variable

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.

[Sai Gudlur]

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]

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

Answer 1

[Cris LaPierre]

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

[Forrest Ward]

When I used your code I got an error message saying

Unrecognized property 'ReadVariableNames' for class 'matlab.io.spreadsheet.SpreadsheetImportOptions'.
Error in ReadDataTable (line 4)
opts.ReadVariableNames = true; 

[Forrest Ward]

Here is what my code looks like currently.

function DataTable = ReadDataTable(filename)
opts = spreadsheetImportOptions('NumVariables',6);
opts = setvaropts(opts,'FillValue','0');
opts.PreserveVariableNames = true;
DataTable = readtable(filename,opts,'ReadVariableNames',true,'ReadRowNames',true);
end

And for some reason I'm still getting 'Var1, Var2, Var3..." as my headers for each column.

[Walter Roberson]

do not mix name/value pairs with passing the options object.

[Forrest Ward]

Could you elaborate a little more on that? Sorry, I'm still very new to MatLab

[Cris LaPierre]

See my post above. You can either use Name-Value pairs with readtable (like the first code example), or you can use readtable with options specified, like the second code example. You cannot combine the two techniques.

Update your code to be something like this. Note that we don't have your file, so this code is untested.

function DataTable = ReadDataTable(filename)
opts = spreadsheetImportOptions('NumVariables',6);
opts = setvaropts(opts,'FillValue','0');
opts.PreserveVariableNames = true;
opts.ReadVariableNames = true;
opts.ReadRowNames = true;
DataTable = readtable(filename,opts);
end

[Forrest Ward]

What you're saying makes sense to me, but I copied and pasted your code above into matlab (just to test it with my file) but I'm still getting the error message

Unrecognized property 'ReadVariableNames' for class 'matlab.io.spreadsheet.SpreadsheetImportOptions'.
Error in ReadDataTable (line 5)
opts.ReadVariableNames = true; 

I'm getting that for the 'ReadVariableNames' and the 'ReadRowNames'

[Forrest Ward]

Also, my file is an .xlsx and not a .txt. file. Not sure if that makes much difference, but just letting you know

[Cris LaPierre]

I think we've gotten as far as we can with hypotheticals. Please attach your file, or at least something matching the format and layout for us to compare against. Also, what version of MATLAB are you using.

[Forrest Ward]

I am using the newest version of matlab (only bought it only a couple weeks ago). I am attaching the spreadsheet that I've been using to test my function. I hope to use this function for a much much larger spreadsheet in the future, but for now I've found it easier to just test it on a smaller file with less variables.

[Forrest Ward]

And I've been calling my function with

ReadDataTable('MyTable.xlsx')

[Walter Roberson]

function DataTable = ReadDataTable(filename);
   opts = detectImportOptions(filename);
   opts.PreserveVariableNames = true;
   opts = setvaropts(opts, strcmp(opts.VariableTypes,'double'), 'FillValue', 0);
   opts = setvaropts(opts, strcmp(opts.VariableTypes,'char'), 'FillValue', '0');

   opts.RowNamesRange = 'A2';
   DataTable = readtable(filename, opts);
end

Are you sure you want empty text areas to be filled with '0' ?? Filling double with numeric 0 makes sense, but rarely filling text with '0' (instead of something like 'N/A' or 'NA' or 'missing')

[Forrest Ward]

Your code worked perfectly for setting my column headers in my spreadsheet as the variables! Thank you so much for that!! And I was just using the test '0' as a placeholder. And was going to change it to 'missing' or 'N/A' later on. Although, the FillValue doesn't seem to work with my imported spreadsheet. The missing information in my table is coming out as '0x0char'. Kind of like what would happen if I didn't even use FillValue.

[Forrest Ward]

Also, do you always have to state the type of variable that your using to fill the missing information with (i.e. char, double, str)?

[Cris LaPierre]

And here is a minimalist approach. This lets detectImportOptions do most of the work.

function DataTable = ReadDataTable(filename)
opts = detectImportOptions(filename);
opts.VariableTypes = ["string", "double", "categorical", "categorical", "categorical", "categorical"];

DataTable = readtable(filename,opts);
end

[Cris LaPierre]

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]

fillmissing can fill '' to a preferred text.

[Forrest Ward]

Thank you guys very much for the help! I think I'm getting it now!!

Answer 2

[Jeremy Hughes]

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