Merging Excel Workbooks

Help
, , ,
1

I have created an automation that grabs data from 2 different web page sources for information about soccer players. Those two automations are separate and does not interfere with each other. However in the end I would like the scraped datatables to be put into the same excel workbook.
Problem is that one web page is in English and one is in Japanese, so it would prove difficult to use the same automation for both web pages (thus why I created 2 separate) and now I have come across the problem of trying to merge both excel workbooks into a single workbook.
If anyone knows a fix around this problem, either if it is the merging of the excel workbooks or if there are any other tips on how to extract the data and put it into a single workbook from the start, anything would help.

For more context, the way the datascrape works is that I first extract all the Japanese written soccer player names, extract to a datatable, translate them through google translate in a for each row and paste the name into the workbook + rename the file. This however also get miss-translated sometimes, but that is another question.

Any help would be appreciated!

Example:
Transferring the A+B column to the other workbook’s C+D column

SoccerPlayer Example Workbook1 Edit
SoccerPlayer Example Workbook1 Edit1023Ă—327 21.2 KB

SoccerPlayer Example Workbook2 Edit
SoccerPlayer Example Workbook2 Edit1031Ă—347 22.3 KB

2

Hello
1. Use Read Range Activity & Output stored as Dt1
2. Same for 2nd data table and store is has Dt2

Merge Data table activity Source DT2 Destination Dt1
writre range dt2

Just try @Robert_Wennberg

1 Like
3

@Shriharsha_H_N

Thank you for your reply and explanation on how to merge together two files.
However, the bigger issue lies with me being able to automatically do that for all the other files that have been collected through data scraping.
Looking at the picture is only 1 out of hundreds of soccer players and I would like to know if it is doable somehow to automatically merge the matching soccer players data together.

Thanks

4

same for all
@Robert_Wennberg

5

@Shriharsha_H_N

Thanks for the quick response.
Yeah I got that part, but the thing is that how can I do it automatically? Since a lot of files are stacking up, I can’t possibly pair them up manually. I am uploading a screenshot to make it a bit more understandable on what I am trying to achieve.

SoccerPlayers

Is there any way I can find respective file based on the players name or something, take data from 1 of the workbooks and apply it to the other? Or is this way out of RPA’s league to handle?

Thanks

6

@Robert_Wennberg
Use input file system string and use for each

Cheers :slight_smile:

7

@Shriharsha_H_N

Thanks again for quick response.
Sorry but I am a beginner at RPA so is there any chance you could explain a bit more in depth? I am not sure what you mean. How am I going to make sure that both Cristiano Ronaldo files are merged together, and not Neymar + Messi?

8

Hi @Robert_Wennberg

StrArray=Directory.GetFiles(input file path)
use for each item in StrArray
use an if condition

Item.ToString.Contains(“Christina Ronaldo”)
then use merge datatable or else use do some activity like delete file

Thanks
Ashwin S

9

@AshwinS2

Sorry for the late response.

I will try out your solution and get back to you if I have any further questions (which I probably will have)!
Thank you

10

@AshwinS2

After some thinking, using your code would pretty much mean that I have to input the names of every soccer player anyways, making it a lot of manual work?
Seeing as how there will be more soccer players data scraped I am not sure that your provided solution fixes that problem.

Thanks

11

Hi @Robert_Wennberg

You mean to say that soccer player list like Messi etc are in Excel

Thanks
Ashwin.S

12

@AshwinS2

I mean that, using Item.ToString.Contains(“Cristiano Ronaldo”) would mean that I would have to do the same for the other players as well right? Meaning I would have to create another Item.ToString.Contains(“Lionel Messi”) and so forth.
Is there any way to create something that iterates through the already created files, look if there are 2 files with the same player name, and then merge them together?

Thanks