Data Transformation for Conversions

Data Transformation for Conversions

The Specify schema has data entry constraints and requirements for maintaining data consistency. When migrating from another system, some data may need to be transformed to conform to the Specify schema. Data often needs to be cleaned, concatenated, or parsed.

Before you begin

1. Back up your data:
Before making any changes to the data, keep a backup of the original data. We recommend creating a new copy of your spreadsheets before making each change rather than editing the original data directly.

2. Maintain consistency across your files:

  • CSV format: We highly recommend using the CSV format to export, edit, and save your data. If possible, export your data in .csv format from the very beginning.
  • Applications: We also recommend using a spreadsheet management application that does not automatically alter raw text data. We found the most success with using CSV format alongside the applications LibreOffice Calc and Modern CSV, while data was more susceptible to unexpected formatting changes in .xlsx format and the applications Excel and Google Sheets.
  • Encoding: UTF-8 is the recommended encoding standard. However, regardless of which encoding standard your data uses, it is important to consistently use the same format each time you open and save your files.

When opening and saving your files between edits, be sure to use the same encoding, delimiter, and column type settings. See the image below for an example configuration dialog in LibreOffice Calc.


In addition to keeping your configurations consistent between files, it is good practice to frequently check that data was not mistakenly edited or automatically reformatted.

3. Track your changes:
Maintain an updated document tracking the changes that took place, the date each change was made, and the scripts, functions, and actions used to execute the change.

Field Type Conformity

For each field in your schema mapping, check the field type of the associated Specify field in the Data Model Viewer. Review your data to identify any conflicts between your data and the constraints of the Specify field type. Below is a list of conflicts to look for in your data:

Text fields

  • Encoding: Check for encoding errors, which may manifest as characters with missing or incorrect accents.
  • Character Limit: Check that your data does not exceed the Specify field’s character limit

Number fields

  • Invalid characters: Check that no values contain invalid characters, such as text, punctuation, or spaces
  • Thousands separators: Check that numbers do not contain delimeters (eg. 2,000)
  • Extra decimal separators: Check that each field does not have more than one decimal separator (eg. 76.3.03)

Integer fields

  • Invalid characters: Check that no values contain invalid characters, such as text, punctuation, or spaces
  • Thousands separators: Check that numbers do not contain delimeters eg. 2,000
  • Decimals: Check that no values contain decimals

Boolean Fields

  • Yes/No or True/False: Ensure all values are Yes, No, True, or False
  • Typos: Check that no values are misspelled (eg. YYes)

Date Fields

  • Concatenate: Dates are often parsed out into Day, Month, Year in other data management systems, but they must be concatenated into a formatted date to upload to Specify. Check that all dates have been concatenated.
  • Precision Dates: Dates mapped to a “Precision” (full date) field must contain a full date
  • Partial Dates: Check that partial dates contain only a Year or only a Year and month. Dates with a Year and Day but no Month are invalid
  • Date Validity: Dates cannot have a greater number of days than there are in that month, although leap years are accommodated. They also cannot have more than 12 months in a year.

Note: Excel is unable to parse dates from before 1899-12-31, and will thus leave these untouched. If you’re mixing historic data from before and after this date, Excel will translate only the post-1900 dates into its internal format, thus resulting in mixed data. If you’re working with historic data, be extremely careful with your dates!

Pick Lists

  • Vocabulary: Check that no values are outside of the pick list’s controlled vocabulary
  • Typos/Duplicates: Check that no values are misspelled or inconsistent (eg. EtOH 70% vs 70% EtOH)

Formatted Fields

  • Leading zeros: Check that no values are missing leading zeros as defined by the format
  • Invalid characters: Check that no values contain characters that are prohibited by the format
  • Character limit: Check that no values exceed the character limit defined by the format

Uniqueness

Check that fields with uniqueness rules have no duplicated values. Though custom uniqueness rules can be configured from within Schema Config in Specify, default uniqueness rules can’t be disabled or changed and therefore must be accounted for.

The chart below lists each table with default uniqueness rules, which fields have a uniqueness rule, and the scope of the uniqueness rule.

Uniqueness Rules

Table Field(s) Unique In
Accession accessionNumber division
AccessionAgent role, agent accession
AccessionAgent role, agent repositoryAgreement
Appraisal appraisalNumber accession
Author agent referenceWork
Author orderNumber referenceWork
BorrowAgent role, agent borrow
Collection collectionName discipline
Collection code discipline
CollectingEvent uniqueIdentifier Database
CollectionObject catalogNumber collection
CollectionObject guid institution
CollectionObject uniqueIdentifier Database
Collector agent collectingEvent
Determiner agent determination
Discipline name division
DisposalAgent role, agent disposal
Division name insitution
Extractor agent dnaSequence
FundingAgent agent collectingTrip
Gift giftNumber discipline
GiftAgent role, agent gift
GroupPerson member group
Institution name Database
Loan loanNumber discipline
LoanAgent role, agent loan
Locality uniqueIdentifier Database
LocalityCitation referenceWork locality
PcrPerson agent dnaSequence
Permit permitNumber Database
PickList name collection
PrepType name collection
RepositoryAgreement repositoryAgreementNumber division
SpAppResourceData spAppResource Database
SpecifyUser name Database
TaxonTreeDef name discipline
TaxonTreeDefItem name treeDef
TaxonTreeDefItem title treeDef

Table Legend

Table: Name of table that unique field(s) are in.

