Help with excel to matlab
1 view (last 30 days)
Show older comments
Bart
on 22 Jul 2014
Commented: Matz Johansson Bergström
on 22 Jul 2014
Good morning (in Europe),
I'm relatively new to Matlab. I know a few things, but putting it in the correct code is usually the problem.
My next problem is as follows: (I will try to explain is as good as I can)
In my excel file (I will at one as an example), I have multiple worksheets. In every worksheet is a column with text, and in de column next to it, a corresponding number. So: H6 is "A", and I6 is "1". These two has to be together. That means that the text A has give 1 in matlab.
Until now, I did this with the following code:
dir_struct = dir('*.xlsm');
for i = 1:numel(dir_struct)
number(i) = str2double(dir_struct(i).name(10:(10+numel(dir_struct(i).name)-16)));
end
[revised_numbers, order] = sort(number);
for i = 1:numel(order)
[num, tekst, raw] = xlsread(dir_struct(order(i)).name,'sheet1','H6:I10');
i=1;
if strcmp(deblank(tekst(i,1)),'A')
A = num(i,1);
else
disp('A is not specified correctly')
end
i=i+1;
if strcmp(deblank(tekst(i,1)),'B')
B = num(i,1);
else
disp('B is not specified correctly')
end
i=i+1;
if strcmp(deblank(tekst(i,1)),'C')
C = num(i,1);
else
disp('C is not specified correctly')
end
etc. until the last (specified) text and number.
But my excel is way to large and text will be added or deleted, or the name will be adjusted to maintain this code. I know there is a way to do this in an other way.
I think it has to be done with "isempty" or something. And if the word "END" is in the H column, the code must stop.
I hope that this is clear so far.
If this works, something has to be specified further.
If the text in the H column is there, there MUST be a number behind it. Otherwise, an error has to be displayed in matlab. The code has to go further though. If the number in the I column is there, a name in the H column is not necesarry and this number won't be needed in matlab.
I have made an Excel file to explain my problem a bit further and I hope that someone understands what I want to do.
Many thanks in advance, and if something is not clear, I will explain is further!
Regards, Bart
ps. I'm using MATLAB R2012b
0 Comments
Accepted Answer
Matz Johansson Bergström
on 22 Jul 2014
Given the new problem I will give a new answer ;-)
I would not store the characters as variable names, it is better to store the data inside a single variable. Say we name it 'validrows'. Write before the while loop:
validrows = {}; i_valid=1;
Inside the first if-statement (in my previous answer) we can write
validrows{i_valid, 1} = raw{i, 1};
validrows{i_valid, 2} = raw{i, 2};
i_valid = i_valid+1;
So the data is in validrows which contains only the valid rows in the excel file in this cell structure.
However, if you still really want to store the data in A, B etc. you could write a modified version of above:
[num, tekst, raw] = xlsread('Example - 001.xlsx', 'Sheet1', 'H:I');
i = 1;
while ~strcmp(raw{i,1}, 'END')
%fprintf(1, 'i= %d ', i)
%nan is a double, so we have to take care of it
if ~isnan(raw{i, 1}) & strcmp(class(raw{i, 1}), 'char')
if ~isnan(raw{i, 2}) & strcmp(class(raw{i, 2}),'double')
%fprintf(1, 'Found character(s) and digit pair\n')% %s', char(raw{i,1}))
eval(sprintf('%s = raw{i, 2}', raw{i, 1}));
else
fprintf(1, 'Found character(s) but no digits\n')
end
else %no letter, just ignore
%fprintf(1, 'Didn''t find anything\n')
end
%fprintf(1, 'raw value:\n')
%raw(i, :)
%fprintf(1,'\n--------\n')
i = i + 1;
end
Now, notice that calling whos from the command window will show you that only the valid variables has been created.
2 Comments
Matz Johansson Bergström
on 22 Jul 2014
This would do what you want:
[num, tekst, raw] = xlsread('Example - 001.xlsx', 'Sheet1', 'H:I');
i=8;
while ~strcmp(raw{i,1}, 'END')
if ~isnan(raw{i, 1}) & strcmp(class(raw{i, 1}), 'char')
if ~isnan(raw{i, 2}) & strcmp(class(raw{i, 2}),'double')
eval(sprintf('%s = raw{i, 2};', raw{i, 1}));
else
fprintf(1, '%s has not been specified correctly\n', raw{i,1})
end
else %no letter, just ignore
end
i = i + 1;
end
disp 'Example is OK'
clear i num raw tekst
More Answers (3)
Matz Johansson Bergström
on 22 Jul 2014
Edited: Matz Johansson Bergström
on 22 Jul 2014
Edit: I fixed the while so it uses Raw instead of tekst.
I think this code will do what you want. I only consider the example file and print out the errors and warning etc. but it can easily be extended to process several files.
I use the raw data, because Matlab returns empty strings if there are digits in tekst in http://www.mathworks.se/help/matlab/ref/xlsread.html#outputarg_txt they say
"Text data, returned as a cell array. Numeric values in inner spreadsheet rows and columns appear as empty strings, '', in txt.".
[num, tekst, raw] = xlsread('Example - 001.xlsx', 'Sheet1', 'H:I');
i=1;
while ~strcmp(raw{i,1}, 'END')
fprintf(1,'i= %d ', i)
%nan is a double, so we have to take care of it directly
if ~isnan(raw{i, 1}) & strcmp(class(raw{i, 1}), 'char')
if ~isnan(raw{i, 2}) & strcmp(class(raw{i, 2}),'double')
fprintf(1, 'Found character(s) and digit pair\n')% %s', char(raw{i,1}))
else
fprintf(1, 'Found character(s) but no digits\n')
end
else %no letter, just ignore
fprintf(1, 'Didn''t find anything\n')
end
fprintf(1, 'raw value:\n')
raw(i, :)
fprintf(1,'\n--------\n')
i=i+1;
end
0 Comments
Bart
on 22 Jul 2014
2 Comments
Matz Johansson Bergström
on 22 Jul 2014
Edited: Matz Johansson Bergström
on 22 Jul 2014
Modified my answer above, I missed that I switched from tekst to raw at one point.
I only gave you the skeleton code with printouts so you could see how the different cases could be built from strcmp.
The question is: how would you like the store the data, as a binary vector? A cell of valid character strings?
I would solve it by using a matrix, say: valid(i,j) = 1; in the first if statement for the jth excel file I go through. It all depends what you wish to do with the data afterwards.
See Also
Categories
Find more on Get Started with MATLAB 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!