Using the Merge Data Wizard

To merge data from another file into your own .IOK file, you must have at least one column in your data matching one of the columns in the external merge file. For example, to use our By Countries merge files, you must have a column in your data that has the names of countries, or their ISO codes, or other standard identifiers. Sample merge .IOK files containing geographic co-ordinates for countries and major cities, plus examples of other useful reference data are available here.

To open the Merge Data wizard, click File > Import into Current File > Merge Data from another file:

1. Choose external data file- point to the external merge file containing the fields (columns) you wish to join into your data. 2. Choose type of merge- You have a choice of types of merge, Join or Concatenate. To add new fields/columns to your data from a Merge File, choose Join. To add similar files together, for example combining regional report files together to form a global report, choose Concatenate. 3. Select fields to merge from external data- the drop-down list will show all the columns in the external merge file.  Untick the box next to any columns that you do not wish to import into your new, merged file. 4. Join by matching records where- If you choose Join, specify one or more fields (columns) present in your file and the merge file you wish to import, combining its columns with your own. Choose the field (column) in your data set, and select the corresponding field (column) in the external merge file. If you chose Concatenate, all the Join options will be greyed out. Scroll to the bottom and... 5. Execute Merge.  For Joins, the wizard provides feedback on the matching process, reporting records in your file that do not match any merge file records, records that do match (and how many duplicate matches there are). It also reports the number of records in the merge file that do not match any records in your file. 6. Which record sets do you want to retain? for Joins, depending on your needs you can keep your non-matching records, the matching records, and/or the external merge file records that do not match as well. For Concatenates, if the file being added contains fields (columns) that are not in your original file, Omniscope will inform you you that new column(s) have been added and colour their headers blue.

Note: If you use Date and Time fields as Join criteria, the names of the fields (columns) can be different but be sure that the format of the data in both fields (columns) is exactly the same.

SUGGESTION: Before using a field (column) as a matching field, it is good practise to check whether the values in the matching fields in both your own (target) file and the incoming merge file are unique ( using Table View > View Tools > Tools > Select duplicate records). If both are unique, the number of merged records will be the same as the number of matching records, plus any non-matching records if you choose to keep them.  If your (target) file's matching field has repeated records (such as multiple addresses with the same post code), Omniscope will warn you that permutations (additional records) will be created in the incoming post code merge file so that all address records receive their map coordinates. If you attempt to merge two files, both of which have repeated values in the matching fields, try to specify as many additional join criteria as you can so as to minimise the number of permutations created. 

7. If there are conflicting field names...before accepting the merge, protect yourself in the event of duplicate column names (both files may contain columns called City and Country). If you tick this option, Omniscope will modify the names of duplicated columns to, for example, City(2) and Country(2) to avoid mixing/overwriting dissimilar data. Click OK, Apply Merge to modify your file by adding in the corresponding columns from the Merge File.  

Warning: If you click File > Save .IOK (or Ctrl+S) after merging, your original file will be overwritten with the merged file. If you do this inadvertently, you can always recover the original file by deleting all the merged columns (highlighted in blue). If you want to preserve your original file (recommended) be sure to use File/Save As to save the merged file as a new file with a different name.

Back to File Commands