Discover MakerZone

MATLAB and Simulink resources for Arduino, LEGO, and Raspberry Pi

Learn more

Discover what MATLAB® can do for your career.

Opportunities for recent engineering grads.

Apply Today

Thread Subject:
building structure arrays from excel data?

Subject: building structure arrays from excel data?

From: Jeremy

Date: 27 Apr, 2010 01:12:05

Message: 1 of 10

I would like to import a complicated data set from excel into MatLab. What I'd like to achieve is a structure array where each field represents a column variable for example.

aa.TrialNumber
aa.MaleID
aa.FemaleID
aa.TimeInTrial
aa.FileNames

What I'd like to be able to do is access 1 trial worth of data by using a similar command as: aa(1,1) to get the following output

TrialNumber: 1
MaleID: 23.4
FemaleID: 22.1
TimeInTrial: [0 12 16 36]
FileNames: ['P0' 'P12' 'P16' 'P36']

I know one problem I could encounter is that not all of my fields have the same number of rows due to trial level variation. I'm relatively new to MatLab, so if you have suggestions please take that into consideration - Thanks you. Please let me know if you need other information to assist with this question.

Subject: building structure arrays from excel data?

From: TideMan

Date: 27 Apr, 2010 02:50:35

Message: 2 of 10

On Apr 27, 1:12 pm, "Jeremy " <jsg...@mail.missouri.edu> wrote:
> I would like to import a complicated data set from excel into MatLab.  What I'd like to achieve is a structure array where each field represents a column variable for example.
>
> aa.TrialNumber
> aa.MaleID
> aa.FemaleID
> aa.TimeInTrial
> aa.FileNames
>
> What I'd like to be able to do is access 1 trial worth of data by using a similar command as: aa(1,1) to get the following output
>
> TrialNumber: 1
> MaleID: 23.4
> FemaleID: 22.1
> TimeInTrial: [0 12 16 36]
> FileNames: ['P0' 'P12' 'P16' 'P36']
>
> I know one problem I could encounter is that not all of my fields have the same number of rows due to trial level variation.  I'm relatively new to MatLab, so if you have suggestions please take that into consideration - Thanks you.  Please let me know if you need other information to assist with this question.

Looks good to me.
But don't sit thinking about.
Do it!!

e.g., for trial ino:
[a,b]=xlsread(xlsfile);
for icol=1:size(a,2)
  aa(ino).(b{icol})=a(:,icol);
end

Subject: building structure arrays from excel data?

From: us

Date: 27 Apr, 2010 04:29:05

Message: 3 of 10

"Jeremy " <jsgth5@mail.missouri.edu> wrote in message <hr5dl5$fgp$1@fred.mathworks.com>...
> I would like to import a complicated data set from excel into MatLab. What I'd like to achieve is a structure array where each field represents a column variable for example.
>
> aa.TrialNumber
> aa.MaleID
> aa.FemaleID
> aa.TimeInTrial
> aa.FileNames
>
> What I'd like to be able to do is access 1 trial worth of data by using a similar command as: aa(1,1) to get the following output
>
> TrialNumber: 1
> MaleID: 23.4
> FemaleID: 22.1
> TimeInTrial: [0 12 16 36]
> FileNames: ['P0' 'P12' 'P16' 'P36']
>
> I know one problem I could encounter is that not all of my fields have the same number of rows due to trial level variation. I'm relatively new to MatLab, so if you have suggestions please take that into consideration - Thanks you. Please let me know if you need other information to assist with this question.

a hint:
- also, look at

     help cell2struct;
     help struct;

us

Subject: building structure arrays from excel data?

From: Ashish Uthama

Date: 27 Apr, 2010 12:36:48

Message: 4 of 10

On Mon, 26 Apr 2010 22:12:05 -0300, Jeremy <jsgth5@mail.missouri.edu>
wrote:

