2020a readtable error when specifying rectangular range

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.

Answer 1

[Stephen23]

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

[VB ABQ]

Oh my goodness! That is it! The named value is "DataRange" not "Range". The documentation says 'Range' Argh %$#@$^

Thank you!!!

[Stephen23]

"The named value is "DataRange" not "Range"."

Both of them are valid options.

"The documentation says 'Range' "

Because RANGE is a valid option. So is DATARANGE. So are several other types of range.

Being more specific about which range means READTABLE needs less automagic file interpretation.

[VB ABQ]

Hi,

Well, that may be, but all I know is that when I use 'Range','C2:F4' it throws the error and when I use readtable with 'DataRange','C2:F4' it is happy.

Thanks again for the insight.

[dpb]

That seems to be a "feature" of R2020a -- I've always used the "-b" releases only so I never ran across it -- by R2020b it takes either...

I HATE it when TMW does stuff like this -- like they changed "HeaderLines" to 'NumHeaderlLines" from the venerable textscan and textread that had been around "since forever" with the new readtable and friends...then to compound that, they also changed the spelling at least one incarnation I recall. It just adds confusion for no reason and has to add to the input parsing complexity to have multiple names meaning the same thing.

Well, at least you did get it resolved...

[dpb]

It also appears to be worth an enhancement/bug report on the bad direction the error message gives pointing to the first argument as being a problem instead of identifying the named parameter it had trouble parsing...

[VB ABQ]

Agreed. Actually I never understood 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.

Thanks for your help. I will have to upgrade to the most current B version of MATLAB. Good idea :-)

[dpb]

"-- by R2020b it takes either..."

OBTW, the tab completion at the command line of available named parameters lists "Range' only; it doesn't show the alternate spelling.

[dpb]

"...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]

"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]

"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]

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'....

[VB ABQ]

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]

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');

[dpb]

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.