Create lists in CSV format

Create lists by uploading records in comma-separated value (.csv) format. Format the data that you want to comply with .csv syntax encoded in UTF-8 before uploading or appending it to a list:

  • Each record is one line terminated by a line feed or carriage return/line feed pair.
  • Column names cannot contain special characters, such as a backslash.
  • Use commas to delimit data items (called fields).
  • The first line must be a header row of field names separated by commas.
  • Enclose fields that contain spaces or commas in double quotes.
  • Enclose a string that contains leading or trailing spaces in double quotes.
  • If a field is empty, insert a comma at that position in the record.
  • Enclose fields that contain commas inside a pair of double quote characters. For example, “firstname,lastname”,”phonenumber”,”address”.
  • If a field contains double quotes, enclose the entire field in double quotes and convert each double quote character to two double quote characters.
  • The .csv file must not include any blank rows at the end of the data, which can cause an import failure.

    Required columns

    At minimum, a .csv file must contain one column of telephone numbers. For example, a single column is appropriate for Do Not Contact lists:

    Phone
    317-123-4567

    Note: Formatting characters are ignored, except for the leading plus sign (+) with E.164 formatted numbers.

    Most contact lists need more columns, such as contact name or account code. Be sure to define all columns needed in your .csv file.

    When you upload the file to create a new list, the system creates a table with matching columns in the cloud. Afterward, you cannot append data that contains a different set of columns.

    Do not exceed 50 columns

    The maximum number of columns allowed in a contact list is 50. An error message appears if this new contact list exceeds that limit, but an entry does not appear for it in the Event Viewer.

    Column names must be unique

    All column names in the first row must be unique. If you duplicate a column name, the file does not import.

    Include a unique identifier column

    Include a column in the .csv that uniquely identifies each row. Doing so ensures that you can accurately append new or updated data in the future. Typical unique identifiers are a customer’s ID or account number. Later, when you append records to your contact list, select your Unique Identifier column. Using that column, the append process can most accurately match incoming data against existing contacts.

    Without a unique identifier, the entire row is the primary key. If you append a modified record, a subsequent upload appends that record as a new row, even if an existing row has the same identifier. This behavior is not what people expect when they export a list, change it, and then append (upload) their data.

    When you use a unique identifier, the system replaces the existing record instead.

    Do not use special characters in unique identifiers

    You can compose a unique identifier from alphanumeric characters. Spaces, backslashes, and these special characters do not work with URLs:

    – . _ ~ : / ? # [ ] @ ! $ & ‘ ( ) * + , ; =

    Use zone columns

    Contact list records often store multiple phone numbers per contact, such as work, cell, and home numbers. To use the callable times feature, each phone number column must have a corresponding time zone column containing the zone name in Tz database format. For example, a column named “Home Time Zone” to contain the time zone corresponding to each number in the “Home Phone” column. Do Not Contact lists do not use zone columns, by the way.

    • As a best practice, specify both a unique Id and a zone column corresponding to each phone number column (for example, home or work), when you create a contact list.

    • You cannot add zone columns later, but you can populate or update existing zone columns by appending (uploading) records that have the same unique ID. When Genesys Cloud finds a matching row, it updates columns using data in the append file. For example, the system updates any zone columns in a record with time zone data read from the .csv file. The zone columns must exist.

    • Associate each time zone column with a contact number column, which you typically do when you create the list. If you forget, you can edit the contact list later, and map its time zone columns to phone number columns by selecting from the time zone column drop list.

      Figure shows how to associate a phone number column with a column that identifies its time zone

    Do not remove leading and trailing whitespace

    When you save a contact list, the leading and trailing whitespace is removed from the contact field of the contact list. If you do not want to remove the whitespace from your contact list, switch the Trim leading/trailing whitespace toggle to No.

    Use a spreadsheet instead of a text editor to edit .csv files

    We recommend that you Save As .csv from a spreadsheet editor. When the spreadsheet saves a .csv file, it inserts needed quotes and commas automatically. Using a spreadsheet is less error prone than formatting records manually.

    spreadsheet_data

    For example, the File > Save As > CSV (Comma Delimited) command in Microsoft Excel generates the following .csv records:

    Last Name, First Name,Home Phone, Home Time Zone,Cell Phone,Cell Time Zone,Balance Due,Account Code,HomeAddress
    Doe,"James ""Big John""",317-123-4567,America/Indiana/Indianapolis,317-987-6543,America/Indiana/Indianapolis,175,1003,"123 Anystreet, Indianapolis, IN"
    Downing,Mary,424-999-2334,America/Los_Angeles,,,234,3799,"724 Bel Air Drive, Los Angeles, CA"
    
    
    Note: If you are using Excel for your spreadsheet app when creating CSV lists for SMS campaigns, the phone number columns must be in E.164 format. For phone number columns, use the Excel Custom format, with the +# ?/? number format code. If you do not specify Custom formatting, Excel default formatting removes the ‘+’ prefix.