Splitting data table based on headers

i have data in one excel sheet,
ID | Amount | Date
E1 | 203.6 | 01/05/2018
E3 | 333.6 | 05/05/2018
ID | Amount | Date
E5 | 415.5 | 01/05/2018
E9 | 666.6 | 05/05/2018

and i want to dynamically split the data based on the headers, into 2 different excel sheets.

Hi @Manoj_Gandham1,

Can you explain logic briefly ?

actually my original excel sheet contains,
and i want to take payments and invoices to 2 different excel’s.


Logic is

  • Read the excel use excel scope activity
  • Create a new variable as Datatable ‘dtNew’
  • Create another variable as an integer ‘rowCount’
  • Use assign activity
    rowCount = dtExcel.Rows.IndexOf(dtExcel.Select("Column1='Invoices'").First())
  • Assign the below code to get Invoice data
    dtNew = dtExcel.AsEnumerable().Skip(rowCount).CopyToDataTable()
  • To get payments data use below code
    dtNew = dtExcel.AsEnumerable().Take(dtExcel.Rows.Count-rowCount).CopyToDataTable()

thanks @aanandsanraj for the help its done.

You are Welcome @Manoj_Gandham1 :slightly_smiling_face:

Assign: Cannot perform ‘=’ operation on System.Double and System.String.
Im encountering this problem with rowCount Assigning

Hi @nikhil.s

Actually the column data type is Double so you need to convert it. Use the below code to convert

dtExcel.Select("Convert([Column1],'System.String') ='Invoices'")