How do I read in the data from 23 sheets/tabs (of excel file) and create field (within a structure) that contains each tab's data?

Hi all, I have an excel file with 23 sheets/tabs. I want to read the data from each sheet/tab and assign the data into separate fields (within a structure) for each sheet/tab. Is this possible? Can I do it using a loop, instead of individually? Thank you

[Hermes Suen]

Does it need to be in a struct, or could you import into a table? What does the data look like? If it it is organized by columns you can consider using a Table which would be easier. If it is a table, then you can create a cell array of the numbers 1-23, and use a loop (happy to show you some example code if it is). Do you want the data from each sheet to go into a specific field in a larger Struct, or does EACH sheet have its OWN struct?

[Mathew Grey]

Hi Hermes, Thank you for the reply. Each tab has a lot of things in it (headings, other variables, graphs at the bottom) and I just want to select a certain range of data (E8:KE15) from each tab. I have been using this code:

clear all;
close all;
clc

[~, Sheets] = xlsfinfo('GPSdata2018.xlsx');
nSheets = length(Sheets);
Data =[]
for i      = 1:nSheets
    Player = Sheets{i};
    Data   = [Data, xlsread('GPSdata2018.xlsx', Player, 'E8:KE15')];
end
GPS.Data = Data;

This reads the right data and places it in the structure GPS.Data but because I have concatenated it, it is really hard to tell where one tab starts and the other ends. Also I wanted to keep each tab separate for now because they refer to different players' data. So I was hoping I could assign each tab to a specific field (labelled as the tabs are labelled) in a larger structure. Then if I wanted to look at a certain player(tab) I could go into the structure and open the field labelled with the players initials and the data would be in there. Does that make sense?

Kind regards, Mat

[Image Analyst]

I can't seem to find where you attached the workbook. What question or comment did you attach it to?

Answer 1

[Hermes Suen]

Hi Matt; from my understanding the variable 'Sheets' is a cell array containing all of the player names. From this, you can use the setfield function, which takes an existing struct, a field name, and some form of data and creates a new field in the struct(if it doesn't already exist) with the data stored under that new field name. In your case, the field name will be the player name, which is in Sheets{i}, and the data is found using the xlsread.

for i      = 1:nSheets
  Data =[];
  Player = Sheets{i};

    %the data in E8:KE15 will be stored in a field under GPS with the name given in Player
    Data   = xlsread('GPSdata2018.xlsx', Player, 'E8:KE15');
    GPS = setfield(GPS, Player, Data); 
end

What the setfield function does is creates a field under the player name, and stores the Data from the excel file into that field. Thus, if you had a player named Joe, you can access his data using:

GPS.Joe

More information can be found in the documentation for setfield: https://www.mathworks.com/help/matlab/ref/setfield.html

NOTE: in order for this to work, make sure that GPS is an existing struct prior to using the setfield function.

[Stephen23]

Mathew Grey's "Answer" moved here:

Hi Hermes, Yes that is correct, the variable 'Sheets' contains the names of each sheet. And that is exactly what I want - A structure called GPS which contains the fields (named for each player/sheet name). And each field holds the data read from E8 to KE15 for that player/sheet.

However, when I run this:

clear all;
close all;
clc

[~, Sheets] = xlsfinfo('GPSdata2018.xlsx');
nSheets = length(Sheets);
Data =[]
for i      = 1:nSheets
    Player = Sheets{i};
    Data   = xlsread('GPSdata2018.xlsx', Player, 'E8:KE15');
    GPS = setfield(GPS, Playeri, Data);
end

it returns the error 'Undefined function or variable 'GPS'.' Im sure it is only a minor problem, but I can't seem to work it out.

Thanks again for all your help. Mat

[Hermes Suen]

Hi Matt, I believe the problem is because setfield requires the struct to exist prior to its function call. So before entering the loop add this one line of code:

GPS = struct

This will create a 1x1 struct variable called GPS with no fields which will then allow setfield to work. I also noticed a typo in your code:

GPS = setfield(GPS, Player, Data); %This is the correct call

Cheers, Hermes

Answer 2

[Mathew Grey]

That is absolutely perfect Hermes! Thank you so much.

Happy coding.

Mat