Preparing a CSV or TSV File for Import

Preparing a CSV or TSV File for Import

A correctly formatted CSV (Comma-Separated Values) or TSV (Tab-Separated Values) file is essential for a successful import. While these are standard formats, your file must adhere to specific rules to ensure data is processed accurately.

Basic File Requirements

Every file you import must meet these fundamental criteria.

1. Header Row

The first line of your file must be a header row. This row contains the unique names of your columns, which are used for mapping data to your form fields.

2. Identifier (ID) Column

One column in your file must be designated as the identifier for Person Matching (e.g., StudentID). For standard imports, the value in this column should be unique for each row. For multi-row imports, the same ID is used across multiple rows to group them.

3. Delimiters

For CSV files, every value within a row must be separated by a comma ( , ).

For TSV files, every value must be separated by a tab. Be consistent throughout the file.

4. Character Encoding

Save your file using UTF-8 encoding. This is the modern standard and ensures that all characters are interpreted correctly.

Formatting for Standard Field Types

Data must be formatted correctly to be accepted by different types of form fields.

Dates

All date values must use the exact same format (e.g., yyyy-MM-dd), which you must also specify in the Date Format field of your Import Configuration.

Checkboxes

To import data into a set of checkboxes, you must have a separate column for each individual checkbox option. The only valid values for these columns are:
  1. true (the box will be checked)
  2. false (the box will be unchecked)
  3. A blank value (also interpreted as unchecked)

Radio Button Groups

Similar to checkboxes, you need one column for each option in the radio button group. For any given row, only one of those columns can be set to true. All other columns for that radio group must be blank.

Value Length Limit

The data within any single cell cannot exceed 4000 characters. Longer values may be cut off or cause import errors.

Advanced Data Structures

The Import Module can handle complex data that goes beyond a simple one-to-one row-to-record import.

Formatting for Multi-Row Imports (Indexed Expressions)

To import data from multiple rows into separate, static sections of a single form (like Guardian 1 and Guardian 2), ensure all rows belonging to the same person have the same value in the Identifier Column.

Example File (guardian_data.csv):
  1. StudentID,GuardianFirstName,GuardianLastName
  2. 4876,Maria,Garcia
  3. 4876,Luis,Garcia
In your form mapping, you would use Indexed Expressions like {GuardianFirstName[0]} and {GuardianFirstName[1]} to pull from the first and second rows, respectively.

Formatting for Repeaters (CSV Files Only)

A repeater allows you to import multiple values into a section that can be duplicated on the form. This method is only supported for CSV files because it requires using a tab character within a single cell.

To format data for a repeater, the values for each instance must be separated by a tab character. If you are populating multiple fields within the repeater (e.g., Contact Name and Contact Phone), the tab-separated values in each corresponding column must align perfectly. If a value is missing for an instance, you must still include a tab as a placeholder to maintain the alignment.

Notes
To make the invisible tab character easy to see in our examples, we will use a pipe symbol ( | ) to represent it. In your actual CSV file, you must use a real tab character, not a pipe symbol.

Scenario 1: Single Field Repeater
Here, we import multiple phone numbers into one repeater field.
  1. StudentID,EmergencyContacts
  2. 4876,"555-0101|555-0102"
This will create two repeater instances, one for each phone number.

Scenario 2: Multi-Field Repeater with Missing Data
Here, we import a list of contacts into a repeater that has two fields: "Contact Name" and "Contact Phone". Notice the second contact is missing a name. The two consecutive pipe symbols ( || ) in the ContactName column act as a placeholder, ensuring the data remains aligned.
  1. StudentID,ContactName,ContactPhone
  2. 4876,"Maria Garcia||Jane Smith","555-0101|555-0102|555-0103"
When imported, the system correctly matches the data:
  1. Instance 1: Maria Garcia aligns with 555-0101.
  2. Instance 2: The empty value aligns with 555-0102.
  3. Instance 3: Jane Smith aligns with 555-0103.
Important: When creating this in a spreadsheet program, you can insert a tab within a cell. When the file is saved as a CSV, the program will typically enclose the cell's contents in double quotes ( " ) to preserve the tab characters, which is the correct format.

Best Practices for Validation

Before running a large import, it's wise to check your file's integrity.
  1. Test with a small file first. Use a manual run with just a few rows of data to confirm your formatting is correct before processing a large file.
  2. Verify in a text editor. Open your CSV or TSV file in a basic text editor (like Notepad or TextEdit) to spot any inconsistencies with delimiters or quoting that might be hidden by spreadsheet software.

Next Steps

Now that you understand how to correctly format your CSV or TSV file, you're ready to learn about the main interface for managing all your import configurations.

Next Article: