Data Transformation Considerations

Several complications can occur when populating Vault metadata. Consider the following best practices to transform data before a migration.

CSV Format

CSV files used to create or update documents using Vault Loader must use UTF-8 encoding and conform to RFC4180.

Date Formatting

Dates migrated into Vault must use the format YYYY-MM-DD.

Date/Time Formatting

Date/time conversion must use the Coordinated Universal Time (UTC) format YYYY-MM-DDTHH:MM:SS.sssZ, for example 2019-07-04T17:00:00.000Z. Hence it must end with the 000Z UTC expression, although the zeros can be any number. Ensure that date/time fields map to the correct day. This may be different depending on the time zone.

Case

If Vault metadata is case-sensitive, convert it to match the expected format.

Special Characters

Metadata must not contain special characters, for example, tabs and smart quotes. These special characters can be lost when migrating data into Vault.

Character Encodings

Saving Excel files in CSV format for use with Vault Loader can corrupt the file in an undetectable manner. If the file becomes corrupt, your load will fail. Failure logs contain a record of each row that has failed and are accessible by email or Vault notification. Correct the CSV files to continue loading.

Language

If the data being migrated is multilingual, ensure your Vault is configured to support different languages.

Multi-value Field Comma Separator

When mapping multi-value fields, values with commas can be entered through quoting and escaping. For example, “veeva,,vault“ is equivalent to “veeva,vault“.

Windows & MacOS Formatting

Data formatting can differ per environment. For instance, a line separator behaves differently when being from Windows or a MacOS.

Boolean Fields

Format Yes/No fields as true or false when migrating using the API. This doesn’t apply to Vault Loader, as it handles boolean values regardless of case.

Trailing Spaces

Remove any trailing spaces from metadata. These are commonly found after commas.

Leading Zeros

Migrate numbers in String fields as String values to preserve leading zeros and prevent their conversion to integers.

Unique Identifiers

On documents or object records where Name is not unique or is system-managed, set the External ID (external_id__v or external_id__c) to relate it to the original ID used in the legacy system. Additionally, this field helps distinguish between records in success and failure logs.

Maximum Field Length

Values in Long Text fields must not exceed the maximum length configured in Vault. Vault Loader does not truncate these values.

References to Users and Persons

Documents and objects can reference User (user__sys) and Person (person__sys) records. These records must be active in order to be referenced. If referencing people who have left the company or had a name change, reference a Person record as it does not have to be linked to a Vault user account. User names and Person names are not unique, therefore, external IDs must be referenced for these objects.

Object Lookups

Many object records have relationships with other records. For example, the Study Site object has a field called study_country__v of data type Parent Object which links it to the Study Country object. If you create a new Study Site record using Vault Loader or the API and happen to know the ID for the desired Study Country record,you can populate it. However, these IDs will change based on the Vault environment. Use a lookup table to obtain the Vault record IDs from the name__v or external_id__v fields. An alternative is to use an object lookup field in the form study_country__vr.name__v = 'United States'.