Extracting specified data from Excel

Hi! First of all: English is not my first language, but I hope I can describe my problem good enough for you. I have some excel sheets. These excel files contain several information like item number, date, measured value, test characteristics and so on. My final goal is to make several plots for every item number depending on the test characteristics (every item number has the same test characteristics) containing the measured values and the should be values. So my question is: Are there any possibilities I can realize that? The easiest would be, if there was a command I can type in the item number, test characteristic and maybe the date and matlab searches for the measured value and puts them in a vecor or matrix or something. I am pretty new with this and I really hope I could explain my problem well enough.

I am thankful for every input I can get.

thanks in advance

[dpb]

Certainly it's doable but not by searching in the Excel workbook...instead, read the data into memory and do the searches from there.

It would ease our task a bunch if you'd attach a short sample of the data (or the spreadsheet itself if it's not just huge; of course a sample works just as well).

BTW, your English is surely not bad... :)

Oh, look at

doc xlsread  % first how to read data

It's quite possible the table data structure might be very handy for what you're trying to do; look at doc/examples for it...

[HaDu]

first of all thanks! I prepared an example of how it could be, but of course much smaller. When you open the file, you will see for some measurements just a 1. At this point I am not quite sure what these have to say. my guess is, it just means they are tested by their functionality and are okay. so for now I ignore these and try to figure out later what I do with these. Also in this example the item numbers are kind of sorted, normally this is not the case.

In my first excel file there was just one item number and one test characteristic. So there was no extracting necessary and I coded this.

[xlsfile,path2xls] = uigetfile('*.xlsx', 'Import-Daten');
import = xlsread(fullfile(path2xls,xlsfile));
NM=import(:,6);
USG=import(:,7);
OSG=import(:,8);
MW=import(:,9);

MW1=[];
for index = 1:size(MW)
     if MW(index)>2
          MW1=[MW1 MW(index)];
     end
end

NM1=[];
for index = 1:size(MW)
     if NM(index)>2
          NM1=[NM1 NM(index)];
     end
end

USG1=[];
for index = 1:size(USG)
     if USG(index)>2
          USG1=[USG1 USG(index)];
     end
end

OSG1=[];
for index = 1:size(OSG)
     if OSG(index)>2
          OSG1=[OSG1 OSG(index)];
     end
end

MW1=MW1';
NM1=NM1';
USG1=USG1';
OSG1=OSG1';

plot(NM1);
hold on
plot(USG1);
hold on
plot(OSG1);
hold on
plot(MW1);

My guess is this was not the most elegant way of doing this, but it worked. I will study the documentation for xlsread and try to find something interesting.

thanks again I am looking forward to read from you again :)

[dpb]

While your English is really quite good, one educational point is in "I hope I can describe my problem good enough for you". This should be "well enough" rather. To see why, see--

<Adjectives - adverbs: "good" vs. "well">

Again, though, this is a pretty minor point in understanding but just as a sidebar... :)

Answer 1

[HaDu]

Thank you dpb. This helps really really much! I will try some things out and try to sort out how to plot the data, I really need. Thanks again! There is just one question: In my example.xls the characteristic column was kind of just numbers. Is there a chance, I can 'search' for them, if they are a string, like 'gewicht' or 'zentrierborhung d1'?

with

AN = import(import.Artikel_Nr_ == 1000003132, :)

I can create a new table AN from the table import with just Artikel_Nr ==1000003132 I would like to do the same with strings instead of numbers

Thanks again, you are really helping me out here!

[dpb]

Sure, use one of the string comparison functions; precisely which will depend on just what the form is and what/how you need to do the matching.

Try

help strfun  % to see catalog of string functions.

I don't know what import is as a Matlab function for use other than for library loading--is there some database toolbox or something that overloads the builtin function or did you name a function import or get a file from somewhere else?

[HaDu]

'import' is a table, which i got from the excel file like the one above. Seems like

PM1 = AN(strcmp(AN.PrMerkmal, 'Gewicht'), :);

worked fine. Thanks again! Do I have to convert the table to a Matrix to plot, or is there a way to plot directly some columns?

with

data=PM1(:,6:9);
hL=plot(data);

I get an error. PM1 is the table I got and :,6:9 the numbers I want to plot.

So far (thanks to you) it looks like this

tab=readtable('Messwerte CX50-380 KW 13.xlsx', 'Range', 'A3:M87');   
tab.Properties.VariableNames(1:6)={'AuftrNr','ArtNr','Name','Datum','PrMerkmal','Sollwert'};
tab.Properties.VariableNames(9)={'Messwert'};

tab.Datum=datetime(tab.Datum,'Format','dd.MM/yyyy hh:mm:ss');

AN = tab(tab.ArtNr == 1.000000271000000e+09, :);
PM1 = AN(strcmp(AN.PrMerkmal, 'Gewicht'), :);
PM2 = AN(strcmp(AN.PrMerkmal, 'D10 Passung Deckel'), :);

data=PM1(:,6:9);
hL=plot(data);

Edit: I got it! I had to A = table2array(data) and plot it after that. I can't tell you how grateful I am, you helped me with that!

