Need to know how to readtable() when file has row 1 variable names and row 2 units and the other rows data

Given this file format:

Time  MotorCurrent  Time1  Pressure1  Time2  Pressure2  Time3  Pressure3
sec A   sec mmHg    sec mmHg    sec mmHg
0  0.508069  0  21.6337  0  -0.417497  0  -0.530318  
0.0002  0.513559  0.0002  21.61  0.0002  -0.399605  0.0002  -0.531096  
0.0004  0.517756  0.0004  21.4696  0.0004  -0.410236  0.0004  -0.509577  
...

And these settings: DelimitedTextImportOptions with properties:

   Format Properties:
                    Delimiter: {'\t'}
                   Whitespace: '\b '
                   LineEnding: {'\n'  '\r'  '\r\n'}
                 CommentStyle: {}
    ConsecutiveDelimitersRule: 'split'
        LeadingDelimitersRule: 'keep'
                EmptyLineRule: 'skip'
                     Encoding: 'ISO-8859-1'

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

   Variable Import Properties:   Set types by name using setvartype
                VariableNames: {}
                VariableTypes: {}
        SelectedVariableNames: {}
              VariableOptions: Show all 0 VariableOptions 
  Access VariableOptions sub-properties using setvaropts/getvaropts

   Location Properties:
                     DataLine: 3
            VariableNamesLine: 1
               RowNamesColumn: 0
            VariableUnitsLine: 2
     VariableDescriptionsLine: 0

Why do I still get .Var1, .Var2, etc.?

Answer 1

[Jeremy Hughes]

Hi Doug,

The good news is that readtable using import options was built for this.

I think there might be some confusion on how the import options is used. I noticed something odd about your import options; there are no variable names. Are you creating this by hand? If so, are you passing the import options as an argument to readtable? If so, then I would expect you to get 'ExtraVar1','ExtraVar2',... because of ExtraColumnsRule: 'addvars'. Let me talk through the recommended workflow and see if we can't get this to import.

Try the following:

opts = detectImportOptions(filename)

This should be pretty close, but it doesn't have the capability of detecting if line two should be VariableUnits, VariableDescriptions, or something else. You will need to set this manually.

opts.VariableUnitsLine = 2

Everything else should be good because this format looks straightforward. From here, you can then inspect the variable names to see if they are the ones you want and modify them accordingly. If you see Var1, Var2, ... then the variable names were not detected correctly. If that's the case, you can pass 'NumHeaderLines',0 to detectImportOptions, and that should help, but I don't think you'll need that.

From the import options you can then modify the DataLine, VariableNamesLine, etc. to be what you want, if they aren't already. Now you should be able to read your file without any issue.

T = readtable(filename,opts);

I also cannot be sure if your choice of '\t' is wholly correct for delimiter. Some of the units look like they might be separated by space characters (but likely it's just the tab stops are shor). If you have problems reading the units line try:

opts.Delimiter = {'\t',' '}

You can specify as many delimiters as you need, but they are applied to every line/field in the data.

I hope this helps, Jeremy

(Edited for typo)

[dpb]

Huh. Hadn't read the doc on this before; is pretty slick way to do it.

Looks like should handle OPs problem so I deleted the Answer I gave based on release prior R2016b.

I notice in the 'Delimited Text Properites' section they've still not solved the fixed-width field, however (altho it has no bearing here, just a comment).

[dpb]

I'd suggest attach a short section of the file so others with recent-enough release can play with your exact case to see if reproduce problem you're having or can more easily find a syntax/input problem or an issue in the file itself.

Answer 2

[Doug Ivers]

I can't get it to work. Even if I just make the units line commented out. So I'm removing the units line.

Next issue is just getting the variable names from the header. I don't know how to both set HeaderLines to 0 and ExtraColumnsRule to 'ignore' at the same time, because the following generates an error.

opts.ExtraColumnsRule = 'ignore';
test = readtable('fileForImportTest.txt', opts, 'HeaderLines',0);

[dpb]

Be better to make follow-up discussion as Comment rather than Answer...

Need to know what the error is...attach in context

I note the example outputs from the detectimportoptions function returns the variable names in a list in the 'VariableNames' property as well as a line number.

It may be in early incarnation that is needed to make it function correctly; I'd consider it a bug.

It looks to me like this is worthy a bug report submitted at www.mathworks.com as the documentation surely implies to me that your initial options file should work--or at least the doc should say that the names fields must be populated first which surely seems to defeat the purpose of the table to reduce labor.

Answer 3

[GUNHEE MOON]

opt = detectImportOptions( 'inputfile.xlsx' )
opt.VariableUnitRanges = 'A2'
data = readtable( 'inputfile.xlsx' , opt);