OpenClinica Bulk Loader

From CVRG Wiki

Jump to: navigation, search



The OpenClinica Bulk Loader allows you to import the contents of a CSV file into an OpenClinica Study Event's Case Report Form

Intended Use

The Bulk Loader is intended for transferring a large chunk of data into the OpenClinica system.

It is not intended to be a substitute for the OpenClinica web interface and does not provide the same level of user feedback or validation included in the OpenClinica core application.

Please only use the bulk loader when you have too much data to enter into the OpenClinica one at a time. A good rule of thumb might be any cluster of data less than 25 rows should be entered individually into the core OpenClinica app.

Use the bulk loader at your own risk. Although the CVRG has made every effort to prevent data integrity errors resulting from the bulk loader, we cannot guarantee it will always upload clean, non-duplicate data if the data in the import file is improperly formatted.

The safest way to enter OpenClinica data is always through the core OpenClinica application, not the bulk loader.

Bulk Loader Workflow

Step One: Download CSV File Template

Click here for a template for the Bulk Loader's CSV file.

Notice that the first row is prepopulated with non-CRF field headers, and the subsequent rows contain some sample data.

Bulk Loader Template
Bulk Loader Template

Step Two: Retrieve Case Report Form Field Names

  • Identify the Case Report Report form you want to populate with data within OpenClinica.
  • If you have access to the parent study for your site, go to the Tasks menu and select CRFs under the "Monitor and Manage Data" or "Administration" submenu.
  • Select the down arrow icon to the right of the CRF version you want to populate. This will download an Excel spreadsheet with the Case Report Form Design information.
List of CRFs
List of CRFs

Make sure you choose the appropriate CRF Version -- look for the Default Version under the Event Definition in the "Build Study" page for this information.

Default CRF Version for a Study Event Definition
Default CRF Version for a Study Event Definition
  • In the Case Report Form Design Spreadsheet, select the Items Worksheet.
Case Report Form Design Template
Case Report Form Design Template
  • Copy all the values under the first column (ITEM_NAME) into the first row of the template spreadsheet (after the other headers).
Pasting Columns from the Case Report Form Design into the Bulk Loader Import File
Pasting Columns from the Case Report Form Design into the Bulk Loader Import File

Step Three: Enter Data

Non-Case Report Form Fields

The following table is a description of the NON-CRF fields in the Bulk Loader Template.

IMPORTANT: Do NOT change the names of any of these fields in the first row of the template! They must be an EXACT match or the bulk loader will not populate them!

Field Name Description Required? Default Value Notes
Study Subject ID Unique, non-PHI identifier for this subject within your study's site. Yes You may enter the same Study Subject ID twice. However, all instances of that study-subject must be part of the same study event.

For instance, if you are populating an OpenClinica study event called "EKG Exam," you shouldn't enter data for when a subject came in for another OpenClinica Study Event (perhaps "CT Exam"). Both study events may have include Case Report Form, but you should create a separate import file for each one.

Gender Subject's gender at birth Yes, for new Subjects Male
Birth Year Birth Year Yes, for new subjects 1900
Person ID A unique ID can be used to identify the same subject across multiple sites and studies. No Only place unique GUIDs in this field that you have generated with the CVRG's GUID Generator application.

You cannot CHANGE an existing Study Subject's Person ID with the bulk loader, but if an existing subject has it blank, the bulk loader will populate it.

Secondary ID This can be any NON-PHI identifier that a particular study or site uses. No You cannot CHANGE an existing Study Subject's Secondary ID with the bulk loader, but if an existing subject has it blank, the bulk loader will populate it.
Interviewer Person who interviewed the subject when data was collected No "unknown"
Interview Date Date subject's data was collected. No Today's date
Date Started Date the study event you are populating was started. No Today's date
Date Ended Date the study event you are populating was ended. No Today's date
Location Free text field -- place where subject's data was collected No "unknown"

Case Report Form Fields

  • After you've populated the non-CRF fields, please go back to the CRF Design Spreadsheet and scroll to the RIGHT of the Items tab.
Important CRF Design Fields
Important CRF Design Fields

  • Look closely at the following columns:
