OpenClinica Bulk Loader
From CVRG Wiki
The OpenClinica Bulk Loader allows you to import the contents of a CSV file into an OpenClinica Study Event's Case Report Form
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.
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.
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.
- In the Case Report Form Design Spreadsheet, select the Items Worksheet.
- Copy all the values under the first column (ITEM_NAME) into the first row of the template spreadsheet (after the other headers).
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.
|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.
- Look closely at the following columns:
|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.
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 ALL TILDE (~) CHARACTERS FROM THE FILE!
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
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
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.
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.