Migrating Lab Data

EDC Migrator supports lab data migration in two extract formats: normalized and denormalized.

A normalized format condenses the number of columns by stacking matching data types into a single column. This eliminates the need to add new database columns if a Data Manager introduces new tests mid-study. Conversely, a denormalized format assigns one column to every item on the lab panel without stacking matching data types, often resulting in high column counts. Rave uses log forms to create a normalized format, while InForm treats lab panels like a paper form, creating a denormalized layout.

All lab data migrations require significant preliminary work to reconcile local lab ranges, units, and analyte libraries, no matter which extract format you select.

Required Columns

Migration requires the following column headers when loading lab data:

  • Collection Date Time
  • Lab Location
  • Age
  • Analyte Name
  • Lab Result
  • Lab Result unit type

Analyte Lab Panel Column Structure Example

For a lab panel with 15 analytes, a normalized extract will consolidate the data into the required six columns. A denormalized extract will use single columns for Collection Date Time, Lab Location, and Age, but assign unique columns for each Analyte Name, Lab Result, and* Lab Result Unit*, totaling 45 additional columns (three for each analyte).

Configuring Required Settings

Before you initiate the lab data migration, you must complete the following configuration steps.

Enable Local Labs

Enabling local labs in EDC allows you to build the lab panels, site assignments, and normal range dictionaries required to receive your migrated data.

To enable local labs:

  1. Navigate to your Study in Studio and open the Study Settings tab.
  2. Click Edit.
  3. Select Yes to Enable Local Labs.
  4. Click Save.

Disable Pending Locations

If you leave pending lab locations turned On, the Migrator halts execution and throws a Workflow Progress error.

To disable pending lab locations:

  1. Navigate to Labs > Study General Settings.
  2. Click your Study to open it, or click Select Study and select your Study in the breadcrumb menu.
  3. Click Edit.
  4. Select No for the Enable Pending Lab Locations setting.
  5. Click Save.

Enable Clinical Significance

Enabling this setting allows the Migrator to map the Clinical Significance field from the source data CSV file to the applicable analyte test result.

To enable clinical significance:

  1. Navigate to Labs > Study General Settings.
  2. Click your Study to open it, or click Select Study and select your Study in the breadcrumb menu.
  3. Click Edit.
  4. Select Yes to Enable Clinical Significance.
  5. Click Save.

Allow Site Overrides

Enabling this setting helps prevent Validation & Staging errors, which occur if any lab ID cannot be matched or validated against the study’s lab locations in EDC for that site.

To allow site overrides:

  1. Navigate to Labs > Study General Settings.
  2. Click your Study to open it, or click Select Study and select your Study in the breadcrumb menu.
  3. Click Edit.
  4. Select Yes to the Allow Site Overrides for Lab Normals setting.
  5. Click Save.

Enable Query For Missing Lab Results

Enabling this setting activates automated missing data alerts to catch scenarios where an analyte contains a result with no unit or a unit with no result, which would otherwise cause an error.

To enable this setting:

  1. Navigate to Labs > Study General Settings.
  2. Click your Study to open it, or click Select Study and select your Study in the breadcrumb menu.
  3. Click Edit.
  4. Select Yes to Enable Query For Missing Lab Results.
  5. Click Save.

Preparing Lab Data

Prepare your lab data for migration by checking your Labs and Demographics source files. The following header fields must be present:

Labs File:

  • Collection DateTime
  • Lab Location

Demographics File:

  • Age

The Sex field is optional and does not have to be present in the source file. In the YAML file, Lab Location is mapped to the Item Definition’s external ID.

Collection DateTime

In the YAML, Collection DateTime is also mapped to the Item Definition’s external ID. You can use derived columns to create separate date and time fields from your source file. This arrangement supports the typical representation of lab data, where each Form row corresponds with one item.

EDC only accepts time in the dd-MMM-yyyy HH:mm format. If DateTime is marked as Intentionally Left Blank (ILB) in the Attributes CSV file, or if the date is present but the time is missing, errors will be recorded in the Log File. These errors are not recorded in the Migrations Report.

Missing Collection Times

The Migrator sends empty lab Collection Datetime values, allowing lab data to be migrated even if the collection time is missing in the source data. In these cases, the resulting lab collection form displays header information, but the lab results are uneditable until a supported time is provided.

Sex & Date of Birth

The subject’s sex is initially taken from the Gender field in the Demographics form within the EDC casebook variable. The sex value must match the approved codelist. If the sex value is missing, an error occurs. This also applies to the date of birth, which is obtained from the Demographics form within the EDC casebook variable.

Lab Data Migration

During the Run step, the system displays the number of created lab panels in the Item Group count found on the Load Results - Summary page.

During the Post-Run step, the system links lab data to queries and updates the Queries count in the Post-Run Summary table. Queries are obtained from the Queries CSV file.

When a migrating study uses lab normal range overrides, the reason for the override is included in the audit trail.

The system also applies attributes to lab data using the Attributes CSV file. It identifies analyte in the ITEM column and their related Item Definitions in the ITEM_VALUE column. Both ITEM and ITEM_VALUE are required to apply attributes. If either field is missing, the system generates an error. The Attribute count in the Post-Run Summary table displays the applied attributes.