Importing Data Files

Importing Data from Files

Supported data file formats and import options

Data File Import Options: 

Importing data from delimited data files: Omniscope data sources include delimited tabular data that has been exported in a supported data file format from relational (SQL-compliant) database transactional tables or reporting views, analytical OLAP 'cubes' and other business intelligence data repositories; data warehouses, 'clouds' and 'data marts', etc.

Importing data from XML files: Omniscope also imports and exports tagged data in XML format, assuming it is (or can be transformed on the fly using XSL) a tabular layout using the very simple Visokio XML schema. The Visokio Omniscope XML Schema is essentially the equivalent of a CSV file expressed in XML. See XML schema for more details.

Opening other application data file formats: Omniscope either currently supports (or plans to support) almost all of the most appropriate data import and export file formats, whether used by desktop-installed applications, such as Microsoft Office/Excel, Corel WordPerfect X3/4, or open-source files from suites such as Open Office, IBM Lotus Symphony (and others) or by online, remote document hosting services such as Google Docs & Spreadsheets (with live synchronisation of edits), Microsoft Office Live, etc.

Omniscope Connectors: A growing number of direct and web-services-based Connectors are available to import and synchronise data to/from remote 'cloud' sources. 

 


Note: Omniscope can also import the results of any SQL statement that returns a table directly from  relational databases. Importing data using either ODBC (one database, multiple tables) or JDBC (multiple databases, multiple tables) standard protocols is configurable using the Omniscope File > Connect to database dialogue as explained in the Database Connections section. Direct connections to MDX-compliant multi-dimensional data sources will be available soon.

Delimited data file formats supported:

Tabular data files usually use a special character as a delimiter, a way of separating values and associated headers from the subsequent values.

CSV: Comma separated values

Omniscope supports the most common implementation of .CSV, which is more a de-facto than a formal standard. Because CSV is not a formally-documented standard, some databases will export CSV files with different conventions from those used in spreadsheets, for example the treatment of single double quotes in text.  If you are having trouble getting all records imported from a CSV file received direct from a database, try opening and saving the file in a spreadsheet, then importing the spreadsheet file into Omniscope.

TSV: tab separated values

Omniscope recognises and opens .TSV files.

TXT: text files

Omniscope imports and exports column lists of values from a field (column) as .TXT files. Note that some files with a .TXT extension are actually tables with a tab delimiter. You must change the extension on these files to .TSV in order to open them as tables. (see .TSV files above)

Semi-colon delimited files

These files tend to be used in countries where the comma is used in numeric values as a decimal separator. These files may have .CSV or sometimes .TXT extensions. Viewing the first lines of the file in a text editor like Windows WordPad will show if the delimiter being used is a semi-colon. If you find that that your .CSV file is a semi-colon delimited file, you must change the file extension to .CSV (if it is not already). Omniscope will then permit you to import the data by setting the delimiter to semi-colon. To do this, use File > Open file to point at the .CSV file. Be sure to tick the box at lower right 'Customise data import behaviour'. This will display a list of additional options, including the option to change the delimiter character from the default comma to, in this case, a semi-colon.

Other delimiter characters e.g. ' | ' pipe delimiters

Some files with .TXT and other file extensions actually use other characters as delimiters. For example, a common SAP and SAS data file delimiter is the | (pipe) character. To open a file with an unusual delimiter, first change the file extension to .CSV, then use File > Open file to point at the file. Be sure to tick the box at lower right 'Customise data import behaviour'. This will display a list of additional options, including the option to change the delimiter character from the default comma to whatever character is used in your file.

Locally-installed application data file formats supported:

Microsoft Office: .XLS & .XLSX Excel Workbooks

Omniscope inter-operates with Excel files for import & export, provided that the copy of Excel is installed locally on the same machine. Excel interoperability is currently on the Windows operating system only. Omniscope interoperability with Excel is constantly being improved, and will soon be available on the Mac operating system as well. For information regarding support for Flash DataPlayers by Microsoft Office documents/applications, please see the DataPlayer document compatibility list)

Corel WordPerfect X3/X4: .QPW Quattro Pro files

Omniscope supports Quattro Pro .QPW v9X3 & X4 files. Using Quattro Pro, Lotus 1-2-3 and Borland database files (Paradox & dBase) can be opened, saved as .QPW or other supported file formats on this page, and imported into Omniscope. For information regarding support for Flash DataPlayers by Corel/WordPerfect documents/applications, please see the DataPlayer document compatibility list.

Open Office/Star Office/Symphony: Open Docs

Omniscope currently does not, but will soon support the Open Docs standard for tabular data files. There is a plug in to upload Open Office documents directly from OO.org into Google Docs, the spreadsheet of which is supported in Omniscope (see below). For information regarding support for Flash DataPlayers by Open Docs documents/applications, please see the DataPlayer document compatibility list.

Online/collaborative application data file formats supported:

Online document/office suite services are sometimes used to enable collaboration on the assembly, scrubbing and approval of data and image sets, together with associated web links, map coordinates, etc. Importing online data sets into Omniscope can greatly assist this process, especially during the scrubbing and approvals phase. Omniscope can also be configured to refresh from these data sources, providing live dashboards that can be deployed as Omniscope files and exportable DataPlayers. 

Google Docs & Spreadsheets

Support for collaborative Google Docs & Spreadsheets is currently supported. Depending on demand, support for other emerging online application suites may also be added.

Other directly-importable data file formats

Omniscope is continuously being expanded to import many other data file types.

POP3/IMAP Mailboxes

 

Apache .LOG Web Access log files

Omniscope Professional & Enterprise will open Apache Web Access .LOG files directly for visualisation, analysis and reporting.

FIX logs and other file types

If you would like Omniscope to open/import or export other file formats associated with applications you control, please contact us.