2. Customer‎ > ‎

2.2 Upload new customers

The following fields are supported for import of customer data. Note that a customer may have up to 3 payment methods on file (for auto-pay), so this could be a combination of (say) 2 credit cards and 1 bank account. The total number of payment methods should not exceed 3. In the table below, the


If tokenization is supported by your selected 3rd party payment processor and you have existing customers with tokenized payment methods on file, then use only the tokenization fields starting on No 72 below and not the credit card or ACH fields. For example if a customer had a single credit card that was tokenized by your payment processor, then populate the customer data (fields 1-26) and the 1st tokenization record (fields 72- 82).


  1. Navigate to Customers >> Upload Multiple Customers
  2. Download the customer upload template as below

Fig 11.1 shows how to download template for customer

3. Fill the applicable below fields as per the template

NoFieldRequiredNotes
1 First NameYFirst name of the billing contact
2 Last NameYLast name of the billing contact
3 Company NameNRequired for business accounts
4 Account NumberYYour customer account number. Create an arbitrary one if you do not have one. Must be unique
5 Primary EmailY
6 LoginYCreate a unique logon name for each customer. Must be at least 6 characters
7 PasswordYThe Password must be at least 8 characters and MUST INCLUDE AT LEAST ONE UPPER CASE LETTER, ONE LOWERCASE LETTER AND ONE NUMBER
8 StreetNCustomer address fields are recommended
9 Suite/DeptNCustomer address fields are recommended
10 CityNCustomer address fields are recommended
11 State / ProvinceNCustomer address fields are recommended
12 Zip / Postal CodeNCustomer address fields are recommended
13 CountryNCustomer address fields are recommended
14 Country CodeNCustomer address fields are recommended
15 Primary PhoneN
16 Cell PhoneN
17 Outstanding BalanceYThe current outstanding balance for this customer, should ideally be 0.00 unless you have imported previous invoices. This equation MUST hold true: OutstandingBalance = PreviousBalance + CurrentBalance - LastPaymentAmount
18 Previous BalanceYThe amount carried over from the previous invoice
19 Current BalanceYThe amount of current charges for the current invoice
20 Last Payment AmountYThe amount for the last payment received for this customer
21 Last Payment DateYThe date of the last payment
22 Last Invoice AmountYThe last invoiced amount
23 Last Invoiced DateYThe date on the last invoice
24 Lifetime PaymentsYThe total payments made by this customer to date excluding adjustments such as credits, refunds, etc. Set to 0.00 if unknown
25 Lifetime ValueYThe total value derived from this customer to date (including payments and credits). Set to 0.00 if unknown
26 Next Due DateYThe next due date for the outstanding invoice
27 Credit Card No1NThe credit card to store with the customer's profile for auto-pay
28 Card Expiry Date1NThe expiry date for the credit card
29 Card Type1NOne of the following: 'Mastercard', 'Visa', 'Amex', 'Discover', 'JCB', 'Diners'
30 Cardholder Name1NThe name on the credit card
31 Cardholder Street1NBilling address if different from the customer address
32 Cardholder City1NBilling address if different from the customer address
33 Cardholder State1NBilling address if different from the customer address
34 Cardholder Zip1NBilling address if different from the customer address
35 Cardholder Country1NBilling address if different from the customer address
36 Cardholder Countrycode1NBilling address if different from the customer address
37 Credit Card No2NThe credit card to store with the customer's profile for auto-pay
38 Card Expiry Date2NThe expiry date for the credit card
39 Card Type2NOne of the following: 'Mastercard', 'Visa', 'Amex', 'Discover', 'JCB', 'Diners'
40 Cardholder Name2NThe name on the credit card
41 Cardholder Street2NBilling address if different from the customer address
42 Cardholder City2NBilling address if different from the customer address
43 Cardholder State2NBilling address if different from the customer address
44 Cardholder Zip2NBilling address if different from the customer address
45 Cardholder Country2NBilling address if different from the customer address
46 Cardholder Countrycode2NBilling address if different from the customer address
47 Credit Card No3NThe credit card to store with the customer's profile for auto-pay
48 Card Expiry Date3NThe expiry date for the credit card
49 Card Type3NOne of the following: 'Mastercard', 'Visa', 'Amex', 'Discover', 'JCB', 'Diners'
50 Cardholder Name3NThe name on the credit card
51 Cardholder Street3NBilling address if different from the customer address
52 Cardholder City3NBilling address if different from the customer address
53 Cardholder State3NBilling address if different from the customer address
54 Cardholder Zip3NBilling address if different from the customer address
55 Cardholder Country3NBilling address if different from the customer address
56 Cardholder Countrycode3NBilling address if different from the customer address
57 Aba Routing Number1NThe 9 digit bank routing number for ACH
58 Account Number1NThe bank account number for ACH
59 Institution Name1NThe name of the bank or financial institution
60 Account Type1NOne of the following: 'Checking', 'Savings', 'BusinessChecking'
61 Account Name1NThe name on the bank account
62 Aba Routing Number2NThe 9 digit bank routing number for ACH
63 Account Number2NThe bank account number for ACH
64 Institution Name2NThe name of the bank or financial institution
65 Account Type2NOne of the following: 'Checking', 'Savings', 'BusinessChecking'
66 Account Name2NThe name on the bank account
67 Aba Routing Number3NThe 9 digit bank routing number for ACH
68 Account Number3NThe bank account number for ACH
69 Institution Name3NThe name of the bank or financial institution
70 Account Type3NOne of the following: 'Checking', 'Savings', 'BusinessChecking'
71 Account Name3NThe name on the bank account
72tokenA1NIf using tokenization with a payment gateway, then the value of the 1st token. For the Authorize.Net payment processor, this corresponds to the customerProfileID
73tokenB1NIf using tokenization with a payment gateway, then the value of the 2nd token (if any). For the Authorize.Net payment processor, this corresponds to the customerPaymentProfileID
74tokenC1NIf using tokenization with a payment gateway, then the value of the 3rd token if any
75expiry1NThe expiry date for the underlying instrument (credit card expiry date or ACH authorization expiry). If not provided, then no expiry notifications can be sent.
80baseType1NMust be one of: 'CreditCard', 'ACH'
81last4Digits1NThe last 4 digits of the credit card or bank account. Use "XXXX" if unknown. This value will be displayed on the payment related screens
82friendlyName1NThe customer assigned name for this tokenized payment method (eg: "Capital One Visa"). The BluSynergy system usually assigns something like "Visa - 9876" for newly entered cards, the data import file should use something like "my creditcard" if unknown.
72tokenA2NIf using tokenization with a payment gateway, then the value of the 1st token. For the Authorize.Net payment processor, this corresponds to the customerProfileID
73tokenB2NIf using tokenization with a payment gateway, then the value of the 2nd token (if any). For the Authorize.Net payment processor, this corresponds to the customerPaymentProfileID
74tokenC2NIf using tokenization with a payment gateway, then the value of the 3rd token if any
75expiry2NThe expiry date for the underlying instrument (credit card expiry date or ACH authorization expiry). If not provided, then no expiry notifications can be sent.
80baseType2NMust be one of: 'CreditCard', 'ACH'
81last4Digits2NThe last 4 digits of the credit card or bank account. Use "XXXX" if unknown. This value will be displayed on the payment related screens
82friendlyName2NThe customer assigned name for this tokenized payment method (eg: "Capital One Visa"). The BluSynergy system usually assigns something like "Visa - 9876" for newly entered cards, the data import file should use something like "my creditcard" if unknown.
72tokenA3NIf using tokenization with a payment gateway, then the value of the 1st token. For the Authorize.Net payment processor, this corresponds to the customerProfileID
73tokenB3NIf using tokenization with a payment gateway, then the value of the 2nd token (if any). For the Authorize.Net payment processor, this corresponds to the customerPaymentProfileID
74tokenC3NIf using tokenization with a payment gateway, then the value of the 3rd token if any
75expiry3NThe expiry date for the underlying instrument (credit card expiry date or ACH authorization expiry). If not provided, then no expiry notifications can be sent.
80baseType3NMust be one of: 'CreditCard', 'ACH'
81last4Digits3NThe last 4 digits of the credit card or bank account. Use "XXXX" if unknown. This value will be displayed on the payment related screens
82friendlyName3NThe customer assigned name for this tokenized payment method (eg: "Capital One Visa"). The BluSynergy system usually assigns something like "Visa - 9876" for newly entered cards, the data import file should use something like "my creditcard" if unknown.




