Import Data Phase

Adding/importing data into Omniscope files

The Data Import phase is documented primarily in the Importing Data sections of this User Guide, which includes topics such as importing data files, making and refreshing database connections, and using connectors to online/remote sources and more. More technical articles and examples are available in the KnowledgeBase, which includes sections on supported data file formats, examples of database connections using ODBC and JDBC and other related topics, such as importing transformed XML.

Import Data Checklist:

Getting started with Omniscope usually involves importing a copy of a structured table of data, such as a spreadsheet file or a reporting view from a SQL-based relational database. Options include:

1-01 Create new file - manually type or cut and paste data into a blank Omniscope file

1-02 Open data file - import data from a delimited data file (see potential issues list below)

1-03 Connect to database - connect to a relational database using ODBC or JDBC protocols

1-04 Using Connectors - use one of the available Connectors to access/import remote-hosted data

1-05 Open image collection - open and import a folder of images, together with user-editable tags

1-06 Folder of files - access and analyse a folder of files (documents, audio/visual media, etc.) to create a data set of filenames/paths and user-editable tag names

1-07 Open map - access our Map Library, download a geo-coded map file and build a data set, including manual or automatic placement of records by decimal longitude/latitude. Alternatively, screenshot an online map image, note some reference coordinates, then use Omniscope to geo-code the image and build a data set to overlay the image map.

1-08 Perform merges - Use the Merge Wizard if additional data from other sources needs to be joined or concatenated with your initial data set. Before performing a join, test the uniqueness of join criteria columns using the Table View: View Tools > Tools > Select duplicate records command.

Note: If new, currently unspecified columns might be added to one or more of your merge files in future, do not specify the fields to import, so that Omniscope will continue to import all fields in future. You can then Hide or Delete any unneeded fields as a remembered action.

Potential Data Import Issues

Data files are often not delivered formatted according to precise international standards. Differences in formatting of data within tables and delimted files around the world can affect data import and display in Omniscope. Below are some common issues which you may encounter when importing data tables into in Omniscope:

Regional formatting - When importing data by opening a CSV or Excel XLS file, Omniscope analyses the data in the file to determine how the data is arranged and which fields (columns) are most likely of data type Text, Numbers, or Dates & Times. The regional settings in your computer's operating system (Windows, or Mac, etc.) have a strong influence on this operation. For some combinations of settings and data formats, Omniscope cannot reliably detect the situation automatically. If your data opens incorrectly and your fields are not correctly structured, numbers have the wrong decimal place, etc. please consult the File > Open file  dialog page, which contains options to help with these situations.

For example, if you know your data has been formatted for a different region, change the Locale drop-down accordingly. For example, if you live in Germany but have received a CSV from an American customer, you should choose "English (US)". If you find Omniscope gets things wrong and your data is incorrectly recognised, you can disable auto-detection of dates and numbers by deselecting the two Recognise... check boxes on the File > Open file dialog. All fields will open as Category or Text data and their text values will be preserved. You can then use Data > Manage fields to configure each field (column) as you would like it to display.

Data file delimiters - CSV stands for "Comma Separated Values", which means the cells in each record (row) of the data table are separated by the comma character. However, this is not a formally-documented standard, and if you are in a region that also uses the comma in numbers instead of the decimal point (e.g. several European countries, such as France), some versions of Excel create CSV files that use a semicolon as the separator character. Sometimes these multi-column tables files are saved with a CSV or even a TXT extension. Omniscope is unable to detect this situation automatically. If you open such a file in Omniscope, your data will very clearly be wrong, with most columns appearing as text with semicolons. To avoid this problem, use the File > Open file Customise data import dialog to change the separator character to a semicolon " ; ". You can also do this with 'pipe' "|" delimited files, but you must first change the file extension on the data file to .CSV, even though the file is really is 'pipe' and not comma delimited.

Dates & numbers - If you are running on Windows, when importing data that is in a delimited text format such as Excel XLS, CSV or TSV (tab-separated values), Omniscope will use your Windows Regional Settings to try to automatically recognise data. For example, for a PC with United States Regional Settings, Omniscope will recognise "5/13/2005" as a date and "1,500.5" as a number. For some international users, however, this may not work as expected and the result will be an import of all columns as data type Text (shown in Omniscope either as multi-coloured Category columns or white Text columns). If this occurs, you can usually manually convert fields (columns) containing numbers or dates and times using the Data > Manage fields dialog. For more information on formatting dates and times and managing time zones, see the section on Dates & Times.

Go to Data Management Phase