> I would like to import a complicated data set from excel into MatLab.
> What I'd like to achieve is a structure array where each field
> represents a column variable for example.
>
> aa.TrialNumber
> aa.MaleID
> aa.FemaleID
> aa.TimeInTrial
> aa.FileNames
>
> What I'd like to be able to do is access 1 trial worth of data by using
> a similar command as: aa(1,1) to get the following output
>
> TrialNumber: 1
> MaleID: 23.4
> FemaleID: 22.1
> TimeInTrial: [0 12 16 36]
> FileNames: ['P0' 'P12' 'P16' 'P36']
>
> I know one problem I could encounter is that not all of my fields have
> the same number of rows due to trial level variation. I'm relatively
> new to MatLab, so if you have suggestions please take that into
> consideration - Thanks you. Please let me know if you need other
> information to assist with this question.

Code in this example might be useful:
http://www.mathworks.com/matlabcentral/fileexchange/19707-xls2var

Subject: building structure arrays from excel data?

From: Jeremy

Date: 27 Apr, 2010 15:56:04

Message: 5 of 10

"Ashish Uthama" <first.last@mathworks.com> wrote in message <op.vbtpfme5a5ziv5@uthamaa.dhcp.mathworks.com>...
> On Mon, 26 Apr 2010 22:12:05 -0300, Jeremy <jsgth5@mail.missouri.edu>
> wrote:
>
> > I would like to import a complicated data set from excel into MatLab.
> > What I'd like to achieve is a structure array where each field
> > represents a column variable for example.
> >
> > aa.TrialNumber
> > aa.MaleID
> > aa.FemaleID
> > aa.TimeInTrial
> > aa.FileNames
> >
> > What I'd like to be able to do is access 1 trial worth of data by using
> > a similar command as: aa(1,1) to get the following output
> >
> > TrialNumber: 1
> > MaleID: 23.4
> > FemaleID: 22.1
> > TimeInTrial: [0 12 16 36]
> > FileNames: ['P0' 'P12' 'P16' 'P36']
> >
> > I know one problem I could encounter is that not all of my fields have
> > the same number of rows due to trial level variation. I'm relatively
> > new to MatLab, so if you have suggestions please take that into
> > consideration - Thanks you. Please let me know if you need other
> > information to assist with this question.
>
> Code in this example might be useful:
> http://www.mathworks.com/matlabcentral/fileexchange/19707-xls2var

Thanks everyone for giving advise - much appreciated. Tideman, I came up with a similar solution as the one you proposed, I just couldn't figure out how to combine the created arrays into a structure array.

Ashish - thanks for suggesting the above post! It is very close to what I'd like to accomplish more automatically. Currently we create the same type of structure by copying each excel column into Matlab (which is a serious pain for large datasets). So the issue now, is how can I turn the 1x1 structure into a 1xN structure where each N is (from my above example) all of the fields for trial 1? - Thanks

Subject: building structure arrays from excel data?

From: Ashish Uthama

Date: 27 Apr, 2010 19:52:05

Message: 6 of 10

On Tue, 27 Apr 2010 12:56:04 -0300, Jeremy <jsgth5@mail.missouri.edu>
wrote:

