Handling excel file

I need to read an excel formated data into Matlab. This data file contains 33757x50 cells, and there are several nonconsecutive rows and columns that hold characters. What I need finally is a sub-sheet, say, the 3,7,10,11,14,19,25,27,31,...,232,300th rows from the original sheet of the excel file. the excel read command >>xlsread(filename,sheet,range) does not have such kind of function since the option 'range' should be consecutive, e.g., B2:F50. Any good suggestions are appreciated!

Richard

[Walter Roberson]

It sounds to me as if something like a mysql database would be more appropriate for your needs.

Answer 1

[Matt Tearle]

Why not import everything, then strip out any blank rows?

[~,~,x] = xlsread('foo.xls');
x(all(cellfun(@isempty,x),2),:) = [];

[Changqing Xu]

Thanks for your response.

The problem is: there is no blank row in matrix x. Let's put like this:

through some kind of analysis on x, we find that some rows of x, say, the 3,7,10 etc.are what we need. So we choose these rows and the output shall be a sub-sheet consisting of these rows. Can we selectively produce this sub-sheet by matlab?

[Matt Tearle]

OK, so if you know which rows you need, again, why not just extract them?

idx = [3,7,10,...];

x = x(idx,:);

[Changqing Xu]

Thanks! But this does not work since the excel sheet contains different classes of data (e.g. char and numeric), and who is char and who is numerical is not so clear. The first job we shall do, I think, is to separate the char from the numerical data and save them by different variables which obviously cannot be fulfilled by xlsread; Based upon some analysis on the numerical part, we then come to the original sheet and choose some rows to form the sub-sheet, which is our destination.

[Matt Tearle]

See Chris Hinkle's comments. x in my example is a cell array of everything, so the indexing I showed works just fine, as long as you know what idx is. You could also investigate using the is* functions and/or cellfun. E.g. c(~cellfun(@isnumeric,c(:,1)),:) = []; would remove any rows where the first column is not numeric.

Answer 2

[Oleg Komarov]

Other than importing the entire excel as Matt suggest (which I would go for unless the number of rows selected is just a tiny part of a huge excel), it would require to go to the low level using actxserver.

Then you can select specific ranges as this tutorial shows: How to select cells/ranges by using Visual Basic procedures in Excel

Oleg

Answer 3

[chris hinkle]

Some tips

1. Xlsread allows you to bring in raw numeric and text [Numeric text raw] = xlsread() so everything will be parsed for you

So once you have this you will see numeric is an array of doubles and text is a cell array of strings while raw is a cell array of mixed types

2. Depending on your selection criteria you need to use the find command with criteria for numbers or if for example you wanted to get rid of rows with empty fields get the indices by using strcmp, this DOES work with cell array of strings. Hope this helps