community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More
SOLVED

How to load multiple json files into a single table?

Highlighted
Meteoroid

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.

Aurora
Aurora

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

Meteoroid

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

Moderator
Moderator

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]

GcjX9O

 

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

 

haIxyT

 

Sample workflow attached

Sophia Fraticelli
Customer Support Engineer
Meteoroid

That works @SophiaF !  I hadn't used batch macros before, so that's another thing I've learnt.  Thanks!

Labels