[dpb]

"I had to A = table2array(data) and plot..."

Shouldn't have to do that extra conversion, no. What release of Matlab are you using? At least by R2014b (and I think R2012b altho I didn't go back to make absolutely sure) plot was table-aware.

Read the section in the Examples under tables Access Data in a Table". If you use parentheses, the result is another table but similar to cell arrays curly braces or "dot indexing" will return the underlying data.

hL=plot(data{:,6:9})

should work directly.

Answer 2

[dpb]

OK, not a bad effort for first time... :)

IF the data are as the sample spreadsheet shows such that the uninteresting values are NaN and the four columns of interest are directly correlated as it appears they are, then

data=import(:,6:9);                  % pick up the area of interest
data=data(isfinite(data(:,1)),:);    % save only those rows with finite values in first column
hL=plot(data);                       % plot the four columns
label(hL,'MW1','NM1','USG1','OSG1')  % and label the lines

The "tricky" part above is that isfinite(data(:,1)) returns a logical vector that is True for the positions matching, False elsewhere and that vector is the row address in the subscripting expression for the data array and the ':' means all columns. Look up "logical indexing" in the documentation for more discussion on details, but is extremely important in how to use Matlab vector operations effectively which is one of (if not the) prime strengths.

One stylistic note, once you've set hold on, it can't get any "on"-er; no need to repeat that multiple times.

If the title row had useful names for all the columns, you could read those from the spreadsheet as well if you also returned the text and/or raw optional return variables from xlsread

ADDENDUM

Been tied up...here's a start using tables--

tab=readtable('example.xls');  % read in as a table

% Make variable names cleaner  to be simpler to use
tab.Properties.VariableNames(1:6)={'Job','Number','Name','Date','Characteristic','Target'};
tab.Properties.VariableNames(9)={'Measured'};

% Remove NaN records
tab=tab(isfinite(tab.Target),:);

% convert appropriate variables to categorical and datetime...
for i=1:3,tab.(i)=categorical(tab{:,i});end
tab.Characteristic=categorical(...
      cellfun(@(c) sscanf(char(c),'characteristic %d'), ...
                                      tab.Characteristic)); 
tab.Date=datetime(tab.Date,'Format','MM/dd/yyyy hh:mm:ss a');

The above leaves a cleaned-up table to work with that looks like--

>> tab
tab = 
    Job       Number     Name              Date             Characteristic    Target    USG      OSG     Measured    Var10       Var11        Var12    Var13
  ________    ______    ______    ______________________    ______________    ______    ____    _____    ________    _____    ____________    _____    _____
  66403003    12345     Deckel    03/30/2017 02:06:37 AM    2                  60       58.2     61.8    59.6        NaN      'plot these'    NaN      NaN      
  66403003    12345     Deckel    03/30/2017 02:06:36 AM    2                  60       58.2     61.8    59.6        NaN      ''              NaN      NaN
  66403003    12345     Deckel    03/30/2017 02:06:34 AM    2                  60       58.2     61.8    59.6        NaN      ''              NaN      NaN
  66403003    12345     Deckel    03/29/2017 01:38:58 PM    4                 121        121    121.2       0        NaN      ''              NaN      NaN
  66500003    6789      Spule     03/24/2017 07:37:31 AM    6                  22       21.6     22.4    22.2        NaN      ''              NaN      NaN
  66500003    6789      Spule     03/24/2017 07:37:29 AM    6                  22       21.6     22.4    22.2        NaN      ''              NaN      NaN
  66500003    6789      Spule     03/24/2017 07:37:28 AM    6                  22       21.6     22.4    22.2        NaN      ''              NaN      NaN
  66500003    6789      Spule     03/23/2017 10:48:54 PM    6                  22       21.6     22.4    22.2        NaN      ''              NaN      NaN
>>

Now you can do things like--

>> varfun(@mean,tab,'InputVariables',{'Target', 'Measured'},'GroupingVariables','Characteristic')
ans = 
       Characteristic    GroupCount    mean_Target    mean_Measured
       ______________    __________    ___________    _____________
  2    2                 3              60            59.6         
  4    4                 1             121               0         
  6    6                 4              22            22.2         
>>

Note the functional definition in varfun can be any function, not just a builtin as mean shown above.

[HaDu]

Hi dpb! First of all thank you for your effort. I really appreciate that. The difficulty with your solution is I can't pick the item characteristic, the item number or the date, for which purpose I want the plot to be made. For example: I have an item number and a production date. Now i would like to figure the individual mass of the products out. matlab plots the measured values, USG, OSG and the nominal measures. In that case I would see directly if there were any problems with the mass on a specific day for a specific item number. Is there a possibility I can manually name my 'variables' like item number, date and item characteristic and matlab plots MW, NM, USG and OSG?

Thanks again. I hope I could describe my problem and I hope there is a way of solving it.

[dpb]

Well, I just duplicated your code using "more Matlab-y" syntax... :)

Sure, as noted, it looks like a table might be useful for the kinds of thinks you're after...