> "Ashish Uthama" <first.last@mathworks.com> wrote in message
> <op.vbtpfme5a5ziv5@uthamaa.dhcp.mathworks.com>...
>> On Mon, 26 Apr 2010 22:12:05 -0300, Jeremy <jsgth5@mail.missouri.edu>
>> wrote:
>> > I would like to import a complicated data set from excel into
>> MatLab. > What I'd like to achieve is a structure array where each
>> field > represents a column variable for example.
>> >
>> > aa.TrialNumber
>> > aa.MaleID
>> > aa.FemaleID
>> > aa.TimeInTrial
>> > aa.FileNames
>> >
>> > What I'd like to be able to do is access 1 trial worth of data by
>> using > a similar command as: aa(1,1) to get the following output
>> >
>> > TrialNumber: 1
>> > MaleID: 23.4
>> > FemaleID: 22.1
>> > TimeInTrial: [0 12 16 36]
>> > FileNames: ['P0' 'P12' 'P16' 'P36']
>> >
>> > I know one problem I could encounter is that not all of my fields
>> have > the same number of rows due to trial level variation. I'm
>> relatively > new to MatLab, so if you have suggestions please take
>> that into > consideration - Thanks you. Please let me know if you
>> need other > information to assist with this question.
>> Code in this example might be useful:
>> http://www.mathworks.com/matlabcentral/fileexchange/19707-xls2var
>
> Thanks everyone for giving advise - much appreciated. Tideman, I came
> up with a similar solution as the one you proposed, I just couldn't
> figure out how to combine the created arrays into a structure array.
>
> Ashish - thanks for suggesting the above post! It is very close to what
> I'd like to accomplish more automatically. Currently we create the same
> type of structure by copying each excel column into Matlab (which is a
> serious pain for large datasets). So the issue now, is how can I turn
> the 1x1 structure into a 1xN structure where each N is (from my above
> example) all of the fields for trial 1? - Thanks

Depending on the size of your existing code, it might be easier to handle
a 1x1 structure with 1xN sized fields in your existing code. Modifying the
FEX submission design to return a 1xN structure would be very inefficient,
since you would have to do translation per row rather than once per
variable (column).

A kludge might be to write a loop to turn the struct inside out. Dont
think this is the way you want to go...

Aside: I dont get why you would have two indices? (as in aa(1,1) ?)

Subject: building structure arrays from excel data?

From: Jeremy

Date: 27 Apr, 2010 21:48:03

Message: 7 of 10

> Depending on the size of your existing code, it might be easier to handle
> a 1x1 structure with 1xN sized fields in your existing code. Modifying the
> FEX submission design to return a 1xN structure would be very inefficient,
> since you would have to do translation per row rather than once per
> variable (column).
>
> A kludge might be to write a loop to turn the struct inside out. Dont
> think this is the way you want to go...
>
> Aside: I dont get why you would have two indices? (as in aa(1,1) ?)

I only included the aa(1,1) as an example because that is the nomenclature I've seen used when referring to structures with multiple elements. For some time now we have been manually cutting and pasting data into matlab to construct structure arrays that are 1X1. The link you provided above has now automated that process which is a huge step forward - thank you! However, the code also builds a 1x1 structure array. While this is how we've been processing the data, were each fieldname contains all relevant data related to that specific column on the data sheet. What we'd like to move towards is more in line with examples I've seen of database like structure arrays, where each row is a new element in the structure. In this case every structure element would contain all the fieldnames and data for trial #1. Hence if you typed in the command window aa.(1) for example, it would return
all the values for the fieldnames for just 1 trial. While what we've accomplished, with your suggestions have been a huge step forward, I'd like to go one step farther and build the array more like a database structure.

Subject: building structure arrays from excel data?

From: Ashish Uthama

Date: 28 Apr, 2010 12:01:08

Message: 8 of 10

On Tue, 27 Apr 2010 18:48:03 -0300, Jeremy <jsgth5@mail.missouri.edu>
wrote:

>> Depending on the size of your existing code, it might be easier to
>> handle a 1x1 structure with 1xN sized fields in your existing code.
>> Modifying the FEX submission design to return a 1xN structure would be
>> very inefficient, since you would have to do translation per row
>> rather than once per variable (column).
>> A kludge might be to write a loop to turn the struct inside out. Dont
>> think this is the way you want to go...
>> Aside: I dont get why you would have two indices? (as in aa(1,1) ?)
>
> I only included the aa(1,1) as an example because that is the
> nomenclature I've seen used when referring to structures with multiple
> elements. For some time now we have been manually cutting and pasting
> data into matlab to construct structure arrays that are 1X1. The link
> you provided above has now automated that process which is a huge step
> forward - thank you! However, the code also builds a 1x1 structure
> array. While this is how we've been processing the data, were each
> fieldname contains all relevant data related to that specific column on
> the data sheet. What we'd like to move towards is more in line with
> examples I've seen of database like structure arrays, where each row is
> a new element in the structure. In this case every structure element
> would contain all the fieldnames and data for trial #1. Hence if you
> typed in the command window aa.(1) for example, it would return all the
> values for the fieldnames for just 1 trial. While what we've
> accomplished, with your suggestions have been a huge step forward, I'd
> like to go one step farther and build the array more like a database
> structure.

