Prepare your EHR data for REDCap Import

Definitions:
EHR: Electronic Health Record
EDW: Electronic Data Warehouse
CCTS: Center for Clinical and Translational Sciences
MRN: Medical Record Number

Initial Considerations

Currently, the University of Utah does not have a direct communication between Epic and REDCap.

If your team would like to include EHR data in your REDCap project, you’ll need to either manually transcribe the data or request EHR data from the Electronic Data Warehouse (EDW) then prepare it for REDCap import.

EDW requests can be completed in connection with your CCTS registration. All REDCap projects are required to have a CCTS ID at the University of Utah. If you do not currently have one, you can request one here https://protracks.ccts.utah.edu/ which also includes the opportunity to request EDW services. If you already have a CCTS ID you can reach out to our team to send you the EDW service request form helpdesk@ccts.utah.edu.

The Process

Once you have your EHR data, follow these steps to prepare it for REDCap import.

1. Assure the variables you plan to import from the EHR exist in your REDCap project.

   1. If your EHR variables do not exist as REDCap fields, navigate to the REDCap project left menu > designer > enter draft mode > create the fields in the desired instrument(s) > submit changes for approval

   2. Confirm the variables are coded the same in the EHR dataset and your REDCap project. If not, be prepared to re-code the EHR data before import. You can check your REDCap coding scheme by navigating to the REDCap project left menu > codebook > find the variable and review the code

2. Open your EHR data in excel

3. Assure the data is oriented such that the first row is variable names, then each subsequent row is a record/participant

    

   Open

   

    

4. The first column will need to be the [record_id] of existing (or new) records in your REDCap project

   1. Cell A1 should have the following text: record_id

   2. Cell A2-# should have the record_id values

   3. If you use MRN to match EHR data to REDCap records, follow these steps:

      1. REDCap project left menu > data exports > custom selection > select the instrument that contains the MRN field > export data > export raw data as .csv file > open in Excel

      2. Manually match your data, or use a VLOOKUP Excel function or INDEX & MATCH Excel functions

5. Save your EHR data as a .csv file

6. Navigate to the REDCap project left menu > data import > select the EHR data csv file > import

Special Considerations

Re-coding data
Many instances of transferring data from the EHR to REDCap will require you to re-code data. For example, you may download sex from the EHR as string values (male vs. female), whereas in REDCap your [sex] field is coded as a radio button 1 male vs. 2 female. You’d need to replace all “female” text with “2” in the “sex” column of your EHR data in excel, and subsequently replace all “male” text with “1” in this column.

Missing data
If some records are missing data for fields that need to be imported, leave their cell blank.

Longitudinal projects

1. If you have EHR data that only represents one event…

   1. The 1st row of your EHR data should still be variable names

   2. The 1st column of your EHR data should still be: record_id

      1. cells A2-# should be the values for record_id’s

   3. The 2nd column of your EHR data should be: redcap_event_name

      1. cells B2-# should be the name of your event

      2. you can find the name of your event by navigating to REDCap left menu > project setup > define my events > far right column of the event table

   4. Columns 3-# should be your data variables

2. If you have EHR data that represents more than one event…

   1. Your data will need to be oriented so that the columns are the variables, and each row represents 1 record/participant at 1 event

      1. If your EHR data is originally formatted such that each row is 1 record/participant at all events, and the columns are each variable at each event, you’ll want to consider splitting the events into separate sheets on your EHR excel data sheet, naming the variables according to the REDCap field names (removing any event descriptor from the variable name), and adding a column for [redcap_event_name] to each sheet to perform separate imports of each event’s data

   2. The 1st column of your EHR data should still be: record_id

      1. cells A2-# should be the values for record_id’s

   3. The 2nd column of your EHR data should be: redcap_event_name

      1. cells B2-# should be the name of your event

   4. The data can be organized either by [record_id] or by [redcap_event_name], this will not affect your import. Either of these examples is correct orientation of the data:

      Open

      

Repeating Instruments
If you are importing data from the EHR for a repeating instrument, the formatting is similar to longitudinal imports.

1. Your data will need to be oriented so that the columns are the variables, and each row represents 1 record/participant at 1 repeat instance

   1. If your EHR data is originally formatted such that each row is 1 record/participant at all instances, and the columns are each variable at each instance, you’ll want to consider splitting the instances into separate sheets on your EHR excel data sheet, naming the variables according to the REDCap field names (removing any instance descriptor from the variable name), and adding columns for [redcap_repeat_instrument] and [redcap_repeat_instance] to each sheet to perform separate imports of each instance’s data

2. The 1st column of your EHR data should still be: record_id

   1. cells A2-# should be the values for record_id’s

3. The 2nd column of your EHR data should be: redcap_repeat_instrument

   1. cells B2-# should be the name of your instrument

   2. you can find the name of your instrument by navigating to the REDCap left menu > designer > in the list of instruments your instrument’s name is lowercase instrument title with _ for spaces

      1. if the instrument is Numeric Pain Rating Scale, you’d label this as follows in excel: numeric_pain_rating_scale

4. The 3rd column of your EHR data should be: redcap_repeat_instance

   1. cells C2-# should be the instance number, starting at 1

   2. note, you may have 5 instances of an instrument for 1 record but only 3 instances for a different records; this is okay

5. Here is an example of formatting for repeat instruments: