Home‎ > ‎Developer's Guide‎ > ‎

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:

  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
http://answers.microsoft.com/en-us/office/forum/office_2007-excel/disabling-scientific-notation/943b8103-8c50-451d-8037-c697422e2307




Ĉ
Unknown user,
Nov 9, 2011, 10:18 AM
Comments