Matlab iteration through excel rows

Hello,

I have a problem for which I'm not finding a solution for now, so I decided to ask you.

I have a given Excel File with several columns and Rows. I only need the Columns where the names are defined and their values are stored.

After knowing which columns are the right ones, I want to iterate through every row of these columns to extract the values. Can you may help me? Here is my Code for now.

filename = 'example.xls';
excelSheet = 'example';
columnFirst = {'A1','B1','C1','D1','E1','F1','G1','H1','I1','J1','K1','L1'};
columnName = 'Name';
columnNameLetter = '';
columnDefault = 'Default';
columnDefaultLetter = '';
n = 13;
for i=1:n
    [~,text] = xlsread(filename,excelSheet,columnFirst{i});
    if(strcmp(columnName,text))
        columnNameLetter = columnFirst{i};
    end
    if(strcmp(columnDefault,text))
        columnDefaultLetter = columnFirst{i};
    end
end

I'm getting the result that the Names are in column G and their values in column L

Now how can I iterate through all the rows to extract the values?

[Guillaume]

Bit of advice

columnFirst = {'A1','B1','C1','D1','E1','F1','G1','H1','I1','J1','K1','L1'};
n = 13;
for i = 1:n

So, whenever you change the list of column, you also have to change n to match the number of elements, AND you have to make sure that you've counted them correctly. Forget to change n, or put the wrong value and you've got a bug.

Why not let matlab determine the number of elements and avoid the risk altogether. It's also less work:

columnFirst = {'A1','B1','C1','D1','E1','F1','G1','H1','I1','J1','K1','L1'};
for i = 1:numel(columnFirst)

You should never hardcode the size of inputs, always let matlab find it out for you.

[Florian Azemi]

Thanks for that hint I will change it :D

Answer 1

[Guillaume]

It sounds like your spreadsheet has a row header and you want to find the columns with a particular header. That can be trivially achieved with readtable:

data = readtable('example.xlsx', 'Sheet', 'example', 'Range', 'A:L');

data.Name  %return the content of the Name column
data.Default   %return the content of the Default column

I would recommend that you keep all the data in the table and use . indexing to access the columns as you need them. There is no need to extract them into individual variables

name = data.Name;  %no point to that. Simply use data.Name everywhere

[Florian Azemi]

That was the solution for my problem thank you very much :)

Answer 2

[Alex Mcaulley]

Try this, is that you want?

filename = 'example.xls';
excelSheet = 'example';
columnName = 'Name';
columnDefault = 'Default';
[~,~,raw] = xlsread(filename,excelSheet);
try
    names = raw(2:end,contains(raw(1,:),columnName));
catch
    names = [];
end
try
    defaults = raw(2:end,contains(raw(1,:),columnDefault));
catch
    defaults = [];
end

[Florian Azemi]

It is giving me an empty [] as result

[Alex Mcaulley]

Can you upload your excel? It is difficult to know what is happening. What is the result of running this?

contains(raw(1,:),columnName)
contains(raw(1,:),columnDefault)

[Guillaume]

I'm not seeing the points of the try...catch statements. The lines in the try can never error* anyway so the catch will never be invoked.

Also note that contains is not the same as strcmp. contains(x, 'Name') returns true if x is for example 'a rose by a any other Name', strcmp only returns true for 'Name'.

So, overall the code should be:

filename = 'example.xls';
excelSheet = 'example';
columnName = 'Name';
columnDefault = 'Default';
[~, ~, raw] = xlsread(filename,excelSheet);
name = raw(2:end, strcmp(raw(1, :), columnName));
defaults = raw(2:end, strcmp(raw(1, :), columnDefault));

*edit: well, they can error if the 1st row does not contain text, but in that case, you'd be better off knowing than just ignoring the problem.

[Alex Mcaulley]

It's true @Guillaume Thanks ;)

Answer 3

[Florian Azemi]

Here is my Excel File.

After running this Code:

filename = 'example.xlsx';
excelSheet = 'example';
columnFirst = {'A1','B1','C1','D1','E1','F1','G1','H1','I1','J1','K1','L1'};
columnName = 'Name';
columnNameLetter = '';
columnDefault = 'Default';
columnDefaultLetter = '';
n = 13;
for i=1:n
    [~,text] = xlsread(filename,excelSheet,columnFirst{i});
    if(strcmp(columnName,text))
        columnNameLetter = columnFirst{i};
    end
    if(strcmp(columnDefault,text))
        columnDefaultLetter = columnFirst{i};
    end
end

I get following results:

columnNameLetter = C1 and columnDefaultLetter = D1 that means that all Names are stored in the C column and their default values in the D column.

Now what I want is to iterate through all rows and create new variables with the name extracted of the rows as name and the value extracted of the rows as value.