Transform JSON file to table format CSV file

An exported Analytics Report from Oracle Service Cloud Right Now API (reference) is a Json file with the following format:

{
	"count": 2,
	"name":	"filename",
	"columnNames": [
		    "Name1",
        	"Name2",
        	"Name3",
        	"Name4",
        	"Name5"
	],
	"rows": 
	[
		[
	     "ValueColumn1Row1",
         "ValueColumn2Row1",
         "ValueColumn3Row1",
         "ValueColumn4Row1",
         "ValueColumn5Row1"
		],
		[
		 "ValueColumn1Row2",
         "ValueColumn2Row2",
         "ValueColumn3Row2",
         "ValueColumn4Row2",
         "ValueColumn5Row2"
		]
	],
	"links": 
	[
		{
		"rel": "self",
		"href": "https://somecompany.custhelp.com/services/rest/connect/v1.4/analyticsReportResults"
		}
	]
}

But in order to process the data I need a table format in csv. The report in this example has five columns with two rows of values.

I tried Powery Query and different online converters. But they don't seem to identify the column names as headers and set the corresponding values in the columns. Except for the "links" section.

In human language I can define the transform process something like this:

1. Define the columnnames from "columnNames"
2. Read the values from "rows" (every five rows per item)
3. Set every value in the corresponding column
4. Save the result as a CSV file

But my programming skills are very very basic. I hope someone can shed some light on how to do is.

Kind Regards

You can easily achieve that with the help of python and the pandas library

To install:

pip install pandas

then create a python file with the following content:

import pandas as pd
import json

# assuming json_data is your loaded json data

json_data = {
    "count": 2,
    "name": "filename",
    "columnNames": [
            "Name1",
            "Name2",
            "Name3",
            "Name4",
            "Name5"
    ],
    "rows": [
        [
         "ValueColumn1Row1",
         "ValueColumn2Row1",
         "ValueColumn3Row1",
         "ValueColumn4Row1",
         "ValueColumn5Row1"
        ],
        [
         "ValueColumn1Row2",
         "ValueColumn2Row2",
         "ValueColumn3Row2",
         "ValueColumn4Row2",
         "ValueColumn5Row2"
        ]
    ],
    "links": [
        {
        "rel": "self",
        "href": "https://somecompany.custhelp.com/services/rest/connect/v1.4/analyticsReportResults"
        }
    ]
}

# Converting json to dataframe
df = pd.DataFrame(json_data['rows'], columns=json_data['columnNames'])

# Write DataFrame to CSV
df.to_csv('output.csv', index=False)

Explanation of the script:

1. Importing the necessary libraries (pandas and json).
2. Reading the JSON data. In this case, I have defined the JSON data directly in the script. In your case, you might read it from a file.
3. Converting the JSON data into a pandas DataFrame. This is a tabular data structure that pandas provides which is very similar to a table in a database.
4. Writing the DataFrame to a CSV file. The index=False parameter is used to prevent pandas from writing row numbers.