I have a number of identically structured json files. I'd like to load them into a single table such that each file is a row in the table. The table column names should be derived from the 'key' of the json files.
For a single file, I am able to use a simple crosstab and it works. However, when I try for multiple files using a Directory + Dynamic Input, followed by CrossTab, then only 1 row is created with comma separated values. What I need is for these to be in separate rows, not concatenated into a single row.
Any help is appreciated.
Solved! Go to Solution.
Hey @iiyoob,
Firstly, welcome to the community and congratulations on your first post.
Are you able to attach a simplified version of your workflow, and 2 or 3 of your JSON files to this thread, along with a quick table that shows the outcome you are looking for? That would really help to get you to a good solution as quick as possible (very similar to the way that the weekly challenge team do this).
Hope you have a good Sunday.
Sean
Hi @SeanAdams,
Below are two sample json files.
auto1.json
{ "AutomobileType": "tesla3", "LaunchIndex": 0, "ProductCodes": [], "PurchaseTime": "2017-02-20 23:35:27+00:00", "NetworkInterfaces": [ { "Attachment": { "AttachTime": "2017-02-20 23:35:27+00:00", "AttachmentId": "eni-attach-3u8fns94", }, "NetworkInterfaceId": "eni-4n4c9emf", "Status": "in-use", } ] }
auto2.json
{ "AutomobileType": "teslaS", "LaunchIndex": 0, "ProductCodes": [], "PurchaseTime": "2017-02-21 21:35:27+00:00", "NetworkInterfaces": [ { "Attachment": { "AttachTime": "2017-02-20 23:35:27+00:00", "AttachmentId": "eni-attach-j4j2b4j4", }, "NetworkInterfaceId": "eni-9dj38m33", "Status": "in-use", } ] }
I am able to get what I want using the 'jsonFileJoin.yxmd' workflow, but I need to be able to get the same output using the 'jsonFolderJoin.yxmd' workflow because I have thousands of such json files.
Thanks
-Ilyas
In order to get each JSON file on each row, you need to set a field to GroupBy in the Crosstab - ideally this would be some type of File_Id field. Since you can't pass any records through the Dynamic Input (other than the actual data that comes in), there are 2 ways you could do this:
1. Use a multi-row formula tool after the Dynamic Input to create a field that groups the appropriate records together by File
2. Use a batch macro to read in each file
I personally think the Batch Macro approach is easier (and less error-prone), as you can use the constant [Engine.IterationNumber] to easily make a File_ID field (this is done with the following expression in the Formula tool:
1+[Engine.IterationNumber]
You can then insert this macro instead of the Dynamic Input, and use the newly created FileID field as your Groupby field in the Crosstab
Sample workflow attached
That works @SophiaF ! I hadn't used batch macros before, so that's another thing I've learnt. Thanks!