2020a readtable error when specifying rectangular range

Question

VB ABQ 2022-6-8

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1736520-2020a-readtable-error-when-specifying-rectangular-range

编辑： dpb 2022-6-10

I am trying to read a .xlsx file with readtable specifying a rectangular range. I get the error shown below. I have tried reading other .xlsx files with the same result. When I do not specificy the rectangular range or when I specify only the starting cell it reads the .xlsx file OK.

ChTableXLS is a string with a path to the .xlsx file

Below is a screen shot of the .xlsx sheet I am trying to read. It is an example of a longer file - only 200 rows.

Is this a known bug in 2020a readtable? I did not find it in the bug list.

I know I can read the full sheet in and only keep what I need as a work around, but the options should work. Perhaps I am doing something wrong? I don't see anything wrong in the .xlsx files I tried.

Thank you

dat = readtable(ChTableXLS,'Sheet','Sheet3','Range','C1:F4','PreserveVariableNames',true);
Error using readtable (line 198)
First input must be either a character vector or a string scalar.

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

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

Answer 1

Stephen23 2022-6-8

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1736520-2020a-readtable-error-when-specifying-rectangular-range#answer_981730

编辑：Stephen23 2022-6-8

在 MATLAB Online 中打开

'DataRange','C2:F4', 'VariableNamesRange','C1:F1'

10 个评论
显示 8更早的评论隐藏 8更早的评论

dpb 2022-6-8

编辑：dpb 2022-6-10

"...why TMW is getting rid of xlsread. They recommend NOT using it, but it is a very simple to use function to read .xlsx files."

Well, "yes and no" -- if the file is regular and consists of all numeric or all text data, then agreed it is pretty simple. Take a file with mixed data as is so often the case with Excel spreadsheets, however, and it can quickly become a nightmare -- the numeric and text return variables don't always line up with each other in having the same number of rows and columns so you can't reliabley reference the two arrays with same indexing expression and get the corrollary data as is on given row in the spreadsheet.

In that case you're reduced to reading the raw data alone and then that leaves you with the problem of having to do all the conversions on the numeric data that, potentially being scattered around can be a pain to address because the various conversion routines don't play nice with nonnumeric input so you can't just pass the column data..."been there, done that!"

The table class and readtable have been a major step forward in dealing with such -- it comes at a price, granted; performance may not be quite as good, but development time is a minute fraction of what it used to be for the kinds of spreadsheets I've had to deal with over the last 5-6 years with the pro bono work for the local community college foundation.

For simple files, there's readmatrix to get to the base numeric classes directly.

I've never tried to time them comparatively, but I've not yet run into a case where the performance has been an issue; obviously there are cases out there where people have huge spreadsheets where it may be a problem. Where either breaks I don't know; I know in my case with multiple years of data with a workbook for every month of the year and with 5-10 sheets per workbook, the table has make life much simpler and can process those for as much as 20 years of historical data quite within reasonable time.

The only place I've found either breaks down is on the writing side -- but xlswrite has the same problem in that it opens/closes the file for every call so if one cannot arrange to have all the data to be written in just a few chunks at a time; it will bring the system to its knees to try to iterate over a spreadsheet cell-by-cell.

In that case, one has to resort to ActiveX -- there are FEX submissions that have modified xlswrite to not close the ActiveX connection automatically, but to open the file/create the connection first, then do all the output writes and close the connection/save the workbook when done. I've also used that technique before the new functions -- and still find occasoinal need to write ActiveX directly to do things that do require either lower level access (like comments/formulas) or do need to write by cell instead of by array and so the open/close overhead can't be tolerated (it will, in fact, actually hang/crash a system to try it, been there/done that exercise, too!).

I've not really missed xlsread/xlswrite -- I would expect that they'll never be completely dropped because of compatibility, but looks like their development is not going to ever progress. That might eventually mean they quit working if MS were to modify Excel sufficiently -- I don't know that TMW has said they'll continue to keep them working even if don't make upgrades/enhancements.

dpb 2022-6-8

