CODEWITHSUNDEEP
excelmatlabloopsmatlab-table
Mary
Mary

Reputation: 788

Automatically Create and Name Tables in Matlab from Excel Tabs

I have the following cell array which is a list of some (but not all) of the tab names in an excel file:

chosenTabs = 

'Screen'
'SectorAbsolute'
'SectorRelative'

How do I get it to read off each tab of the excel sheet according to what is on this list and return a table of the tab's contents? The new tables should have the same name as the tab that is read.

I have tried (for example to create a table called 'SectorAbsolute' containing the contents of the tab 'SectorAbsolute'):

char(chosenTabs(2))=readtable(inputFile,'Sheet',char(chosenTabs(2)))

but this returns the error:

You can not subscript a table using only one subscript. Table subscripting requires both row and variable subscripts.

Upvotes: 3

Views: 266

Answers (2)

Matthew Gunn
Matthew Gunn

Reputation: 4519

So you could do the following, but be aware that dynamically naming variables and using eval is not recommended:

my_command_string = [chosenTabs{i},'=readtable(inputFile,''Sheet'',chosenTabs{i})'];
eval(my_command_string);

If I were coding:

I'd probably just write it out (unless there are tons...):

tab_Screen         = readtable(inputFile,'Sheet','Screen');
tab_SectorAbsolute = readtable(inputFile,'Sheet','SectorAbsolute');

And if there are tons and tons...

Utilizing a struct, a suggested in this answer by excaza looks pretty slick. Some other methods are:

Method 1:

n_chosenTabs    = length(chosenTabs);
chosenTabs_data = cell(n_chosenTabs, 1);
for i=1:n_chosenTabs
  chosenTabs_data{i} = readtable(inputFile,'Sheet',chosenTabs{i});
end

Method 2:

You could also go from tab name to the actual table using a containers.Map. Load the map with:

tab_map = containers.Map;
for i=1:n_chosenTabs
  tab_map(chosenTabs{i}) = readtable(inputFile,'Sheet',chosenTabs{i});
end

And then you can access individual tables with something like.

local_copy_of_sector = tab_map('Sector');

Be aware though that if you change local_copy_of_sector it won't change the copy stored in the containers.Map;

Upvotes: 3

sco1
sco1

Reputation: 12214

A method utilizing a structure array:

chosentabs = {'Sheet1', 'Sheet3'};
ntabs = length(chosentabs);

for ii = 1:ntabs
    mytables.(chosentabs{ii}) = readtable('test.xlsx', 'Sheet', chosentabs{ii});
end

Which returns mytables, a structure array containing your tables. You can access your sheets explicitly (e.g. mytables.Sheet1) or by utilizing dynamic field referencing (e.g. mytables.(somestring))

Upvotes: 4

Related Questions