CSV file requirements

Keep these considerations in mind when you prepare a CSV file to be imported into the application.

Quick checklist

Ensure your CSV file meets all formatting requirements to be imported successfully. More information about each requirement is covered below.

Done	Requirement
	The CSV file is less than 50 MB.
	The CSV file contains a maximum of 50,000 rows.
	Include required columns: Generally, the CSV file must include a column called MemberId or Email. If you are performing a system upload to change members' email addresses, the CSV file must include both MemberId and Email. If you are performing a system upload to switch members' communities or add new members to a Community, the CSV file must include a column called Community.
	If you plan to pipe first and last names into emails and these values do not already exist in the system for the email recipients, the CSV file has columns called FirstName and LastName.
	None of the column headers use reserved system variables or keywords unless: You are preparing a CSV file for system upload. You are uploading FirstName and LastName values for recipients of ad hoc activity invitations.
	The CSV file uses the same CSV delimiter specified in your personal user settings.
	Each row is on a separate line.
	There are no duplicate emails.
	Single Choice variables are formatted correctly, and the Single Choice profile variables already exist in the application. Ensure the number values for each single choice question does not exceed 1000.
	Text values are formatted correctly, and the Text profile variables already exist in the application.
	Multiple Choice variables are formatted correctly.
	Date values are formatted correctly, and the Date profile variables already exist in the application.
	No Calculated Dates are included. Importing Calculated Dates is not supported.
	Number values are formatted correctly, and the Number profile variables already exist in the application.
	Identity values are unique, and the Identity profile variables already exist in the application.
	For non-Latin character sets, you've used a program other than Excel to create the CSV file so it imports correctly.

Maximum file size

The CSV file size cannot exceed 50 MB. As long as the file does not exceed the maximum file size, there is no limit on the number of rows. However, we recommend splitting large files into smaller files with a maximum of 50,000 rows each. This makes it easier and faster to import files and identify and troubleshoot errors.

Required columns

The columns you require depend on the task you are performing:

• Generally, the CSV file must include a column called MemberId or Email.
• If you are performing a system upload to change members' email addresses, the CSV file must include both MemberId and Email.
• If you are performing a system upload to switch members' communities, the CSV file must include a column called Community.

To import the CSV file successfully, each row must have values for required columns. The values cannot be empty.

Column	Description
MemberId	MemberId represents the unique IDs used to identify members in the application. The MemberId values must correspond to real members, and not be test MemberId values that are auto-generated to go with test responses. Upon importing the CSV file, the system checks whether there is a MemberId column, and matches profile variables to members based on MemberId. If your CSV file does not have a MemberId column, Email is used instead.
Email	Email represents the valid email addresses of active or prospective members. Upon importing the CSV file, the system checks that the email addresses are in a valid email address format. If your CSV file does not have a MemberId column, the system matches profile variables to members based on email addresses. Note: Unless you are performing a system upload, email addresses are not updated when you import a CSV file, so that members' current email addresses are not overwritten accidentally. To find out if an email address in your CSV file does not match a member's current email address, download the Errors and Alerts file that was generated from your import. You can then update your external system's records.
Community	Community represents the names of the communities to which members belong. Include this column if you are performing a system upload to: Add new members to a Community. Change the communities to which members belong. If you only have one Community or you are not performing the aforementioned tasks, Community is not required.

The following table describes what happens to members and profile variables when you import a CSV file.

If the CSV file contains	Then
A new email address	A prospective member is added with a status of nonmember.
An existing email address	The application updates the member and profile variables associated with that email address to match what is in the CSV file.
An old email address	The application keeps track of all email addresses, old and new, that have been associated with members and nonmembers at any point in time. When the application detects an old email address in a CSV file, it updates the member and profile variables associated with that email address to match what is in the CSV file. The current email address stored in the application does not change to match what is in the CSV file, unless you are performing a system upload. Instead, an Errors and Alerts file is generated notifying you of the outdated email address.
A new profile variable	The profile variable is added to the application, and can then be used: In surveys for conditional logic In reports to enhance your data analysis In sampling to define your member group or recruitment sample If a profile variable has been renamed but the CSV file uses the old profile variable name, the application will create a new profile variable under the old name upon import, instead of merging the values into the renamed profile variable.
An existing profile variable	The application updates or adds variable values. This process is case-insensitive. For example, if the file contains City values spelled as vancouver, toronto, and montreal but they were spelled as Vancouver, Toronto, and Montreal when the profile variables was first created, they values will be imported as the latter.

FirstName and LastName

If FirstName and LastName values were added to the system previously, you do not need to include these columns. However, if these values do not exist for members or recipients of ad hoc activity invitations, you can add them using these column names:

• FirstName
• LastName

Tip: Upgraded customers have the option of using a column named PanelistName to import members' first and last names.

System variables

