How to divide the 2 datatables from one sheet to different sheet

Hi Team,

Kindly find the below screenshot FYR, i need to divide the below 2 datatables using empty row or deduction string as a separator and write it in two different sheets, rows are dynamic so please tell me how to achive this

Hi @bhanupriya.cb,

Are the column names fixed?


One strategy could be:

  • calculate all row indexes for the EMP ID column with value EMPID (read range without add headers)

  • slice from the found row index all rows up the first empty EMP ID value is found

In general we can do the slicing with Skip and TakeWhile method

For handling the datatable column names we can e.g. use incorporate some clonings of an already prepared dynamicly constructed template datatable

Let us know if you need further help once you started the implementation

@bhanupriya.cb Can you provide a sample input excel

Hi, Please find the below sample file

Sample_file.xlsx (78.9 KB)

Hi PPr,

Thanks for your reply,

I am able to understand the first part but slicing is quite difficult to implement it can u please share me a sample workflow…

give me a little time, will help you on the implementation

Hello @bhanupriya.cb ,

Here my suggestion is :grinning:

  1. use lookup datatable activity and get the index of the Occurrence of Deduction. Suppose indexVal
    2)Use Read Range activity and for the first table it will A1:C(indexVal-2). to get first table
    3)use find first and last free row. supposr last row as lastrowVal
    3)Read Range Activity and A(indexVal+1):C(lastrowVal)

Sure, thank you so much

ExcelDemoSearch.xaml (9.0 KB)
Sample_file.xlsx (134.9 KB)

Try the attached.

@bhanupriya.cb Try attached workflow. I am writing data to the different sheets in the same excel. You can modify it accordingly (74.2 KB)

we can fine tune and optimize as well

Read excel data / preperation of the column headers
cn = dtData2.Rows(0).ItemArray.Select(Function (x) x.toString)
dtData2.columns(cidx).columnName = cn

find indexes and split the into the tables
arrIndex =

(From i In Enumerable.Range(0, dtData2.Rows.Count)
Where dtData2.Rows(i)("EMP ID").toString.Trim.Equals("EMP ID")
Select x = i).toArray

TableList =

(From i In arrStartIDX
Let tbl = dtData2.AsEnumerable.Skip(i+ 1).TakeWhile(Function (x) Not String.IsNullOrEmpty(x(0).toString.trim)).CopyToDataTable
Select t = tbl).toList

So the approach is dynamic and driven by the column header line. In case that more tables will be present within the sheet, it will recognize it.

Afterward just iterate over the tablelist and write out the different tables to excel or process it.

Find starter help here:
ppr_ExcelBox_SplitExcel_ManyTablesInOneSheet_SplitByHeaderToEmptyRow.xaml (9.9 KB)

Thank you it is working as expected

Thank you so much I tried your code also and it is working as i wanted

Hi Thank you so much, it is working as expected

Hi @bhanupriya.cb,

You can loop over datatable assigned after read range activity, check if condition where first column has string “deduction”.

  1. Assigned this row number to some variable(deductRow).
  2. Two separates read range for same sheet with reading cell with the help of deductRow.
  3. First read range can be read from A1:C+deductRow, second can be read from

Note: +2 is used due to different row number logic of excel and datatable, it can be changed based on you are taking header, etc…

Hy but one small doubt actually in the sample data i have added only few columns, so i have almost 24 columns but it is considering only 13 columns hw do I copy everything?

@bhanupriya.cb Change the column in the code. Based on the sample data, we are considering from A to L. If it is from A to Z replace L with Z in the code

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.