Convert two tables on one Excel sheet into separate datatables where row numbers are variable

Help
,
1

I have an excel file that contains 2 tables on a single worksheet. The table1 always has headers starting in row 1 and contains a variable amount of rows. Because of this, table2’s headers begin at a different row in each excel file.

Table1’s structure looks like this in Column A:

Column1
123556612
672437742
123785246
[random # of blank rows]
[random string]
[random # of blank rows]
Column1:
672437742
672437742

I’d like to put these in two separate datatables. What is the best way to go about this?

I can think of 2 ways, but not sure if either are all that great. I’m leaning towards the 2nd way as that seems like it’d be faster & more straightforward. Please let me know if anyone has some suggestions on how to improve this. Table1 generally has only 15-30 rows whereas table2 will have anywhere from 1k-20k rows (estimated). Note that only steps 3 & 5 differ between the two.

1st way:

  1. Read entire sheet with read range activity and put in tempDatatable
  2. Get the End index of the first table by finding the first blank row & subtracting 1
  3. Copy the first rows until the end index of table 1 using a loop & put in datatable1
  4. Find the start index of the 2nd table by searching for “Column1” within Column A.
  5. Copy all rows starting from the start index of table2 and put in datatable2

2nd way:

  1. Read entire sheet with read range activity and put in tempDatatable
  2. Get the End index of the first table by finding the first blank row & subtracting 1
  3. Use read range activity and input “A1:S” + end index [// fixed number of columns are used]
  4. Find the start index of the 2nd table by searching for “Column1” within Column A.
  5. Use read range activity and input “A” + start index + “:S” + tempDatatable.count.tostring
2

This might be another method to consider.
Output the entire table to a string, then split by the empty row to convert to an array of 3 strings (1st table, random string, 2nd table). Then use Write Text file to output the 2 tables as comma-delimitted strings to .CSV extension, followed by a Read CSV to store them into datatables.
Example,

Output data table to text
Write text file
    temp1.CSV
    text.Split({",,,,,,,"},System.StringSplitOptions.RemoveEmptyEntries)(0)
Write text file
    temp2.CSV
    text.Split({",,,,,,,"},System.StringSplitOptions.RemoveEmptyEntries)(2)
Read CSV to table1
Read CSV to table2

Your solutions would probably work ok too though.

Regards.

3

Hi @Dave,

Go to 1st way then you can get the exact result I think.

Regards,
Arivu

4

@Dave, Modifying your logic a bit,

Let’s say you have table in datatable dt

1st Step - get the indices of (Column1)

ASSIGN → Integer Array arr = (From row in dt.Select() Where String.Join(“”,row.ItemArray()).Contains(“Column1”) Select dt.Rows.Indexof(row)).ToArray()

2nd Step - Copy from first Column1 value till before next Column1 value

ASSIGN → Datatable dt1 = (From row in dt.Select() Select row).Skip(arr(0)).Take(arr(1)-arr(0)).CopyToDatatable()

3rd Step - Copy from second Column1 value till last index

ASSIGN → Datatable dt2 = (From row in dt.Select() Select row).Skip(arr(1)).Take(((dt.Rows.Count) - arr(1))).CopyToDatatable()

Regards,
Dominic :slight_smile: