Web Service to Data Table

#This script calls a webservice, parses JSON and dumps data to a datatable.

#GETS JSON DATA FROM URL
def getData(url):
import clr
clr.AddReference('System.Web.Extensions')
from System.Net import WebClient
from System.Web.Script.Serialization import JavaScriptSerializer
from System.IO import StreamReader

#Get the json data from web service
wc = WebClient()
wc.Headers.Add('Content-Type', 'application/x-www-form-urlencoded')
requestData= wc.OpenRead(url)
reader = StreamReader (requestData);
response = reader.ReadToEnd()

#Parse the results
js = JavaScriptSerializer()
responseDict = js.Deserialize(response,object)
return responseDict

#Convert JSON to tab-delimited text format
def Dict2TXT(dict, delimiter="\t"):
# Get first row to get headers
data=dict
tmp=[]
for x in data[0]: tmp.append(x.Key)
textData = "\t".join(tmp) + "\r\n"

#Get the rest of the data
for x in data:
tmp=[]
for y in x: tmp.append(y.Value)
textData += "\t".join(tmp) + "\r\n"

return textData

#Read tab-delimited text format in Spotfire
def txt2table(textData, dataTableName="Web Service Data",delimiter="\t"):
from System.IO import StreamWriter, MemoryStream, SeekOrigin
from Spotfire.Dxp.Data.Import import TextFileDataSource, TextDataReaderSettings
from Spotfire.Dxp.Data import DataType, DataTableSaveSettings

#Make a stream from the string
stream = MemoryStream()
writer = StreamWriter(stream)
writer.Write(textData)
writer.Flush()
stream.Seek(0, SeekOrigin.Begin)

#Set up the text data reader
readerSettings = TextDataReaderSettings()
readerSettings.Separator = delimiter
readerSettings.AddColumnNameRow(0)

#Create a data source to read in the stream
textDataSource = TextFileDataSource(stream, readerSettings)

#Add the data into a Data Table in Spotfire
if Document.Data.Tables.Contains(dataTableName):
Document.Data.Tables[dataTableName].ReplaceData(textDataSource)
else:
newTable = Document.Data.Tables.Add(dataTableName, textDataSource)
tableSettings = DataTableSaveSettings (dataTableName, False, False)
Document.Data.SaveSettings.DataTableSettings.Add(tableSettings)


#GETS JSON DATA FROM URL
data = getData("http://services.groupkt.com/country/get/all")
dict = data["RestResponse"]["result"]

#Convert JSON to tab-delimited text format
txt = Dict2TXT(dict)

#Read tab-delimited text format in Spotfire
txt2table(txt,"Countries")

No comments: