The following article provides advice on the specification of a compatible CSV file for it to be successfully integrated with Tradebox Finance Manager. The article covers:
- Rules on the CSV file
- Data fields accepted
- Data fields in depth
- Notes on publishing the file
An example CSV file is attached to this article.
CSV File Rules
- Data in the file can be separated by commas, tabs or semi-colons.
- Multiple Item purchases have the same Order ID and need to be grouped together in the file.
- Each individual item in an order appears on a separate line in the file.
- The total shipping cost for a multi-line order should appear within all of the lines.
- Column headers are recommended.
- 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.
- Tradebox can only accept files in a single currency. If a website deals in multiple currencies then individual export files need to be created for each set of sales in the different currencies. These will need to be set up as separate sales channel connections in Tradebox.
- Financial information should be inclusive of VAT and reflect the gross item cost for each unit.
- Where a mixture of VAT and non VAT goods are sold a SKU needs to be present to allow Tradebox to discriminate between the items in Sage.
- Ideally, the website SKU should match the Sage Product Code
- Date formats accepted are dd/mm/yyyy or mm/dd/yyyy.
- Export files with multiple orders statuses (completed, pending, failed etc) can be accommodated and filters applied against them.
- Discounts need to be populated in a separate column as a positive amount. Discounts should not be populated as a negative amount.
- Discounts in the file can be either line discounts or order discounts but NOT a mixture of both.
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.
The following list illustrates the data that makes up the ideal web export file. All fields in red are mandatory. Other fields are not mandatory but contain data which will be useful to the user. Each field is explained in greater depth further on in the document.
- Order Date
- Order ID
- Product Name
- Product Price
- Carriage Amount
- Payment Method
- Shipping Method
- Billing Name
- Billing Company Name
- Billing Address 1
- Billing Address 2
- Billing Address 3
- Billing Address 4
- Billing Address 5
- Billing Country
- Billing Country Code
- Customer Email
- Customer Telephone
- Shipping Name
- Shipping Company Name
- Shipping Address 1
- Shipping Address 2
- Shipping Address 3
- Shipping Address 4
- Shipping Address 5
- Shipping Country
- Shipping Country Code
- Order Status
Data fields in depth
Order Date: Order dates need to be in either a UK (dd/mm/yyyy) format (22/07/2011) or in a US (mm/dd/yyyy) format (07/22/2011)
Order ID: Tradebox records the order ID in its database and uses this information to prevent the order being imported as a duplicate.
Order ID's 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.
SKU: The website SKU (Stock Keeping Unit) uniquely identifies the product being sold. Where stock control in Sage is required then the web SKU will be mapped to the corresponding Sage Product Code. Ideally the web SKU and Sage Product Code will match, exactly. However, if they don’t then a relationship between the non-matching IDs can still be manually created in Tradebox. This only needs to be done once as Tradebox will remember the relationship. This also allows many online SKUs to be mapped to a single Sage Product Code.
Product Name: This field is the title of the product sold. The Product Name can also be manually mapped to the Sage stock code if no SKU is available. To achieve this, the Product Name need to be unique to the product.
Product Price: The product price is the UNIT price of the individual product, regardless of quantity sold. It is NOT the total price of the products SOLD. The Product Price should be to 2 decimal places and cannot be preceded by any currency signs (£,$,€). Tradebox can only accept files in a single currency. Values must always be positive. Ideally product prices should be gross or inclusive of VAT. Tradebox can be configured to handle net prices exclusive of VAT. However, net prices can often be subject to rounding errors causing small differences. Tradebox strongly recommends values in the file should be inclusive of VAT.
Quantity: Quantity of products purchased on each line of an order. Value must be a whole number greater than zero. When creating the entries in Sage, Tradebox will multiply the quantity and product price together, in keeping with the requirements of Sage Accounts.
Carriage Amount: The Carriage Amount should be to 2 decimal places and cannot be preceded by any currency signs (£,$,€). Tradebox can only accept files in a single currency. Values must always be positive. Product prices can either be inclusive or exclusive of VAT but MUST be uniform throughout the file. Tradebox recommends values inclusive of VAT. For multi-line orders, the carriage amount may be the total carriage amount for that order, or a per-item carriage amount. The behaviour is controlled by a setting in Tradebox, the default behaviour is to treat the carriage as the total carriage amount for the order. Where this method is used, the carriage amount must be duplicated on every line of the order.
Discount: The Discount field allows a discount to be applied against the order. The discount value can be either a Line 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. Please refer to the CSV template attached.
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 Sage. 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
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.
Billing Company Name: If a Billing Company Name exists on an order then this can be used as the Sage customer name, in addition to the customer billing Name.
Billing Address 1: First line of the billing address, such as Suite 3 Dockmasters House
Billing Address 2: Second line of the billing address, if available, such as 40 Fish Quay
Billing Address 3: Third line of the billing address, usually the town/city, such as North Shields
Billing Address 4: Fourth Line of the billing address, usually the county, such as Tyne and Wear
Billing Address 5: Fifth line of the billing address, usually the postcode, such as NE30 1JA
Billing Country: Billing Country, such as United Kingdom
Billing Country Code: 2 character country code (ISO 3166 Country Codes). Auto defaults to GB if blank.
Customer Email: Email address will be populated in the Sage customer record (if individual customers have been configured) and also can be copied to a ‘Notes’ field on the invoice.
Customer Telephone: Telephone number can be populated in the Sage customer record (if individual customers have been configured) and also can be copied to a ‘Notes’ field on the invoice.
Message: If order messages are included in the export file then these can be included on the body of the Sage invoice.
Shipping Name: If a Shipping Name exists on an order then this can be used as the delivery name of the Sage customer record and invoice.
Shipping Company Name: If a Shipping Company Name exists on an order then this can be used on the delivery address.
Shipping Address 1: First line of the shipping address, such as Suite 3 Dockmasters House
Shipping Address 2: Second line of the shipping address, if available, such as 40 Fish Quay
Shipping Address 3: Third line of the shipping address, usually the town/city, such as North Shields
Shipping Address 4: Fourth Line of the shipping address, usually the county, such as Tyne and Wear
Shipping Address 5: Fifth line of the shipping address, usually the postcode, such as NE30 1JA
Shipping Country: Shipping Country, such as United Kingdom. Were sales are made overseas VAT is calculated based upon the shipping country.
Shipping Country Code: 2 character country code (ISO 3166 Country Codes). Auto defaults to GB if blank
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.
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 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.