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

Jon
2013 4 4
2 个回答
回答已采纳
5 次查看（30 天）

0 个投票

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.

请先登录，再回答此问题。

 采纳的回答

Peter Perkins
Peter Perkins 2013-4-5

0 个投票

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.

1 个评论
显示 -1更早的评论 隐藏 -1更早的评论

Jon
Jon 2013-4-5
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.

请先登录，再进行评论。

更多回答（1 个）

Kye Taylor
Kye Taylor 2013-4-4

0 个投票

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

3 个评论
显示 1更早的评论 隐藏 1更早的评论

Jon
Jon 2013-4-4
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
Kye Taylor 2013-4-4
编辑：Kye Taylor 2013-4-4
Then try
d = dataset('XLSFile',yourFileName,'Sheet','mysheet','ReadVarNames',false)
Can you be more specific about where the data starts in the sheet?
Jon
Jon 2013-4-5
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.

请先登录，再进行评论。

类别

在 帮助中心 和 File Exchange 中查找有关 Spreadsheets 的更多信息

产品

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by