Join Between a JSON Input (Through API) and a CSV file

[Daniel_Figueredo]

Hello all,

I need to JOIN the data between two different inputs, one coming from an API (Json) and another coming from a CSV file and output the result as a CSV file.

My biggest problem is with the JSON input, since I'm not being successful on converting it to a CSV with the JSON_NAMEs as the column titles so it matches the other input.

I am currently using the download tool and the json_parse but dont know what else to use after those.

Does anyone have any idea on how to this?

[morr-co · Accepted solution]

Hi @Daniel_Figueredo : here are the steps that I usually go through to reformat JSON into a tabular format:

1. Use the Download tool to submit the request
2. Use the JSON Parse tool to extract the DownloadData field
3. Use a Text to Columns tool to split the JSONName field on the period separators
4. Here you may or may not need to filter the records depending on the JSON response. For example, if you have a "data" object, or a "request" object, you will potentially want to isolate the objects separately. 
5. Use a Cross Tab tool to convert the vertical data into a horizontal table. The JSON Parse tool automatically creates a numeric record id for each record. You will to need group by this id to ensure each record gets its own row.

[Daniel_Figueredo]

Hi!

I managed to build the workflow with your input, thanks a lot!

Best Regards,

Daniel