Excel merging


#1

Hi developers,
i have different excels , in which the column names are different , in one excel its like entity name and in another excel it was like statutory entity name and also the headers were not in fixed range i need to merge all the excels and create a master file

please find the attachments of the excels and suggest me the solution Insurance Update (Weekly) Sunway Berhad - 21.04.2018 - 27.04.2018.xlsx (14.2 KB)
Sunway Berhad - Insurance Declaration Form - AHILA GANESAN-done.xlsx (44.0 KB)

Thanks in advance:smiley:


#2

Hello.

Just making a super-fast reply here…

I think the best options are either 1)Merge Data Table or 2)Append Range.
–For “Merge Data Table” you need to rename all the columns so they can be merged correctly.
–For “Append Range” you need to reorder the columns so they can be aligned.

To rename columns you can simply use an Assign activity and use:
dt1.Columns(“name”).ColumnName = newname
–It might be beneficial to have the column names stored in array variables, like maybe {“entity name”,“statutory entity name”}, then you can Assign with dt1.Columns(array(1)) = array(0) and for each column
(but there are many approaches to this)

To reorder the columns you will want to probably use the Invoke Method for SetOrdinal, with the columnIndex you are moving the column to in the Arguments (there are examples of this on the forums too)

I hope this helps. I apologize for not providing any samples.

Good Luck.