I am working with a file that contains two separate headers, each followed by multiple rows of data. The number of rows under each header is dynamic

Help Studio
1

Hello,

I am working with a file that contains two separate headers in one sheet, each followed by multiple rows of data. The number of rows under each header is dynamic - for example header1 may contain 10 rows today and 11 rows tomorrow and the same applies to Header2.

Could you please guide me on how to accurately identify both headers and split data in two separate datatables.

2

Hi @mohammed_nawazuddin1

Read Range to get MainDT, then find header rows using row(0).ToString.Contains(“Header1”) and row(0).ToString.Contains(“Header2”)
with index from MainDT.Rows.IndexOf(row),
then split using DT1 = MainDT.AsEnumerable().Skip(header1Index+1).Take(header2Index-header1Index-1).CopyToDataTable()
and DT2 = MainDT.AsEnumerable().Skip(header2Index+1).CopyToDataTable()

Happy Automation

3

Hi @mohammed_nawazuddin1

You can solve this dynamically by reading the complete sheet first and then identifying the row numbers where Header1 and Header2 are present.

  1. Use Read Range to read the full sheet into one DataTable

  2. Loop through the rows and identify:

    • Header1 row index
    • Header2 row index

  3. Once both header positions are found:

    • Rows between Header1 and Header2 belong to DataTable1
    • Rows after Header2 belong to DataTable2

This works well even if the number of rows changes every day.

You can identify headers using conditions like:

row(0).ToString.Trim = "Header1"
row(0).ToString.Trim = "Header2"

Then use Skip and Take (LINQ) or a simple loop to move rows into separate DataTables dynamically.

Example:

header1Index = dt_Main.AsEnumerable.ToList.FindIndex(Function(r) r(0).ToString.Trim = "Header1")

header2Index = dt_Main.AsEnumerable.ToList.FindIndex(Function(r) r(0).ToString.Trim = "Header2")

DataTable 1:

dt1 = dt_Main.AsEnumerable().
      Skip(header1Index + 1).
      Take(header2Index - header1Index - 1).
      CopyToDataTable()

DataTable 2:

dt2 = dt_Main.AsEnumerable().
      Skip(header2Index + 1).
      CopyToDataTable()
4

Hi @mohammed_nawazuddin1

You can handle this by reading the full sheet first and then splitting based on header positions

First, read the Excel without headers so you get the raw data. Then loop through the rows and identify where Header1 and Header2 appear (use a condition like row contains specific header text). Once you find the index of both headers, you can split the data accordingly rows between Header1 and Header2 go into the first DataTable, and rows after Header2 go into the second DataTable. Since the row count is dynamic, this approach works reliably because it depends on header position, not row count.