XLSREAD: "Subscript indices must either be real positive integers or logicals."

Hello,

I get this error when trying to open XLSX files created by LabVIEW using the function XLSREAD() to open them.

I am calling the function as follows:

[NUM TXT RAW] = xlsread(filepath);

I am running MATLAB r2016a on Mac OS X Sierra, and the excel files are created via LabView 2016 on Windows 7. I have been able to get around this by opening the XLSX file with Excel, and saving it again.

While this does work, it slows down the process of parsing experimental data. I have been unable to locate any solutions via google search, since the error "Subscript indices must either be real positive integers or logicals" is common and not necessarily specific to XLSREAD.

I have attached an XLSX file that throws the error.

Any help appreciated.

[Walter Roberson]

Unfortunately the file did not get attached.

[Christopher Jones]

Can't seem to get it to work properly.

Here is a google drive link

https://drive.google.com/open?id=0BxHjoOeQvWjTbEZ4dHZUZ2lkTk0

[Walter Roberson]

Okay, it will take me a few minutes to install R2016a on my Sierra virtual machine to check this with.

[Christopher Jones]

Incredibly generous. Thanks for taking the time

Answer 1

[Walter Roberson]

.xlsx files are .zip files that contain directories and a series of different .xml files, including information about the different worksheet names.

The routine to parse the worksheet names is expecting that the XML will look like similar to

<sheet name="sheet1" sheetId="1" r:id="rId1"/>

but instead in that file it looks like

<sheet name= "sheet1" sheetId="1" r:id="rId1"/>

notice the space between the = and the " that follows it.

I do not know enough about the XML standards to know whether a space there is permitted or not, but what we do know is that in those files the space is there . And so whether it is "proper" or not, we can hack around that part of the problem fairly easily.

edit( fullfile(toolboxdir('matlab'),'iofun','private','getSheetNames.m') )

and head to line 19, which will have

      match = regexp(workbook_xml, '<sheet[^>]+name="(?<sheetName>[^"]*)"[^>]*r:id="(?<rid>[^>]+?)"[^>]*/>|<sheet[^>]*r:id="(?<rid>[^>]+?)"[^>]*name="(?<sheetName>[^"]*)"[^>]*/>', 'names');

Change that to

      match = regexp(workbook_xml, '<sheet[^>]+name=\s*"(?<sheetName>[^"]*)"[^>]*r:id="(?<rid>[^>]+?)"[^>]*/>|<sheet[^>]*r:id="(?<rid>[^>]+?)"[^>]*name=\s*"(?<sheetName>[^"]*)"[^>]*/>', 'names');

And save the file. You should then be able to xlsinfo() the file.

(You might need to do a "rehash toolboxcache", maybe)

However, more is needed to be done to xlsread() the file. It appears the xml does not have a dimension declaration; I will need to investigate how to deal with that.

[Christopher Jones]

Great investigative work! Thanks for spending the time on this. I wonder if this issue is due to a bug in LabVIEW when the excel files are created.

I'll tinker with the function and try to get things to work

[Walter Roberson]

Technical references:

https://msdn.microsoft.com/en-us/library/office/documentformat.openxml.spreadsheet.worksheet.aspx

 <http://standards.iso.org/ittf/PubliclyAvailableStandards/c061750_ISO_IEC_29500-1_2012.zip>

[Walter Roberson]

The next adjustment is more complicated

edit( fullfile(toolboxdir('matlab'),'iofun','private','xlsreadXLSX.m') )

at line 197, inside function extractDataAndRange you will find

        if isempty(span.end)
            span.end = [':' span.start];
        end

This is in error for the case that there is no dimension parameter stored in the spreadsheet. The dimension parameter is optional according to section 18.3.1.35 of IEC 29500-1, which you can find on page 1531 of http://standards.iso.org/ittf/PubliclyAvailableStandards/c061750_ISO_IEC_29500-1_2012.zip

In the extractDataAndRange code, when there is no dimension parameter, the regexp just above will come out empty, and then the isempty(span.end) fails because span itself is empty .

Unfortunately, code later on, in the routine getRangeCorners in the same file, counts on there being valid range information.

Perhaps it would have made sense to change getRangeCorners to accept some kind of "empty" range and let it emit some special values, and then change the code that calls upon getRangeCorners to accept those special values. But I did not do that. Instead I choose to analyze the XML to figure out which rows and columns are used in the spreadsheet, and generate a bounding box around that.

