MATLAB Answers

Error using readmatrix - Unable to determine range. Range must be a named range in the sheet or a single cell within 'XFD1048576'.

67 views (last 30 days)
I am using the Name-Value pair "Range" with the readmatrix function to extract a specific range of rows from a text file, and then create a new matrix with these rows. This works well until the range of interest reaches a certain value that I think is somewhere around 1100e3.
In the example code below, if you use the first range set (First = 1000e3 and Last = 1005e3), the code works as expected. If you use the second range set (First = 1000e3 and Last = 1200e3), then I get the error mentioned in the title. Both ranges fall within the range of the original matrix X, so I don't know why this works for some values but not others. Any ideas on hot to fix this? Thanks!
X = rand(1300000,2);
writematrix(X, 'MyFile.txt');
%%% This range works
% First = 1000e3;
% Last = 1005e3;
%%% This range does not work
First = 1000e3;
Last = 1200e3;
C = readmatrix('MyFile.txt','Range',strcat(num2str(First), ':' , num2str(Last)));

  0 Comments

Sign in to comment.

Accepted Answer

Jeremy Hughes
Jeremy Hughes on 4 May 2020
'Range' also accepts numeric inputs [r1 c1 r2 c2], the drawback being you need to specify the columns.
You can specify only the rows using the import options.
>> opts = detectImportOptions(fn)
>> opts.DataLines = [First Last];
>> A = readmatrix(fn,opts)

  4 Comments

Show 1 older comment
dpb
dpb on 4 May 2020
I shoulda' thought of the import options object.
It works where the other doesn't because it (the import object) when passed will bypass the other input checks and assume the parameters in it are correct (at least if not default values, not sure if any other checks might still occur or not).
Your use case is valid by the documentation however and that it fails is still a bug and needs to be reported as such.
Jeremy didn't report whether he filed it or not so I'd recommend to go ahead.
Guillaume
Guillaume on 5 May 2020
"I shoulda' thought of the import options object."
I can't remember if that came with 2020a or before, but note that readmatrix (and other readxxx) now always call detectImportOptions. There's no longer two different behaviours depending on whether or not you called detectImportOptions.
In particular, the error complaining about the range being too big is thrown by detectImportOptionsText.
The error is thrown during validation of the range specified by the user, so indeed not specifying the range but specifying the DataLines works around the problem and is actually more appropriate in the context of this question.
However, I do agree that the limit on range for text files is absurb so I'll be raising a SR for that anyway. (I'm on a roll, that'll be the 4th for the past 7 days). It doesn't hurt if other raise a SR as well. The more people complain, the more likely something will be done about it.
"'Range' also accepts numeric inputs [r1 c1 r2 c2]"
The limitation on range also applies to this syntax.
Jeremy Hughes
Jeremy Hughes on 5 May 2020
@dbp Yes, for the most part import options properties are validated when they are set. Ranges are an exception since the limits are different for XLS vs XLSX type spreadsheets, and named-ranges are supported which depends on the file and sheet. Range limits on text files shouldn't be imposed. I will create a bug report, but that shouldn't stop anyone from making a service request.

Sign in to comment.

More Answers (2)

dpb
dpb on 3 May 2020
Edited: dpb on 3 May 2020
"Worksheet and workbook specifications and limits
Feature Maximum limit
Open workbooks Limited by available memory and system resources
Total number of rows and
columns on a worksheet 1,048,576 rows by 16,384 columns..."
>> MaxRows=1048576;
>> First = 1000e3;
>> Last = 1200e3;
>> [First Last]<=MaxRows
ans =
1×2 logical array
1 0
>>
>> Last=1005E3;
>> [First Last]<=MaxRows
ans =
1×2 logical array
1 1
>>
Seems reasonable result to me given Excel limitations...

  5 Comments

Show 2 older comments
dpb
dpb on 4 May 2020
BTW, I overlooked it earlier but the error message makes the diagnosis clear about the limit -- it has the magic number buried in it:
Range must be a named range in the sheet or a single cell within 'XFD1048576'.
although it is somewhat misleading about being a range or single cell, the upper limit is there. A message saying exceeded that value would be more informative and accurate it appears.
Guillaume
Guillaume on 4 May 2020
To answer a few of the questions raised here, I went through the code of readmatrix. In R2020a, the range validation is specific to each filetype (whether supplied or not). Text files range validation and excel files range validation are completely different code paths. That's the good news.
Bad news, is that indeed the text range validation indeed limits the range to XFD1048576, which doesn't make much sense. There is unfortunately no way to bypass this check.
I suggest raising a service request asking for that artificial limit to be removed. I suspect it's a legacy limit from early implementations of detectImportOptions.
dpb
dpb on 4 May 2020
" I went through the code of readmatrix..."
If you can fight your way thru that maze after the initial dispatch line, power to you...I'm too much an old fogey to be able to even figure out where the pieces are buried, what more read it. I wish TMW had stayed w/ mostly procedural code; the complexities are just more than justified seems to me...

Sign in to comment.


dpb
dpb on 4 May 2020
Given that it appears readmatrix is fatally flawed for you use case, try something like
First = 1000e3;
Last = 1200e3;
L=First-Last+1; % number records to read
buf=cell(L,1); % allocate a cell to hold the input lines
fid=fopen('yourfile.txt');
for i=1:First-1 % get past unwanted records at beginning...
fgetl(fid);
end
for i=1:L % read L wanted records
buf(i)={fgetl(fid)}; % put in cellstr buffer
end
fid=fclose(fid);
You can then pass the buffer to textscan w/ cellfun to convert to char data.
Alternatively, use textscan directly
fmt='fmtstringtomatchinputrecord';
fid=fopen('yourfile.txt');
data=cell2mat(textscan(fid,fmt,L,'collectoutput',1));
fid=fclose(fid);

  1 Comment

Ben Himes
Ben Himes on 4 May 2020
Thank you very much for looking into this! I think this option should work as well. The option suggested by Jeremy Hughes seems to work with the readmatrix function. I'm not sure how that fixed it, but I guess it works.

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!