Data Import

If you have existing customer and order data that needs to be inported into the BluBilling system, the following two data file import capabilities are available. Both file formats are CSV (comma separated variables) that are readily compatible with both spreadsheet applications (like Microsoft Excel) and various database systems.

Please contact BluSynergy customer support for sample files or for processing this data import.

Customer Import File Definition

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

No	Field Name	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	TRequired 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.

Order Import File Definition

Once the Customer data has been imported, the Order data may be imported. The order associates a customer with one or more "Plans" so that Invoices may be generated at periodic intervals per the Plan specification. If a customer has multiple active orders, then a line item (row) is required for each order for this customer.

Note that the optional fields for the Order must have empty values (i.e., 2 adjacent commas) and the OrderLineItems start at field number 13. The three fields for the OrderLineItems (fields 13-15) may be repeated for each OrderLineItem.

No	Field Name	Required	Notes
1	CustomerId	C	The BluSynergy assigned customer id. Either this field or the CustomerAccountNumber must be specified to identify the customer that owns this order
2	CustomerAccountNumber	C	The externally assigned customer reference number. Either this field or the CustomerId must be specified to identify the customer that owns this order
3	ContractCode	Y	The Plan code for the order (specifies the subscription plan that will be used for generating invoice charges)
4	OrderNumber	N	Auto-generated if not supplied.
5	OrderStatus	Y	Defaults to 'Active'. Can be one of: 'Draft', 'Provisioning', 'Active', 'Suspended', 'Complete', 'Superseded', 'Canceled'
6	StartDate	N	Defaults to current date if not provided
7	EndDate	N	No termination date if not provided
8	IsAutoRenew	N	Defaults to true
9	PurchaseOrderNo	N	The purchase
10	OrderType	N	Either 'Subscription' or 'Installment'. Defaults to 'Subscription' if not provided
11	PayoffAmount	C	Required for installment orders (i.e., if OrderType is Installment)
12	InstallmentsRemaining	C	Required for installment orders (i.e., if OrderType is Installment)

*The following fields define the order line items associated with the order and may be repeated for each order*
13	Quantity	Y	The quantity must be specified
14	PriceCode	Y	The PriceCode corresponding to the Charge for the Plan. This PriceCode must obviously be one of the charges under the Plan identified by the 'ContractCode' field
15	AmountOrPercent	C	If the PricingType for this PriceCode requires an amount or percent to be entered at the order creation time, then provide a value

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

http://answers.microsoft.com/en-us/office/forum/office_2007-excel/disabling-scientific-notation/943b8103-8c50-451d-8037-c697422e2307

BluBillingDataImportFieldSpecification.xls

(41k)

Unknown user,

Nov 9, 2011, 10:18 AM

v.3

Comments