ok, that makes sense to me.

I have updated the FEX submission with an option to return a structure
array instead of a structure with field arrays. While it goes through:



function xlsStruct=xls2struct(file,flag)
%xls2struct Load Excel file contents as a structure
% xlsstruct = xls2struct(file) loads the Excel file 'file'. The first row
is
% used to generate the field names for the 1x1 structure xlsstruct. Each
% column (excluding the first row) is then converted to a numeric/cell
% array and assigned to the corresponding field name. All columns are
% expected to have equal number of entries.
%
% xlsstructArray = xls2struct(file,'structArray') will return a 1xNumCol
% structure array xlsstructArray. The kth element of the structure will
% have field names corresponding to the first row and values corresponding
% to the kth row.
%
% GENVARNAME is used to generate a valid MATLAB structure field name from
% the first row data.
%
% Blank entries are returned as 'NaN'.
%
% Platform support is dependent on XLSREAD.
%
% %Example: Given this Excel file content (9 columns, 5 rows):
%
% %one two three four ' ' six se ven
% %1 2 3 6 7
% %11 22 three
% % seven
% % 222 33
%
% xlsStruct=xls2struct('example.xls')
% % Where:
% % xlsStruct.one' = 1 11 NaN NaN
% % xlsStruct.two' = 2 22 NaN 222
% % xlsStruct.three' = [3] 'three' [NaN] [33]
% % xlsStruct.four' = NaN NaN NaN NaN
% % xlsStruct.x' = NaN NaN NaN NaN
% % xlsStruct.six' = 6 NaN NaN NaN
% % xlsStruct.seVen' = [7] [NaN] 'seven' [NaN]
%
% xlsStructArray=xls2struct('example.xls','structArray')
% % Where:
% % xlsStructArray =
% %
% % 1x4 struct array with fields:
% % one
% % two
% % three
% % four
% % x
% % six
% % seVen

% See also: xlsread genvarname

%% handle argin
if nargin < 1
     error('MATLAB:xls2struct:FileName',...
         'Input excel filename must be specified.');
elseif nargin ==1
     flag = '';
elseif nargin ==2
     if(strcmpi(flag,'structArray'))
         flag=true;
     else
         error('MATLAB:xls2struct:flag',...
             'Invalid second argument');
     end
else
     error('MATLAB:xls2struct:inputArgCount',...
         'Invalid number of input arguments.');
end


if ~ischar(file)
     error('MATLAB:xls2struct:InputClass','Filename must be a string.');
end


%% XLSREAD the file
try
     %obtain numeric and text data (mutually exclusive contents)
     [num,txt]=xlsread(file);
catch ME
     error('MATLAB:xls2struct:xlsreaderr',...
         'XLSREAD was unable to read this file: %s',ME.message);
end

%% Process the data

% The cell array txt contains all the strings in the excel file
% including the first 'header' row which we assume to be variable names
[rows,numVars]=size(txt); %#ok<ASGLU>

