Write to a Particular Cell if Value is a match

activities
#1

Hi All :slight_smile:

I have two files - CSV and XML. I want to match the ID stored in both files. Upon a match than enter a variable on the matched row in CSV file. I able able to read xml file and have two list which has the ID> I am not sure if this is the best way but would love to hear any other options. I am stuck on how to enter in a particular law if its a match.

e.g
CSV
ID Name
12 Radha
16 Krishna

XML
Service ID 175
ID 16

Expected Result- CSV
ID Name Service No.
12 Radha
16 Krishna 175

I will really appreciate if anyone could help me with this :slight_smile:

#2

Hi Senshil_Kumar,

Are you familiar with joining datatables?

Assuming your DT1 = CSV, your DT2 = XML, you may use the join datatable activity specifiying your join type as left join then compare if DT1.ID =DT2.ID.

You may want to research on join datatables and left join. :slight_smile:

#3

You will want to read the XML doc as a string then deserialize it.

image

Once it is now held as an XDocument variable, you can access the values in it using the tree structure of the data, taking into account any namespaces that are used:

image

You can then build your datatable of ID/Service IDs from that.

Once you have achieved that the matching process is straightforward using the ‘lookup range’ activity

#4

Thanks Emman, it may not apply to this as the service ID is not on a column but the header of the XML file, but i have other tables that i could use it for. I will definitely look into join data table option for other projects i am working on. :slight_smile:

#5

Hi @ronanpeter Thanks for your suggestion. I have completed all the steps, its just writing to corresponding cell if its a match. how do we reference that cell?
Thanks again :):grinning:

#6

So if you have your DataTable of ServiceIDs/IDs taken from the XML data it should look something like this:

ID ServiceID
16 175
22 190

So you want to essentially do a vlookup between the two datatables. There are a number of ways to achieve this and you should monitor performance if your dataset is large. However, for me, this is the easiest. Just read in both datasets and use the Lookup DataTable Activity - The CSV datatable lookups up the XML datatable to return a ServiceID, if available.

The CSV datatable is then updated and exported.

Sequence.xaml (11.3 KB)

Input.xlsx (9.1 KB)

How to compare column values from two different tables and if they match then paste the value present in the first excel sheet into another
#7

hi @ronanpeter. Thanks so much for this. I get this error -" Acitivitty could not be loaded" for Lookup Data table when opening the xaml file. I have checked the updates in manage package, however its not availabe. How do i get this activity loaded.

Thanks again for your kindness and effort to assist with this issue :slight_smile:

#8

@Senshil_Kumar

It is available as a core activity from version v2018.2. of UiPath Studio.

You should check that you have a version from then.

#9

@ronanpeter. Thank you :slight_smile: