3

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.

Mary
  • 788
  • 6
  • 19
  • 43
  • 1
    You are attempting to dynamically name a variable. You don't want to do this. Either store your data with a numeric index (array, cell array) or utilize a structure array and [dynamic field referencing](http://blogs.mathworks.com/loren/2005/12/13/use-dynamic-field-references/) – sco1 Dec 09 '15 at 17:26
  • 3
    As an aside, you can [access data in a cell array](http://www.mathworks.com/help/matlab/matlab_prog/access-data-in-a-cell-array.html) using curly braces `{}` and eliminate the need to call `char` – sco1 Dec 09 '15 at 17:29

2 Answers2

4

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

sco1
  • 12,154
  • 5
  • 26
  • 48
3

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;

Community
  • 1
  • 1
Matthew Gunn
  • 4,451
  • 1
  • 12
  • 30
  • Sure. I follow the logic against dynamic naming. It is the best solution to this problem, however, and your code works perfectly. The tabs will be fixed and only different combinations of them will be chosen. Their names however will not change. Thank you! – Mary Dec 09 '15 at 17:42
  • @Mary apparently you do not follow the logic against dynamic naming, because then you'd never dare to use `eval`. [This post](http://stackoverflow.com/a/32467170/5211833) contains information on why `eval` is the most `evil` function in MATLAB and why you should strive at all cost to avoid using it. – Adriaan Dec 09 '15 at 17:54
  • @Mary It's a convenient solution but it's certainly not the best solution. It's not just an issue with magically creating variables out of thin air, it's also an issue with MATLAB's compiler, which cannot accelerate `eval` statements. – sco1 Dec 09 '15 at 17:54
  • Thanks @Adriaan. No I am not familiar with the pitfalls of using `eval`. I leave the question open to alternatives if you have them? – Mary Dec 09 '15 at 17:57
  • @Mary restructure your variables. Usually this occurs because you stored your variables in a way which is highly undesirable. Try and store them like Excaza said using numerical cell assignments, or numerical structure assignments. If you did not create this data yourself, I'm afraid you can't do anything about that and are forced to use the evil `eval`. – Adriaan Dec 09 '15 at 18:01
  • 1
    @MatthewGunn I think you should be able to add all tab names into a cell-array, and loop over that whilst loading, storing variables in a numerically accessible cell array, i.e. `data{ii} = readtable(inputFile,'Sheet',tabnames{ii});` and loop over `ii` – Adriaan Dec 09 '15 at 18:05
  • @Adriaan If the code already exists that use those variable names, and Mary is simply avoiding about 30 lines of copy paste, it's really not the end of the world to use `eval`. But yeah, I pretty much never use it. – Matthew Gunn Dec 09 '15 at 18:07
  • 1
    @MatthewGunn it's also not the end of the world to use globals, that doesn't make them a good idea. – sco1 Dec 09 '15 at 18:07
  • Cheers for using [my commented solution](http://stackoverflow.com/questions/34185068/automatically-create-and-name-tables-in-matlab-from-excel-tabs#comment56118015_34185320), though might I suggest [not using `i` or `j` as a variable name](http://stackoverflow.com/questions/14790740/using-i-and-j-as-variables-in-matlab)? – Adriaan Dec 09 '15 at 18:14
  • 1
    @Adriaan High BBC coding style is to use `i` and `j` as counting variables. I understand the desire for a Matlab specific culture of using `ii` instead, but few do that. Using `1i` for the imaginary number and `i` for a counting variable is a fine solution, especially if one's work doesn't involve using imaginary numbers. `1i` is quite a nice sol because it can never be a valid variable name! – Matthew Gunn Dec 09 '15 at 18:28
  • Thanks all. These are all great suggestions and comments. Utilizing a struct is the most suitable for my problem. Thanks again! – Mary Dec 10 '15 at 09:23