Splitting data table based on headers

Help
, ,
1

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.

2

Hi @Manoj_Gandham1,

Can you explain logic briefly ?

3

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

4

@Manoj_Gandham1

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()
1 Like
5

thanks @aanandsanraj for the help its done.

6

You are Welcome @Manoj_Gandham1 :slightly_smiling_face:

7

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

8

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'")
9

image
I tried to replicate same things what you have mentioned. But getting error like this in the rowcount assign activity

10

Please tell what are you trying to do? If possible share the xaml file

11

Hi

Step 1: Read the data into a data table

Use the Read Range activity to read the data from the Excel sheet into a data table.

Step 2: Split the data into two data tables

Create two empty data tables to hold the split data.

Iterate through the data table and add each row to the appropriate data table based on the value of the “Date” column.

  • If the value of the “Date” column is “01/05/2018”, add the row to the first data table.
  • If the value of the “Date” column is “05/05/2018”, add the row to the second data table.

Step 3: Write the split data to two Excel sheets

Use the Write Range activity to write the data from each data table to a separate Excel sheet.

Thank you