"I will have to upgrade to the most current B version"

NB: There's nothing particularly magic about -b versu -a; I just limit the pain/time consumed to go to a new version to no more than annually.

I just mentioned it here because I hadn't seen the symptom -- and it was apparently an aberration that occurred with the R2020a version; I had used the table extensively prior to it and don't recall ever noticing or having the problem. I just don't have any earlier releases installed against which to check.

IOW, that I hadn't seen this issue was pure luck only...

dpb 2022-6-9

"there are FEX submissions that have modified xlswrite to not close the ActiveX connection automatically, but to open the file/create the connection first, then do all the output writes and close the connection/save the workbook when done. "

This feature would be a most welcome enhancement to the writeXXX class of functions; it would be ideal if the ActiveX session handle could be a persistent variable internally and there be another optional named parameter to let one control the Open/Close status programmatically. Does add a layer of UI complexity and the onus upon the user code to ensure proper synchronization/use, but would be HUGE in potential performance gains -- plus, if had access to the handle, one could then do other customizations at the same time.

请先登录，再进行评论。

Answer 2

dpb 2022-6-8

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1736520-2020a-readtable-error-when-specifying-rectangular-range#answer_981690

在 MATLAB Online 中打开

The error isn't anything to do with the 'Range' argument; it's the file name ("First input must...")

"ChTableXLS is a string with a path to the .xlsx file"

It's almost guaranteed to be a cellstr variable, then. While this is an annoyance and I fail to see why TMW doesn't expand the input parsing to handle it, the input file name must either be the dereference cellstr variable content or a string variable, NOT a cellstr() variable.

dat = readtable(ChTableXLS{:},'Sheet','Sheet3','Range','C1:F4','PreserveVariableNames',true);

or

dat = readtable(string(ChTableXLS),'Sheet','Sheet3','Range','C1:F4','PreserveVariableNames',true);

will either work, I'll betcha'....

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

VB ABQ 2022-6-8

在 MATLAB Online 中打开

Thanks, but readtable works fine when I omit Range. This statement works fine.

I found readcell does the same thing, BTW

dat = readtable(ChTableXLS,'Sheet','Sheet3','PreserveVariableNames',true);

请先登录，再进行评论。

Answer 3

VB ABQ 2022-6-8

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1736520-2020a-readtable-error-when-specifying-rectangular-range#answer_981735

在 MATLAB Online 中打开

Thanks for the idea, but it did not work :-(

But specifying only the 1st .xlsx cell 'C2' is OK

>> dat = readtable(ChTableXLS,'Sheet','Sheet3','Range','C2:F4');
Error using readtable (line 198)
First input must be either a character vector or a string scalar.
>> dat = readtable(ChTableXLS,'Sheet','Sheet3','Range','C2');

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

dpb 2022-6-8

在 MATLAB Online 中打开

I've never experienced such a situation -- looks like the error message may need some fixup to reflect the actual problem.

To debug this would need the file -- attach the .xls file using the paperclip icon.

Just for satisfying curiosity, what does

whos ChTableXLS

return?

Have you tried

dat = readtable(ChTableXLS,"Sheet","Sheet3","Range","C2:F4");

? Just to see if a string vis a vis char() makes any difference -- wouldn't expect to.

Has the range actually had anything entered in it? I rarely use ranges on reading preferring to just clean up later; when I do, it's almost always just to limit a column range rather than a preset rectangular range.

If I do have such specialized kinds of requirements I almost always end up using an import options object and any such range would end up being defined there.

But, I have used the syntax on the rare occasion and have never seen the symptom so 'tis a puzzle off top of head, yes.

请先登录，再进行评论。

2020a readtable error when specifying rectangular range

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

10 个评论
显示 8更早的评论隐藏 8更早的评论

更多回答（2 个）

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

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

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

2020a readtable error when specifying rectangular range

0 个评论 显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

10 个评论 显示 8更早的评论隐藏 8更早的评论

更多回答（2 个）

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

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

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

10 个评论
显示 8更早的评论隐藏 8更早的评论

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

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