Preparation of the code set to be imported and the column headings of the Excel file
Code files can be imported both with an older Excel version (.xls) and with a newer (.xlsx) version. In an Excel file, the order of the columns does not matter. The imported file can also have columns with other names, but these are not imported, as they are skipped. Below is an empty template from an import excel, which is used to export the data of the code set and its codes to the Reference Data Tool:
First import empty template for Reference data tool .xlsx
Code import from an Excel file
- After you have logged in to the Reference Data Tool, the "Add code list" button appears on the front page of the tool. Click the "Add code list" button.
- Click "Import code list from a file"
First select the registry from the drop-down menu. Registers have already been created for the Reference Data Tool. Users cannot add registries.
- Select the format as "Excel"
- Select the file from your own storage
- Click the import button and the code list importing starts
Naming tabs (sheets of the Excel workbook)
The Excel file should have tabs named CodeSchemes and Codes and in that order.
- From the CodeSchemes tab, code set metadata is imported
- From the Codes tab, the codes and their descriptions, as well as other metadata of the codes, are imported
The names of the tabs must be spelled exactly like this (uppercase and lowercase letters matter, i.e., the spelling is case sensitive). The names of the tabs correspond to the physical names of the database tables in the Reference Data Tool.
CodeSchemes tab (Information on the Code set, its name and metadata)
CODEVALUE | Codeset ID; this information identifies the code set within the Reference Data Tool and is given by the code set owner/editor. This information is mandatory. | x |
URI | URI identifies the code set universally and is automatically generated from CODEVALUE. This field is left blank when the editor imports a new code set. | |
ORGANIZATION | The organisation(s) that own the code set. This field can be left blank when the editor imports a new code set. If no value is given, the code set will be automatically linked by the registry of the code set (i.e., to the organisations that have been defined as the owners of the registry). | |
INFORMATIONDOMAIN | Classification of domains/subject areas. This information is mandatory. This classification of domains is based on the Classification of Information Domains, where 27 main categories of the classification are used (https://koodistot.suomi.fi/codescheme;registryCode=jupo;schemeCode=serviceclassification). The information is given as a code, like "P10" (="Work and unemployment"). | x |
LANGUAGECODE | The language code of the code set. IETF language codes are used (http://uri.suomi.fi/codelist/interoperabilityplatform/languagecodes). The information is given as the value of the language code. There may be more than one language choice, in which case the separator is a semicolon. Example: fi;sv;en specifies that the content can be in Finnish, Swedish and English. | |
VERSION | The version number of the code set. This field can be left blank when the editor imports a new code set. | |
STATUS | Status code. The default value in import is DRAFT. This information is mandatory. | x |
SOURCE | Source. If the code set is based on a specific source like a law or an international parent code, it can be described here. | |
LEGALBASE | If the use of the code set is based on a specific law or other jurisdiction, it can be described here. | |
GOVERNANCEPOLICY | Binding nature or level of management of the code set, e.g., "National Recommendation", "International Recommendation", "Mandatory for municipalities", "Statistical Classification". | |
CONCEPTURI | URI address of a specific concept in the Terminologies Tool, in case the definition of the code set has been linked to a concept | |
DEFAULTCODE | Default code, if there in specific cases is the need to define a code that is used by default, if no other data is given | |
PREFLABEL_FI | The name of the code set in Finnish. *Excel import must contain at least one PREFLABEL column and it must not be empty. Not all languages are mandatory. | x* |
PREFLABEL_SV | The name of the code set in Swedish. *Excel import must contain at least one PREFLABEL column and it must not be empty. Not all languages are mandatory. | x* |
PREFLABEL_EN | The name of the code set in English. *Excel import must contain at least one PREFLABEL column and it must not be empty. Not all languages are mandatory. | x* |
DEFINITION_FI | Definition text of the concept in Finnish, when the code set has been linked to a concept. | |
DEFINITION_SV | Definition text of the concept in Swedish, when the code set has been linked to a concept. | |
DEFINITION_EN | Definition text of the concept in English, when the code set has been linked to a concept. | |
DESCRIPTION_FI | Description text of the code set in Finnish. In long texts, you can create a paragraph break by typing \n in the text. | |
DESCRIPTION_SV | Description text of the code set in Swedish. In long texts, you can create a paragraph break by typing \n in the text. | |
DESCRIPTION_EN | Description text of the code set in English. In long texts, you can create a paragraph break by typing \n in the text. | |
CHANGENOTE_FI | Change information in Finnish. The editor can describe how this version of the code set has been changed compared to the previous versions etc. | |
CHANGENOTE_SV | Change information in Swedish. The editor can describe how this version of the code set has been changed compared to the previous versions etc. | |
CHANGENOTE_EN | Change information in English. The editor can describe how this version of the code set has been changed compared to the previous versions etc. | |
STARTDATE | The date on which the validity of the code set starts. If the field is empty, the information is generated on import based on the time of import. | |
ENDDATE | The date on which the validity of the code set ends. The import function can read multiple date formats as long as the fields are defined as date fields in Excel. Fill in only if the expiry date is known in advance! Otherwise leave blank. | |
HREF | Links attached to the code set, presented either via URI or the UUID of the link. If more than one link is to be given in this column, the "|" character should be used as a separator. On the Links tab (sheet), you can define the links to be imported, their type, and provide their metadata in their own columns. The possible link types (values) are: link, source, instructions, standard, isReferencedBy, isRequiredBy, and license. | |
CODESSHEET | The tab defining the codes included in the code set. Normally, the default is to parse the codes from the Codes tab (sheet). If there is a need to import multiple code sets with the same Excel file, all the code sets must be specified explicitly by using separate tabs (sheets).
| |
LINKSSHEET | More detailed information on the links attached to the code set, presented either via URI or the UUID of the link. On the Links tab (sheet), the editor can define the types of links to be imported, and provide their metadata in their own columns. If the URI of the link used is not explicitly specified on the Links tab, the tool will create a link without metadata, just using the generic "link" type. The possible link types (values) are: link, source, instructions, standard, isReferencedBy, isRequiredBy, and license.
| |
FEEDBACK_CHANNEL_FI | Contact information (email address) in Finnish | |
FEEDBACK_CHANNEL_EN | Contact information (email address) in English | |
FEEDBACK_CHANNEL_SV | Contact information (email address) in Swedish |
Codes tab (Information on the codes and their metadata that are imported from the tab)
CODEVALUE | Code ID (code value); this information identifies the code within the code set and is given by the code set owner/editor. This information is mandatory. Often the codes are in numeric form, but they can also be text. | x |
URI | URI identifies the code universally and is automatically generated from CODEVALUE. This field is left blank when the editor imports a new code set. | |
BROADER | The upper-level code in hierarchical code sets, i.e., the value of the parent code of this code. The field is also used to generate the tree structure of a hierarchical code set. | |
STATUS | Status code. The default value in import is DRAFT. This information is mandatory. The allowed values are INCOMPLETE, DRAFT, SUGGESTED, SUBMITTED, VALID, SUPERSEDED, RETIRED and INVALID. | x |
PREFLABEL_<IETF-language-code> | The name of the code presented in the language defined in the column header. Note that the language code is written in upper case, like FI. If there is no prefLabel field, the codeValue field is used in the interface. The most commonly used headers are as follows: PREFLABEL_FI (Finnish), PREFLABEL_SV (Swedish), PREFLABEL_EN (English). Other IETF language codes can be found in: http://uri.suomi.fi/codelist/interoperabilityplatform/languagecodes | |
DESCRIPTION_<IETF-language-code> | The description of the code presented in the language defined in the column header. Note that the language code is written in upper case, like FI. The most commonly used headers are as follows: DESCRIPTION_FI (Finnish), DESCRIPTION_SV (Swedish), DESCRIPTION_EN (English). Other IETF language codes can be found in: http://uri.suomi.fi/codelist/interoperabilityplatform/languagecodes | |
DEFINITION_<IETF-language-code> | Definition text of the concept in the specified language, when the code has been linked to a concept in the Terminologies tool. Note that the language code is written in upper case, like FI. The most commonly used headers are as follows: DEFINITION_FI (Finnish), DEFINITION_SV (Swedish), DEFINITION_EN (English). Other IETF language codes can be found in: http://uri.suomi.fi/codelist/interoperabilityplatform/languagecodes | |
SHORTNAME | Short name or abbreviation of the code | |
CONCEPTURI | URI address of a specific concept in the Terminologies Tool, in case the definition of the code has been linked to a concept. | |
SUBCODESCHEME | URI address of a specific code in the Reference Data Tool, in case the code has been linked to another code | |
HIERARCHYLEVEL | The hierarchy level of the code. This field is left blank when the editor imports a new code set. Value 1 indicates the first level (main level), value 2 the second level, etc. The levels are determined by the hierarchical structure of the data (based on BROADER field values) | |
ORDER | Order number (used internally in the Reference Data Tool to sort codes) | |
STARTDATE | The date on which the validity of an individual code starts. The import function can read multiple date formats as long as the fields are defined as date fields in Excel. | |
ENDDATE | The date on which the validity of an individual code ends. The import function can read multiple date formats as long as the fields are defined as date fields in Excel. | |
HREF | Links attached to the code, presented either via URI or the UUID of the link. If more than one link is to be given in this column, the "|" character should be used as a separator. On the Links tab (sheet), you can define the links to be imported, their type, and provide their metadata in their own columns. The possible link types (values) are: link, source, instructions, standard, isReferencedBy, isRequiredBy, and license |
Links tab: Links attached to the code set and its codes
ID | System-generated UUID. The value can be imported if a specific UUID value is to be used. As UUID is a unique identifier, the imported UUID value must not conflict with any other value assigned to the codes in the Reference Data Tool. | |
HREF | The URI identifier of the link. This value must be unique for each code set, so creating two similar URI links for a code set it is not allowed | x |
PROPERTYTYPE | Type of the link. The possible link types (values) are: link, source, instructions, standard, isReferencedBy, isRequiredBy, and license. | |
TITLE_<IETF-language-code> | The name of the link presented in the language defined in the column header. Note that the language code is written in upper case, like FI. The most commonly used headers are as follows: TITLE_FI (Finnish), TITLE_SV (Swedish), TITLE_EN (English). Other IETF language codes can be found in: http://uri.suomi.fi/codelist/interoperabilityplatform/languagecodes | |
DESCRIPTION_<IETF-kielikoodi> | The description of the link presented in the language defined in the column header. Note that the language code is written in upper case, like FI. The most commonly used headers are as follows: DESCRIPTION_FI (Finnish), DESCRIPTION_SV (Swedish), DESCRIPTION_EN (English). Other IETF language codes can be found in: http://uri.suomi.fi/codelist/interoperabilityplatform/languagecodes |