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

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

Answer 1

[Jeremy Hughes]

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

[Ben Himes]

I tried using this option, and it looks like it worked! (Code below). I'm not really sure why this worked given what dpb and Guillaume said in the above converstation, but thanks for the tip!

X = rand(1300e3,2);
writematrix(X, 'MyFile.txt');
fn = 'MyFile.txt';

%%% This range works
% First = 1000e3;
% Last  = 1005e3;

%%% This range also works now
First = 1000e3;
Last  = 1200e3;

opts = detectImportOptions(fn);
opts.DataLines = [First Last];
C = readmatrix(fn,opts);

[dpb]

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]

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

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

Answer 2

[dpb]

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

[Ben Himes]

I see, so you're saying the problem is that I can only read in files up to a certain number of rows (even if I am just reading in a small section of the data), and that max row number is determined by my computer's memory/resources.

The problem is that I have many large text files with time series data, and I want to perform analysis of specific portions of them. Do you have any suggestions for how to read in only the sections of the text files that I want, even when these sections are later in the text file (past 1,048,576 rows in the example you gave above)?

[dpb]

The problem/limitation is in Excel, NOT MATLAB. The maximum number of rows in a spreadsheet is the problem.

Hmmm....well, looking at the documetation it says the range named parameter is supposed to be usable for both--I had presumed that would only apply for a spreadsheet.

That appears to then turn it into implementation bug in readmatrix or the documentation is wrong in treating both Text and Spreadsheet files alike using row numbers--but would appear to be bad logic internally in the function in applying the limit algorithm to a text file as well as spreadsheet file.

To use readmatrix, you can first try the workaround to see if you force text mode that it might work by

rnge=sprintf('%d:%d',First,Last);
C=readmatrix('MyFile.txt','Range',rnge,'FileType','text'); % try force to ignore Excel limits

If that still fails, submit a bug report and use textscan with the 'headerlines' and repeat count option.

Or, just read the full array into memory if it will fit and discard the unwanted rows.

[dpb]

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]

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]

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

Answer 3

[dpb]

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

[Ben Himes]

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.