Spotfire: Writing to files and databases from Spotfire analyses

Summary

Spotfire can write back to a database in different ways using different techniques such as IronPython from a Text Area, Information Links or Data Functions. It can also be as complex as creating a custom extension using the Spotifre SDK. It can write back to different databases such as text files, excel, relational databases or even cloud.

Overview

Spotfire can write back to a database in different ways using different techniques such as IronPython from a Text Area, Information Links, or Data Functions. It can also be as complex as creating a custom extension using the Spotfire SDK. It can write back to different databases such as text files, excel, relational databases or even the cloud.

Export data using the Cloud Drive Export for Spotfire®

The Cloud Drive Export for Spotfire® is a custom extension which allows exporting of data from Spotfire into files on the Cloud Drives of the following vendors:

• Google - write to CSV, Excel and Google Sheets
• Microsoft SharePoint Online Files - write to CSV and Excel files
• Amazon S3 - write to CSV and Excel files
• Dropbox - write to CSV and Excel files

Exporting of data can be performed manually in Spotfire Analyst or behind the scenes using Spotfire Automation Services Tasks.

Writing back using IronPython

IronPython is very powerful as is possible to do almost anything that can be done with .NET as IronPython is a Python implementation of the .NET framework. Along with the Spotfire API, it is possible to write to files, export images, create pdf documents, automate windows tasks and write to databases.

Writing back using Data Functions

Data functions are as powerful as IronPython, but these serve a different purpose and can be written in Python or R programming language. There are packages for almost anything. Data functions are commonly used by Data Scientists to perform complex calculations. Writing back to files or databases using Data Functions is another way to achieve this.

Writing back using Information Links

Information links are predefined database queries. They require Java Drivers (JDBC) on the server to create data connections. Writing back using information links can be a handy tool to execute queries before or after the data is loaded or executing complex database queries using stored procedures. One great advantage of using Information Links is the ability to pass parameters from markings or document properties. 

Examples

Write to files

• Writing back from Spotfire to SQLite using Python - basic example
• Write back to Excel file from Spotfire using IronPython via ODBC - basic example
• Writing back from Spotfire to SQLite using Python - complete example
• Export cross table to memory as a data table (IronPython/StreamWriter) - basic example
• MS Access (IronPython / OleDbConnection)  - basic example
• Export Spotfire Binary Data Files (sbdf) - the first part covers exporting data to sbdf
• Write to PDF - exports the current page to PDF
• Write to bitmap images  - writes an image file from a visualization

Write to server databases

• MS SQL (IronPython/.net System.Data.SQLClient) - basic example
• MS SQL (IronPython/.net System.Data.SQLClient) - inserts a column and updates data
• Write to SQL Server (IronPython/ODBC) - basic example
• Insert data to database using Information Links - modified SQL/stored procedures
• Write back to database from Spotfire using R Data Functions - ODBC dsn

Write to Cloud

• Write back to Google Sheets from Spotfire using Python Data Function 
• Write back to Google Big Query from Spotfire using Python Data Function 

See Also

• Database writeback in Spotfire
• How to Perform an Action after a Table Refresh Completion in Spotfire