Matlab iteration through excel rows

9 views (last 30 days)
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
Guillaume
Guillaume on 10 Apr 2019
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
Florian Azemi on 10 Apr 2019
Thanks for that hint I will change it :D

Sign in to comment.

Accepted Answer

Guillaume
Guillaume on 10 Apr 2019
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
Florian Azemi
Florian Azemi on 10 Apr 2019
That was the solution for my problem thank you very much :)

Sign in to comment.

More Answers (2)

Alex Mcaulley
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
Guillaume
Guillaume on 10 Apr 2019
Edited: Guillaume on 10 Apr 2019
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
Alex Mcaulley on 10 Apr 2019
Edited: Alex Mcaulley on 10 Apr 2019
It's true @Guillaume Thanks ;)

Sign in to comment.


Florian Azemi
Florian Azemi on 10 Apr 2019
Edited: 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.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!