The PoolParty Excel Format

Guideline to set up Excel sheets for importing data into PoolParty correctly.

You can use Excel sheets as basis for a PoolParty taxonomy, or for extending existing data.

Prerequisites for Adding Custom Classes, Relations or Attributes to Concepts

In order to be able to use the Excel sheet for adding custom classes, relations or attributes to concepts, you have to make sure that these prerequisites are met:

  • You have created a custom scheme for your thesaurus.

  • The custom scheme is also active for the thesaurus you want to update data for.

  • For custom class import or update define a type column containing the URI of the class. Details find below.

Setup Rules for a Compliant Excel Sheet

You have to observe the following rules to create a compliant Excel sheet:

  1. All column headers have to be lower case.

  2. Two areas are differentiated in PoolParty's Excel rules:

    • the Fixed Area

    • the Optional Area

  3. The Fixed Area has to contain certain columns in this order:

    1. uri/notation/custom attribute (optional): if present, it must be the first column.

    2. scheme (mandatory): contains one or more custom schemes.

    3. concept (mandatory): one or more columns containing top concepts or concepts.

  4. The Optional Area is indeed optional, so columns and headers can be present. The following options exist:

    • You can add SKOS attributes and relations.

    • You also can add custom attributes.

    • You can use multiple rows or columns.

Details on the respective columns find below.

27891255.png

Note

Special Column Headers*
  • uri: case sensitive (enter URI of a concept into the cells below)

  • notation: case sensitive (enter custom defined contents of the skos:notation field in the cells below)

  • <custom attribute>: http://vocabulary.semantic-web.at/cocktail-ontology/image (URI of the attribute, enter value or values into the sheet's cell)

  • <custom class type>: http://vocabulary.semantic-web.at/cocktail-ontology/Garnish (URI of the class, enter value or values into the sheet's cell)

Excel Sheet Column Headers - Usage and Definition

Fixed Area in the Excel SheetSheet Column: ID column

The ID column (uri / notation / <custom attribute>) is optional.

  • For an import with the update option this column allows you to define how existing concepts are detected if the default mechanism should not be used. Default is the detection via the preferred label in the concept column.

    • The following rules apply for adding an ID column:

      • The ID column has to be the first column in the sheet, if you provide one.

      • There can only be one ID column.

      • For the type <custom attribute> you have to provide the URI of the attribute as a column header.

    • You can provide the ID in the following ways:

      • URI: The concept is identified by the URI you provide.

      • Notation: The concept is identified by the value in the skos:notation attribute.

      • <custom attribute>: The concept is identified by the value provided for the custom attribute of the respective custom scheme.

  • For an export to Excel the URI column including the URIs for the exported concepts is added.

    Note

    In order to be able to use a custom attribute as a definition in the ID column, you need to have a custom scheme in place that also has been assigned to the project thesaurus in question.

Sheet Column: scheme

The scheme column is optional.

  • If this column is present, it will create a concept scheme.

  • The label you provide will be used as a title in the default language of the project for this concept scheme.

    Note

    If you define this column, you cannot use the import file to create a subtree.

Sheet Column: concept (one is mandatory)

Note

At least one column is required, unless only concept schemes should be created and a scheme column exists.

  • The label you provide will be used as a preferred label in the default language of the project for this concept.

  • You can use multiple columns labeled concept to form a hierarchy.

Optional Area in the Excel SheetSheet Column: SKOS attributes
  • Allowed values: prefLabel@Language, altLabel@Language, hiddenLabel@Language, notation, scopeNote@Language, example@Language, definition@Language

  • If no language tag is defined, the default language of the project is used.

  • You can define the language by adding a language tag for example: prefLabel@fr

    Note

    Preferred labels can only be defined once per language, all other properties can be defined multiple times.

  • The language codes you can use PoolParty should be compliant with the Oracle Java definition.

Sheet Column: SKOS relations
  • Allowed values: exactMatch, closeMatch, relatedMatch, broaderMatch, related

  • You can create the relation only if the respective concept already exists.

  • If no ID column is provided the related concept is identified by the prefLabel in the default language.

  • If an ID column is available, the related concept is identified by the value in that column.

    Note

    You have to label the column with the URI of the respective relation.

    SKOS relations are not available in the Excel export.

