xlsread with changing starting point

Hello all,

I've had some questions before, and everytime I did get an answer, so now I will try it again.

I'm using matlab to get some data out of a Excel document.

At the moment I'm using this code:

 dir_struct = dir('*.xlsx');

 for i = 1:numel(dir_struct)
  number(i) = str2double(dir_struct(i).name(10:(10+numel(dir_struct(i).name)-16))); % get the number-string out of the filename and turn it into a number (I have fixed the format there)
 end

 [revised_numbers, order] = sort(number);

 for i = 1:numel(order)
  [num, tekst, raw] = xlsread(dir_struct(order(i)).name,'MISC','B9:C16');

I've received this code in a question earlier. I wanted to use xlsread, but the excel file changes in name over time, so the part where the excel file is mentioned, is now numerical.

This code works fine for me.

My next problem has this code involved.

In the worksheet MISC, the matrix defined is now B9:C16. B9 is i=1, B10 is i=2 etc.

Now, the start of i=1 changes. At the moment, B9 is i=1. But in the future, B12 or B13 for example can be i=1.

What I want is the following code, if possible ofcourse.

Above the data that I want, there is always the text: "Simulation input".

Is it possible for matlab, to 'search' the column B for this text, and set that cell to i=1? So that the place of the start can be everywhere in column B.

I hope that this is clear for you to understand. If not, I will change it and I will try to make it more sence.

I would like to thank you in advance!

Greets, Bart

Answer 1

[Image Analyst]

You can use ismember() to search your column for particular text string. It will give you the row(s) where it was found. Then set them to 1.

[Image Analyst]

[num, tekst, raw] = xlsread(dir_struct(order(i)).name,'MISC','B9:C16');
columnB = txt(:,2)
simulationRow = find(ismember(columnB, 'Simulation input'))
topRow = simulationRow+1;

It's a little trickier than that because the arrays don't all start at the same row in Excel. That is, if the first number is at row 3 and the first text is at row 8, for the num cell array num(1,1) will come from row 1 while txt(1,1) is not from row 3, it's from row 8.

[Bart]

Sorry for the late response, again... I'm going to try the above code in my code. I will respond soon, if it worked or not. But nevertheless, thank you for your time and help!

Bart

[Bart]

I've tried to use youre code. I think it can work. But I get the following error:

Undefined variable txt.

Error in PI_342W2_TLB_test (line 18)
    columnB = txt(:,2)

I have to devine txt? But how do I have to do that? I don't know where I have to refer to...

Thanks again! Bart

ps. PI_342W2_TLB_test is the name of the matlabfile.

[Ilham Hardy]

the txt should be tekst, i think..

So,

columnB = txt(:,2)

needs to be..

columnB = tekst(:,2)

[Bart]

Thank you, I've tried that, but I'm getting the same error. Again, the undefined variable tekst.

Bart

[Image Analyst]

Let's see your code. Apparently you're not spelling txt, tekst, or text consistently. Let's just go with txt, which is how the MATLAB help calls it.

[Bart]

You were right. I had two forms of txt and tekst.

This is the start of my code:

   dir_struct = dir('*.xlsx');

   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,'Test TLB','B9:C88');
      columnB = tekst(:,2)
      simulationRow = find(ismember(columnB, 'Simulation input'))
      topRow = simulationRow+1;

i=1;

if strcmp(deblank(tekst(i,1)),'TLB.A1')
    TLB.A1 = num(i,1);
else
    disp('TLB.A1 is not specified correctly')
end

i=i+1;

When I run this, I get the following message:

columnB = 

      'TLB mass'
      'x coordinate TLB'
      'y coordinate TLB'
      'z coordinate TLB'
      'TLB short tail mass'
      'x coordinate short tail'
      'y coordinate short tail'
      'z coordinate short tail'
      'TLB long tail mass'
      'x coordinate long tail'
      'y coordinate long tail'
      'z coordinate long tail'
      ''

etc. This is indeed my excel file. next:

simulationRow =

      23

TLB.A1 is not specified correctly
TLB.Iyy1 is not specified correctly
TLB.Izz1 is not specified correctly
TLB.Ip1 is not specified correctly

etc.

Now, the Simpulation input tekst is at row 32. And my first data that i need to store in the workspace is TLB.A1. And this is on row 34.

So, the message that TLB.A1 is not specified correctly, has to do with the counting. But all the rest must not be shown, because I have a lot of these m-files, and I just need the data stored in the workspace, and I don't need the part that it is showing "ColumnB" and "Simulationrow =".

Do you understand what I mean? Or do I have to specify it better?

Thanks again for your help! I realy appreciate it!

[Image Analyst]

Attaching your workbook to your message would make it easier if someone wants to try something. I can't get to it for several hours, if at all.

[Bart]

Excuse me, here are my two files.

In excel, the data that I need are under Simulation input. I can only upload .xls, so i've changed that in my m-file also. I hope that it still works.

Thanks again. Greets, Bart

Answer 2

[Bart]

Hello,

Excuse me for my late answer!! I will try to use your answer, thank you for that.

If I understand it correctly, I can put the code:

ismember(Simulation input)= 1

in front of my start with i=1 etc? (I'm not that good with matlab, so maybe I need a little more help with this than intended...)

Thank you in advance!

Bart