How to add prefixes to data table

datatable
uiautomation
studio

#1

I’ve got a setup that does data scraping to grab hundreds of links from certain sites and lists them in the data table, which gets exported as a spreadsheet. Problem is, certain sites only give me incomplete URLs, missing the https and site name prefix at the start. So I have tried using the “for each row” function, to try to add this prefix to each cell in the URLs column. But I’m unsure of how to do this.
Alternatively, I’d like to know why the data scraping isn’t pulling the full URL from some links.


#2

For example, the full link when i manually click them, is “https://www.indeed.com/rc/clk?jk=bac78eb47a0b93a9&fccid=a1a304ce21ebd16f”, but UiPath only pulls "/rc/clk?jk=bac78eb47a0b93a9&fccid=a1a304ce21ebd16f"
And that’s why I wanted to automatically add the “https://www.indeed.com” prefix as it creates the data table.


#3

Hello!

You can add the prefixes using the following code in a For Each Loop:

row.item(“yourVariable”) = row.item(“yourVariable”).ToString.Insert(1,“https://”)

Let me know if works.

Regards,


#4

I added a “write line” activity inside the “for each row” activity, and put in the code “row.item(“Prefix”) = row.item(“Prefix”).ToString.Insert(1,“https://www.seek.com.au”)” But it’s telling me Option Strict On disallows the ‘=’ operand. Am I doing it wrong, or can I just turn off the ‘strict’ option somewhere?


#5

Try something like this:

image


#6

I’m getting an error from varPrefix. Apparently I need to declare that variable. But shouldn’t this very action be setting up the variable? How else can I set up the variable? Many other languages like javascript and GML let you create and assign a value to a variable all in the same action.


#7

Oh, sorry. I worked it out. I’m quite new to this program, lol.


#8

Can you tell me what you typed in that middle box? The text is cut off. I assumed it’s varPrefix.Insert(1,“Prefix”). Is that correct? And I replace “Prefix” With my https://?
There are no apparent errors in the workflow, but when I run the workflow, and it grabs the links, I get this error:


#9

Hello!

There’re a mistake on your code. On the command “row.item(”")" you must have to pass the column name.

Example:

row.item(“colunmName”)

Regards,


#10

You could try messing with the data scraping yourself, changing it to get the element and then get attribute. Or you could write a custom javascript that gets you the proper link text. Or you could use Regex to check if the prefix is missing, and insert it if it is. You have many options :slight_smile:


#11

ericmyrs Thanks, But I’m still a beginner with this software, so Whatever is simplest will work best for me. Lucas’s explanation will probably help. I’ll try to complete and test that method first, since I’m already working on it. If His method doesn’t work, then I’ll move on to trying yours. Thanks.


#12

Lucas, Thanks for your response. But I’m still not sure what to put in that middle box, due to your example text being obscured. I tried you suggestion, but I still have and error resulting from that middle box.


#13

Make sure your varPrefix is of type String.

Late binding usually indicates that you’re trying to call a method that is not available for that type, f.e. calling .Insert on an Object.


#14

Ah thanks, yeah it was set to string. (I set it to object after I thought string was causing an issue) Anyway I changed it to object, and the error went away. However it still isn’t adding the prefix


#15

Interesting…
What shows if you MessageBox the varPrefix?
Are you writing the results back to the excel?

Could you also try without Insert?
Just do the simplest approach of string concatenation:

In Assign:
row.Item("URL") = yourVariableWithPrefix + row.Item("URL").ToString()

#16

oh btw, in case it was confusing, “URL” is the name of the column. hopefully that doesn’t change anything.


#17

Oh wow, I just realised that I had the order messed up. Editing the Data table is being performed AFTER the table is written to the spreadsheet. I’ll amend that and get back to you.


#18

Alright, I fixed the order, still no success. So I reverted it back to before making the change that andrzej suggested, and it seems to be working now. Except it’s moving the first letter of the stock url before prefixng it, and putting it at the very start, before the prefix. So instead of being this: "https://www.aarp.org/job/clinical-operations-manager-plainfield-plainfield-illinois-23621117"
It’s giving me this: “jhttps://www.aarp.org/ob/clinical-operations-manager-plainfield-plainfield-illinois-23621117” Notice how the j from job, is now at the start. I wonder what’s causing that?


#19

If you’re still using the .Insert method, it’s actually working as designed.
Since you’re passing index = 1, it will start from the second character (indexing is 0-based).
Try with 0 as the first parameter or just use the concatenation method.

More on .Insert on MSDN:
https://msdn.microsoft.com/en-us/library/system.string.insert(v=vs.110).aspx

It’s actually quite “hilarious” that none of us spotted this so far. Simple things, it’s always them :wink:


#20

yeah, it was caused by the (1, part in the middle box. I changed it to a 0, and it works now. Why did they use 1 as an example? What would that be useful for?