How can I construct a dataset array from data on an excel worksheet with an unknown number of rows

I would like to construct a dataset array from data on an Excel worksheet. I know the location of the upper left corner of the data and the number of columns, but not the number of rows. So I can't use for example ds = dataset('XLSFile','Sheet','mysheet',Range,'G8:H25') because I don't know where the lower right hand corner is. I do know that the sheet is blank below the last row of data of interest and also blank to the right of the last column of data of interest, with no entirely blank columns or rows in between. So it would be nice if I could just specify the range using the upper left corner, e.g. 'G8', but this is not accepted by the dataset constructor. I would appreciate any solutions that you may suggest.

Answer 1

[Peter Perkins]

Jonathan, it's possible to use named ranges per the XLSREAD reference page, but that may not solve your problem. I don't know of a way to specify a range like b5: without a lower right corner.

Hope this helps.

[Jon]

I did notice the feature you refer to in the documentation, but I would prefer not to have to create all the named ranges as this is extra work and as a manual operation it has the possibility of creating errors. Actually if I just had the Headerlines property that is available when reading text files I would be all set as the only thing on the worksheet is a contiguous table of data with some header rows, with all other cells blank. Its just that I don't know how many rows of data I have in advance. It varies from worksheet to worksheet.

Answer 2

[Kye Taylor]

Just try

d = dataset('XLSFile',yourFileName,'Sheet','mysheet')

[Jon]

Thanks for your suggestion but I have already tried this. The problem is that the rows above the data of interest are not empty. The dataset constructor then tries to make variable names out of the first non-empty row which is not where my data actually starts.

[Kye Taylor]

Then try

d = dataset('XLSFile',yourFileName,'Sheet','mysheet','ReadVarNames',false)

Can you be more specific about where the data starts in the sheet?

[Jon]

Typically the first few rows of the spread sheet have header data that I'm not interested in (for the purpose of making datasets anyhow), and then there is a row that has the variable names. So I don't want to set 'ReadVarNames' to false, because I want to read them, I just don't want it to assume that they are in the first non-empty row. The 'HeaderLines' property that is available when reading text files would be perfect, but unfortunately it is not available when reading Excel files.