Excel templates and import
Page in Finnish: Excel tallennuspohjat ja tiedon tuonti Excelistä
General, tips and tricks
Entering data using Excel templates is useful for larger amounts of specimens with similar data. You need to create an empty Excel template using the tool in Kotka or if you already have the data in a table, you need to transform it to Kotka Excel format. More information about data transformation: Data cleaning and transformation.
The excel templates generated using the Excel generator include the correct options for dropdowns. To have all the latest fields and options on the template, you need to take a new one from Kotka from time to time.
You can keep one template excel with all possible fields as a source for correct Excel column headers. You may need to add headers when you are for example editing Kotka data in Excel or transforming data from an old database to Kotka import format.
To import specimens using an Excel template, you need to give the specimens identifiers, NamespaceID and ObjectID. This is to prevent accidental duplicate entries. On the web form it is possible to leave the fields empty and let Kotka generate an ID using the namespace HT. But if this was possible in Excel import, there would be the risk of importing the same specimens many times with different identifiers.
Excel template
On the import sheet, there are two header rows. The first header row is the important one, that Kotka uses to read the column headers to put data to correct fields. On the first header row, the key values or the "database names" for the fields are used and they also follow the hierarchical structure of Kotka. For example MYCollectionID is the column where you need to enter the ID for the specimen collection and MYGathering[0][MYUnit][0][MYIdentification][0][MYTaxonRank] means the Taxon rank for the first identification.
Repeating elements like identifications are added using the numbers in square brackets (count starts from zero), for example MYGathering[0][MYUnit][0][MYIdentification][1][MYTaxonRank] would be the second identification in the first unit, and MYGathering[0][MYUnit][1][MYIdentification][1][MYTaxonRank] is the second identification in the second unit. If specimens have many units and identifications, Excel sheets can become very wide, as many columns need to be repeated.
The second header row is voluntary and can be used as a help to more easily tell which column is which. The headers can be edited as desired or the row deleted.
The order of the columns in the template can be changed apart from the two first columns. NamespaceID always has to be the first column, and Object ID the second. Unnecessary columns can be deleted, except for the mandatory ones. When exporting specimens from Kotka, one more mandatory column appears on the excel: MZDateEdited. Leave this column and all its cells as they are, do not edit it at all. This column is very important in the import: Kotka uses it to check that the specimens being imported have not been edited in Kotka between the export and the import attempt. The import fails and the data can't be saved, if the specimens have been edited in Kotka meanwhile. Do not delete any of the columns that include data in the Kotka export, even if you don't edit the contents (this can cause some columns to be overwritten with empty data)
Import
You find the import in Kotka top menu bar, under Tools. Choose the datatype for the specimens, the owner of the records (remember to change this if you are connected to several organisations) and browse for the file and choose the correct sheet. You can use the normal import with preview for a maximum of 5000 rows/specimens (file size max???) or the huge import without the preview for maximum 10 000 specimens. Click validate and preview. This can take some time depending on the amount of data. First Kotka makes some checks (for example the column headers) and if there are critical error, the preview may not be shown. If possible, Kotka proceeds to the preview.
In the preview, Kotka shows possible errors and warnings the data has, if any. Column Errors shows those errors that prevent you from saving the data (like missing mandatory columns) and column warnings shows those validation warnings that don't prevent you from saving the data but are good to fix if possible. The rows in the preview are reordered so, that the rows with errors are on the top by default. The cells that have errors are highlighted with red.More about Validations
Sometimes Kotka says that data can't be saved because it contains errors (data is missing and can't be empty), but nothing is highlighted. Most often it is the case that a mandatory column (like RecordBasis) is missing, and it can't be highlighted because it doesn't exist. Fix the errors and warnings and try to import the data again. When Kotka says Validation successful, you can go ahead and save the data, click save and Kotka starts to process the Excel. This can take some time, especially if there are other files in the queue. Imports are done in the background, so when doing an import, you don't need to wait on the import page. You can anytime access the list of imported specimens using the tool Last saved Excel
Once done, always check the list of imported specimens for any failed records.
Comparison to web forms
See Specimen web forms and Comparisons to Excel import.