This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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).
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:
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