Want Add a Column and Consolidate Multiple Excel File into One Excel File

Help Robot
,
1

Hi Folks,

Please help me on the below issue mentioned.

I want to open a excel file and want to add a column in the last. Header name to give as “region”, below header (“region”) to fill as “East” till my data is available. in excel file rows may be 1k or 2k more than that but I want to fill it as “east” to entire data.

For and Example: I opened a excel file “FK_Inventory_East_Uluberia”, I want to add column “Header as (region)” below data to file “east”.
for your reference I have attached screenshot.

And final step is to consolidate the excel into one excel file.

All File Names:

  1. FK_Inventory_East_Uluberia
  2. FK_Inventory_North_Farrukhnagar_BTS
  3. FK_Inventory_North_Jaipur
  4. FK_Inventory_North_sanpka_01
  5. FK_Inventory_South_malur_bts
  6. FK_Inventory_West_Bhiwandi_BTS

image
image1292×364 8.91 KB

2

@Vijay_R1

Please follow the below steps.

  1. Use Assign activity to create an empty data table (dt_Output = New DataTable)
  2. Use For each file activity to loop through all the Excel files.
  3. Inside For each Activity use the read range activity to read the Excel.
  4. Add DataColumn Activity to add a new column and in the properties give default value.
  5. Use Merge Data Table to merge current file data to Final Datatable
  6. Finally after completion of For each activity add Write Range activity to write consolidated output into the Output file

Refer attached Sequence.xaml for your reference.
Sequence.xaml (10.9 KB)