The columns in your CSV file cannot use the same names as system variables, unless you are performing a system upload. If you attempt to import a CSV file that contains columns with the same names as system variables, the import will fail. For more information, see System variables and Perform a system upload.

Rows and columns

You must use commas as delimiters between values and separate lines for each row.

Required or recommended CSV columns can be in any column of the CSV file. As long as the columns are named correctly (MemberId, Email, FirstName, LastName), the system will detect them and import them correctly.

Tip: To avoid CSV import errors, review your CSV file in a text editor, and delete any blank rows or rows without email addresses.

Single Choice variables

A member can have a single value for each profile variable.

Keep these considerations in mind when you are formatting single choice variable data:

• Use a distinct column header for each new Single Choice variable you import.
• To update values for an existing profile variable the column header must exactly match the profile variable name.
• If a value previously existed for a member, you cannot import a blank value to overwrite it.

• Ensure that you do not to inadvertently create a Multiple Choice variable or convert an existing Single Choice variable to a Multiple Choice variable. This can happen if you use the attributeName_attributeValue column naming convention required for Multiple Choice variables for Single Choice variables.

  For example, specifying device_iPhone, device_android, and device_blackberry creates a Multiple Choice variable named device with three choices (iPhone, android, blackberry) if the import values match the requirements for Multiple Choice questions (true/false, yes/no, or 0/1). For more information, see CSV file requirements.

Tip: Whenever possible, do not use underscores (_) in Single Choice column headers to avoid situations where the wrong type of profile variable is created during import.

Text variables

A member can have an open-ended text value for a profile variable.

Multiple Choice variables

A member can have multiple values for one profile variable.

Keep these considerations in mind when you are formatting multi-choice variable data:

• For every multi-choice value, create a column header called attributeName_attributeValue.
• There must be at least two corresponding attributeName_attributeValue column headers for the data to be imported as multi-choice.
• If you have attributeName or attributeName_ as a column header, it will read as an error when you try to import the file.
• To denote which multi-choice values are selected or cleared, use these sets of binary values in the rows.

  Selected	Not Selected	Usage Notes
  1	0
  true	false	Values must be lowercase.
  yes	no	Values must be lowercase.

• Use blank spaces to indicate no data exists for a member.

Note: You must ensure that only the supported binary values, or blank spaces, are included for Multiple Choice variables. If a string, number, or date is included where a binary value is expected a new Single Choice variable will be created based on that entry.

Example

You are importing information about members' favorite colors and prepare the following CSV file.

When you import the CSV file, the profile variable color is created. Each member's profile data for color is as follows:

• Ellie's favorite color is blue.
• Ken's favorite colors are red, blue, and yellow.
• Paul's favorite colors are red, yellow, and green.
• Fran has no favorite color.

Date variables

Date and time values follow the ISO 8601 format (YYYY-MM-DDThh:mm:ssZ).

Example

2015-04-29T15:16:55Z

Note:

• Before importing Date values, you must create a Date profile variable with the same name.
• Only Coordinated Universal Time (UTC) time values are supported.

Tip: You can use custom cell formats in Excel to quickly reformat all dates. To do this, right-click on a cell and select Format Cells. In the Format Cells dialog, on the Number tab, click Custom. Type YYYY-MM-DDThh:mm:ssZ in the Type field and click OK. After the date format is saved, you can select a range of cells with date values and apply the custom format.

Number variables

Numeric values with and without decimals are supported. Do not use commas or symbols (such as dollar signs).

Note:

• Before importing numeric values, you must create a Number profile variable with the same name.
• Microsoft Excel truncates numbers over 15 digits, so digits over the limit will display as zeros. However, this display limit will not affect the system recalling the information and opening the CSV file using a text editor will confirm the actual figure.

Identity variables

Identity profile variables let you assign a unique identity value to members.

Use Identity profile variables if:

• You already have unique IDs in an external system, and you want to map member records between systems.
• You want members to add unique identifying information (for example, a social media handle or a phone number).
• You want to use something other than Email or MemberId to identify members.

Examples of possible Identity values include:

• Twitter handle
• Loyalty card number
• Customer ID
• Phone number

There are no character limitations.

Note: Before importing identity values, you must create an Identity profile variable with the same name.

Non-Latin character sets

Warning: If your file contains non-Latin characters such as Chinese, Arabic, or Cyrillic characters, do not use Excel to create your CSV file. If you use Excel, these characters will not appear correctly when you import the file. Instead, use Notepad or a Google spreadsheet to create the file.

Table 1. Using a text editor to create and save the CSV file for non-Latin character sets

Windows	Mac
Create the file in Notepad following the general guidelines for CSV files. Click File, and select Save As. Give the file a name. From the Encoding list, select UTF-8. Click Save.	Create the file in TextEdit following the general guidelines for CSV files. Click File, and select Save As. Give the file a name. From the Plain Text Encoding list, select UTF-8. Click Save.