Data Table to JSON

Sometimes you want to serialize the data table into a JSON string so you can feed javascript widgets that require such format. In this example, we get the filtered rows from a given data table and generate it's JSON

Consider the following input table

The output should look like this after running the script:


#get filtered rows
#dataTable can be a Script parameter pointing to a specific Data Table. In this case, we use the current one
dataTable = Document.ActiveDataTableReference

#get rows and columns from our selection
rows = Document.ActiveFilteringSelectionReference.GetSelection(dataTable).AsIndexSet()
cols = dataTable.Columns

#generate our "json" data
for r in rows:
 for c in cols:
  item[c.Name] = c.RowValues.GetFormattedValue(r)

#convert json to string
import clr
from System.Web.Script.Serialization import JavaScriptSerializer

#save to document property to use later in javascript

Note:  If you want to limit the number of columns in your output, you can use the FindAll method. Suppose you have many columns that contains the word Project (Project Name, Project Date, Big Project, Small Project and you also want to include the Budget column, then you can use the Spotfire Search Syntax

for c in cols.FindAll("*Project* OR Budget"):
  print c

No comments: