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).

Navigate to Customers >> Upload Multiple Customers
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

No	Field	Required	Notes
1	First Name	Y	First name of the billing contact
2	Last Name	Y	Last name of the billing contact
3	Company Name	N	Required for business accounts
4	Account Number	Y	Your customer account number. Create an arbitrary one if you do not have one. Must be unique
5	Primary Email	Y
6	Login	Y	Create a unique logon name for each customer. Must be at least 6 characters
7	Password	Y	The Password must be at least 8 characters and MUST INCLUDE AT LEAST ONE UPPER CASE LETTER, ONE LOWERCASE LETTER AND ONE NUMBER
8	Street	N	Customer address fields are recommended
9	Suite/Dept	N	Customer address fields are recommended
10	City	N	Customer address fields are recommended
11	State / Province	N	Customer address fields are recommended
12	Zip / Postal Code	N	Customer address fields are recommended
13	Country	N	Customer address fields are recommended
14	Country Code	N	Customer address fields are recommended
15	Primary Phone	N
16	Cell Phone	N
17	Outstanding Balance	Y	The 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 Balance	Y	The amount carried over from the previous invoice
19	Current Balance	Y	The amount of current charges for the current invoice
20	Last Payment Amount	Y	The amount for the last payment received for this customer
21	Last Payment Date	Y	The date of the last payment
22	Last Invoice Amount	Y	The last invoiced amount
23	Last Invoiced Date	Y	The date on the last invoice
24	Lifetime Payments	Y	The total payments made by this customer to date excluding adjustments such as credits, refunds, etc. Set to 0.00 if unknown
25	Lifetime Value	Y	The total value derived from this customer to date (including payments and credits). Set to 0.00 if unknown
26	Next Due Date	Y	The next due date for the outstanding invoice
27	Credit Card No1	N	The credit card to store with the customer's profile for auto-pay
28	Card Expiry Date1	N	The expiry date for the credit card
29	Card Type1	N	One of the following: 'Mastercard', 'Visa', 'Amex', 'Discover', 'JCB', 'Diners'
30	Cardholder Name1	N	The name on the credit card
31	Cardholder Street1	N	Billing address if different from the customer address
32	Cardholder City1	N	Billing address if different from the customer address
33	Cardholder State1	N	Billing address if different from the customer address
34	Cardholder Zip1	N	Billing address if different from the customer address
35	Cardholder Country1	N	Billing address if different from the customer address
36	Cardholder Countrycode1	N	Billing address if different from the customer address
37	Credit Card No2	N	The credit card to store with the customer's profile for auto-pay
38	Card Expiry Date2	N	The expiry date for the credit card
39	Card Type2	N	One of the following: 'Mastercard', 'Visa', 'Amex', 'Discover', 'JCB', 'Diners'
40	Cardholder Name2	N	The name on the credit card
41	Cardholder Street2	N	Billing address if different from the customer address
42	Cardholder City2	N	Billing address if different from the customer address
43	Cardholder State2	N	Billing address if different from the customer address
44	Cardholder Zip2	N	Billing address if different from the customer address
45	Cardholder Country2	N	Billing address if different from the customer address
46	Cardholder Countrycode2	N	Billing address if different from the customer address
47	Credit Card No3	N	The credit card to store with the customer's profile for auto-pay
48	Card Expiry Date3	N	The expiry date for the credit card
49	Card Type3	N	One of the following: 'Mastercard', 'Visa', 'Amex', 'Discover', 'JCB', 'Diners'
50	Cardholder Name3	N	The name on the credit card
51	Cardholder Street3	N	Billing address if different from the customer address
52	Cardholder City3	N	Billing address if different from the customer address
53	Cardholder State3	N	Billing address if different from the customer address
54	Cardholder Zip3	N	Billing address if different from the customer address
55	Cardholder Country3	N	Billing address if different from the customer address
56	Cardholder Countrycode3	N	Billing address if different from the customer address
57	Aba Routing Number1	N	The 9 digit bank routing number for ACH
58	Account Number1	N	The bank account number for ACH
59	Institution Name1	N	The name of the bank or financial institution
60	Account Type1	N	One of the following: 'Checking', 'Savings', 'BusinessChecking'
61	Account Name1	N	The name on the bank account
62	Aba Routing Number2	N	The 9 digit bank routing number for ACH
63	Account Number2	N	The bank account number for ACH
64	Institution Name2	N	The name of the bank or financial institution
65	Account Type2	N	One of the following: 'Checking', 'Savings', 'BusinessChecking'
66	Account Name2	N	The name on the bank account
67	Aba Routing Number3	N	The 9 digit bank routing number for ACH
68	Account Number3	N	The bank account number for ACH
69	Institution Name3	N	The name of the bank or financial institution
70	Account Type3	N	One of the following: 'Checking', 'Savings', 'BusinessChecking'
71	Account Name3	N	The name on the bank account
72	tokenA1	N	If using tokenization with a payment gateway, then the value of the 1st token. For the Authorize.Net payment processor, this corresponds to the customerProfileID
73	tokenB1	N	If 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
74	tokenC1	N	If using tokenization with a payment gateway, then the value of the 3rd token if any
75	expiry1	N	The expiry date for the underlying instrument (credit card expiry date or ACH authorization expiry). If not provided, then no expiry notifications can be sent.
80	baseType1	N	Must be one of: 'CreditCard', 'ACH'
81	last4Digits1	N	The last 4 digits of the credit card or bank account. Use "XXXX" if unknown. This value will be displayed on the payment related screens
82	friendlyName1	N	The 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.
72	tokenA2	N	If using tokenization with a payment gateway, then the value of the 1st token. For the Authorize.Net payment processor, this corresponds to the customerProfileID
73	tokenB2	N	If 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
74	tokenC2	N	If using tokenization with a payment gateway, then the value of the 3rd token if any
75	expiry2	N	The expiry date for the underlying instrument (credit card expiry date or ACH authorization expiry). If not provided, then no expiry notifications can be sent.
80	baseType2	N	Must be one of: 'CreditCard', 'ACH'
81	last4Digits2	N	The last 4 digits of the credit card or bank account. Use "XXXX" if unknown. This value will be displayed on the payment related screens
82	friendlyName2	N	The 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.
72	tokenA3	N	If using tokenization with a payment gateway, then the value of the 1st token. For the Authorize.Net payment processor, this corresponds to the customerProfileID
73	tokenB3	N	If 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
74	tokenC3	N	If using tokenization with a payment gateway, then the value of the 3rd token if any
75	expiry3	N	The expiry date for the underlying instrument (credit card expiry date or ACH authorization expiry). If not provided, then no expiry notifications can be sent.
80	baseType3	N	Must be one of: 'CreditCard', 'ACH'
81	last4Digits3	N	The last 4 digits of the credit card or bank account. Use "XXXX" if unknown. This value will be displayed on the payment related screens
82	friendlyName3	N	The 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:

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.
See this post for how to prevent dates and numbers from getting mis-formatted when opening CSV files in Excel
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