How can I shift column information from "Excel 1" file to another "Excel 2" file, which column have already some data?

[Sojol_Hamid]

Hello All,
I want to shift columns information from “Data Extract” excel to “Main Database” excel. “Main Database” excel columns’ have already some existing information, which I can’t delete or alter. “Data Extract” yellow marked 8 no columns data need to be shifted to “Main Database” 8 no columns. Problem is “Main Database” 8 columns are not located together, rather they are separated by some other columns, which have some default value.

I can copy each columns of “Extract Data” excel file by “read range” activity & store them in different data table but how can i write them in already filled columns of “Main Database” file.

For your convenience/better understanding, I am attaching column name files and “Extract Data” & “Main Database” excel file.

Column Data Transfer (Column Names).xlsx (9.0 KB)
Data Extract File.xlsx (10.8 KB)
Main Database.xlsx (10.4 KB)

[rahulsharma]

If your requirement is to add the data without altering the existing one, just use Append Range activity. That would add the data below existing rows

[Sojol_Hamid]

Hello @rahulsharma ,

Thanks for your reply. but destination file has columns, which are not located in one place, they are separated by additional other columns. in that case, can i use “Append range” activity? furthermore, Append range activity sometimes does not work properly.

Do you have any other solutions?

[pravin_calvin (pravin kumar)]

Hi @Sojol_Hamid

Use write cell activity and iterate based on index!

Please refer to the xaml below!

Main.xaml (9.7 KB)

Regards

[rahulsharma]

if the destination file doesn’t have a fixed column structure then how would you know where to add the new data?

[Sojol_Hamid]

Many Thanks for your excellent demonstration. Just one question, can i use below expression instead of your one:

My expected VB expresision: “E”+(mainDt.rows.Count+1).ToString

Your provided expression: “E”+((mainDt.rows.Count)+(idx+1)).ToString

please share your suggestions. Thanks in advance.

[pravin_calvin (pravin kumar)]

Hi @Sojol_Hamid

You expression will not iterate the Loop as 1 is Static and it will give result as E6 only

Instead Idx is a variable from the for each row activity It will Iterate as per index So the Resultant would be as E6 E7… and so on as per row values!

Hope the above explanation would be clear!

regards

[Sojol_Hamid]

Many Thanks!

[Sojol_Hamid]

Many Thanks. I just applied your ways & it works perfectly. You saved a lot of my time!! You are a true robot master! You can sense the actual problem/root cause & know the pulse of solution seeker (which is very good quality, i must admire).
just one point, i want to share: I just add “+1” after MainDB.rows.count expression in order to consider header/column name, otherwise it paste in last “value filled” cells:

“B”+((MainDB.Rows.count+1)+(idx+1)).ToString
Thanks once again!!