How to figure out the number of rows for headers?

I have Excel files with unknown number of headers. For exmple, some files only have one row of header info. Another one could have 12 header lines.

I would need to know the accurate count of the number of rows of headerlines, so that my program would know the Excel Row# for a cetain data value. Right now, it only knows the relative Row # of the numerical portion of the Excel file.

What makes things more complicated is that my data also routinely contain columns that are made of text-strings.

Right now, I use readtable to read the Excel file. It basically assume the first Row is the headerline, and the rest of them are data. Here is my question. Is there a straightforward way to identify the number of headerlines in my case?

Thanks!

[Leon]

The below code works for flat csv or txt files. It does a good job of reading all of the headers. Is there an easy way to make it work for xlsx files as well?

header_end = false;
header_count = 0;
 
% preallocate 50 rows of header text
header = cell(50, 1);
 
%% Loop through header lines
while ~header_end
    %% Get a whole line from the file
    aline = fgetl(FID);
    
    %% Use a regular expression to look for the data block header
    tokens = regexp(aline, ...
        ['(\d+)' ...     % capture a number
        '\s+!\s+' ...    % followed by an !
        '(\w+\s+)+'],... % and capture a series of strings
        'tokens', 'warnings');
    
    %% Parse data block header if found
    if ~isempty(tokens) && numel(tokens{:}) == 2
        % parse numeric token using string to double
        num_datum  = str2double(tokens{1}(1));
        
        % parse variable string
        varNameStr = deblank(tokens{1}(2));
        names = strsplit(varNameStr{:});
        %names = names{1};
        
        % stop looping
        header_end = true;
    else
        % Increment header line count and add new line
        header_count = header_count+1;
        header{header_count} = aline;
    end
end

Answer 1

[Fangjun Jiang]

If you use [Num, Txt, Raw]=xlsread(), can you figure it out based on the numerical, text and raw data?

[Leon]

Thanks. Unfortunately, I can't.

Answer 2

[Josh Zagorski]

[A DELIM NHEADERLINES] = importdata(...) returns the detected number of header

lines in the input ASCII file.

So, [Matrix_converted,'delimiter',No_headerlines = importdata('file.xlsx');

[Leon]

Many thanks for pointing me towards this direction. However, I got nothing for the three values:

>> [Matrix_converted,delimiter,No_headerlines] = importdata('aaa.xlsx');
>> No_headerlines

No_headerlines =

     0

>> Matrix_converted

Matrix_converted =

     []

>> delimiter

delimiter =

   NaN

Does it only work for txt file?

[Josh Zagorski]

For ASCII files, data contains a double array. Other fields contain cell arrays of character vectors. textdata includes row and column headers.

For spreadsheets, each field contains a struct, with one field for each worksheet.

https://www.mathworks.com/help/matlab/ref/importdata.html

I'm using it to load .csv and .xlsx files

[Leon]

Thanks. As to my line of code, is there a way I can fix it to let it work?

[Josh Zagorski]

Do you have the correct filepath?

Otherwise, I glossed over your "text-strings" data comment - wondering if data needs to be double/numeric "data contains a double array."

[Leon]

Yes, I do.

Just tried a different file, below is what I get:

>> [A,B,C]= importdata('G01.xlsx')

A = 

  struct with fields:

          data: [1×1 struct]
      textdata: [1×1 struct]
    colheaders: [1×1 struct]


B =

   NaN

C =

     0