Follow

Tradebox One Template CSV File

The following article provides advice on the specification of a compatible CSV file to be used with Tradebox One. The article covers:

  • Data fields in depth
  • Rules on the CSV file
  • Notes on publishing the file

An example CSV file containing sample data is attached to the bottom of this article.

 

Data fields in depth

The following list specifies the data that makes 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. 

  1. Order DateOrder dates need to be in either a UK (dd/mm/yyyy) format (10/05/2017) or in a US (mm/dd/yyyy) format (05/10/2017).
  2. Order ID: 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. 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.
  3. SKU: The SKU (Stock Keeping Unit) uniquely identifies the product being sold. This is an essential piece of information if stock control is required.
  4. 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.
  5. Product Price: The product price represents the price of each item on the order. The price can be either the unit price or the total line price. 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 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 product price is zero, the value must appear as 0.00. The field should not be left blank.
  6. 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.
  7. Carriage Amount: 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. 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 carriage is zero, the value must appear as 0.00. The field should not be left blank.
  8. Other Amount: 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 engraving. 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.00. The field should not be left blank.
  9. Discount: 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 Tradebox needs to be configured to use either the Line or the Order discount method. The Discount Amount should be to 2 decimal places and cannot be preceded by any currency signs (£,$,€). Values must always be positive. Where the Discount is zero, the value must appear as 0.00. The field should not be left blank.
  10. Payment Method: Identifies the payment method used to purchase each online order, i.e. 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.
  11. Shipping Method: Identifies the shipping method chosen for each order. Useful for packing lists.
  12. Billing Name: Billing Name of the customer who places the online order. Generally taken from a single field, i.e. John Smith. Tradebox can concatenate forename and surname fields together if provided as separate fields.
  13. Billing Company Name: Useful field for B2B Orders.
  14. Billing Address 1: First line of the billing address, such as Suite 3 Dockmasters House.
  15. Billing Address 2: Second line of the billing address, if available, such as 40 Fish Quay.
  16. Billing Address 3: Third line of the billing address, usually the town/city, such as North Shields.
  17. Billing Address 4: Fourth Line of the billing address, usually the county, such as Tyne and Wear.
  18. Billing Postcode: Postcode, such as NE30 1JA.
  19. Billing Country: Billing Country, such as United Kingdom.
  20. Billing Country Code: 2 character country code (ISO 3166 Country Codes). Auto defaults to GB if blank.
  21. Customer Email: Email address gets recorded against the customer record and order in Tradebox and can be passed to records in an accounts package if integrated.
  22. 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.
  23. Message: If order messages are included in the export file then these are recorded against the order in Tradebox and displayed as an icon in the order list. Messages can be passed to records in an accounts package if integrated.
  24. Shipping Name: Shipping Name of the recipient of the order. Generally taken from a single field, i.e. John Smith. Tradebox can concatenate forename and surname fields together if provided as separate fields.
  25. Shipping Company Name: Useful for B2B orders.
  26. Shipping Address 1: First line of the shipping address, such as Suite 3 Dockmasters House.
  27. Shipping Address 2: Second line of the shipping address, if available, such as 40 Fish Quay.
  28. Shipping Address 3: Third line of the shipping address, usually the town/city, such as North Shields.
  29. Shipping Address 4: Fourth Line of the shipping address, usually the county, such as Tyne and Wear.
  30. Shipping Postcode: Shipping postcode, such as NE30 1JA.
  31. Shipping Country: Shipping Country, such as United Kingdom. Where sales are made overseas VAT is calculated based upon the shipping country.
  32. Shipping Country Code: 2 character country code (ISO 3166 Country Codes). Auto defaults to GB if blank.
  33. Telephone 2: A 2nd telephone number, such as the recipient's number, stored against the order.
  34. 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 amongst a number of webstores that integrate with other marketplaces like eBay and Amazon.
  35. VAT Number: If populated this can be recorded against the customer and order record in Tradebox.
  36. 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. Where this field is not populated, Tradebox defaults to the currency of the channel.
  37. Order Status: Enables user to apply import filters based upon the status of the order ensuring that only orders of a given status(es) are imported. Marketplace statuses are mapped against internal Tradebox statuses which trigger rules within the software.
  38. 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. 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.00.
  39. Payment Date: This reflects the date the order is paid for and gets recorded against the order record in Tradebox. 
  40. Shipping Date: This reflects the date the order is shipped to the recipient and gets recorded against the order record in Tradebox. 
  41. Courier: Reflects the courier delivering the order and gets recorded against the order record in Tradebox.
  42. Shipping Status: Reflects the shipping status of the order and gets recorded against the order record in Tradebox.
  43. Tracking Number: Reflects the tracking number of the dispatched order and gets recorded against the order record in Tradebox.
  44. Payment Status: Reflects the payment status of the order and gets recorded against the order record in Tradebox.
  45. Voucher: Reflects any discount codes or vouchers used by the buyer and gets recorded against the order record in Tradebox.
  46. Payment Reference: The payment reference gets recorded against the order record in Tradebox and can be passed to an accounts package, if integrated. This is useful for bank reconciliation.

 

 

CSV File Rules

  • Data in the file can be separated by commas, tabs or semi-colons.
  • The CSV should have headers which greatly assists in mapping.
  • Multiple Item lines in an order must have the same Order ID and need to be grouped together in the file.
  • Where data is not provided on individual orders the separator should still exist to keep the field in alignment.
  • Financial information, such as sales prices, shipping prices, discounts etc., should be rounded to 2 decimal places.
  • Financial information should NOT be preceded by currency signs (£, $ etc.)
  • If a financial value is zero, the field should be populated with 0. It should NOT be left blank.
  • Where a mixture of VAT and non VAT goods are sold a SKU needs to be present to allow Tradebox to calculate the appropriate VAT on each item and apply the correct tax code. 
  • Date formats accepted are dd/mm/yyyy or mm/dd/yyyy.
  • Discounts need to be populated in a separate column as a positive amount. Discounts should NOT appear as a separate line and NOT be a negative amount.

Once the format for the export file and the data has been established this format needs to be retained for all future files otherwise Tradebox will need to be remapped.

 

 

 

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 where Tradebox can electronically 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.

 

  • An alternative is to create the file with a new name every time it is created (e.g.Tradebox20120804110503). This ensures that orders can never be overwritten. A setting in Tradebox allows the file to be removed from the FTP so this location does not become too full.

 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

Powered by Zendesk