for varInd=1:numVars

     %loop through each column in the excel sheet

     %Assume first row element in the current column is the variable name
     varName=txt{1,varInd};

     %since this string might not be a valid MATLAB variable name (it might
     %contain spaces, create one from it:
     varName=genvarname(varName);

     %if there is a string in this column (other than the first one of
     %course) we create a cell array for the data.
     stringData=txt(2:end,varInd);
     strInds=~cellfun(@isempty,stringData);

     if( any(strInds) )
         %this column contains strings, use cells
         varData={};

         try %#ok<TRYNC>
             %try to convert any numbers present in this column to cells
             varData=num2cell( num(:,varInd) );
         end
         varData(strInds)=stringData(strInds); %#ok<AGROW>

     else
         %this column only contains numbers, use arrays
         varData=num(:,varInd);

         if(flag)
             %we need a cell array to 'deal' to fields of structure array
             varData=num2cell(varData);
         end

     end



     %Use dynamic field names for MATLAB structures
     if(flag)
         %create structure array as output
         [xlsStruct(1:length(varData)).(varName)]=deal(varData{:});
     else
         %create field arrays as output
         xlsStruct.(varName)=varData;
     end

end

Subject: building structure arrays from excel data?

From: Jeremy

Date: 28 Apr, 2010 14:49:04

Message: 9 of 10

Ashish, - FANTASTIC!!!

It's on the money! Thank you so much for your assistance! I really appreciate the time you took in helping us resolve these issues - thanks!

Subject: building structure arrays from excel data?

From: Yuji Zhang

Date: 7 Jul, 2013 21:55:09

Message: 10 of 10

Hi Ashish~

Thank you for the contribution! Do you have a 2D version of the code? where the headers are in the 1st row and the 1st column, conceptually like this:

                   Adam Bob1 Bob2
No 1 2 3
Address 'some ave' 'some st' 'some rd'
Code [1 2 3] [4 5 6] [7 8 9]
....

Let me know. Thank you~

yuji


"Ashish Uthama" <first.last@mathworks.com> wrote in message <op.vbvif60pa5ziv5@uthamaa.dhcp.mathworks.com>...
> On Tue, 27 Apr 2010 18:48:03 -0300, Jeremy <jsgth5@mail.missouri.edu>
> wrote:
>
> >> Depending on the size of your existing code, it might be easier to
> >> handle a 1x1 structure with 1xN sized fields in your existing code.
> >> Modifying the FEX submission design to return a 1xN structure would be
> >> very inefficient, since you would have to do translation per row
> >> rather than once per variable (column).
> >> A kludge might be to write a loop to turn the struct inside out. Dont
> >> think this is the way you want to go...
> >> Aside: I dont get why you would have two indices? (as in aa(1,1) ?)
> >
> > I only included the aa(1,1) as an example because that is the
> > nomenclature I've seen used when referring to structures with multiple
> > elements. For some time now we have been manually cutting and pasting
> > data into matlab to construct structure arrays that are 1X1. The link
> > you provided above has now automated that process which is a huge step
> > forward - thank you! However, the code also builds a 1x1 structure
> > array. While this is how we've been processing the data, were each
> > fieldname contains all relevant data related to that specific column on
> > the data sheet. What we'd like to move towards is more in line with
> > examples I've seen of database like structure arrays, where each row is
> > a new element in the structure. In this case every structure element
> > would contain all the fieldnames and data for trial #1. Hence if you
> > typed in the command window aa.(1) for example, it would return all the
> > values for the fieldnames for just 1 trial. While what we've
> > accomplished, with your suggestions have been a huge step forward, I'd
> > like to go one step farther and build the array more like a database
> > structure.
>
> ok, that makes sense to me.
>
> I have updated the FEX submission with an option to return a structure
> array instead of a structure with field arrays. While it goes through:
>
>
>
> function xlsStruct=xls2struct(file,flag)
> %xls2struct Load Excel file contents as a structure
> % xlsstruct = xls2struct(file) loads the Excel file 'file'. The first row
> is
> % used to generate the field names for the 1x1 structure xlsstruct. Each
> % column (excluding the first row) is then converted to a numeric/cell
> % array and assigned to the corresponding field name. All columns are
> % expected to have equal number of entries.
> %
> % xlsstructArray = xls2struct(file,'structArray') will return a 1xNumCol
> % structure array xlsstructArray. The kth element of the structure will
> % have field names corresponding to the first row and values corresponding
> % to the kth row.
> %
> % GENVARNAME is used to generate a valid MATLAB structure field name from
> % the first row data.
> %
> % Blank entries are returned as 'NaN'.
> %
> % Platform support is dependent on XLSREAD.
> %
> % %Example: Given this Excel file content (9 columns, 5 rows):
> %
> % %one two three four ' ' six se ven
> % %1 2 3 6 7
> % %11 22 three
> % % seven
> % % 222 33
> %
> % xlsStruct=xls2struct('example.xls')
> % % Where:
> % % xlsStruct.one' = 1 11 NaN NaN
> % % xlsStruct.two' = 2 22 NaN 222
> % % xlsStruct.three' = [3] 'three' [NaN] [33]
> % % xlsStruct.four' = NaN NaN NaN NaN
> % % xlsStruct.x' = NaN NaN NaN NaN
> % % xlsStruct.six' = 6 NaN NaN NaN
> % % xlsStruct.seVen' = [7] [NaN] 'seven' [NaN]
> %
> % xlsStructArray=xls2struct('example.xls','structArray')
> % % Where:
> % % xlsStructArray =
> % %
> % % 1x4 struct array with fields:
> % % one
> % % two
> % % three
> % % four
> % % x
> % % six
> % % seVen
>
> % See also: xlsread genvarname
>
> %% handle argin
> if nargin < 1
> error('MATLAB:xls2struct:FileName',...
> 'Input excel filename must be specified.');
> elseif nargin ==1
> flag = '';
> elseif nargin ==2
> if(strcmpi(flag,'structArray'))
> flag=true;
> else
> error('MATLAB:xls2struct:flag',...
> 'Invalid second argument');
> end
> else
> error('MATLAB:xls2struct:inputArgCount',...
> 'Invalid number of input arguments.');
> end
>
>
> if ~ischar(file)
> error('MATLAB:xls2struct:InputClass','Filename must be a string.');
> end
>
>
> %% XLSREAD the file
> try
> %obtain numeric and text data (mutually exclusive contents)
> [num,txt]=xlsread(file);
> catch ME
> error('MATLAB:xls2struct:xlsreaderr',...
> 'XLSREAD was unable to read this file: %s',ME.message);
> end
>
> %% Process the data
>
> % The cell array txt contains all the strings in the excel file
> % including the first 'header' row which we assume to be variable names
> [rows,numVars]=size(txt); %#ok<ASGLU>
>
> for varInd=1:numVars
>
> %loop through each column in the excel sheet
>
> %Assume first row element in the current column is the variable name
> varName=txt{1,varInd};
>
> %since this string might not be a valid MATLAB variable name (it might
> %contain spaces, create one from it:
> varName=genvarname(varName);
>
> %if there is a string in this column (other than the first one of
> %course) we create a cell array for the data.
> stringData=txt(2:end,varInd);
> strInds=~cellfun(@isempty,stringData);
>
> if( any(strInds) )
> %this column contains strings, use cells
> varData={};
>
> try %#ok<TRYNC>
> %try to convert any numbers present in this column to cells
> varData=num2cell( num(:,varInd) );
> end
> varData(strInds)=stringData(strInds); %#ok<AGROW>
>
> else
> %this column only contains numbers, use arrays
> varData=num(:,varInd);
>
> if(flag)
> %we need a cell array to 'deal' to fields of structure array
> varData=num2cell(varData);
> end
>
> end
>
>
>
> %Use dynamic field names for MATLAB structures
> if(flag)
> %create structure array as output
> [xlsStruct(1:length(varData)).(varName)]=deal(varData{:});
> else
> %create field arrays as output
> xlsStruct.(varName)=varData;
> end
>
> end

Tags for this Thread

What are tags?

A tag is like a keyword or category label associated with each thread. Tags make it easier for you to find threads of interest.

Anyone can tag a thread. Tags are public and visible to everyone.

Contact us