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

조회 수: 184 (최근 30일)
FW
FW 2020년 1월 24일
편집: Sharmila 2023년 2월 27일
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.

답변 (1개)

Sindar
Sindar 2020년 1월 24일
편집: Sindar 2020년 1월 24일
(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);
  댓글 수: 8
FW
FW 2020년 1월 25일
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
Sharmila 2023년 2월 27일
편집: Sharmila 2023년 2월 27일
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

댓글을 달려면 로그인하십시오.

카테고리

Help CenterFile Exchange에서 Spreadsheets에 대해 자세히 알아보기

제품


릴리스

R2019a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by