Importing Your Data (2.3)

Importing your data into Omniscope

Applies to version 2.3 only

Thus far, you have been learning how to navigate Omniscope files prepared by others. Now you are ready to start importing your own data in Omniscope, and configuring IOK files for use by yourself and recipients of your files using the configuration checklist.

Omniscope imports tabular data (data in rows and columns) from spreadsheets and databases. The simplest way to get data into Omniscope is to open/import the data from a spreadsheet, such as an Excel XLS file. All popular spreadsheets and databases allow exporting or saving as comma-separated values (CSV) files. You can import CSV files into Omniscope just like a spreadsheet, without the row limits imposed by spreadsheets. Tab separated values (TSV) and some other common delimited text data file formats are also supported, as is XML. You can also cut and paste tables of data directly into the Table View of Omniscope.

Opening/Importing Tabular Data

If you have both Excel and Omniscope installed, Omniscope can open data from native Excel .XLS files directly. Before opening a spreadsheet file in Omniscope, first check that the spreadsheet has a suitable tabular structure (see example).

A sample 6-column .CSV data file containing repeated observations of location, air pressure and wind speed for a series of hurricanes over time is available here.

To open your spreadsheet data file in Omniscope, simply choose File > Open file from the Omniscope main menu, and browse to your .XLS or .CSV spreadsheet file. Omniscope will only import data from a single worksheet tab in a spreadsheet file. If you have a data set spread across more than one tab, you will need to start by opening/importing the data from each tab and saving it as a separate Omniscope file, then using the Merge Data wizard to combine them into a single Omniscope file. For more information, see Using the Merge Data wizard.

Opening/ and importing a new table of data in Omniscope creates a new .IOK/.IOM file, which by default opens with 3 views of the data; a Table View, a Chart View, and a Pie View (which will be displaying the record-count split of one of your columns of data):

More detailed information on supported data file formats and methods of dealing with common issues is also available in our KnowledgeBase section on Data File Import. For more information on connecting directly to database tables/views instead of data files, see the section on Connecting to Databases.

Refreshing from Linked Sources

Maintaining links to data sources enables Omniscope files to be updated whenever the data in the original source file changes. This is especially useful when working with reporting views automatically extracted from central databases, or when a specific tab on a specific spreadsheet is being used to generate the source data. Every time a new view is generated and a new file produced, if it is saved to the same location with the same name, Omniscope will automatically detect the change on next opening and import the newest values. More on Refreshing Data.

Unlinking from/Re-linking to Source file

Every time a linked .IOK file is opened or closed, the user is given the option to unlink the file from its original source. It is also possible to re-establish a link to a source file using the command Edit > Change source and pointing at the data file again as before, which will preserve any field conversions and formulae applied to the previous source.

Editing and Saving back to Linked Sources

Upon opening an Excel spreadsheet or other supported tabular data file, you'll be working on a new, unsaved Omniscope file. When you save the Omniscope file, all the imported data (and any edits you may have made), together with the view configurations and layout settings will all be saved in the new file. In addition, by default, you will have established a 'pairing' or 'linked source' relationship between the Omniscope file and the original data source file. As long as the linked data source file remains in the same relative location, Omniscope will be aware of this 'linked source' file and its most recent version. If it changes, Omniscope will notice and offer to refresh the data from the linked source file.

If you make changes to the data in Omniscope, on closing the .IOK file, you will be asked if you want the changed data Saved back to source or whether you wish to Unlink the file from its source. If you chose to save your data edits back to source, the original spreadsheet will be replaced. Please note the following warnings about writing Omniscope files back to linked source spreadsheets:

Warning - Edit only one file at a time: If you make changes to both the Omniscope file and the linked source file, you risk not being able to preserve or "merge" both sets of edits

Warning - Multiple Worksheets: If your spreadsheet has more than one non-empty worksheet tabs, Omniscope will ask which of the tabbed worksheets you want to import from. However, if you edit this data in Omniscope, choose to preserve the link, and choose to save back to source, Omniscope will overwrite the entire multi-tabbed spreadsheet file with a single-worksheet spreadsheet.

Warning - Formulae and Formatting: All formulae, formatting and other non-tabular information in the source spreadsheet file are ignored on import, only the formula result value is imported. If you choose to maintain the link, and you chose to write back to source, you will overwrite your spreadsheet formulae with static data. Merged cells and other formatting, embedded graphs, etc. are ignored.

Warning: remember, saving back to source will overwrite the spreadsheet with a plain unformatted file of tabular data with only one worksheet.

Suggestion: If you have worksheets with macros and other report formatting, but wish to visualise, filter, edit and otherwise manage the source data in Omniscope, try using cut and paste to move the data from Omniscope onto the first tab of your spreadsheet, and move all the macros and formatting onto subsequent tabs referencing the required values from the first tab in the spreadsheet pasted from Omniscope.