MATLAB Answers

0

Matlab iteration through excel rows

Asked by Florian Azemi on 10 Apr 2019
Latest activity Commented on by Florian Azemi on 10 Apr 2019
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?

  2 Comments

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.
Thanks for that hint I will change it :D

Sign in to comment.

3 Answers

Answer by Guillaume
on 10 Apr 2019
 Accepted Answer

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

  1 Comment

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

Sign in to comment.


Answer by Alex Mcaulley on 10 Apr 2019

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

  4 Comments

Show 1 older comment
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)
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.

Sign in to comment.


Answer by Florian Azemi on 10 Apr 2019
Edited by Florian Azemi on 10 Apr 2019

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.

  0 Comments

Sign in to comment.