...
Channel Partner API - Spreadsheet Import
The spreadsheet import interface for the generic channel partner is intended to allow importing of complete orders from a spreadsheet. There are two methods of processing the spreadsheet:
- Uploading it through the web interface
- FTPing the file to UltraCart's Virtual FTP server
Either way you want to send the file to UltraCart, the format of the file is the same. In this tutorial we will cover building the file first and then uploading it.
Table of Contents | ||
---|---|---|
|
Building the Spreadsheet
UltraCart will support three different formats for the spreadsheet:
- .csv (comma separated values)
- .xls (Microsoft Excel 1997-2002 format)
- .xlsx (Microsoft Excel 2007+ format)
The first row of the spreadsheet must contain headers. The headers need to come from the table below.
Acceptable values for boolean parameters:
Info |
---|
|
Header Name | Alternate Header Name | Format | Description | Required |
---|---|---|---|---|
order.channelPartnerOrderId | String | A unique order ID from the external system. | Y | |
order.paymentMethod | String | The method of payment. Credit Card or Purchase Order | Y | |
order.noRealtimePaymentProcessing | Boolean | Leaves the order in Accounts Receivable instead of processing the card in real-time. |
| |
order.skipPaymentProcessing | Boolean | Skip over the payment processing and move the order on to shipping. |
| |
order.autoApprovePurchaseOrder | Boolean | Automatically approve the purchase order. |
| |
order.storeIfPaymentDeclines | Boolean | Store the order in Accounts Receivable if the credit card declines | Recommend - Y | |
order.creditCardAuthorizationReferenceNumber | String | If you authorized the order outside of UltraCart, this is the transaction identifier that UltraCart will use to capture the order. |
| |
order.creditCardAuthorizationAmount | Number | If you authorized the order outside of UltraCart, this is the amount of the authorization. |
| |
order.creditCardAuthorizationDts | Timestamp | If you authorized the order outside of UltraCart, this is the timestamp of the authorization in the format MM/DD/YYYY HH:MM:SS |
| |
order.creditCardType | String | Visa, MasterCard, AMEX, or Discover | Y - CC Orders | |
order.creditCardNumber | String | 15 or 16 digit credit card number (spaces or dashes OK) | Y - CC Orders | |
order.creditCardToken | String | Token of the credit card (Stripe.com or other tokenizing gateway supported by UltraCart). |
| |
order.creditCardExpirationMonth | Number | Month 1 through 12 (January = 1, December = 12) | Y - CC Orders | |
order.creditCardExpirationYear | Number | Four Digit Year | Y - CC Orders | |
order.creditCardVerificationNumber | Number |
|
| |
order.rotatingTransactionGatewayCode | String | The rotating transaction gateway code to use for this order. | ||
order.purchaseOrderNumber | String | The purchase order number. | Y- Purchase Order | |
order.billToFirstName | String |
| Y | |
order.billToLastName | String |
| Y | |
order.billToTitle | String |
|
| |
order.billToCompany | String |
|
| |
order.billToAddress1 | String |
| Y | |
order.billToAddress2 | String |
|
| |
order.billToCity | String |
| Y | |
order.billToState | String |
| Y | |
order.billToPostalCode | String |
| Y | |
order.billToCountry | String | Use the full spelling that UltraCart uses or provide the ISO-3166 two letter country code. | Y | |
order.billToDayPhone | String |
|
| |
order.billToEveningPhone | String |
|
| |
order.email | String |
|
| |
order.ccEmail | String |
|
| |
order.associatedWithCustomerProfileIfPresent | String | If this is yes, the order will be associated with the customer profile that has the same email (if it exists) and they will receive their discounted pricing. |
| |
order.shipToFirstName | String |
| Y - physical goods | |
order.shipToLastName | String |
| Y - physical goods | |
order.shipToTitle | String |
|
| |
order.shipToCompany | String |
|
| |
order.shipToAddress1 | String |
| Y - physical goods | |
order.shipToAddress2 | String |
|
| |
order.shipToCity | String |
| Y - physical goods | |
order.shipToState | String |
| Y - physical goods | |
order.shipToPostalCode | String |
| Y - physical goods | |
order.shipToCountry | String | Use the full spelling that UltraCart uses or provide the ISO-3166 two letter country code. | Y - physical goods | |
order.shipToPhone | String |
| Y - physical goods | |
order.shipToEveningPhone | String |
|
| |
order.shippingMethod | String | If the order requires shipping then you either need to specify the name of the method in this field, or pass order.leastCostRoute = true and let UltraCart pick the method of shipment | Maybe | |
order.arbitraryTax | Number | The tax charged by the external system |
| |
order.arbitraryTaxableSubtotal | Number | The taxable subtotal the tax was based upon by the external system |
| |
order.arbitraryTaxRate | Number | The tax rate used by the external system |
| |
order.arbitraryShippingHandlingTotal | Number | The shipping/handling cost charged by the external system |
| |
order.taxExempt | Boolean |
|
| |
order.giftMessage | String |
|
| |
order.deliveryDate | Date | If specified, use the format MM/DD/YYYY |
| |
order.shipOnDate | Date |
|
| |
order.ipAddress | String | The IP address of the remote customer (pass 127.0.0.1) if not available | Y | |
order.shipToResidential | Boolean | Will default to a business if not specified | Recommended | |
order.mailingListOptIn | Boolean | Will default to opted out if not specified | Recommended | |
order.specialInstructions | String | Special instructions from the customer about shipment |
| |
order.screenBrandingThemeCode | String | The screen branding theme code to associate the order with. | Y | |
order.advertisingSource | String |
|
| |
order.customField1 | String | Custom value such as the DNIS of the caller up to 50 characters. |
| |
order.customField2 | String | Custom value up to 50 characters. |
| |
order.customField3 | String | Custom value up to 50 characters. |
| |
order.customField4 | String | Custom value up to 50 characters. |
| |
order.customField5 | String | Custom value up to 50 characters. |
| |
order.customField6 | String | Custom value up to 50 characters. |
| |
order.customField7 | String | Custom value up to 50 characters. |
| |
order.taxCounty | String | Tax county name if the state the order is going to charges tax at the county level. |
| |
order.affiliateId | String | The affiliate ID to associate the order with. |
| |
order.gift | Boolean | True/False if the order is a gift (defaults to false) |
| |
order.giftEmail | String | Email to send the gift receipt to. |
| |
order.leastCostRoute | Boolean | Either this needs to be True or the name of a shipping method must be specified in *order.shippingMethod* | Maybe | |
order.leastCostRouteShippingMethods[#] | String | Restrict the least cost routing to these shipping methods. |
| |
order.coupons[#] | String | Coupons to apply to the order. |
| |
order.items[#].itemId | order.items.itemId | String | Item ID of the item | Y |
order.items[#].quantity | order.items.quantity | Integer | Quantity to purchase | Y |
order.items[#].arbitraryUnitCost | order.items.arbitraryUnitCost | Number | Specific price for the item. |
|
order.items[#].autoOrderSchedule | order.items.autoOrderSchedule | Boolean | Auto order schedule if the item is a customer selectable auto order. |
|
order.items[#].upsell | order.items.upsell | Boolean | Flag indicating the item was an upsell (default to false) |
|
order.items[#].options[#].name | order.items.options[#].name | String | Name of the option | Y - if the item has options |
order.items[#].options[#].value | order.items.options[#].value | String | Value of the option | Y - if the item has options |
Multiple Rows Per Order or Multiple Columns for Items
Order data by it's very nature is multi-dimensional which is hard to represent in a spreadsheet. If you are using multiple columns in your spreadsheet to represent the item data you would construct the headers like this:
order.items[0].itemId | order.items[0].quantity | order.items[1].itemId | order.items[1].quantity |
---|---|---|---|
SHIRT | 1 | PANTS | 1 |
The other way is to use multiple rows to represent the items. In this case the other non-item data is repeated on all the rows and the item data varies by row. UltraCart will roll up the rows based upon the order.channelPartnerOrderId value.
order.items.itemId | order.items.quantity |
---|---|
SHIRT | 1 |
PANTS | 1 |
Uploading via the Web Interface.
First navigate to:
Panel |
---|
Main Menu Configuration Custom Channel Partners |
Click on the "import orders" button as shown below.
On the next screen select the file to import and then click Submit Job.
Info | ||
---|---|---|
| ||
Use this flag if your are importing historical orders. This will keep them processing payments. |
Note | ||
---|---|---|
| ||
If you import orders associated with items that have an auto order schedule configured, they WILL setup auto order schedule. This applies even to historical orders if the import as completed orders checkbox is selected. |
Once you submit the job you will be shown a screen informing you that the results will be available soon under the report pickup.
FTPing the File to UltraCart's Virtual FTP Server
You can also FTP the file to the FTP server. To do this connect to:
Setting | Value |
---|---|
Server | merchantftp.ultracart.com |
Username | <merchant id>/<channel partner code> |
Password | The FTP/API password configured for this channel partner. |
Once you connect to the server you will see the following folders:
Directory | Meaning |
---|---|
/import/in/ | Deposit the spreadsheets into this folder. Once a file is stored it can not be read again, but can be deleted if it was placed there erroneously |
/import/out/ | The processing reports will appear in this folder. |
Processing of files occurs once per hour. If you configured the "Email FTP File Processing Reports To" field on the custom channel partner then an email with the processing report will be sent immediately after the processing is complete. This is a good way to keep tabs on the processing from a user perspective.
Info | ||
---|---|---|
| ||
UltraCart will delete any processing reports in the /import/out/ folder older than sixty days. |
Note | ||
---|---|---|
| ||
You must transfer the order data over FTP SSL or PGP encrypt the file if you are using FTP. To use PGP encryption make sure you use the UltraCart public PGP key uc-file-transfer-pgp-public.pgp |