Transactional Data File Formats

This article outlines the rules and formats required for transactional mailing data files.

Transactional Data File Formats

*Indicates a Required Field

Field Data Type Valid Field Names (in first row of data file)
Email* varchar(100) Email (required)
Invoice ID varchar(50) invoice_id, invoiceid, invoice id, invoice number, invoice_number, invoice_num, invoice num
Invoice Date datetime invoice_date, invoice_dt, invoice date, invoice dt
Customer ID varchar(50) customer_id, customer id, customerid
First Name varchar(50) first_name, first name, first, fname
Middle Name varchar(50) middle_name, middle name, middle, mname
Last Name varchar(50) last_name, last name, last, lname
Title varchar(100) title
Company varchar(100) company
Address 1 varchar(100) address_1, address 1, address1, address, addr
Address 2 varchar(100) address_2, address 2, address2
City varchar(50) city
State varchar(50) state, province, state_province , state province
Zip varchar(50) zip, zipcode, zip code, zip_code
Country varchar(50) country
Phone varchar(50) phone, phone number, phone_number, telephone
Fax varchar(50) fax, fax number, fax_number, facsimile
Email varchar(100) email, e‐mail, e mail, e_mail
Quantity int quantity, qty
Product Code varchar(50) product_code, product code, item_code, item code, item_name, item name
Description varchar(255) description, desc, full description, full_description, item_description,  item description
Unit Price money unit_price, unit price, unit_cost, unit cost, item_price, item price, item cost, item_cost
Bill Begin datetime bill_begin, bill begin
Bill Thru datetime bill_thru, bill thru, bill_through, bill through
Bill Date datetime bill_date, bill date
Bill Amount money bill_amount, bill amount
Balance money balance, amount due, amount_due, balance due, balance_due
Cutoff Date  datetime  cutoff_date, cutoff date, cutoff_dt, cutoff dt
User Field 1 varchar(max) user_field_1, user_field1, user field 1, user field1, userfield1, uf1
User Field 2 varchar(max) user_field_2, user_field2, user field 2, user field2, userfield2, uf2
User Field 3 varchar(max) user_field_3, user_field3, user field 3, user field3, userfield3, uf3
User Field 4 varchar(max) user_field_4, user_field4, user field 4, user field4, userfield4, uf4
User Field 5 varchar(max) user_field_5, user_field5, user field 5, user field5, userfield5, uf5
User Field 6 varchar(max) user_field_6, user_field6, user field 6, user field6, userfield6, uf6
User Field 7 varchar(max) user_field_7, user_field7, user field 7, user field7, userfield7, uf7
User Field 8 varchar(max) user_field_8, user_field8, user field 8, user field8, userfield8, uf8
User Field 9 varchar(max) user_field_9, user_field9, user field 9, user field9, userfield9, uf9
User Field 10 varchar(max) user_field_10, user_field10, user field 10, user field10, userfield10, uf10
User Field 11 varchar(max) user_field_11, user_field11, user field 11, user field11, userfield11, uf11
User Field 12 varchar(max) user_field_12, user_field12, user field 12, user field12, userfield12, uf12
User Field 13 varchar(max) user_field_13, user_field13, user field 13, user field13, userfield13, uf13
User Field 14 varchar(max) user_field_14, user_field14, user field 14, user field14, userfield14, uf14
User Field 15 varchar(max) user_field_15, user_field15, user field 15, user field15, userfield15, uf15
User Field 16 varchar(max) user_field_16, user_field16, user field 16, user field16, userfield16, uf16
User Field 17 varchar(max) user_field_17, user_field17, user field 17, user field17, userfield17, uf17
User Field 18 varchar(max) user_field_18, user_field18, user field 18, user field18, userfield18, uf18
User Field 19 varchar(max) user_field_19, user_field19, user field 19, user field19, userfield19, uf19
User Field 20 varchar(max) user_field_20, user_field20, user field 20, user field20, userfield20, uf20
Date Format varchar(50) date_format, date_fmt, date format, date fmt

