Help with excel to matlab

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

Answer 1

[Matz Johansson Bergström]

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.

[Bart]

Wauw, thank you! The last code I'm going to use. That is exactly how I want it.

Now, there are just two things which has to be a littlebit different. (one was added later by my supervisor)

If I use this code, every value is displayed in the command window like below:

A =

       1

B =

       2

C =

       3

etc.

This can be left away, but I can't find how I can do this. I know it is in this part:

eval(sprintf('%s = raw{i, 2}', raw{i, 1}));
          else
              fprintf(1, 'Found character(s) but no digits\n')

But just leaving some parts out is not the solution. Maybe sprintf has to be replaced by something else?

Furthermore, the second one is the part where you show 'found character(s) but no digits'.

This part has to be shown in the command window. But now, I can't see which variable is not found. In the excel file, "y" does not have a number. Is it possible to show something like: "Y doens't have a number".

Because of the fact that I have a really long list of variables, and my simulink model will not run if a variable specified doesn't have a number, I can quickly see which variable gives the problem.

The best solution would be if I would get a list in the command window after running the code, for example as follows:

M has not been specified correctly
Y has nog been specified correctly

Is this possible?

I'm sorry if I'm asking to much from you. But you are a really great help. If this works, a lot of stress is falling from my shoulders. (because I have more than 15 worksheets, with more than 100 variables each...).

Thank again for your great help.

Bart

ps. My (your) code so far:

[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, 'Found character(s) but no digits\n')

            end

        else %no letter, just ignore

        end
        i = i + 1;

end

disp 'Example is OK'

clear i num raw tekst

[Matz Johansson Bergström]

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

Answer 2

[Matz Johansson Bergström]

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

Answer 3

[Bart]

Thank you for your answer Matz Johansson Bergström ,

This is a start in the right direction. Thank you for that.

A few remarks:

This is my outcome (part of): (I changed i = 1; to i = 8; because that is my starting point)

    i= 8  Found character(s) and digit pair
    raw value:

    ans = 

        'A'    [1]

    --------
    i= 9  Found character(s) and digit pair
    raw value:

    ans = 

        'B'    [2]

    --------
    i= 10  Found character(s) and digit pair
    raw value:

    ans = 

        'C'    [3]

etc.

When Column H as well as Column I has a tekst and number, this must be stored in the workspace instead of in the command window. Maybe I wasn't clear about that. So after running the script, I want all the data in the workspace (because the data has to be used in Simulink).

Only the data which has only a text in column H and no number in I, has to be shown in the command window as an error.

Also i noticed that the code is only going to "22", instead of going to "END". I can't find the problem...

I hope you can help me further with this. But I really think this is the way to do it. Thank you!

Bart

[Matz Johansson Bergström]

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.

[Bart]

I'm sorry, but I lost you here. (newbe...)

After running the script, and I will type A into the command window, the return has to be 1. And B, 2 etc.

How do I store the data directly into the workspace? And I'm not sure what to do with valid(i)=1..

Thank for your help! I really appreciate it!

Bart

Answer 4

[Bart]

Thank you very much for your help! This is really perfect and exactly the way I was picturing it!

I appreciate the time you are giving up for me.

Thanks again and have a really nice day.

Kind regards, Bart

[Matz Johansson Bergström]

No problem, glad I could help.