How to import a specific range using readtable

Hi,

I want to import some data from Excel sheets to Matlab using readtable. My problem is that the data is in the collums D, E and I (so not adjacent in Excel) and i only want the data from rows 37 until the end.

My code so far is:

cd 'C:\Users\julia\Desktop\Test\PCR\';
pathname = 'C:\Users\julia\Desktop\Test\PCR\';
fileList = dir('*.xls*');
numberOfFiles = length(fileList);
data = table
for i = 1:numberOfFiles
    fileName = fileList(i).name;
    table = readtable(fileName, 'Sheet', 'Results', 'Range', '?');
    data = [data; table];    % not sure if this will work, the goal is to have a single table in the end with all the data
end

Your help is very much appreciated :)

[dpb]

Simplest will be to just read the whole spreadsheet and remove rows/columns not wanted.

The 'range' named parameter isn't flexible enough unless you know the full extent of the region desired in both columns and rows and it won't accept a non-contiguous range or multiple ranges at all.

The closest for your case would be to select the columns that include your wanted ones...

table=readtable(fileName, 'Sheet', 'Results', 'Range', 'D:I');  % read included columns
table=table(:,[1:2 6]);                                         % purge unneeded cols

[Julian]

Thank you for your help, it worked.

But i found a better solution using the detect import options function, where i can specify which variables I want to import.

Answer 1

[Neuropragmatist]

Can you not just read the whole table and the select the data you want from the resulting matrix?

cd 'C:\Users\julia\Desktop\Test\PCR\';
pathname = 'C:\Users\julia\Desktop\Test\PCR\';
fileList = dir('*.xls*');
numberOfFiles = length(fileList);
data = table
for i = 1:numberOfFiles
    fileName = fileList(i).name;
    table = readtable(fileName, 'Sheet', 'Results');
    
    data_range = table(37:end,[4 5 9]); % rows 37 to the end and columns D, E and I which should be 4 5 and 9

    data = [data; data_range];    % not sure if this will work, the goal is to have a single table in the end with all the data
end

Unless the table you are loading is very big with lots of rows/columns you don't want, this shouldn't really add much time. As the range feature of readtable works as in rectangular coordinates I don't think you can read discontiguous sections.

You could also just read rows 37 to end and columns 4 to 9, then remove columns 6-8.

Hope this helps,

M.

[Brian Hartigan]

The documentation for readtable specifically says, under "'Range' — Portion of worksheet to read":

--------------------------------------------------------------------------

'NamedRange' : Excel’s Named Range

In Excel, you can create names to identify ranges in the spreadsheet. For instance, you can select a rectangular portion of the spreadsheet and call it 'myTable'. If such named ranges exist in a spreadsheet, then readtable can read that range using its name.

Example: 'Range','myTable'

--------------------------------------------------------------------------

(emphasis mine).

THIS DOES NOT WORK, at all, ever... so why is it in the documentation?

And it very much is in tthe documentation for 2019b.

Are we to assume that MATLAB's documentation includes descriptions of functionality that developers wish was the case?

If so, it would be good to have some guidance as to which parts of the documentation are actual, real, implemented things, and which are (ahem) "aspirational".

I am very much a 'RTFD' guy, and people who ask dumb questions that are documented get no sympathy from me when someone gives them short shrft.

But this section of the site is full of sniffy responses that basically say "Oh, our $N/yr software [N = O(10^2)] doesn't do what you want although the docs say it does? Simple - change how you do things."

I take it most users are on student licenses then: that would not fly for commercial licensees.

[Neuropragmatist]

The named range works for me using the attached excel file and this code:

ename = 'MatTest.xlsx';
t = readtable(ename,'Range','MyRange','ReadVariableNames',0);

My range is called 'MyRange', you can see its values in Excel under Forumulas > Name Manager. The values loaded by Matlab into table t are correct on my system, although I don't use this feature so I don't know how fallible it is.

My guess is your version of Matlab is outdated (although that's unlikely because this feature of readtable has been around since at least 2016) or your version of Excel is outdated or there is some issue with compatibility crossover. I have Matlab 2018a and Microsoft Office 2016. Or you are saving as a .csv instead of .xlsx and the named range is being scrubbed.

If you still have an error/problem you could ask for help on this forum or contact TMW support.

RTFD is cathartic for us but not very helpful for the OP or anyone else who comes looking for a solution in the future.

Hope this helps,

NP