How to extract certain rows and columns with the readtable option for Excel files.

Suppose we have an Excel file Data.xlsx. The file have certain descriptive text in the initial rows and then there is tabular data on several sheets. I would like to extract column 1 and column 3 but the useful row number starts from say, 30 and ends at 10000.

Table1=readtable('Data.xlsx', 'Sheet', 'Signal', 'Range', 'A:C');

Is there a better way to read such an Excel file? There is a lot of text in the initial rows of the Excel file. This is default way the instrument exports the data. How can we only read column A and column C whose useful rows start from 30 and end at 10000?

Thanks.

Answer 1

[Sindar]

(edited)

Using the import tool, then generating code and clearing unnecessary details, this appears to be the way:

opts = spreadsheetImportOptions("NumVariables", 3);
% Specify sheet and range
opts.Sheet = "Signal";
opts.DataRange = "A30:C10000";
% Specify column names and types
opts.VariableNames = ["A", "Var2", "C"];
opts.SelectedVariableNames = ["A", "C"];
% Import the data
Table1 = readtable("Data.xlsx", opts);

[Sindar]

if this split range doesn't work, loading each separately and combining the tables may be more efficient than loading everything (i.e., A30:C10000) and discarding the unwanted slice (from column B)

[FW]

Sindar, this split range does not work. Here is the error message.

Unable to determine range. Range must be of the form 'A1' (cell), 'A:B' (column-select), '30:10000'

(row-select), 'A30:B10000' (rectangle-select), or a valid named range in the sheet.

[Sindar]

Well, my optimism was misplaced. I've edited to a method that should actually work.

[FW]

Thanks. I can export the table. Still there are problems with it. When I used table2array, it generates a cell which has A and C only, but no matter what I tried, I cannot convert that cell into an array for further mathematical operations.

[Sindar]

Try adding this line before readtable:

opts.VariableTypes = ["double", "char", "double"];

I assumed it would default correctly, but maybe not

[Sindar]

Try looking at Table1.Variables, rather than using table2array. What type does this have?

Also, please don't edit comments to ask new questions, it makes it less likely that people will notice and be able to answer, and also makes the conversation harder to follow for people that have similar issues

[FW]

Sorry, I don't think it is working.

I found this from another source:

opts = detectImportOptions('A.xlsx','Sheet','Sheet1','Range','A30:C10000'); %still have to specify the full range

opts.SelectedVariableNames = opts.SelectedVariableNames([1, 3]); %ignore second column

Table_1 = table2array(readtable('A.xlsx', opts));

The exported Table is now an array. You may wish to edit the answer.

[Sharmila]

is it possible to get the values if we dont know the number of rows or where exactly the column is present and identify the columns only with the header names