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.

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:

true: true, TRUE, yes, YES, on, ON, y, Y, 1
false false, FALSE, no, NO, off, OFF, n, N, 0

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:

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.

Import as Completed Orders

Use this flag if your are importing historical orders. This will keep them processing payments.

Auto Orders

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.

Old Processing Reports

UltraCart will delete any processing reports in the /import/out/ folder older than sixty days.

Encryption

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