Matlab iteration through excel rows
9 views (last 30 days)
Show older comments
Florian Azemi
on 10 Apr 2019
Commented: 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
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.
Accepted Answer
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
More Answers (2)
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
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.
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!