How to read Excel files with unknown number of header rows?

17 次查看（过去 30 天）
Leon
Leon 2020-2-11
评论： Leon 2022-10-7
Below is my code to read an Excel file using readtable:
T1 = readtable ('test.xlsx','PreserveVariableNames',true);
Headers = T1.Properties.VariableNames;
A = T1{:,1};
Here is the problem. My Excel file could have unknow number of header rows (from 1 to 20). It seems that (a) the Headers are always the first row, and (b) the A values always start from the first all numerical row.
What I need is the Row # of the A values. If I know there is only one header row, I know the first element of A starts from Row # 2. With unknown number of header rows, how do I derive that Row # info of A?
Thanks!
  6 个评论
显示 4更早的评论
Walter Roberson
Walter Roberson 2022-10-5
Is there anything that is consistent between the files? Same number of variables with the same headers?
If there is a variable number of header lines, then is the variable names always going to be the first row, and the variable units always going to be the second row? Or is the variable names always the first row but the variable units is always the row before the data? Or are there a variable number of headers all followed by names and then units and then data?
Leon
Leon 2022-10-6
Thanks for the reply, Walter.
The first row is always the header row. The 2nd row can either be the data or the unit row.

请先登录，再进行评论。

请先登录，再回答此问题。

采纳的回答

Walter Roberson
Walter Roberson 2022-10-7
I notice you are using R2019b. Starting with R2019a, you can use readcell . Then you would ask isnumeric(T{2,1}) to determine whether the second row was header (units) or numeric. Then cell2table() using the first row of the cell as the VariableNames, and either 2:end or 3:end indexing for the content depending where the numerics start; if row 2 was not numeric then use the content to set the VariableUnits property.
  1 个评论
Leon
Leon 2022-10-7
Many thanks for the recommended solution, Walter!
That will work most of the time, except that T{2,1} may not always be numeric even when they are part of the data. Sometimes, it can be a string. My data does containt some strings such as the Station ID, Cruise_name, Expedition code, etc.
Maybe the only way is to identify a column that is always numerica first?

请先登录，再进行评论。

更多回答（1 个）

Shashwat Bajpai
Shashwat Bajpai 2020-2-13
The spreadsheetDataStore function can help with this alongwith detectImportOptions
You can also use the Import Tool in the MATLAB Toolstrip to select the rows required.
Hope this Helps!

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

产品


版本

R2019b

Community Treasure Hunt

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

Start Hunting!

Translated by