Field(s): A comma separated list of fields that must be unique in the given table/row.
Note: Multiple fields means both fields must be unique. For example, if both field1 and field2 are unique to Database, then there can be multiple field1’s which have the same value, but there can not be two records of Table which has both field1 and field2 be the same value.

Unique In: The “scope” of the uniqueness rule.
Note: This is either a relationship to another table which exists on Table or Database. When checking for uniqueness, the unique fields must only be unique within the scope stated in the Unique In column.

(Table and Legend from https://speciforum.org/t/business-rules-and-logic-in-specify-7/1231#p-2149-uniqueness-rules-6)

Special Cases

Latitude/Longitude

All values in the latitude or longitude fields must be valid, meaning latitude must be equal to or between -90 and 90, and longitude must be equal to or between -180 and 180.

Parsing

The following data is often concatenated in other databases:

  • Agent First name + Last name
  • Agents + Dates

Specify has the ability to store this information in separate fields and then format it as one field in query results, labels, and data exports. Therefore, we recommend parsing out agent information into columns for first name, last name, and initials.

Required Fields

The Specify schema requires some fields to be populated if any data is entered in the table. Some frequently used fields that are required by the schema include:

  • Locality Name (Locality)
  • Prep Type (Preparation)
  • Loan Number (Loan)
  • Accession Number (Accession)
  • Reference Work Type (Reference Work)
  • Role (Accession Agent, Loan Agent, Borrow Agent, Deaccession Agent)

Current (Determinations)

This field is for whether or not a determination is the most current. Specify’s default behavior is to display this record first in the list of determinations associated to a collection object.

Workbench’s in-built logic assumes that the most recently uploaded determination is the most current, but this may not always be true during conversions.

The most recent determination record should be given a True value, while all others associated with that record should be False.

Agent Roles

When creating an Interaction (i.e. a Loan, Borrow, or other such record), there is a table for agents associated with this interaction. This table has a required Agent Role field, which denotes a person’s involvement.

This field is controlled vocabulary by default, but this can be changed. However, if you want to keep it, you will need to make sure to decide Agent Roles for each agent and that they exist in the picklist.

This picklist can be edited.

Preparation Counts

Specify has the ability to track the availability of Specimens using interactions, including Loans, Gifts, Exchange In, Exchange Out, and Disposals.

To use interactions, the Count field in the Preparation table must be populated. Therefore, we always recommend populating the Count field in case your collection decides to use interactions in the future. The Count value must be no lower than 0.

For most collections without count information, simply setting the preparation to 1 should be fine. If you are loaning parts of tissues, you can set it to 100 to represent the percentage of the tissue left.

Quantities for Interactions

Tying into Counts, interactions must include quantities to represent the quantity of Preparations leaving the collection and, if applicable, the quantity being returned.

Different interactions require different quantities to be populated a certain way. If you will upload interactions during the conversion process, you must follow the instructions to prepare your interaction data from the article Uploading Interactions Using the WorkBench.

Preparation Type

Preparation Type is a required field within Preparation that consists of controlled vocabulary.

You will likely need to make your own picklist before uploading any preparations. Below is the documentation for this.

Identifiers

If your current data management system is relational (which means that your database links across different tables/spreadsheets (eg. Locality record links to a Collection Object), we recommend creating unique identifiers for certain tables.

We highly recommend creating identifiers for the following tables:

  • Agent
  • Locality
  • Collecting Event (Shared)
  • Preparation

Using identifiers can help avoid the need to disambiguate records in the Specify WorkBench and prevent the accidental creation of duplicate records.

To create identifiers, you must:

  1. Create a spreadsheet of all your unique records in a table
  2. Create a unique ID for each row
  3. Wherever this record is referred to by another record in the database, create an additional column to add the ID
  4. Remove the other information for the identified record, leaving only the ID

The tables below depict example transformations to create identifiers for Agents.

Collection Object Spreadsheet:

Catalog Number Cataloger First Name Cataloger Last Name
000000019 John Smith
000000020 Grant Fitzsimmons
000000021 John Smith

Agent Spreadsheet:

Agent First Name Agent Last Name Agent Identifier
John Smith 00001
Grant Fitzsimmons 00002

Edited Collection Object Spreadsheet:

Catalog Number Cataloger Identifier
000000019 00001
000000020 00002
000000021 00001

Split Spreadsheets

For the cleanest and most forgiving workflow, data should be separated by table before the upload. Be sure each spreadsheet has the appropriate identifiers if applicable.

We recommend separating data into the following tables:

  • Agents
  • Taxonomy
  • Geography
  • Storage
  • Localities
  • Collecting Events (Shared)
  • Collection Objects
  • Interactions

Tables in dependent relationships should be kept in the same spreadsheet as their independent counterparts. Here are a few examples:

  • Collection Object + Collection Object Attribute + Preparation + Collecting Event (Embedded) + Determinations
  • Locality + Locality Detail
  • Collecting Event (Shared) + Collecting Event Attribute
  • Taxon + Taxon Citation

Next Steps

After cleaning the data to the best of your ability, the next step in the conversion process will be to perform a test upload of a sample of your data. A test upload utilizes the WorkBench validation process to identify any data consistency issues you may have missed. You may need to repeat test uploads and data transformation several times before actually migrating your data.

Before moving on to performing a test upload, familiarize yourself with the WorkBench by reading the documentation available in the WorkBench Workflows category.