The replacement code to put in for those three lines quoted above starting at line 197 is:

        %{
        if isempty(span.end)
            span.end = [':' span.start];
        end
        %}
        if isempty(span)
            %rowrefs = regexp(sheetData,'<row\s+[^>]*r="(?<rowref>\d+)">','names');
            colrefs = regexp(sheetData,'<c\s+[^>]*r="(?<clets>[A-Za-z]+)(?<cnums>\d+)"','names');
            uniq_lets = unique( {colrefs.clets} );
            uniq_nums = unique( str2double({colrefs.cnums}) );
            if ~isempty(uniq_lets)
                span(1).start = sprintf('%s%d', uniq_lets{1}, uniq_nums(1));
                span(1).end = sprintf('%s%d', uniq_lets{end}, uniq_nums(end));
                range = [span.start ':' span.end];
            else
                range = [];  %we could not figure out the range, now what?
            end
        else
            if isempty(span.end)
                span.end = [':' span.start];
            end
            range = [span.start span.end];
        end

The line assigning to uniq_nums can take a moment to execute as it can be working on converting large numbers of strings to their numeric equivalents, and then finding the unique values amongst them. The first and last results of the unique() are used, taking advantage of the fact that unique() sorts numerically. (Perhaps we could just convert to double and then use max() and min() instead of unique, perhaps that would be faster.)

My first pass at this did unique() on the complete strings, but then I realized that it had sorted to the order A1 A10 A100 A1000 ... and ended with E999999 instead of E100001, and Duh! of course, that is a perpetual problem with sorting strings, '10' always comes before '9' ...

I have tested with the above combination of changes, and it seems to work, except that I am not certain it is getting the txt right. Ah, yes, indeed, it is not extracting the header. That might be a consequence of the way I synthesize the range, perhaps, or might be due to some other reason. Oh, and I see that the time from the first line is returned as a serial date number in the year 112 and so obviously has not been corrected for way Excel stores numbers.

....

Ah, it looks like the entire set of problems could be avoided by using readtable() on the file, at least on R2016b. readtable() seems to get it all right.

[Walter Roberson]

Improved code snippet. This is nearly the same as above, but avoids having to do another run through the entire XML text, making use of data that has already been extracted (now that I understand better how it all works.)

      if isempty(range)
          span = regexp(sheetData, '<dimension[^>]+ref="(?<start>[A-Z]+\d+)(?<end>:[A-Z]+\d+)?"[^>]*>', 'names', 'once');
          %{
          if isempty(span.end)
              span.end = [':' span.start];
          end
          %}
          if isempty(span)
              colrefs = regexp({parsedSheetData.ranges}, '(?<clets>[A-Za-z]+)(?<cnums>\d+)', 'names');
              colrefs = vertcat(colrefs{:});
              uniq_lets = unique( {colrefs.clets} );
              uniq_nums = unique( str2double({colrefs.cnums}) );
              if ~isempty(uniq_lets)
                  span(1).start = sprintf('%s%d', uniq_lets{1}, uniq_nums(1));
                  span(1).end = sprintf('%s%d', uniq_lets{end}, uniq_nums(end));
                  range = [span.start ':' span.end];
              else
                  range = [];  %we could not figure out the range, now what?
              end
          else
              if isempty(span.end)
                  span.end = [':' span.start];
              end
              range = [span.start span.end];
          end
      end

[Walter Roberson]

Once the regexp change is made in xlsinfo to handle the space that LabView puts in, it turns out that the simplest work-around for LabView files is to specify the range to read explicitly. When the user specifies the range to read, then the buggy code in xlsread, the code that crashes because of the missing (optional) property, would not be executed.

Answer 2

[Steven Lord]

You've created a variable with the same name as the function you're trying to call on the line where the error occurs. Set a breakpoint on that line (click on the dash to the left of that line of the file while it's open in the Editor) then run your code. Use the which function to confirm that there is a variable with the name of one of those functions. When you determine the name of the variable, rename or remove that variable in your code.

[Christopher Jones]

Are you replying to the intended thread? Sorry if I'm mistaken, but what you're talking about doesn't seem to make sense in this context.

[Walter Roberson]

Steven's reply is often the cause of such messages, but turns out not to have anything to do what the cause of this particular problem. I have (partly) solved it and will write it up now.

[Steven Lord]

Yes, I jumped to the conclusion that this error was being caused by a variable shadowing a function. That is the most common cause of this error. But it sounds like Walter has done more investigation and uncovered a different cause for this same error. That was my mistake, and I've upvoted his answer to move it above mine.