Helpful things to remember about the spreadsheet importer

Explanation

This article lists some tips and helpful information pertinent to the spreadsheet importer tool. They can help troubleshoot or improve your experience with the tool.

Assumptions

• You have an existing model.
• You have the MANAGER, EDITOR, or CONTRIBUTOR role for the model.

Method

This document contains the following sections:

• Matching on preferred label in a model
• Using a multi-step import approach
• Avoiding character rendering issues in CSV files opened in Excel
• Using the importer in Semaphore Cloud vs. Semaphore on-premises
• Using the “Overwrite existing values” function
• Best Practices

Matching on preferred label in a model

When performing an import to update labels or properties of a concept by matching on preferred labels, the importer has two different methods to find a matching concept in the model:

1. URI matching
2. Preferred label string matching

1. URI matching For this method, the importer forms a URI based on the model namespace and the label in the column mapped to preferred label. It then looks for that URI in the model, and if it finds the URI, it will update that concept; if not, it will create a new concept. For example, consider a model with a namespace of sws.geonames.org.

You want to update a concept – in this case, adding a description to the existing concept “Botswana”.

To match on the concept, “Botswana”, the importer will form the URI http://sws.geonames.org/botswana and look for a match on that URI. But if the actual URI of “Botswana” in your model is 933860 (http://sws.geonames.org/933860/), then a match will not occur, and a new concept with the preferred label “Botswana” will be created. There are some common cases where the importer-formed URI will not match the intended target concept even though the preferred label is a match. Examples include:

• The URI of the target concept contains a namespace other than the default model namespace. This can happen in a linked model scenario where source, or upstream, models have a different namespace.
• The URI of the target concept may use the same namespace but contain another element – for example, an ID value instead of the label (as in the example above).

2. Preferred Label String Matching Preferred label string matching occurs for any column with the purpose to link to another concept by a hierarchical or associative relationship. (These columns are mapped to “Associative Property” or “Hierarchical Property” in the importer, and then have further parameters to choose.) For this method, the importer looks for the preferred label itself (i.e., a string match on the label). If there are duplicate preferred labels in the model (homographs), then the importer will only match on the first one found, which may or may not be the one you intended. Consider an import like this one:

The following mappings will use the different matching methods as noted:

In the case where “URI Matching” will not match properly, the way to prevent homographs from being created is to add a column with the concepts’ URI, map it to the URI property field, and then use that information to match existing concepts.

In models with existing homographs, Associative and Hierarchical relationships should only be imported by keying on Concept URIs.

Importing Concepts as Direct Children of Concept Schemes

You can import new concepts and attach them directly to existing concept schemes.

If you want to attach the concepts directly to different concept schemes, this can be done by adding a column to your import spreadsheet for the concept scheme, and putting the concept scheme label in that column. Then when going through the import wizard, on the step where you can select the “destination”, there is a radio button to select “Use spreadsheet to determine concept schemes”. Then select that column.

Note that you can only match on a Concept Scheme by its label, not by its URI or GUID, and the label matching uses the URI matching formula described above - i.e. it will form a URI with the Concept Scheme label in the import column and the model namespace and look for a match that way. If it doesn’t find a match, it will create a new Concept Scheme.

However, if you have a mix of parents in your spreadsheet - i.e. some concepts having concept schemes as parents, and some having other concepts as parents - you can’t import this in one go. For example, if your spreadsheet has “France” and you want to attach that to a “Europe” concept scheme, but also have “Paris” and you want to attach that to the “France” concept - that won’t work since one parent is a concept scheme and the other is a concept.

If you have this second scenario (a mix of parent types), you will need to do multiple imports - one to attach concepts to concept schemes, and a second to attach concepts to other concepts.

Using a multi-step import approach

The spreadsheet importer is designed to allow you to import as much information as possible in a single spreadsheet. However, it can be better at times to perform multiple imports in order to minimize difficulties with the import constraints.

Reasons to use separate sheets or files for a multi-step import:

• You have many different properties or relationships to add. The more columns and data that you have in a sheet, the more complex it becomes overall to verify that the data has imported properly.
• You need to both import new concepts and perform updates to existing concepts.
• You have many specific domains and ranges in your associative relationships. Importing a spreadsheet that adds classes to concepts first makes it easier to add relationships later.

Ways to organize and use import data

• Divide the different data sets into separate files.
• Separate the different sets of data into multiple sheets in the same file. (The importer allows you to choose which sheet to use during import; however, pay attention to how big the file gets, as it can take longer to load.)
• Keep data that will create new concepts in separate files from data that is for updating existing concepts.

Ways to perform multi-step imports