Rules for Importing

  1. Your data file must have fields delimited with a comma or a tab character. You can use a .csv (comma‐separated value) or a .txt file. The same delimiter must be used throughout the data file.
  2. You must use a " [double quote] character as a text qualifier before and after the text data in each field if the data contains a comma or a tab. Each row must be separated with a line break (CR/LF)
  3. The first row of your file must contain the names of the fields. If it does not, the file will be rejected.
  4. Any of the field names listed in the table above can be used.
  5. Field names are not case sensitive, so "Balance," "balance," and "BaLaNCe" are all valid.
  6. Fields with names other than those listed in the table will be ignored.
  7. The file may use any number of the fields listed (in any order).
  8. Each row of the file represents one transactional item.
  9. Every row must contain the same number of fields. If they do not, the entire file will be rejected. Every field does not need to have data; however, when data is blank for one field, the delimiter (comma or tab) must still be present.
     
    For example, this is valid:
    first_name, middle_name, last_name
    "John",,"Smith"
    This is also valid:
    first_name, middle_name, last_name
    "John","","Smith"
    This example would cause the file upload to fail:
    first_name, middle_name, last_name
    "John","Smith"

  10. Field data must match the data type shown in the table. If any field in any line does not match the corresponding data type, the entire file will be rejected. The data types that show as "varchar" will accept any data you put into them. All the other types will cause a file upload to fail if incorrect data is put into them. For example, putting "John" into Quantity (which is expecting an integer) will cause the upload to fail. Putting "John" into one of the date fields will also fail. If you decide you want to put a date value or numeric value into Product Code, it will accept it.
  11. Blank rows in the data file will be ignored, and the import will proceed normally. However, the first line cannot be blank and must contain only field names.
  12. The email field is required. The upload will fail if that field is not present in your data file.
  13. The user will be notified immediately on the success or failure of data file processing, and the user will be told how many records (transactional items) were processed.
  14. If the Invoice ID field is missing from the file, a unique value for it will be assigned automatically for each line.
  15. The Date Format field is not used as a personalization field. It is used to determine the output format of the date fields Invoice Date, Bill Begin, Bill Thru, Bill Date and Cutoff Date. If this column is not in the data file, then the format that is specified on the Transactional Mailing Properties page will be used instead.

    The valid values for this field are listed here:
Date Format
Example
DAYOFWEEK MONTH DD, YYYY 
Wednesday March 5, 2008 
DAYOFWEEK, DD MONTH YYYY 
Wednesday, 5 March 2008 
DAYOFWEEK, DD MONTH YYYY HH:MM 12 
Wednesday, 5 March 2008 9:32 PM 
DAYOFWEEK, DD MONTH YYYY HH:MM 24 
Wednesday, 5 March 2008 21:32 
DAYOFWEEK, MONTH DD, YYYY 
Wednesday, March 5, 2008 
DAYOFWEEK, MONTH DD, YYYY HH:MM 12
Wednesday, March 5, 2008 9:32 PM 
DAYOFWEEK, MONTH DD, YYYY HH:MM 24
Wednesday, March 5, 2008 21:32 
DD MMM YY 
5 Mar 08 
DD MMM YYYY 
5 Mar 2008 
DD MMM YYYY HH:MM 12 
5 Mar 2008 9:32 PM 
DD MMM YYYY HH:MM 24 
5 Mar 2008 21:32 
DD MONTH YY 
5 March 08 
DD MONTH YYYY 
5 March 2008 
DD‐MM‐YY 
05‐03‐08 
DD‐MM‐YYYY 
05‐03‐2008 
DD‐MMM‐YY 
5‐Mar‐08 
DD‐MMM‐YYYY 
5‐Mar‐2008 
DD/MM/YY 
05/03/08 
DD/MM/YYYY 
05/03/2008 
DDMMYY 
050308 
DDMMYYYY 
05032008 
MM‐DD‐YY 
03‐05‐08 
MM‐DD‐YYYY 
03‐05‐2008 
MM/DD/YY 
03/05/08 
MM/DD/YYYY 
03/05/2008 
MMDDYY 
030508 
MMDDYYYY 
03052008 
MMM DD, YY 
Mar 5, 08 
MMM DD, YYYY 
Mar 5, 2008 
MMM DD, YYYY HH:MM 12 
Mar 5, 2008 9:32 PM 
MMM DD, YYYY HH:MM 24 
Mar 5, 2008 21:32 
MONTH DD, YY 
March 5, 08 
MONTH DD, YYYY 
March 5, 2008 
MONTH‐DD‐YY 
March‐5‐08 
MONTH‐DD‐YYYY 
March‐5‐2008 
YY‐MM‐DD 
08‐03‐05 
YYMMDD 
080305 
YYYY‐MM‐DD 
2008‐03‐05 
YYYYMMDD 
20080305 

Examples

Below are examples of valid files:

invoice_id,CITY,email,balance
1,"Fresno","go@fresno.com",23.44
2,"Augusta","jsmith@augusta.com",444
"quantity", "description", "unit price", "bill amount", "email", "user_field_1"
20, "All-beef patties", 0.25, 0.55, "mcd@homedelivery.com", "0.05"
99, "Special sauce", 0.10, 0.11, "one@gmail.com", "0.01"
"customerID","Company","EMAIL","BILL_BEGIN","BILL_THRU","BILL_AMOUNT",invoice_id
"B07833","Microsoft","bill@ms.com",8/1/2007 0:00:00,6/19/2007 0:00:00,40.00,"MS01"
"B08219",,"me@unemployed.com",8/1/2007,7/31/2008,6/19/2007,1240.00,"UN01"