Sheet Column: <custom attributes>
  • Allowed values: custom attributes defined in the corresponding custom scheme.

  • The custom scheme definitions of the attribute (type, multiple/single) are taken into account.

  • For language tagged literals the same rules apply as for skos attributes.

    Note

    You have to label the column with the URI of the respective attribute.

Sheet Column: <custom relations>
  • Allowed values: custom scheme relations defined on the server.

  • You can create the relation only if the respective concept already exists.

  • For identifying the concept the same rules apply as for SKOS relations.

  • The custom scheme definitions of the relation (type, multiple/single) are taken into account.

    Note

    You have to label the column with the URI of the respective relation.

    Custom relations are not available in the Excel export.

Sheet Column: type
  • Using the type column you can apply custom classes to concepts on import.

  • Allowed values: URIs of the custom class to be applied.

  • You can apply custom classes only to the concept directly. Application on a subtree or concept scheme is not possible.

Note

Empty rows are not allowed.

Column headers are case sensitive.

Use of special characters is restricted inside an Excel sheet.

PoolParty will not import, if you use these in a label:

\, ", <, >, tabs and newlines.

Exception: Single quotes ' are allowed.

If you provide an ID column, it defines the way concepts are identified in the import file also when you add data or relations (an ID column contains either URIs, skos:notations or a custom attribute that can be used as ID).

The ID column has to be the first column in the sheet.

SKOS-XL - Additional Information

To use SKOS-XL labels in PoolParty you have to license an add-on in addition to your PoolParty bundle.

It is available for a PoolParty Advanced or Enterprise Server and for a PoolParty Semantic Integrator. Details find here: The PoolParty Excel Format with SKOS-XL Add-On

For further questions on the Excel format, special import or export issues also refer to the FAQ's .

The PoolParty Excel Format with SKOS-XL Add-On

A short guide on how you can define SKOS-XL relations in an Excel sheet.

SKOS-XL in Excel Sheets

Note

SKOS-XL is an add-on you have to license in addition to your PoolParty bundle. It is available for a PoolParty Advanced or Enterprise Server and for a PoolParty Semantic Integrator.

Since SKOS-XL, the extended SKOS format, has been made available in PoolParty, the PoolParty Excel import allows import of SKOS-XL labels and also all custom attributes and relations of those labels.

The following additional definitions in an Excel sheet become available when you have the SKOS-XL add-on licensed and enabled:

Fixed Area in an Excel Sheet:Sheet column: labelRelation
  • This defines the relation to SKOS-XL label(s). For every defined relation you have to provide a literal form in the respective label@Language column.

  • Allowed values: skosxl:prefLabel, skosxl:altLabel, skosxl:hiddenLabel

  • URI of a custom scheme relation between a SKOS concept and a SKOS-XL label, example: http://my.com/List_of_wine_cocktails

Sheet column: label@Language
  • Defines the literal form of the SKOS-XL label.

  • There has to be one column per language e.g. label@en, label@de

  • The language codes you use inside PoolParty should be compliant to the Oracle Java definition.

Optional Area in an Excel Sheet:Sheet column: skos-xl relations
  • Define relations between SKOS-XL labels.

  • Allowed values: skosxl:labelRelation

Sheet column: <custom attributes>
  • Custom attributes can also be applied to SKOS-XL labels. The same rules as for concepts apply.

Sheet column: <custom relations>
  • Custom attributes can also be applied to SKOS-XL labels. The same rules as for concepts apply.

Sheet column: type
  • Custom classes can also be applied to SKOS-XL labels. The same rules as for concepts apply.

Example Excel Sheet with SKOS-XL Relations

Fixed Area

Optional Area

<uri>/<notation>/<custom attribute>

[optional]

scheme

concept

concept

labelRelation

[optional]

label@de

[optional, required for labelRelation]

prefLabel@de

[optional]

altLabel@en

[optional]

hiddenLabel@en

[optional]

hiddenLabel@de

[optional]

<customattribute e.g. boolean>

[optional]

http://my.com/Cocktails

Cocktails

http://my.com/Aperitif_and_digestif

Apéritif and digestif

http://my.com/Spritz_Veneziano

Spritz Veneziano

http://my.com/Wine_Cocktail

Wine cocktail

Bellini cocktail

Cocktails mit Wein

Wein-Cocktail

Cocktails with wine

wine cocktail

wein cocktail

http://my.com/List_of_wine_cocktails

Liste der Wein-Cocktails

List of wine cocktails