Skip to main content

API to CSV

Calls a JSON endpoint, puts one row per record into an in-memory table keyed on id, and writes the table out as CSV. No browser, so it needs no robot window and finishes in a fraction of a second.

The workflow​

users = HttpRequest("https://jsonplaceholder.typicode.com/users", "GET")

WriteLine("{{ len(users) }} records returned")

CreateTable("contacts", schema={"columns": [{"name": "id", "isKey": True}, {"name": "name"}, {"name": "email"}, {"name": "city"}]})

for user in users:  # _name="Collect contacts" _description="One row per API record, keyed on id so a rerun updates rather than duplicates"
    TableUpsertRow("contacts", {"id": "{{ user.id }}", "name": "{{ user.name }}", "email": "{{ user.email }}", "city": "{{ user.address.city }}"})

export = TableExport("contacts", "C:/Temp/rinkt-examples/contacts.csv", format="csv")
WriteLine("Wrote {{ export.rows }} rows to {{ export.filePath }}")

The run​

Activity completed [Create Table]         -> {"columns": 4, "rows": 0, "tableName": "contacts"}
Activity completed [Upsert Row in Table]  -> {"action": "inserted", "totalRows": 1}
...
Activity completed [Upsert Row in Table]  -> {"action": "inserted", "totalRows": 10}
Activity completed [Export Table to File] -> {"filePath": "C:/Temp/rinkt-examples/contacts.csv", "format": "csv", "rows": 10}
Activity completed [Print to Console]     -> Wrote 10 rows to C:/Temp/rinkt-examples/contacts.csv

And the file:

id,name,email,city
1,Leanne Graham,Sincere@april.biz,Gwenborough
2,Ervin Howell,Shanna@melissa.tv,Wisokyburgh
3,Clementine Bauch,Nathan@yesenia.net,McKenziehaven

What it got wrong first​

The first version had a Parse JSON between the request and the loop:

response = HttpRequest("https://jsonplaceholder.typicode.com/users", "GET")
users = ParseJson(response)     # wrong

It failed in a second:

Failed to parse JSON — invalid character 'm' looking for beginning of value

HTTP Request already returns parsed JSON. Its output is typed json, not string. Handing that to ParseJson hands it a map, and the 'm' the parser complains about is the first character of the map's own printed form. Use ParseJson for a JSON string you got from somewhere else — a file, a page, a column — not for a response.

Worth taking from it​

Key columns decide what a rerun does. {"name": "id", "isKey": True} is what makes Upsert Row in Table update record 4 on the second run instead of appending a second copy of it. Without a key column the activity requires append=True, and then reruns grow the file.

Name the loop, not the step inside it. # _name="Collect contacts" on the for line names the For Each on the canvas and in the logs. Control-flow metadata goes in a comment on the header line, because the statement has no argument list to put it in.

A Group hides its variables from the validator. Wrapping the request and the parse in with Group("Fetch"): made the loop below fail validation with Variable 'users' is not defined — the collection could not see a name assigned inside the group. Assign at the top level and group the steps that do not produce anything you need later.