How to insert items after every row in excel file

[shikharno.7]

Hi Folks, could you please provide a xaml file solution to the below query. I would highly appreciate you send out the xaml file on this thread

Query:

I have an excel file and need to perform certain excel operations on it.
Excel File :
Master_File(2).xlsx (9.9 KB)

The source file looks like below.

I want to add NET, PAY, NON-PAY AND INCOME after every row present like below :

Additionally, I want the Region ID column to add 6 digits after every row item, when,
Column data is NET regional id be 102031000000,
PAY regional id should be 102031100000,
NON-PAY regional id should be 102031200000,
INCOME regional id should be 102031300000.

So the final excel must look like below:

[Anil_G (Anil Gorthi)]

@shikharno.7

You can follow the steps

1. Build a datatable with 6 columns and name it outdt
2. read datatable into indt
3. Use for each row in datatable activity with indt
4. Inside loop use add datarow and give datatable as outdt and array row as {currentRow(0).ToString,currentRow(1).ToString,currentRow(2).ToString + "000000",currentRow(3).ToString,currentRow(4).ToString,"Net"}
5. Similar to step 4 add 3 more add data row activities and change values as needed
6. Use write range activity and write the outdt back to excel

cheers

[ppr (Peter Preuss)]

With essentials

• create / build second table - dt2

Code2	Category
000000	NET
100000	PAY
200000	NON PAY
300000	INCOME

• read range your excel - dt1

• Add Datacolumn - Add the Category Column, if not already present to dt1

• Assign activty: dt3 = dt1.Clone

• For each row in datatable | orow in dt1
  |-- * For each row in datatable | irow in dt2
  |----- * Assign Activity: newRow = dt3.NewRow
  |----- * Assign Activity: newRow.ItemArray = orow.ItemArray
  |----- * Assign Activity: newRow("Regional ID") = newRow("Regional ID").toString & irow(Code2).toString
  |----- * Assign Activity: newRow(“Category”) = irow(“Category”)
  |-- * Add Data Row: newRow to dt3

• write range - dt3