Compare datatable with excel field

[Aguirre]

Hello, I want to select a hyperlink from a website according to the data read from an Excel.
I am blocked and what I have tried does not work for me, after performing data scrapping, I store the data in the table “Pais Extraido”, now my idea is that I compare the value of the country in Excel with the extracted country and when they are the same I open a new url with the country address, this last part is the one I’m blocked from.
In the screenshots you can see the extracted table and how I run through Excel.
I honestly think it is something simple, but I have blocked myself.
THANKS.

[lukasziebold]

Hi!

It seems like you are extracting the website data for each line in excel! There is no need to do that.

Try with these steps:

• Read your Excel (dtExcel)
• Read/Scrape your website (dtWeb)
• Use For Each Row in dtExcel
• Use Lookup DataTable activity and lookup the URL by checking the country code from Excel in country code from Web
• Use that url to open the web page and continue process

Does that help?

[Aguirre]

Hello and thanks for the quick response, I have never used this option and it generates doubts.
Which I have to put in the output, my table data is:
ExtraCountry (Country, URL_Country)

[ppr (Peter)]

May we ask you to share a screenshot from Excel.

As far as understood you are trying to open the link when url in excel is same as extracted, right?

Have also a look on the Join DataTable Activity, as it could be option for this

[Aguirre]

Hello, my excel contains a series of data, of which I only need the country, then I extract with data scrapping all the countries with their url, what I want is that when the country of my excel matches one of the table extracted me from the URL.

[ppr (Peter)]

Ok so does mean:
Open Url when Country Token (e.g. FR - FRANCIA) for web extract is present in Excel, right

as mentioned give a try on Join DataTable activity

@ppr

May we ask you to share a screenshot from Excel.

was unanswered / not shared with us. Along with this sample we can give more hints as well

[Aguirre]

Hello, the configuration would be as I show in the image?
If so, how do I tell you to open the URL that matches the country?

I put a screenshot of my Excel, I just leave the related data.

[ppr (Peter)]

Join DataTable is not used for opening the URL. It is Used for calculating what is matching in EXCEL and web extract.

Can you confirm. Pais in Excel = PL – Pais in Webextract: Pl - POLONIA

If values are not the same we can do it e.g. LINQ

table 1 = Excel
Table 2 = web
Join Type inner
using Substring for Join condition

Join result will contain all matches and can be looped for URL Opening

[ahmed.abdo]

Assign 2 Data tables then
You use filter datatable activity.

[Aguirre]

I am not doing something right, I have tried to save the union of the tables in an Excel and it appears empty as I show in the image. Although the objective is that I select the url according to the country, I wanted to prove that I do the union of tables well and it seems that not.

[ppr (Peter)]

@Aguirre

A mini prototype:

find starter help here:
LINQ_InnerJoin_1Col_SubstringMatch.xaml (10.1 KB)

[Aguirre]

Hi that works for me, but I want to get it out of the loop, since I previously have “For each row of data” to loop through Excel with which I loop through all the records.

[ppr (Peter)]

@Aguirre

but I want to get it out of the loop, since I previously have “For each row of data” to loop through

not sure if I got your question. Can you elaborate more on it. What you want to achieve?

[Aguirre]

@ppr

not sure if I got your question. Can you elaborate more on it. What you want to achieve?

OK, I explain it step by step.

1.- The robot makes a query to a Database and stores the Excel,
2.- I open a website and according to the Excel country a new website will open (This is the point we are seeing).
3.- Then I will insert data from each Excel record on that website (This topic does not enter here).

I have used “For each row” to go through all the Excel records, once I read a record that selects the web URL and then I will continue with the other steps until I finish going through the Excel.

I am sorry if I have not expressed myself well.

[ppr (Peter)]

so if dtJoin has excel data and the url then it would better match?

[Aguirre]

Sure, the idea is to go through the loop with the excel data, but if they are already merged in the dtjoin, it would go through with the latter only.
I’m going to send you my structure and maybe I will see it more clearly and advise me something else

[Aguirre]

I have ordered the process and it only goes through the dtjoin, one last doubt to close the topic. Why do I have three windows open?

I have detected that at the end of the URL there are two spaces with the - and country, I suppose I would have to edit the URL

https://www/jsp/facturas/asignarBeneficiario.do?codigo=RO&nombre=RO - RUMANIA

[ppr (Peter)]

@Aguirre
Sounds good so far.

Give a try on following.

Open browser before for the url
Within the loop use attach browser activity