Create lists in CSV format
Create lists by by uploading records in comma-separated value (.csv) format. Format the data you want to comply with .csv syntax 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 which 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.
- 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.
At minimum, a .csv file must contain one column of telephone numbers. For example, a single column is appropriate for Do Not Call lists:
Most contact lists need more columns, such as contact name or account code. Be sure to define all columns needed in your .csv file. There is no limit to the number of columns it can contain. When you upload it 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.
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 Call 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 PureCloud 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.
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.
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"