Write data from one excel to another excel using column mapping

RPA Discussions
, , , , ,
1

Hi All,

I am trying to build workflow where I want to read one excel sheet and write data to destination sheet. But I want to write data into destination excel using column mapping.
Like column are similar in both excel but their sequence is not similar.

Can anyone advise?

2

Hi @sayali_gujarathi1

Can you share the sample input and expected output?

  1. Read the 1st excel file → dt1

  2. Use Wirte range activity path of 2nd excel file → pass the dt1

Regards
Gokul

3

Thiswill not work here.

because whatever column position in my source excel , it is not same with the columns position in destination excel. but columns are same. So , i need to write data in destination sheet using column mapping.

4

HI @`

In this case you can try with lookup activity to get the Rnage of the Column Header

5

@sayali_gujarathi1
Try this :

dt.AsEnumerable.Select(Function(row) dtDestination.Rows.Add(row("SourceColumn1").ToString, row("SourceColumn2").ToString, row("SourceColumn3").ToString)).CopyToDataTable

cheers…!

6

Hi @sayali_gujarathi1

Use Merge DataTable Activity :
1.Excel Application Scope

  • Read Range (dT1)
  • Read Range (dT2)
  1. Merge Dt
    • Destination : dt1
    • Source : dt2
    • Missing Schema Action : (Add/Ignore/Error/AddWithKey) - select Add from this 4 options
    • Add : Add values below the actual values
  2. Write Range (in New sheet Result) destination dt1

Code :
image
Input :
image
image
Output :
image

Hope it helps :slight_smile:

7

@sai_gupta it is not working.

8

@Gokul001 Can you send me workflow image, if you have.

9

@sayali_gujarathi1

try this:


(From row In sourceDataTable.AsEnumerable()
 Let newRow = destinationDataTable.Rows.Add(row("Column1"), row("Column2"), row("Column3"))
 Select newRow).CopyToDataTable()

cheers...!
10

Once try with above steps and let me know @sayali_gujarathi1

11

@sayali_gujarathi1

One way is to find the order of column in the destination…if this is already know then use filter datatable with givign the column names as needed

One more way would be to use setordinal and set the column position for eqch column and then write the data to destination

Cheers