Creating a Struct Array From Excel Sheet

Question

I have written a matlab function that accepts the string (name) of an excel document as input:

function printOut = modData(inputExcel)
[num,txt,raw]=xlsread(inputExcel)

The excel sheet has the following data:

First-Name | Last-Name | Subject1 | Subject 2 | .. (so on to subject n)

For each column in the excel sheet there are a particular number of rows (say 200) of people's name and their mark in each subject n.

I need to create a structure array that contains the data of each person (1x200 struct array with fields) for each column specified above. Also the subject names change with each excel sheet so I need the struct array to be dynamic in that sense (eg Subject1 could be Chemistry, Subject2 Math etc)

How can I do this?

Example of first entry in structure should be:

First-Name: {'George'}
Last-Name: {'Henry'}
Chemistry: [55]
Math: [76]
Subject3: ....

My current attempt is below:

[RawRow,RawCol]=size(raw);
for kk=2:RawRow
    studentFName = raw(kk,1);
    allFName = [studentFName];
    allFName = [allFName;studentFName];
end

DataStruct = struct(raw{1,1},allFirstNames,raw{1,2},allLastNames,raw{1,3},Subject1,....)

The above for loop does not work, allFName only contains the First Name of the last row/entry in the excel file.

Answer 1

You can do this in one call to struct (no loops needed) by first processing your raw data in two ways:

• Replace invalid characters in the column names, since structure field names have to follow the same naming conventions as variables. In this case, '-' can be replaced with '_'.
• Collect all rows after the column names into another cell array. This will allow you to easily pass all the data to struct as a comma separated list.

Here's some sample raw data (after loading from xlsread) and a single line of code that does the above two steps:

>> raw = {'First-Name' 'Last-Name' 'Chemistry' 'Math' 'Killing'; ...
          'George' 'Henry' 55 76 0; 'Krombopulos' 'Michael' 90 90 100}
raw =

  3×5 cell array

    'First-Name'     'Last-Name'    'Chemistry'    'Math'    'Killing'
    'George'         'Henry'        [       55]    [  76]    [      0]
    'Krombopulos'    'Michael'      [       90]    [  90]    [    100]

>> cellData = [strrep(raw(1, :), '-', '_'); num2cell(raw(2:end, :), 1)]

cellData =

  2×5 cell array

    'First_Name'    'Last_Name'    'Chemistry'    'Math'        'Killing' 
    {2×1 cell}    {2×1 cell}    {2×1 cell}    {2×1 cell}    {2×1 cell}

And now you can easily create your structure array like so:

>> DataStruct = struct(cellData{:});
>> DataStruct(1)

ans = 

  struct with fields:

    First_Name: 'George'
     Last_Name: 'Henry'
     Chemistry: 55
          Math: 76
       Killing: 0

>> DataStruct(2)

ans = 

  struct with fields:

    First_Name: 'Krombopulos'
     Last_Name: 'Michael'
     Chemistry: 90
          Math: 90
       Killing: 100