Column Description
RESPONSE_VALUES_OR_CALCULATION If field is a drop-down box, the list of valid values for this field, separated by a comma. If a number field, it is the calculation that OpenClinica performs to automatically derive this field's value. For the Bulk Loader, you will have to manually do this caluclation and enter the value yourself.
DEFAULT_VALUE Default value for this field if blank.
DATA_TYPE One of the following codes:

ST - Character String INT - Integer REAL - Floating Point (i.e: number with decimals) DATE - Date -- place in MM/DD/YYYY format PDATE - Partial Date -- only month and year or just year required FILE - File -- NOT ACCEPTED BY THE BULK LOADER

VALIDATION Validation string that OpenClinica performs on data entered into this field.
REQUIRED 0 for not required, 1 for required.
WIDTH_DECIMAL Width is the first number and the number of decimals allowed is the second.
  • IMPORTANT: If you don't follow all of the rules specified in the CRF design, the Bulk Loader may enter corrupt data into OpenClinica, and you may experience errors when trying to access this data through the OpenClinica interface.
  • The CVRG is not responsible for data corruption resulting from improperly formatted CRF data!
  • Following the validation rules you see, enter data for each Case Report Form Field into the Bulk Loader Import file.
Enter data for each of the CRF fields
Enter data for each of the CRF fields

Conflicts Between CRF and non-CRF Fields

If one of your CRF fields has the same item name as the header for a non-CRF field, the bulk loader will use leftmost column for the non-CRF value and the rightmost column as the CRF field value.

Step Four: Save the Import CSV File on Your Hard Drive

Remove tildes


Easiest way is to do CTRL-H within Excel (for Find/Replace) and Replace All ~ characters with a blank space or a null string.


Change your computer's list separator to the tilde (~) character

-- Windows XP/Vista instructions for changing list separator: Click here

-- Windows 7 instructions:

  • Click Start , point to Settings , and then click Control Panel .
  • Double-click Regional Settings or Regional Options . Click the Number (or Numbers ) tab.
  • Choose the ~ character in the List Separator list
Setting list separator to Tilde in Windows XP
Setting list separator to Tilde in Windows XP

Save as a CSV file

  • In Excel, click Save As...
  • Next to Save As Type..., select the "CSV (Comma Delimited) *.csv" option (even though our delimiter is now a ~, it is still saved as a .csv file)
  • Select a location of your choice and click Save
Saving to a CSV file
Saving to a CSV file

Step Five: Use the Bulk Loader Web Interface to Upload and Import the File

Go to the Bulk Loader web interface. Here you will be able to upload the CSV import file you just created.

Web Interface
Web Interface

Follow these instructions to use the interface:

  • Enter your OpenClinica User Id
  • Enter the Bulk Loader Password -- NOT YOUR OPENCLINICA PASSWORD. If you don't have the bulk loader password, you must contact us to get it.
  • Select the Site you are a member of.
  • Select the Study Event Definition that you want your Case Report Form data to go into.
  • Select the Case Report Form that you are populating.
  • The web interface then allows you to enter the Interviewer, Interview Date, Date Started, Date Ended, and Location non-CRF fields.

The bulk loader will populate study-subjects who don't already have values in these fields with what you enter here.

  • Click the Mark CRFs Complete checkbox if you want OpenClinica to mark each the CRF you enter as Completed (not recommended). Be advised that once you mark a CRF complete, you have to enter a discrepancy note for any changes.
  • Click the Mark CRFs Complete checkbox if you want OpenClinica to mark the Study Event that the CRF is a part of as complete. Do not check this box until you've populated all required CRFs for the study event.
  • Click the Browse button, navigate to where the CSV file is located on your computer, and select it.
  • Click the Send button to send your data to OpenClinica.

Validation and Errors

  • If successful, you will get a message that says "File Submitted"
  • If your OpenClinica user ID is misspelled, the bulk loader will inform you and you will have to resend the file.
  • If your CRF data contains errors, you will receive a message stating what line the error occurred on, and what study subject caused the error.

IMPORTANT: If an error occurs, all previous lines are committed to the database, so please remove them before you resend the file to avoid duplicate data.

Personal tools
Project Infrastructures