The following article provides advice on the specification of a compatible CSV file to be used with Tradebox One. The article covers:
An example CSV file populated with dummy data is available here.
This specification is also compatible with Tradebox Finance Manager and should be used when setting up an order CSV file for a custom channel in either program, however files intended for use in Tradebox Finance Manager must contain unit prices rather than line prices. The instructions for working within the software in this article apply to Tradebox One.
CSV Order File Specification
Tradebox reads standard .CSV files for order import. Setting up a CSV channel requires an example file in order to set up the field mapping; once the format for the export file has been established this format needs to be retained for all future files otherwise Tradebox will need to be remapped.
To remap an existing channel if your file has changed, go to channels and double-click the channel to open it. Click into the Import File tab, then the Field Mapping tab, then click Action > File Import - Edit Mapping. You'll be prompted to browse to the new version of your file, once you've done so the Column In File dropdown against each Tradebox field will be editable.
General
- Data can be separated by commas, tabs or semi-colons.
- If the file will contain the delimiter as part of a field (such commas in addresses or product descriptions) any affected fields must be enclosed in double quotation marks
e.g. “10, Main Street” is fine but 10, Main Street is not. - Where data is not provided or not applicable for some orders the separator should still exist to keep the field in alignment.
- If the file will contain the delimiter as part of a field (such commas in addresses or product descriptions) any affected fields must be enclosed in double quotation marks
- Column headers are encouraged as they’ll help users investigate any discrepancies, but are not required.
- Files should be encoded as plain text; Tradebox will not parse html or other encoding.
- Special characters can cause issues with orders being rejected by Tradebox or can cause unexpected behaviour in your accounts package. Where possible, files should be restricted to A-Z 0-9 . - _@
- Tradebox has a list of aliases for accented foreign characters and will attempt to replace e.g. è with e to maintain compatibility with accounts packages.
Mandatory fields
- Tradebox has 9 mandatory fields that it reads from each CSV file to build a valid order: Order Date; Channel Order Number; Product Name; Product Price (Gross); Quantity; Payment Method; Billing Name; Shipping Name; either Shipping Country Code or Shipping Country.
- Of these, Order Date and Channel Order Number must be provided in the CSV file.
- If any of the other mandatory fields are not provided by the file, orders can still be imported if the user assigns a fixed value to those fields during channel setup.
- Any fields assigned a fixed value will use that for every order imported through the sales channel in question.
- A mandatory field is either fixed or read from the file; it is not possible to have Tradebox read a mandatory field that is only ‘sometimes-populated’.
- If a file contains a blank value on any line for a mandatory field, the import of the entire file will fail. For sake of clarity a value must be a visible character.
- Using fixed values for mandatory fields will limit some functionality. For example. tracking customer information will not be possible by assigning a fixed value to Billing Name, or applying international VAT rules will not be possible by assigning a fixed value to Shipping Country.
- In general we expect all CSV files to provide at minimum the 8 mandatory fields, and setting any of these to a fixed value to only be used as a fall back in rare cases.
Order information
- Each individual item in an order must appear on a separate line in the file.
- Orders containing multiple different items must have the same Channel Order Number (a unique order ID per channel, normally the order number used by the marketplace) and need to be on consecutive lines in the file.
- Product price can be unit price or item total. Choosing the relevant setting within the sales channel will configure Tradebox to multiply unit price by quantity to calculate the item total, or to divide item total by quantity to calculate the unit price.
- Discount information should appear within the item lines and not as a separate line. There should only be as many lines for each order as there are unique items.
- Discounts can be a value for the entire order, or item-specific. Item discounts can be presented as a unit discount (pre-quantity) or as a line total discount (post-quantity).
- Export files with order statuses (e.g. completed, pending, cancelled) are accommodated but not required. Based on status, Tradebox can accept/reject orders, or apply a rule set defined by the user.
Financial information
- Financial information such as sales prices, shipping prices, discounts etc. should be rounded to 2 decimal places.
- All financial information, including discounts, should be displayed as a positive amount.
- Financial information should not be preceded by currency symbols (e.g. £, $).
- Each sales channel in Tradebox handles orders in a single currency. Multiple currencies in a single file are fine, but multi-currency files must contain a currency field in 3-letter ISO 4217 format (e.g. GBP, USD, EUR).
- Where a financial value is zero a 0 figure must be populated in the field. It must not be left blank.
- If a financial value is always zero (e.g. you always offer free shipping, or you never offer a discount), it's fine to have the fields unpopulated, the user would just not map that field in Tradebox's setup.
- Financial information should reflect VAT by either listing gross figures, or having separate fields for net amount per item and VAT amount per item. Net amounts alone are not sufficient.
- Tradebox and the accounts packages we integrate with will calculate net and VAT per unit, prior to multiplying by the quantity on the order. If your online prices are such that you cannot calculate a net and a gross price per unit to a whole pence without rounding, you may encounter rounding discrepancies in your orders of up to 1p per unit.
Product information
- Specific product information such as a SKU or description applies to most users, but is not mandatory if it doesn’t apply to your business
- The exception to this is where a mixture of VAT and non-VAT goods are sold. In this instance a SKU needs to be present to allow Tradebox to calculate the appropriate VAT on each item and apply the correct tax code. These can be generic SKU's included solely for this purpose, but must be a consistent indicator of whether the item is VATable or zero-rated.
Shipping cost
- Shipping/carriage for multi-line orders can be populated as individual cost per item, or as a total amount. For more information see our guide to shipping costs in CSV.
- Where a total amount is used, this must be on the first line of each order. Subsequent lines can repeat the shipping amount or that field may be left blank.
Data Fields in Depth
The following list specifies the data that make up the ideal CSV import file for Tradebox One. All fields in red are mandatory. Other fields are not mandatory but contain data which will be useful to the user.
- Order Date: The date the order was placed. Dates can be formatted as DD/MM/YYYY, MM/DD/YYYY, DD/MMM/YYYY, MMM/DD/YYYY, DD-MM-YYYY, MM-DD-YYYY, DD-MMM-YYYY, MMM-DD-YYYY. Timestamps and time zone codes will be ignored.
- Channel Order Number: Tradebox records the order ID in its database and uses this information to prevent the order being imported as a duplicate. Order IDs need to be unique to each order, per marketplace. Where there are multiple item lines in an order, each line needs to appear on a separate line and have the same Order ID. Multi-item orders should be grouped together in the file.
- Item Number: A unique reference number that identifies the specific item line within the order. It references the combination of product, quantity and item discount (if applicable). This is not the order number or the SKU. Tradebox has a robust system for automatically generating item numbers that work best with our system, so normally we'd expect this column not to be mapped.
- SKU: The SKU (Stock Keeping Unit) identifies the product being sold. This is an essential piece of information if stock control is required.
- Product Name: This field is the title of the product sold. Additional fields, like size and colour variations, can be concatenated by Tradebox against the product name, if required, e.g. Nike Trainers-Red-Size 9 could pull from 3 separate fields within your file. Where users choose no stock control, the product name will be the only descriptor attached to each service line.
- Product Price (Gross): The product price represents the price of each item on the order. The price can be either the unit price or the item total per line. (A true line total that is inclusive of carriage or other charges is not sufficient). The Product Price should be to 2 decimal places and cannot be preceded by any currency signs (£,$,€). Values must always be positive.
Ideally, the values in the Product Price column should be inclusive of VAT. Where the values in the Product Price column exclude VAT the equivalent VAT amount must also be present in a column in the file. This allows the Tradebox software to add the net prices and VAT prices together to arrive at the gross price. Where a product price is zero, the value must appear as 0; the field should not be left blank.
Note - this is a change from Tradebox Finance Manager, where a net price alone was sufficient. To avoid rounding issues which often caused problems for users, this is no longer supported. - Quantity: Quantity of products purchased on each line of an order. Value must be a whole number greater than zero and cannot be a decimal.
- Carriage Amount (Gross): The Carriage Amount should be to 2 decimal places and cannot be preceded by any currency signs (£,$,€). Values must always be positive. Carriage can appear as either individual carriage amounts against each item line or as the total carriage amount repeated across every line in the order. Ideally, the values in the Carriage Amount column should be inclusive of VAT. Where the values in the Carriage Amount column exclude VAT the equivalent VAT amount must also be present in an additional column in the file. Where the carriage is zero, the value must appear as 0; the field should not be left blank. If all your shipping is free of charge, it's fine to leave this field unmapped rather than generating an always-0 field.
- Other Amount (Gross): This field is used to incorporate other financial costs applied to the order that fall outside of the product price or the carriage. This is typically used for additional services like gift wrapping or shipping insurance. The Other Amount should be to 2 decimal places and cannot be preceded by any currency signs (£,$,€). Values must always be positive. The Other Amount can appear as either individual amounts against each item line or as the total Other Amount repeated across every line in the order. Ideally, the values in the Other Amount column should be inclusive of VAT. Where the values in the Other Amount column exclude VAT the equivalent VAT amount must also be present in an additional column in the file. This allows the Tradebox software to add the net prices and VAT prices together to arrive at the gross price, avoiding rounding issues. Where the Other Amount is zero, the value must appear as 0; the field should not be left blank. If you never need to reflect an other amount, it's fine to leave this field unmapped rather than generating an always-0 field.
- Discount (Gross): The Discount field allows a discount to be applied against the order. The discount value can be either a Line Discount, a Unit Discount or an Order Discount. If discounts are used then the user needs to set the appropriate discount method in their channel settings. The Discount Amount should be to 2 decimal places and cannot be preceded by any currency signs (£,$,€). The discount value must be positive. Where the Discount is zero, the value must appear as 0; the field should not be left blank.
- Payment Method: Identifies the payment method used to purchase each online order e.g. PayPal, SagePay, Visa etc. This field can be utilised by Tradebox and reflected in an accounts package, if integrated. Populating this field in the CSV can help the end user significantly reduce time in bank reconciliation.
- Shipping Method: Identifies the shipping method chosen for each order. Useful for packing lists, can also be used for reporting.
- Sage Customer Reference: The Customer Record reference (displayed as A/C Ref on the customer record in Sage). Up to 8 characters. Tradebox's normal logic is to either run all sales through a generic customer or try to find an existing customer based on billing name and email address. If this field is populated and mapped, it will override Tradebox's normal behaviour and use the account specified for Sage posting.
- Billing Name: Billing Name of the customer who places the online order. Generally taken from a single field. Tradebox can concatenate forename and surname fields together if provided as separate fields. Must be a visible character, i.e. not a space.
- Billing Company Name: Useful field for B2B Orders.
- Billing Address 1: First line of the billing address e.g. Office 18a-20a .
- Billing Address 2: Second line of the billing address e.g. Union Quay.
- Billing Address 3: Third line of the billing address, usually the town/city e.g. North Shields.
- Billing Address 4: Fourth Line of the billing address, usually the county e.g. Tyne and Wear.
- Billing Postcode: Postcode e.g. NE30 1HJ.
- Billing Country: Billing Country name e.g. United Kingdom.
- Billing Country Code: 2 character country code (ISO 3166 Country Codes) representing the billing country. Auto defaults to GB if blank. Where populated and mapped, will take priority over the Billing Country name.
- Customer Email: The buyer's email address will be used to identify existing customer records where a repeat buyer has already had an order handled via Tradebox or already has a record in your accounts package. The email address will be recorded against the customer record and the order in Tradebox, and can be passed to records in an accounts package if integrated. If you choose individual customer accounts rather than recording sales through a generic account, an email address needs to be present for this mechanism to work.
- Customer Telephone: Telephone number gets recorded against the customer record and order in Tradebox and can be passed to records in an accounts package if integrated.
- Message: If order messages are included in the export file then these are recorded against the order in Tradebox. Messages can be passed to records in an accounts package if integrated. It's normal for these to be added to printable invoices or shipping documentation.
- Shipping Name: Shipping Name of the recipient of the order. Generally taken from a single field. Tradebox can concatenate forename and surname fields together if provided as separate fields. Must be a visible character, i.e. not a space.
- Shipping Company Name: Useful for B2B orders.
- Shipping Address 1: First line of the billing address e.g. Office 18a-20a .
- Shipping Address 2: Second line of the billing address e.g. Union Quay.
- Shipping Address 3: Third line of the billing address, usually the town/city e.g. North Shields.
- Shipping Address 4: Fourth Line of the billing address, usually the county e.g. Tyne and Wear.
- Shipping Postcode: Postcode e.g. NE30 1HJ.
- Shipping Country: Shipping Country name e.g. United Kingdom. Where sales are made overseas VAT is calculated based upon the shipping country.
- Shipping Country Code: 2 character country code (ISO 3166 Country Codes) representing the shipping country. Auto defaults to GB if blank. One shipping country identifier (either code or name) is mandatory; Shipping Country Code is preferred. If this is not present in your file, mapping Shipping Country is valid in its place. Where both are present, the country code takes precedence.
- Telephone 2: An additional telephone number, normally tied to the shipping address. This will be stored against the order.
- Sales Source: This field identifies the platform the order originated upon and can be used in Tradebox as a filter, if required. This is popular where the file is generated from an order management system that's already collating orders from various marketplaces like ebay and Amazon as well as a webstore, and orders from all of these will appear in the same file.
- Customer VAT Number: If populated this can be recorded against the customer and order record in Tradebox. If a VAT number is present and the shipping country is in the EU, Tradebox will automatically treat the order as an EU B2B sale.
- Currency: This field should be populated with a 3 character currency code (ISO 4217 Currency Codes). Each channel in Tradebox is created in a specified currency and can only import sales of that currency; essentially the currency field lets each single-currency sales channel know whether to accept/reject orders. Where this field is not populated, Tradebox defaults to accepting orders and assuming they're in the currency of the channel.
- Order Status: Enables user to apply import filters based upon the status of the order ensuring that only orders of the chosen status(es) are imported. Marketplace statuses are mapped against internal Tradebox statuses which trigger rules within the software.
- Order Total (Gross): Many websites generate the total of the order or the actual amount paid. Tradebox writes this amount to a 'Reported Total' field in the Tradebox order record and compares it against the calculated total of the order; if these don't match then the Order Total from the file is used and an adjustment line is added to the order in Tradebox. The Order Total Gross should be to 2 decimal places and cannot be preceded by any currency signs (£,$,€). Values must always be positive. Where the value is zero it should appear as 0.
- Payment Date: This reflects the date the order is paid for and gets recorded against the order record in Tradebox. Needs to be consistent with the date format of field 1 - Order Date.
- Shipping Date: This reflects the date the order is shipped to the recipient and gets recorded against the order record in Tradebox. Needs to be consistent with the date format of field 1 - Order Date.
- Courier: Reflects the courier delivering the order and gets recorded against the order record in Tradebox.
- Shipping Status: Reflects the shipping status of the order and gets recorded against the order record in Tradebox. Generally, Order Status is used as the functional status to trigger Tradebox's behaviour and Shipping Status would be used purely for reporting.
- Tracking Number: Reflects the tracking number of the despatched order and gets recorded against the order record in Tradebox.
- Payment Status: Reflects the payment status of the order and gets recorded against the order record in Tradebox. Generally, Order Status is used as the functional status to trigger Tradebox's behaviour and Payment Status would be used purely for reporting
- Voucher: Reflects any discount codes or vouchers used by the buyer and gets recorded against the order record in Tradebox.
- Payment Reference: The payment reference gets recorded against the order record in Tradebox and can be passed to an accounts package, if integrated. Including this field will be very beneficial for users performing bank reconciliation.
- Notes 1: As well as field 25 - message - Tradebox allows 3 additional fields of notes up to 60 characters. These will be stored against the order and can be passed to your accounts package.
- Notes 2: As above.
- Notes 3: As above.
Publishing the CSV File
Once the CSV file template has been created it needs to be saved into local folders on the user’s PC or network to allow Tradebox to import the file. This can be achieved by either:
- Manually downloading the CSV file and saving it into the local folders
or - Creating a web script to routinely publish the CSV file to a location on the user’s FTP server where Tradebox can automatically collect it.
The main benefit of publishing and importing the CSV file from an FTP location is that Tradebox can then routinely download and import the sales without the need for the user to manually generate the file. Tradebox maintains a list of all sales it has previously imported and will only import new sales that it has not previously encountered, regardless of whether it is presented with the same sales more than once. The following notes are worth considering:
- A web script is designed to create and export the CSV file at regular intervals, as required by the customer. This could be once per day, every 4 hours or every hour. In effect, as often as the end user requires it.
- The script could be designed to create a file with the same name every time it runs (e.g. Tradebox.csv). This would mean that the existing file on the FTP would be overwritten by the new file every time it is published. If this is the chosen route then some thought needs to be given to the amount of day’s worth of the sales the file contains to prevent the user missing sales. Files containing 14 days or 30 days of historic orders are most common.
- An alternative is to create the file with a new name every time it is created (e.g.Tradebox[date][time].csv). This ensures that orders can never be overwritten. A setting in Tradebox allows read files to be removed from the FTP so this location does not become too full.
Comments
0 comments
Article is closed for comments.