Extract and verify data in XML and write it in Excel

Hi,

im trying to extract data from a specific TAG in an XML file and write to an Excel cell.

Problem: I need to verify the data in the XML TAG and verify that is corresponding to my rules.
Rules: The TAG must have 8 digits.

When the data is verified ill need to write it to a specific cell in Excel.

which is the best way to do it?

Thanks in advance
Milo

@Milo_Soderberg
welcome to the forum

In general we do use the UiPath.WebApi.Activities and from there the Deserialize XML activity
the returned XDocument variable we use for further processing

Depending on your XML we mabye have to handle also the XML Namespaces

for the retrieval we also have different options like API, LINQ for XML, Activities

Thank you :slight_smile:

I have Deserialized the XML and use a For each loop to iterate through the xdoc variable. The TAG is named 78781212

ive tried the activity Is Match to verify the data in the TAG. And it seems to work but i dont know how to write the verified data in to Excel.

Regards
Milo

lets do not mix up xml element tag name and its value.

Can you share with us

  • xml
  • screenshot of the relevant part of the modelling with details
  • expected output datatable

thanks

ok. ill agree. data=value :slight_smile:

Im sorry but I dont have access to the XML and my automation now. I will post it as soon as i can.

please reformat your sample xml with the editor </> format button, so we can the sample in proper format. Thanks

dear ppr

here are the information you asked for.

UipathForum_Excel.xlsx (8.1 KB)
WriteCell_IndividualNo_Try to verify_IndividualNo.docx (3.1 MB)
XML Uipath.txt (628 Bytes)

ill took three Uipath screenshots

  1. For Each Loop
    2.Write cell
  2. my try to verify that the value had 8 digits.

thanks
Milo

@Milo_Soderberg
Vars:

Preperation:

Retrieval:

Result:
grafik

With regex we ensured the 8 digit Requirement

Find starter help here:
ppr_XMLBox_MiloSöderberg.xaml (13.7 KB)

Data_MiloSöderberg.xml (606 Bytes)

Dear ppr,

Oh! iit was more elaborate then i thought it would be. Ill appreciate your help. thank you so much. i will try this out.

If i want to write the values in a existing Excel sheet with fixed headers. how would you do that?

regards
Milo

writing out the datatable dtData to Excel we can dow with

  • Excel Application Scope
  • write range (check addHeader setting) or append range (when e.g. the sheet is already prepared)

It works…Thank you!

If the value in IndividualNo are not 8 digits then i need to throw an exception. Because that value is vital.

I have tried an IF activity before the Excel Application scope. But i cant get the condition of the IF activity correct.

Please advise.

give a try on:

xDoc.Root.Descendants("Individual").All(Function (x) Regex.IsMatch(x.Element("IndividualNo").Value,"^\d{8}$"))

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.