• Import data in incremental stages using tasks. This allows you to check it often for errors and delete a task without committing changes if there are errors.
• Import small amounts of data first. Create a file with just a few rows of your data, and then import that small amount first so that you can look it over and verify that everything imported properly.
• First import concepts with their hierarchical relationships, then additional classes, then import associative relationships, and then labels and metadata.

Avoiding character rendering issues in CSV files opened in Excel

The importer can handle xls files and any other text formats that Excel can open. However, if you have a CSV or other delimited text file, DO NOT open it by double-clicking! You cannot know what the character encoding is for the file, and if it has characters encoded in anything other than win-1252, Excel will convert them by default into corrupted strings. (For example, if your file contains the word “Piñata”, it might turn into “Piñata”, which would turn up in Knowledge Model Management after your import.) To be absolutely certain that Excel will not auto-convert characters and cause issues, either open the file in a text editor, or use the import wizard to import text data into Excel. During the import process, you will have an option to change the character encoding; change it to UTF-8.

Using the importer in Semaphore Cloud vs. Semaphore on-premises

The importer is fully functional in both environments. However, in Semaphore Cloud, if your session times out in the middle of an import, the import will not complete. To avoid this, you can continue clicking and navigating in Knowledge Model Management using another tab to keep the session active. Another workaround for this depends on you having an established process for removing and restoring models. Perform the import first in an on-premises or local build of Semaphore. Then, follow your established procedures to export the resulting model from there as RDF, remove your cloud model, and then import the RDF file into your Cloud instance. (Do not attempt this method without such an established procedure!)

Using the “Overwrite existing values” function

When you are importing a spreadsheet, the final screen has several check boxes, one of which reads: Overwrite existing values.

This option applies to concepts that already exist in the model and that you match to any of these fields during the import:

• Preferred Label
• URI
• GUID

It controls whether you add information from the spreadsheet to these concepts or remove the existing information and replace it with what is in the spreadsheet.

Example The concept “United States” already exists in your model. It has the alternative label “America”. You have a column in your spreadsheet that contains the existing concept name or URI, another column (mapped to alternative label) that contains new alternative labels “USA” and “US”.

These are the scenarios that can play out with the overwrite option:

Box unchecked: Update the existing concept to add “USA” and “US” as additional alternative labels. Result: “America”, “US”, and “USA” all appear as alternative labels. Box checked: The concept will be updated to remove “America” and replace it with “USA” and “US”. Result: Only “USA” and “US” appear as alternative labels.

Useful Facts

• The overwrite option cannot be used to delete values from the concepts, which is to say it cannot remove a value and replace it with a null value. It can only remove existing labels or metadata and replace them with non-null values from the spreadsheet.
• The overwrite option can add and replace these values or relationships:
• Alternative labels
• Metadata
• Associative and hierarchical relationships
• Classes (see explanation below for behaviors specific to classes)

• *Be aware for classes:
• Even if you specify no classes in the spreadsheet, checking the overwrite option will overwrite all classes for the concepts with the //default class// you choose during the import process!
• When loading classes for the very first time, //uncheck the box//. If you leave the option active, then your new classes will overwrite the default Concept class and cause other issues later one.
• If you have loaded or applied custom classes, it is best to specify those classes in the spreadsheet to avoid any overwrite issues. If you followed the tip above for first-time loading, then the classes will remain whether or not the box is checked.

Mapping Columns - “Column role” Drop-down Organization

When mapping the spreadsheet columns, you will use the “Column role” drop-down box to select a property from your model to map to each spreadsheet column. This list will be quite long if you have a lot of custom alt label types and metadata property types in your model. The list may seem unorganized but there is an organization to it. The list is in 4 groups:

• Base relationship types (2 values)
• System Identifiers for Concept, Concept Scheme, and Class (7 values)
• Alt Label Types (variable number)
• Metadata Property Types (variable number)

Within each group, the list is alphabetical.

Here is an example screenshot of the drop-down:

Best Practices

• Always use a task to perform changes and imports. Should anything go wrong, you can always delete the task and start over without changing the master model.
• Understand how the “Overwrite existing values” checkbox works, and make sure you use it accordingly. The box is checked by default.
• When possible, use URIs or GUIDs to identify your concepts, not labels. Otherwise, be sure that the importer-formed URI will match the URI of your concept (model namespace plus label). Otherwise, you will experience the problems described in the above section “Multiple namespaces in a model”.
• As mentioned in the previous section, if you have loaded or applied custom classes, specify those classes in the spreadsheet to avoid any overwrite issues. If you followed the tip above for first-time loading, then the classes will remain whether or not the box is checked.

Related

• import-new-with-flat-list
• import-new-with-hierarchical-format
• import-label-class-metadata-for-existing-concepts
• import-concepts-into-existing-model
• import-concept-to-concept-relationships-for-existing-model