Add value from DataTable1 to Column of DataTable2

Hi! I have a similar situation. I have 2 tables—Clients and Letters. The Clients datatable has 2 columns—ClientName and Completed. The Letters datatable has 4 columns—ClientName, SentDate, LetterId, and LetterType. What I want to do is have the bot scrape the list of Clients and place the list in the Clients datatable. It will then perform a For Each Row action using the Clients datatable. The For Each Row activity will search each client name, click on a few links, then scrape another table and place the list in the Letters datatable. When it scrapes the data, I want it to add the ClientName for each row of the new datatable (Letters) where the ClientName column is null or blank.

The process has activities that build the datatables because there are columns that do not exist in the scraped data tables—the Clients table on the website does not have “Completed” whereas the Letters table on the website does not have “ClientName.”

1 Like

Which part(s) are you having trouble with?

[quote=“SynergiBot, post:1, topic:248076”]
When it scrapes the data, I want it to add the ClientName for each row of the new datatable (Letters) where the ClientName column is null or blank.
[/quote]

Hi Dave! This is where I’m stuck… For each row that is scraped in the 2nd scraping process where the ClientName is missing, I need the ClientName from the 1st datatable to be entered.

Within the For Each Row activity, I have an Assign activity that assigns row(“ClientName”).ToString to variable CN. The bot performs several more steps (clicks, type into, etc) before it scrapes from another table. After the 2nd table is scraped, I have an If activity with the Condition string.IsNullOrEmpty(“ClientName”).

Nothing I do works. When I write to csv, it always writes the 2nd datatable without the ClientName from the 1st datatable.

Basically, I need the Client Name value of the 1st table to be placed on each Client Letter row of the 2nd table. I have 95 clients currently for which I have to scrape each one’s data. My hope is to have a final datatable that houses client names and their associated data on each row.

Hope that was clear!

Here’s my .xaml file.

Main.xaml (52.8 KB)

I’ll take a look - Can you let me know which activity packages/libraries you are using? I am getting a bunch of ‘unresolved activity’ errors when I try to open your .xaml file.

You can see which activities you’re using by looking in the Projects tab and letting me know what all is included in the ‘Dependencies’ portion. You could also press ctrl+p to show these dependencies, or you could open the project.json and copy+paste the dependencies from there

Ahh! I knew that I installed some packages, but I wasn’t aware that I was actually using what I installed. Here you go, and thank you! Everything I can think of just doesn’t work for me. I’m even thinking about exporting the 2nd datatable to Excel then having UiPath add the clientname as it reads each row before it acts on the next client in the list.

image001.png

image002.png

Turns out it wasn’t the missing balareva activities, it was because i was using the 2019.10 edition so the newer core activites weren’t loading. Fixed that now by switching to a different computer :slight_smile:

The first thing that jumps out to me is in your first ‘for each row’ activity, all of the assigns are likely not working at all. When you want to pull a specific value from a specific column in a datarow, you have to reference the row.item() property. So you should update it to be assign URL = row.item("URL").ToString Do this throughout your whole workflow and see if that fixes the issue

Note that if you want to update a column in the datatable you DO want to have it be something like assign row("YourColumnHere") = YourStringVariable but if you want to pull out the existing value within the current row’s column, you need to access the item property by doing row.item("YourColumnName") or row.item(ColumnIndex)

row(“URL”).ToString actually works. I tried changing everything over to row.item(“ColumnName”) or row.item(ColumnIndex) and it still didn’t add the ClientName from Table1 to each row of Table2.

Ok… I made some modifications to the process. I moved the Message Box around to several spots to see what existed in the ClientName field. I finally ended up placing a For Each Row after the 2nd scrape. Within the For Each Row is the If activity.

  • Condition: string.IsNullOrEmpty(“ClientName”)

  • Then: Assign row.item(“ClientName”) = CN

  • Else: Message Box = row(“ClientName”).ToString

When it got to that part of the process, it moved to the Message Box. This means that the field is not empty, right? If that’s the case, then I’m really confused because the Message Box isn’t showing anything in it.

Here is the updated .xaml file.

Main.xaml (54.5 KB)

I think I figured it out. I don’t know if this is the best way to do it, but it works. In the sequence that builds the datatables, I moved the Write CSV activity. I also removed the If activity from the 2nd For Each Row activity and just placed the Assign activity in it. Then, I added an Append to CSV activity. So far, it seems to be working.

@SynergiBot I see you got your issue resolved, but I just wanted to comment on this portion as it can help you in future builds

You are checking if the string “ClientName” is null or empty. It will always be false because you are literally checking “ClientName”. If you want to check the ClientName field in your datatable you have to specifiy it as String.IsNullOrEmpty(row.item(“ClientName”).ToString) instead.

Also, sometimes you may be finding whitespaces (that wasnt the case this time, but it could in the future) - if that is possible, you may want to change your if statement to be String.IsNullOrWhiteSpace() instead, as that will check the string for null, empty, or whitespace entries

2 Likes