Spotfire provides a variety of mechanisms for accessing data, including local files, relational databases, web services, and everything in between. One request which has come up more and more is the ability to perform data entry using Spotfire. This could be to store data in the underlying data source, it could be for a quick approach to analyze new data in the Web Player, it could be for updating properties in the existing Data Table, or it could be to add rows or columns into a Data Table similar to how you would add them into an Excel file.
We will explore all these requirements in future tips. In this tip, we will focus on how to use a Spotfire file as a data source itself and load data into it one row at a time. In this scenario, we will want Spotfire to keep track of all our training course registrations. Rather than entering them one at a time in Excel or in a database, we will enter them directly for input into Spotfire, appending them to the existing Data Table.
There are a variety of options for entering the data. You can use an HTML form which is outside of Spotfire to enter the data, and then use the Web Player APIs to load the data into Spotfire, or you can use a Text Area inside an existing Spotfire analysis file directly. In this tip, we will discuss the latter.
To start, we should load one row of our training registration data. This would be to initialize the Data Table and Spotfire Analysis file. For this example, the format of the Data Table is shown below.
Then, we need to create the Text Area and Property Controls to allow the user to enter the data. You can choose to simply use the out of the box features in a Text Area to create something similar to the image below:
Or, you can use a Script to create a custom layout for your Text Area, similar to what we discussed in earlier posts. A sample of the information above, displayed in an HTML table, is shown below:
Once you create the Text Area and Property Controls that you would like to use, you will need to create a Script Control which will add the data to the currently loaded Data Table. Assume we have each property control passed in as a parameter, and we call those parameters customer, type, date, costs, location, and company, the following script would add this information as a new row to our Data Table.
import clr
clr.AddReference("SpotfireTraining.ArrayDataSource, Version=1.0.0.0, Culture=neutral, PublicKeyToken=489d6add0b459834")
from SpotfireTraining.ArrayDataSource import ArrayDataSource
from System import Array,String,Object
from Spotfire.Dxp.Data import AddRowsSettings
from Spotfire.Dxp.Data import DataType
temp = Array[object ]((customer,type,str(date),str(costs),location,company))
ads = ArrayDataSource(temp, Document.ActiveDataTableReference)
ars = AddRowsSettings(Document.ActiveDataTableReference, ads)
Document.ActiveDataTableReference.AddRows(ads,ars)
This example uses an ArrayDataSource, which is built using the full C# SDK. This allows us to pass information into Spotfire from an array. If you did not have access to the SDK to create the Array Data Source, you can still use this functionality, but it requires you to use the built-in TextData Source. The code for this solution is shown below:
from System import Array,String,Object
from System.IO import Path, File, StreamWriter
from Spotfire.Dxp.Data import DataTableSaveSettings
from Spotfire.Dxp.Data.Import import TextFileDataSource
from Spotfire.Dxp.Data.Import import TextDataReaderSettings
from Spotfire.Dxp.Data import AddRowsSettings
from Spotfire.Dxp.Data import DataType
#Temp file for storing properties
tempFolder = Path.GetTempPath()
tempFilename = Path.GetTempFileName()
# generate data and write to file
f = open(tempFilename, 'w')
f.write(customer + '\t' + type + '\t' + str(date) + '\t' + str(costs) + '\t' + location + '\t' + company + '\n')
f.close()
# read file data and replace data table content
readerSet = TextDataReaderSettings()
readerSet.Separator = "\t"
readerSet.SetColumnName(0, "Customer")
readerSet.SetColumnName(1, "Type")
readerSet.SetColumnName(2, "Date")
readerSet.SetDataType(2,DataType.Date)
readerSet.SetColumnName(3, "Costs")
readerSet.SetDataType(3,DataType.Integer)
readerSet.SetColumnName(4, "Location")
readerSet.SetColumnName(5, "Company")
textDataSource = TextFileDataSource(tempFilename, readerSet)
ars = AddRowsSettings(Document.ActiveDataTableReference, textDataSource)
Document.ActiveDataTableReference.AddRows(textDataSource,ars)
Once completed, you know have a Spotfire Analysis file, which includes the data entry component for your solution , the analytic component, and also stores the data (the dxp file is the data source). When the user enters the new information in the Property Controls and clicks on the Script Control button, the data is added to the existing Data Table as a new row and then persisted in the Spotfire Analysis file. All the existing pages and visualizations are automatically updated to analyze the latest data, including the row that was just added.
If you have a need to use this data in other applications, or you wish to store it outside the Spotfire Analysis file, you can add another step where you store the information into a database after you enter it in the Property Controls. Then the Data Table in Spotfire is updated using Spotfire’s data on demand technology. This will be discussed in an upcoming tip.