4. Save the template on your desktop and upload it into the system.


Notes when working with Excel:


  1. If you wish to left-pad certain numbers with leading zero's (the customer a/c number, or order number for example), then use this formula: =TEXT(A1,"00000000") This creates a eight digit number, then make sure you select the whole column and save the format as "text", else Excel will trim the zeros of when you save it as a CSV file.

  2. See this post for how to prevent dates and numbers from getting mis-formatted when opening CSV files in Excel
  3. Use the Text Import Wizard to specify Text format for certain fields within your import file. Start by opening Excel to a blank workbook and from the Data tab's Get External Data group, click the From Text button.



Figure 11.0 - Get External Data, Text


After clicking From Text, select your text file from the Import Text File dialog and click Open. This launches the Text Import Wizard. There are three pages to complete. The first page is essentially to determine whether the file is to be considered Delimited or Fixed Width. The second page depends upon the first. You will be asked what characters to consider as delimiters or asked to choose the widths of a fixed width file.


Figure 11.1 - Text Import Wizard: 3 of 3


The third page is what is going to help you determine the format of each field. Select your account id field from theData preview at the bottom then select Text from the Column data format list in the upper left. Note that the header of the field in the bottom Data preview has changed to Text. Repeat for any other fields and click Finish in the lower right to complete your operation.


Comments