How to save Json api respont to datatable (Invoke Code)

[UnyBots]

Hi Everyone. I’m new with OpenRPA and I need some help.

I am using “Invoke code” to do a “API Call” in python, and I wand to pass the response in a openrpa variable and save it in a datatable or openflow database (mongodb).
This is the workflow and I have created and variable “newObjects” with type “System.Object[]”:

In Invoke code I get some element of each “records” and create a new object and append each in “newObjects”.

The problem is that “newObjects” always come “null”. This there other way to do this?
Or how do I get the response from from python script and pass ou save it to the datatable?

Thanks.

[Allan_Zimmermann]

It should be possible but dont have time to recreate this and lookfor solution right now, so it might be easier to just send the json as a string variable and load it using JArray.Parse or to serialize it into a datatable like this

Convert JSON to DataTable

[kowts]

Hi @UnyBots

I agree with @Allan_Zimmermann suggestion.

But I solved the same thing, by converting the json to datatable direct in python. You can create a new variable “dataTable” in OpenRPA with type “System.Data.DataTable”.
Then you can import the library pandas into your code. Don’t forget to install it if needed.

I recommend you to use “newObject” as a array with key-value pairs (dictionary).

import pandas as pd

[...]
   for record in data['records']:
        newObject = {
            "key1": record['Nº de Pedido'],
            "key2": record['Título'],
            "key3": record['Requisitante'],
            "key4": record['Email'],
            "key5": record['Catálogo'],
            "key6": record['Categoria (último nível)'],
            "key7": record['Data de Criação'],
            "key8": record['Estado']
        }
        newObjects.append(newObject)

    # Create a DataFrame from the newObjects list
    df = pd.DataFrame(newObjects)

    # Convert the DataFrame to a datatable
    dataTable = df.to_dict('records')

    # Print the datatable
    print(dataTable)

After executing the code you can use the variable “dataTable” in your workflow and do whatever you need/want.
I hope this helpes you.

[UnyBots]

Hi @Allan_Zimmermann and @kowts

Thanks to both. I was able to implent the suggestion from @kowts and works perfecty, this way was possible for me to finish my workflow and execute it sucessfully.