What is the best way to readtable with variables names not in the first row and detectImportOptions gets the row detection wrong?

Is there a way to easily call a readtable function while specifying the row the variable names are located in and the row that the data starts and ends when the detectImportOptions gets the variables row wrong?

Answer 1

[Prabakar kg]

Hello,

Couple more steps will help you achieve this.

The "options object" which you get from calling detectImportOptions is configurable. Please check the below workflow.

Lets say the variable names are in line 5 and the data starts from line 7. You could do something similar to below and that will resolve your problem.

If your file is text:

opts = detectImportOptions('filename.txt'); % Initial detection

opts.VariableNamesLine = 5; % Set variable names line

% Set the line where data starts. 'Inf' will trigger READTABLE to auto detect the end. It does a pretty good job in detecting the end of the data. If this did not work you could provide the exact range as well. E.g. [7 101].
opts.DataLines = [7 Inf];   

readtable('filename.txt', opts); % This will be get the data from line 7 and use line 5 for variable names

If your input file is spreadsheet:

opts = detectImportOptions('filename.xls'); % Initial detection

opts.VariableNamesRange = 'A5';

% Specify start cell and let readtable take care of the rest. If this did not work you can also specify exact ranges like "A1:E5". Check out the doc for spreadsheetImportOptions - https://www.mathworks.com/help/matlab/ref/matlab.io.spreadsheet.spreadsheetimportoptions.html
opts.DataRange = 'A7'; 

readtable('filename.xls', opts);

Hope this helps! Let me know how it goes.

Thank you!

[Joseph Kutteh]

This works very well. I used the text file version. Thank you very much!