Data Table to JSON Format String - Python Script

I received a request for help from one of my friend who came across the requirement to get Data table information in JSON format with the same column order as per Data Table.

There is an option to convert the data into JSON string directly by using inbuilt JavaScriptSerializer method. It takes Python Dictionary or Set as input to Serialize data into JSON format. But as we store information in Python Dictionary or Set, the order of the columns are not retained.

In order to get the same column order as per Data Table, we can make use of simple String functions to achieve the results.

Lets assume here is our Data table-

Below is the Python code which contains both the ways (using
JavaScriptSerializer and simple String functions)  to compare the results.

from Spotfire.Dxp.Data import *
import clr
from  System import *
from datetime import datetime
clr.AddReference('System.Web.Extensions')
from System.Web.Script.Serialization import JavaScriptSerializer
import cStringIO

## get count of rows in data table
rowCount = dataTable.RowCount

## create index set to loop over rows
## The True specifies this IndexSet contains row index values.
allRows = IndexSet(rowCount,True)

## this index set we will populate with rows to mark - the False specifies it is not populated
rowsToInclude = IndexSet(rowCount,False)

## create a cursor so we can refer to the column of interest
columnCursor = DataValueCursor.CreateFormatted(dataTable.Columns["EMPNO"])

## Loop through all rows
for row in dataTable.GetRows(allRows, columnCursor):
  rowIndex = row.Index
  rowsToInclude.AddIndex(rowIndex)

## rowsToInclude IndexSet now has a list of rows

## Here's how you create a RowSelection object in case you need one of those
rowSelection = RowSelection(rowsToInclude)
cnt=0
counter_rows=0
cols = dataTable.Columns
colcount=cols.Count
data=[]
output = cStringIO.StringIO()
output.write('[')
for r in allRows:
 cnt=cnt+1
 counter_rows=counter_rows+1
 item={}
 i=1
 output.write('{')
 for c in cols:
        colname= c.Name
        value=c.RowValues.GetFormattedValue(r)
        item[c.Name] = c.RowValues.GetFormattedValue(r)
        output.write('\"'+colname+'\"'+':')
        if (colcount==i):
            output.write('\"'+value+'\"')
        else:
            output.write('\"'+value+'\"'+',')
        i=i+1
 data.append(item)
 if(rowCount==counter_rows):
    output.write('}')
 else:
    output.write('},')

output.write(']')


json=JavaScriptSerializer().Serialize(data)
print json[:256]

contents = output.getvalue()
print contents

Here are the results

[{"HIREDATE":"12/17/1980 12:00:00 AM","ENAME":"SMITH","Test":"test","EMPNO":"7369","DEPTNO":"20"},{"HIREDATE":"4/19/1987 12:00:00 AM","ENAME":"SCOTT","Test":"test","EMPNO":"7788","DEPTNO":"20"}]


[{"DEPTNO":"20","EMPNO":"7369","ENAME":"SMITH","HIREDATE":"12/17/1980 12:00:00 AM","Test":"test"},{"DEPTNO":"20","EMPNO":"7788","ENAME":"SCOTT","HIREDATE":"4/19/1987 12:00:00 AM","Test":"test"}]


Yellow: Using JavaScriptSerializer
Green: Using Simple String Functions




 Hope this helps in case you come across this requirement to convert Data Table to JSON String :)