MATLAB 2018a readtable VariableNamesLine bug

Good Day All,

I posed this question a long time ago which I can no longer find however there was no conclusive result anyhow I belive. I have taken the simplified the below script to only include the lines pertinent to this question. I am using MATLAB 2018a.

When using readtable to import a CSV file (or multiple) in to MATLAB the VariableNamesLine can be assigned to a numerical value (2,3,4 etc.) corresponding to the row in which the variables are contained in the CSV file. However even with VariableNamesLine set to 3 or any other value, readtable will only take the first row in the CSV file as the variables for the table.

opts=detectImportOptions('C:\Users\Documents\.....CSV');
opts.VariableNamesLine = 3; %Defines the row location of channel variable name
opts.VariableUnitsLine = 4; %Defines the row location of channel units 
opts.Delimiter =','; %Specifies that the data is comma seperated
t = readtable('C:\Users\Documents\.....CSV',opts);

The documentation suggests that assigning the VariableNamesLine in such a manner is exactly how readtable should work.

FYI: I haven't specified DataLines opts seems to find that perfectly well.

"If you specify the ReadVariableNames argument in addition to opts the import options, then the readtable behavior changes based on the specification:

• If ReadVariableNames is true, then read the variable names from the specified file by using the VariableNamesRange or the VariableNamesLine property of the import options object.
• If ReadVariableNames is false, then read the variable names from the VariableNames property of the import options object."

When I check opts in the comand window, VariableNamesLine is set correctly per the below:

opts = 

  DelimitedTextImportOptions with properties:

   Format Properties:
                    Delimiter: {','}
                   Whitespace: '\b\t '
                   LineEnding: {'\n'  '\r'  '\r\n'}
                 CommentStyle: {}
    ConsecutiveDelimitersRule: 'split'
        LeadingDelimitersRule: 'keep'
                EmptyLineRule: 'skip'
                     Encoding: 'windows-1252'

   Replacement Properties:
                  MissingRule: 'fill'
              ImportErrorRule: 'fill'
             ExtraColumnsRule: 'addvars'

   Variable Import Properties: Set types by name using setvartype
                VariableNames: {'Header1', 'Var2', 'Var3' ... and 81 more}
                VariableTypes: {'datetime', 'double', 'double' ... and 81 more}
        SelectedVariableNames: {'Header1', 'Var2', 'Var3' ... and 81 more}
              VariableOptions: Show all 84 VariableOptions 
	Access VariableOptions sub-properties using setvaropts/getvaropts

   Location Properties:
                    DataLines: [6 Inf]
            VariableNamesLine: 3
               RowNamesColumn: 0
            VariableUnitsLine: 4
     VariableDescriptionsLine: 0 
	To display a preview of the table, use preview

Curiously, the VariableUnitsLine parameter works perfectly well.

For reference a typical CSV File may have this format:

edit: Added 'opts' to read table where it was missing (typo).

[Guillaume]

I've always wondered about this. There seems to be a dichotomy with the design of detectImportOptions, since it does the detecting of the variables and populates the Variable Import Properties as soon as you call it. So, by the time you set the VariableNamesLine it is too late, the variables have already been detected, and as far as I know matlab doesn't rerun the variable detecton part of detectImportOptions.

It would be interesting to have some inputs from the developers indeed. Hopefully, Peter Perkins will see this. It's probably worth raising a bug report anyway, as the documentation is indeed lacking.

[John Doe]

I have submitted a bug report now.

My curiosity lays with why VariableNamesLine and VariableUnitsLine behaves differently. I'll keep a close eye on the bug report and feedback in here if I gain any further information.

[Jeremy Hughes]

A note: if you're going to change the Delimiter on opts, it would be better to pass that into detectImportOptions as a Name-Value pair.

opts=detectImportOptions('C:\Users\Documents\.....CSV','Delimiter',',');

This avoids needing to detect the delimiter, so other parameters like the variable names and datatypes will be more accurate.

Answer 1

[Jeremy Hughes]

What is the result if you pass ReadVariableNames into the function?

T = readtable(fullFileName, opts, 'ReadVariableNames', true)

The readtable function defaults to using the variable names in the import options, so if you've set a variable name,

opts.VariableNames{4} = 'FOUR';

You'll see that reflected in the table without specifying ReadVariableNames.

If you change opts.VariableNamesLine and set ReadVariableNames=true you should get what you expect.

[John Doe]

This did indeed solve the problem.

The documentation is not awefully clear on this, it's odd that VariableNamesLine requires ReadVariableNames to = 'True' yet VariableUnitsLine doesn't require an equivalent. It's also odd that the default is not to use the value set in OPTS, particularly if this has been set by the user.

Thanks for your response, I have accepted your answer and updated my code.

Answer 2

[Steven Lord]

t = readtable('C:\Users\Documents\.....CSV');

You called readtable with just one input argument? The options object returned by detectImportOptions does not change "global" settings that readtable automatically picks up. Try calling readtable passing the options object in as the second input so it uses the options you specified.

t = readtable('C:\Users\Documents\.....CSV', opts);

[John Doe]

Apologies in consolodating the script for asking the question here I missed the opts. No difference though. The actual line of my code is:

x{k} =readtable(fullFileName,opts)

[John Doe]

Anybody?

Answer 3

[D Hanish]

For me, the T = readtable(fullFileName, opts, 'ReadVariableNames', true) did not work
And setting a particular variable name is not helpful as the column order is not fixed.
This however ...
opts= detectImportOptions(filename,'Delimiter', ";", 'VariableNamesLine', 2,'VariableUnitsLine' ,3);
... allowed me to set the options and correctly read the names. It is unfortunate there are so many such inconsitencies in the Matlab language. Names and types of variables appear to be required, but there is no convenient way to read them outside of detectImportOptions. Using the import tool and the script generator will result in a fixed string for variable names, so clearly they know it doesn't work as it should.

[Jeremy Hughes]

You should pose a separate question and include the file you're trying to read. Not many people will see this post as an answer on an already answered question.

[D Hanish]

Mine wasn't a question, it was a real answer to the problem.