Extract Data from API

[ChristianZiegle]

Hello guys,

we are trying out for the first time to develop a bot with API function.

So far we managed to create the connection and extract the data. Within Blue Prism  Web API Services and also via Python.

Both ways we extracted a data set of 100.000.000 characters.

Could someone help me out how I can make the data useable for example as a collection or filter it?

I don't know how to move forward now.

Thank you very much in advance,
Christian


------------------------------
Christian Ziegler
Financial Controller RPA Developer
Daimler AG
------------------------------

[ewilson]

Hi @Christian Ziegler,

Can you provide a bit more detail regarding the data you've collected? When you say you've extracted 1M characters do you mean the output from your API is a character or byte array, a stream of characters, ....? What API are you calling? Most web APIs I've worked with tend to return data in an XML/JSON form. If there's raw binary data being returned it tends to be a file (ex. PDF).

Cheers,
​

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

[ChristianZiegle]

Hi Eric,

Thanks for your answer!

How do I know which kind of API am I calling?

Please let me know if you need more information.

Thank you,
Christian

The data looks like this:


Blue Prism Process:
and Headers:

------------------------------
Christian Ziegler
Financial Controller RPA Developer
Daimler AG
------------------------------

[AnuraagPandey]

Hi Christian,

So your data is in .xml format, the type is chunked and this is something pretty common when you transfer lot/huge amount of data via an API call, data is sent in packets, so to say. 

Now, can you briefly explain as to what you intend to do with the data?

------------------------------
[Anuraag] [Pandey] [RPA Consultant]
Germany
------------------------------

[ewilson]

Ok, so it looks like your response data is XML. There are a few different utilities on the Digital Exchange for working with XML including:

• Utility - XML VBO (Blue Prism)
• Utility - XPath VBO (Blue Prism)
• Utility - XML VBO (Boost Robotics - Paid Asset)

Alternatively, you could use a code stage to process the XML, or you could even do it within the Web API service definition on the Response tab as pictured below:


This assumes you have some knowledge of the layout of the XML, and the last suggestion assumes you have some experience with programming in C# or VB.NET. 

I'd probably start with one of the VBOs mentioned above and see if you can pull out specific elements and then push them into a Collection that you define. If you're simply wanting to search and pullout certain data from the XML you might try the XPath VBO and see what you can accomplish with XPath expressions (essentially a way of search through XML for specific elements).

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

[ChristianZiegle]

Hello Anuraag,

The goal is to create a collection of the data which we need to insert into SAP and replace the step: login to the webpage, filter and download the data in 2 different Excel files.

The Excel files which we download right now with the first bot contents different columns such as the Key which we have to enter in SAP. We use a filter to get only the data for E1 approval and E2 approval (Amount over 10.000€ and over 100.000€)

If possible I would like to have a sorted table with the data I need for SAP.

------------------------------
Christian Ziegler
Head of RPA
Daimler AG
------------------------------

[ChristianZiegle]

Hi @ewilson,

please have a look on the screenshot below. How do I have to fill out the action to get the yellow marked data.

Unfortunately I do not have any experience with C# or VB.NET

Thank you very much,
Christian

​

------------------------------
Christian Ziegler
Head of RPA
Daimler AG
------------------------------

[ewilson]

@Christian Ziegler,

I'd need to see the entire XML contents to understand its structure. For example, the "d" preceding the ATT_KEY is probably tied to some namespace definition. If so, you'd need to supply that as input to the action.

Can you post the XML as an attachment or is it proprietary/secured information? If it is proprietary DO NOT POST IT.

There is another action on that VBO that will return all of the node and their related XPath expressions in a Collection. You might take a look at the output from the action and see if it returns an XPath for that specific element.

Cheers,
​

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

[ChristianZiegle]

Hello @ewilson​,

after a long tryout session I managed to extract the data which I am looking for.

I understand now the basic funcionality of the XML VBO and I feel being able to finish the whole process.

A big thank you for your help!!!

Have a good evening,
Christian 




------------------------------
Christian Ziegler
Head of RPA
Daimler AG
------------------------------