Convert JSON data into tabular/RDBMS format using Alteryx

convert-json-data-in-tabular-format-using-alteryx

 

 

This post will give you an idea to convert any JSON format data file into tabular format or more precisely in RDBMS format which can be either analysed on MS-Excel or can be dumped in any RDBMS System like MySQL.
For this we need a sample JSON file, I got one from HERE. Here is a preview of this file:

json-file-preview-2

 
IMG: sample JSON File
 

json-file-preview
 
IMG: sample JSON File
 
Now, lets start building Alteryx Workflow,

alteryx-workflow-for-json-to-tabular-format

Step-I: Take Input tool, do not set file as .json, rather select it as .csv file. Each JSON array may have lot of data, thus select Field Length as 254000, have a preview as shown below:

input-data-config

Second crucial step is put a Record ID tool, so that we can give each record a numeric ID, as shown in below screenshot:

preview-with-record-id

Next is, take a JSON PARSER tool, and make configurations as shown in image:

json-parse-config

Next is, take a CROSS TAB Tool, take a look at the configurations in below image:

cross-tab-config

How It Works:

We are treating JSON file as CSV file, then giving each record/object an ID, then spliting key/value pairs in each object , all will have same ID as object ID using JSON PARSER Tool, finally CROSS TAB Tool to transpose fields grouping by ID. which will give us final result as shown in below screenshot;

tabular-format-final-output

You may also like...

  • D Ganesh

    thank you for the above workflow explanation. it is working but giving first record only. may be because of my json data file is not with fixed columns. we have multiple fields around 60, in that only first 6 columns are only common. remaining columns are dynamic. please help me.