You can import data on the Import Tabular CRF Data screen. To do so, you need a Tabular Data file that contains your data and a Data Mapping file that maps your Tabular Data file columns to Form items.
When you import a flat file, each data row imports into the Form in a different Event occurrence for the specified Participant.
If you import into a Common Event, new event occurrences are created as needed. If you import into a Visit-Based Event, the data imports into existing scheduled Event occurrences.
Imports appear on the Bulk Actions Log. You can also download the log in a .zip file that contains 5 files.
Note: The flat file import feature does not accept Participant OIDs, it Participant IDs. These are the user-defined IDs that identify Participants in user-facing pages across the system, as opposed to the automatically generated OIDs which identify the Participants on the backend.
- This can be used to import any data except the following:
- Not allowed: importing into forms in non-repeating common events containing multiple forms
- Not allowed: importing into items in repeating groups
To Import Data on the Import Tabular Data screen:
- Download the mapping template.
- Edit the template and save it.
- Create a Tabular Data file.
- Click the Browse button.
- Select your Data Mapping file and Tabular Data file together by pressing the Ctrl key (Windows) or Command key (Mac) and clicking on both files.
- Click the Open button.
- Click Submit.
You can click the Tasks button and select Bulk Actions Log to check the status of the job. You can view, download, or delete the import from there.
Tabular Data File
Use this file to specify Participant IDs for the Participants with data you want to import. You can also include other data items with OIDs.
Note: The following formats are supported: text (.txt), CSV (.csv), Excel (.xlsx), and SAS datasets (.sas7bdat). If you use a text (.txt) file, you can use a delimiter of your choice, for example a comma or tab. If no delimiter is specified, the import will use a pipe ( | ) by default. You can specify the delimiter in your Data Mapping File, for example: #Delimiter=t. Note that Excel automatically formats data in a useable format, but if your data comes from another source, you might need to put the data within each column in quotes if it contains commas or other delimiter values. For example, if the data is a | b | c | d,e,f | g, “d,e,f” would need to be in quotes due to the commas within the data.
To Make a Tabular Data File:
- Create a tabular file as a text (.txt) document or use another supported format (see the note above). Alternately, download the sample file to edit.
- Include a column for Participant IDs. If not configured differently, the heading must be ParticipantID.
- Enter Participant IDs.
- Create columns for associated data for each Participant, separated by pipe characters or, if you have created a text (.txt) file, another delimiter of your choice. Any spaces before or after item values are removed upon import, so the data entered in the system will not include spaces that are in the beginning or end of the file. Column headers should match those in the Data Mapping file.
Note: Make sure to use the internal item name and response values. For example, in the image below, use what_stage for the item name and stage_i, stage_ii, stage_iii, and stage_iv for the responses.
Data Mapping File
You can download the current Data Mapping file template on the Import Tabular CRF Data screen. The components are described below. There are also instructions included in the template in lines beginning with #.
Use this file to map each item to a column header from the Tabular Data file. To reference an item, use the format itemgroupOID.itemOID,
- Under the heading, # Study, Study Event and Form OIDs, enter values in the following in rows:
Note: You can only include one Form version in a Data Mapping file. To include another version in the import, you must create a new Data Mapping file and Tabular Data file. Each pair of files must be imported separately.
- Next to the first three rows, enter the appropriate OID.
- Next to FormVersion, enter the name of the Form version.
- Under the heading, # Data file OIDs, enter a row for the name of each column header in your Tabular Data file, followed by the corresponding Item Group OID and Item OID in the format of itemgroupOID.itemOID, where itemgroupOID and itemOID are the OIDs defined in the study for the item being mapped from the data file column. For example, to import an item under the column header, Ethnicity, enter Ethnicity=IG_PHYSI_GRP3.I_PHYSI_PETHNIC.
You can specify which column delimiter is used in the Tabular Data File by setting the Delimiter value, for example, Delimiter=|.
You can use Match Criteria and MatchAction in the Data Mapping file to skip importing specific data or update specific data when the data in a Participant’s Form matches the specified criteria. To use Match Criteria, you must have created Participants prior to the import.
If you want to use Skip/Match Criteria, enter SkipMatchCriteria=itemgroupOID.itemOID, where itemgroupOID and itemOID are the OIDs for the item defined in the study for the item used as part of the criteria. For example, to use First Name as criteria, enter SkipMatchCriteria=IG_PHYSI_GRP2.I_PHYSI_FNAME.
- You can enter multiple items to use as your criteria by listing them separated by commas. For example, SkipMatchCriteria=IG_PHYSI_GRP2.I_PHYSI_FNAME,IG_PHYSI_GRP2.I_PHYSI_LNAME.
- You can also use ParticipantID or EventRepeatKey as match criteria by including the header for that column you’ve specified in the comma-separated criteria list.
- If the Skip action has been specified and there is a match for all the criteria, that row is skipped.
- If the Update action has been specified and there is a match for all criteria, the existing record is updated to match what is in your data file.
- If there is no match or Skip/Match criteria are not used, the data for that row is imported.
You can elect to ignore columns in your Tabular Data File that are not referenced in your mapping file by setting the IgnoreUnmappedColumns=yes. If you do not use this option and your Tabular Data File has columns that are not mapped, your import will fail.
You can use the FormWorkflowStatus field to define a final status for your forms after import is finished. You can choose a status of Initial Data Entry or Data Entry Complete.
You can specify a Reason for Change in your import for any data changed due to Match Update. The default value is Update via Import.
After entering all configurations, save the file with the extension “.properties”.
Note: To make a .properties file, click File, and select Save As. Then change Save as Type to All Files, and add .properties to the file name. If you previously saved it as a text file, you might need to rename it to change the file type.
The Log File
- Is generated with the name datafilename_log.txt (The system replaces “datafileneame” with the name of your Tabular Data file.)
- If a data file with the same name is used again, the existing log file from a previous upload is appended to the new log file.
- Contains the following:
- Row number: The row number of the record in the Tabular Data file
- ParticipantID: The value of the Participant ID in the Tabular Data file
- Status: The outcome of the import indicated by the values Completed, Failed, or Completed with Errors
- For a failed import, displays an error code.
- For a successful import, displays the values imported or skipped.
Download the Log File:
Once the import is complete, click the Download button in the Actions column.
- Spaces in the Tabular Data or Data Mapping Files
- Invalid OID
- Items spelled differently in Data Test and Data Map File
- Mismatched number of columns in the Tabular Data File and entries in the Data Mapping File
- Items not in the correct format of itemgroupOID.itemOID
- Items listed by item name, not internal item name (variable)
- A status of Completed with Errors will be assigned if the job completed with any row failures. Rows that did not have errors will have imported successfully with this status.
Common Error Codes and Messages
|errorCode.ValidationFailed||This is not the correct response set.|
|errorCode.ValidationFailed||You have a date value which is not in ISO 8601 format at the (Form OID) Check it and try again|
|errorCode.ValidationFailed||This field should be a number.|
|errorCode.ValidationFailed||The Event CRF you are trying to update does not have the correct status.|
|errorCode.ValidationFailed||Your CRF Version (Form OID) did not generate any results in the database. Check it and try again.|
|errorCode.ValidationFailed||Your Participant OID (Participant OID) does not reference an existing Participant in the study.|
|errorCode.ValidationFailed||You do not have a user role set up for (user role) in study (Study OID)|
|errorCode.ValidationFailed||Your Study Event (Event OID) for Participant OID (Participant OID) does not reference an existing Study Event in the Study.|
|errorCode.ValidationFailed||The Item Group (Item Group OID) did not generate any results in the database. Check it and try again.|
|errorCode.ValidationFailed||Your Form Layout OID (Form Layout OID) for Form OID (Form OID) does not reference a proper Form Layout in the Form.|
|errorCode.dataRowMissingPipe||Error-data file format not match header – less pipe than header|
|errorCode.Exception||Error processing data import request.|