How to convert JSON to CSV in Spotfire® using IronPython

Introduction

Spotfire cannot parse json files so its not possible to load them without converting them into a tabular format. This Python script demonstrates how json data can be converted into an object using the JavaScriptSerializer class,which can in turn be written to a csv file, ready to load in Spotfire.

Code sample

# Copyright © 2017. TIBCO Software Inc.  Licensed under TIBCO BSD-style license

# Here is a sample json data set-

myJson = '''[
{"Column 1": 1,"Sepal.Length": 5.1,"Sepal.Width": 3.5,"Petal.Length": 1.4,"Petal.Width": 0.2,"Species": "lily"},
{"Column 1": 2,"Sepal.Length": 4.9,"Sepal.Width": 3.1,"Petal.Length": 1.4,"Petal.Width": 0.2,"Species": "tuli"},
{"Column 1": 3,"Sepal.Length": 4.7,"Sepal.Width": 3.2,"Petal.Length": 1.3,"Petal.Width": 0.2,"Species": "jas"},
{"Column 1": 4,"Sepal.Length": 4.6,"Sepal.Width": 3.1,"Petal.Length": 1.5,"Petal.Width": 0.2,"Species": "rose"},
{"Column 1": 5,"Sepal.Length": 5.0,"Sepal.Width": 3.6,"Petal.Length": 1.4,"Petal.Width": 0.2,"Species": "lotus"}
]'''


from sys import argv
import clr
clr.AddReference('System.Web.Extensions')
from System.Web.Script.Serialization import JavaScriptSerializer

# Deserialize the JSON to a .net object
flowers = JavaScriptSerializer().DeserializeObject(myJson)

# Open the csv file in write mode 
target = open("C:temptestme.csv", 'w')

# Truncate if it exists
target.truncate()

# Add the column names
colnames="Species,Sepal.Length,Sepal.Width,Petal.Length,Petal.Width"
target.write(colnames)
target.write("n")

# Loop through the data to add it to the csv file
for f in flowers:
    row=f['Species'] + ',' + str(f['Sepal.Length']) + ',' + str(f['Sepal.Width']) + ',' + str(f['Petal.Length']) + ',' + str(f['Petal.Width'])
    target.write(row)
    target.write("n")

# Close the csv file after writing the data    
target.close()

References

• .net Serialization API

License:  TIBCO BSD-Style License