Using xlsread to select non-consecutive rows from excel spreadsheet?

Hello, I am trying to find out wether it is possible to use xlsread(filename,-1) to select several rows from an excel spreadsheet that are not consecutive? In more detail, from a large spreadsheet, I want to (manually) select all the rows where certain items fulfill certain requirements - but these requirements change everytime I run the program.

For example, once I want to select all rows, where column 5 contains a certain value and column 21 is not empty - those might be rows 84, 319, and 762. An other time, I want to select all rows which contain a value bigger than 1 in column 7, a certain string in column 33, and a different string in column 120. There might be 14 rows left fulfilling those criteria. By using the filtering option in the excel file, I can easily find out which rows I need to select, but the question is: how can I get this information into Matlab?

Since I'm trying to figure this out for quite a while and all the questions and answers didn't give a solution, I really hope someone can help! Thank you in advance!

Answer 1

[Jan]

If you want to apply a condition to the values inside Matlab, you have to import all data at first, check the condition and selected the matching data afterwards. You cannot let Matlab magically consider the values of the data without importing them at first.

[Christine Klei]

Thank you for your answer, but I do not want to apply the conditions inside Matlab. I only want to be able to select rows from an excel spreadsheet which are not consecutive (as for example row 84, 319, and 762, which I preselect manually in an excel spreadsheet. Is it possible to do so by using the xlsread(filename,-1) function or is there a different way to do this?

[Jan]

@Christine Klei: You can find all information in the documentation:

doc xlsread
doc readtable

You can e.g. use a 'NamedRange' in readtable:

 Excel's Named Range

 In Excel, you can create names to identify ranges in the
 spreadsheet. For instance, you can select a rectangular portion of
 the spreadsheet and call it 'myTable'. If such named ranges exist
 in a spreadsheet, then readtable can read that range using its
 name.  Example: 'Range','myTable'

I'm not sure if this matches what you call "preselect manually". The other options are to import a rectangular block and crop the wanted part in Matlab, or to write a function which imports the wanted rows in a loop. Both need a few lines only. I assume, importing the block is more efficient.

If you explain, how the wanted information about the data is available, I'm sure somebody posts an explicit code here. Unfortunately I did not understand this point yet: Where is the information available, which defines the data to be imported?

[Christine Klei]

Hi, thank you so much for your answer and effort!

The information on which data to be imported is not available as such. It's whatever you can imagine or are interested in at this moment.

I'll try to make it clearer by using a simple and more detailed example: If you have a large spreadsheet holding information on zoo animals all over the world - let's assume 10,000 animals, one in each row. Different questions come up in your research group, making it necessary to select different rows out of this large table.

For example in one case, you'd want to select all rows that are a grizzly bear as "type of animal" in a "zoo location" Europe that are of younger "age" than 1 year (with "xxx" being different columns of the table). While I can easily filter the table in excel for this information, receiving for example rows 84, 319, and 762 as a result.

Since each animal has a distinct "ID number" written in the first column of the table, you might in another situation just want to know what is behind the "ID number" 8500 and get all the information of this animal.

But next time, you'd want to know how many of all the zoo animals need a "surrounding temperature" of more than 30°C and do not come from the Australian continent as their "region of origin". There might be for example 150 rows fulfilling this question, which are also not consecutive.

So my question again is: How can I select several non-consecutive rows (hence not a block or range) in the table to be imported to matlab? So what I want to do is to only import the data needed in this case for further processing in Matlab. In fact, something like the xlsread(filename,-1) function, but with the option of selecting single rows (for example 84, 319, and 762) instead of the entire block from 84 to 762).

If there is no possibility to do so by choosing the rows, might it be possible to select in the excel sheet for example the ID number of the cases by clicking on this field and save it to a vector in matlab and then search the table for those entries and get the remaining information? I know the second part of this is possible, but how can I select the content of non-consecutive cells on the spreadsheet and read it into matlab?

I hope the example makes my question easier to understand. Is there any way to do something like that? I would be so helpful to have something like that!

[Jan]

@Christine Klei: The explanation does not clarify, how the information about the wanted rows is available. You explain:

 The information on which data to be imported is not available as such.

This would mean, that the problem cannot be solved even in theory. If you do not have the information, that you want to import the rows "84, 319, and 762", there is no way to import them in Matlab. This would be pure magic.

If you have a mechanism inside Excel to select the data, you can define an already mentioned "named range" and chose it for importing by readtable.

You can write a macro also, which calls Matlab for the current selection. Is this what you are searching for? Then it might be much easier to display the contents of the table in a uitable in Matlab and perform the complete job in Matlab.

It is not clear yet, how you want to interact Excel and Matlab. Would this be a matching summary of your question:

 When I select some data in an Excel sheet, how can I import these
 values into Matlab?

Maybe a database would be much better for your problem than Excel. Sometimes using a weak tool to process the data is a bad choice.

[Christine Klei]

Hi Jan, thank you again for your effort!! A summary of my question would be:

When I select several rows from an excel spreadsheet that are not consecutive, how can I import these (and only these) rows of data into Matlab?

I will try your suggestion of using uitable in Matlab and selecting data from there using maybe an inputdlg to let the user define which requirements for the data selection.

Thank you very much for your help (and please excuse the delay of my reply)!