Adding data to Spotfire from files

There are usually several ways to achieve the same result in Spotfire and, as you learn to use the tool, you will develop your own preferences. When you launch Spotfire, you will see all the main options to load data.

From the opening menu, you can use the Open File icon or the Add Data Tables icon. If you look at the main menu bar, you'll notice the File option. If you select this option, you should see the Open and Add Data Tables… options. Similarly, if you look at the icon bar just below the main menu bar, you'll notice symbols to open a file and add data tables. Hover your mouse over the icons and you will get a description of their functions.

Whether you want to use the menu options or the icons is a personal preference. The difference between opening a file and adding a data table is important, however.

Opening a file means opening a datafile or a saved Spotfire analysis file and closing any open Spotfire file in the process. Spotfire will prompt you to save your file before you open the new file.

Adding a data table means adding data content to an open Spotfire analysis file, keeping all its existing content. If you simply launched Spotfire, opening a file and adding a data table amount to the same thing but, if you want to add additional data tables, you must use the add data table option.

Importing a Microsoft Excel spreadsheet into Spotfire

Let's open a Microsoft Excel spreadsheet in Spotfire.

The data used is BaseballPlayerData.xls, which you can download from http://www.insidespotfire.com or copy from the TIBCO Spotfire professional client installation directory (~TIBCO\Spotfire\#.#\Example Data\Baseball) on your PC. The TIBCO file is in text form, so you will need to convert it to Microsoft Excel first.

1. Start by clicking on the folder icon (on the far left in the previous screenshot).
2. You will be presented with a standard Open file dialog, allowing you to navigate to the spreadsheet file.
3. Spotfire will open a dialog window asking you to confirm or change key aspects of the Microsoft Excel file.
   
4. The first thing to notice is the Worksheet selection dropdown at the very top of the dialog window. Spotfire can only import one worksheet at a time. There is only one sheet in our file, so we can ignore this option.
5. The next thing to notice is the preview of your data and its structure. Spotfire will automatically detect and assign column headers and data types, but you can change any of these settings. You can also tell Spotfire not to import specific columns or rows.
6. We want to open the file with all defaults, so we're just going to click on OK, but please do explore the dropdown options for columns and rows and experiment with the settings. The core philosophy of Spotfire is discovery, so start as you mean to continue and explore all the options.
7. Once you click on OK, Spotfire will import the spreadsheet, create a new page in your analysis, and display the data as a visualization. The type of visualization will depend on the default option you set under Tools|Options|Document.
8. If the data is not displayed in tabular form, then first close the visualization by clicking on X in the upper right-hand corner and then create a Table visualization by clicking on the New Table icon in the Spotfire toolbar.
   

   After you import the data and set up the Table visualization, you should see the following:

   

   Note

   Take a look at the General tab in Data Table Properties, which you will find under the main Edit menu. The default Store data option for the table you loaded is Linked to source. This option means that the data always remains in the source file and is pulled into memory by Spotfire when the analysis file is opened or the data is refreshed (using the Refresh Data button next to the table list). To make the analysis file more portable, you can change the status to Embedded in analysis, which means that the data resides in the analysis independent of the original file. Refresh Data still works and updates the embedded data with any changes made in the source file.

9. Save your analysis file by clicking on the disk icon or by selecting Save or Save As in the File dropdown list. Name it BaseballPlayerData. We will be returning to this file in future examples.

Importing a text file into Spotfire

Ready for another example? This time, we're going to import a text file into Spotfire, which provides some useful options for structuring text files into a more analyzable form.

1. Follow the exact same procedure as for the Microsoft Excel spreadsheet, except this time we are going to open the data from a text file (BaseballPlayerData.txt, which you can download from http://www.insidespotfire.com).
2. Once again, you will be presented with an import dialog and data preview, but this time you will be able to customize how the file is delimited, which means how you want to separate the information into columns. You can also change properties related to text encoding.
   
3. Spotfire has correctly detected the tab separator in the text. Simply accept the defaults and click on OK to import the text into Spotfire.

The data will display exactly as it did with the Excel import. Compare Spotfire's output with the raw file opened in Notepad.

Importing other file types into Spotfire

Spotfire can import data from other structured file types, such as Microsoft Access (.mdb) and SAS (.sas). The basic process to open these files is no different than it is for Microsoft Excel and text, except Spotfire uses the data structure embedded and defined in these files and gives you control over which parts of the data to import.

Below is an example dialog for a SAS file. The three columns we don't want to import are highlighted. The next step is to click on the < Remove button, after which the columns will flip to the left-hand window. When we're happy with the selections, we click on OK, and Spotfire will import a dataset based on the selections.