Importing Orders

Using our super powerful sales import tool you can import sales from PayPal, eBay, Amazon or your own formatted Excel files. This opens up the way for integrating your e-commerce store with Brightpearl accounting!

In this guide you will learn:

1. What can be achieved with an order import
2. The import process and how to do it
3. How to import paid orders
4. How tax is applied to imported orders (Sales Tax / VAT)
5. Answers to common questions

1. What can be achieved with an order import

What can it do?

• Create sales orders with multiple lines
• Create purchase orders with multiple lines
• Add stock items to the orders
• Add free keyed line items
• Add shipping charges at net or gross amounts - add it against any item on the sale and a new line item is created
• Calculate tax and apply different rates to each line item
• Override price list amounts
• Record payments against sales orders (only at the point the SO is imported not against existing orders)
• Add up to 3 different addresses per order –customer address, invoice, delivery – useful when you’re receiving orders from a third party when the customer has different billing and delivery addresses
• Create new customer records at the same time
• Select what status the orders are given
• Select the payment method for the orders
• Include order notes and comments
• Populate custom fields - create your custom fields first

What this import can’t do:

• Update existing customer or supplier details
• Make payment against orders already in Brightpearl
• Import payments against purchase orders
• Automatically invoice the order (although the status can be applied it won’t generate the invoice and accounting)
• Apply coupons or discounts

2. The import process and how to do it

Here's the process in brief:

1. Create a Data Map in Brightpearl
2. Format the data file
3. Import the file!

Watch this video, or read the steps below:

1. Create a Data Map

A Data Map is used to enable Brightpearl to read the Excel file full of orders that you are going to upload. Essentially what you are doing is telling Brightpearl what columns are in the spreadsheet and where it should put the data contained in that column. You can define a selection of data maps to import files of different formats.

When you create a Data Map the following fields are required for a successful order import:

• Order Reference (must be in the first column)
• Customer email address
• Item name
• Item qty
• Item tax code and / or Item tax amount

Follow these steps to define a Data Map:

1. Go to Setup > Data/Import > Import Orders.
2. Click the Add a new data map button.
3. Enter a name for your data map.
4. The available fields that can be imported are listed in the right-hand column. Click and drag items to the left-hand side. You can just as easily remove them by clicking the - icon. Rearrange the fields to be the same as the column order in your file.
5. If you have data in your spreadsheet that doesn't match a field in Brightpearl, you must delete it from your XLS file.
6. Click Save.

Available Data Map Fields

Once you have defined the Data Map it will be available to use time and time again. Click Export sample to view an empty Excel file with the column headers as per your Data Map settings. Each file you want to import will need to follow this template.

2. Format the data file

The file for import can be created using the template you have exported, or by formatting an existing file. It must follow these guidelines to be imported successfully:

• It must be saved in an Excel XLS file
• The spreadsheet columns must be the same as the Data Map in name and order
• The first column must be Order Reference
• Required columns are Customer email address, Item name, Item qty, Item tax code and / or Item tax amount
• Only one order item can be added per spreadsheet row, so orders that contain several lines and products must use a new row for each product; the import will combine the order based on the identical order references

3. Import the orders!

Once you have defined the data map and import file, you are ready to import your orders. Follow these steps to import:

1. Go to Setup > Data/Import > Order Import.
2. Click the Import link next to the appropriate data map.
3. If this is the first time, test your file by leaving the Import values checkbox unticked.
4. (Sales only) Select the status you want the orders to be created with.
5. (Sales only and optional) Select the payment method used for the sales. This will be marked against the sale in Brightpearl.
6. Sales only) Select the sales channel these orders came through.
7. Click the Browse button and locate the file you want to import.
8. Click the Upload XLS file button.

Brightpearl will tell you whether it can successfully read the file. If it can you can repeat the same process but this time tick the Import values checkbox.

Successfully imported orders are immediately created in the Sales module, any customers that don't already exist are created in the Contacts module. Each order is given a new Sales Order ID number following the last number used. The Order Reference used for import will be added as the "Customer Reference" so that you can use it when searching in the orders Quick Search box.

3. Importing Paid Orders

If the new orders you are importing have already been paid you can create these payments at the same time as importing the orders. Add the payment columns to your data map and import the orders as explained above. Not only will the order will be created (and the contact if necessary) and marked as paid, but the associated customer payment and accounting entries are also created. Note however, that if an order has a payment recorded against it, you won't be able to delete it, so be careful!

4. Imports and Tax

Sales Tax (USA)

When you're importing orders with sales tax there may be many more tax rates used than are set up in Brightpearl. If you don't want to create these tax codes in Brightpearl first, the tax can be applied manually when you import the sales orders by including the "Item tax amount" column. The "Item tax code" column can then be excluded.

• If both columns are included in your import, the tax amount specified will take priority and will be applied to the order.
• If your spreadsheet doesn't contain the "Item tax amount", you will need to include the "Item tax code" so that tax will be applied using the codes set up in Brightpearl.
• If the required columns are not contained in your import, it will fail and no orders will be imported.

This table summarises what tax will be applied to a sales order:

Note: It is possible to tell in your sales order where the tax code has NOT been used to calculate the tax amount as the Tax amount field is shaded.

VAT (UK)

The VAT applied to imported sales orders depends on various factors.

• The Item tax code column
• The Item tax amount column
• The product tax code
• The customer tax code

If an Item tax amount is entered on the spreadsheet this will be used as the tax amount on the order line, even if it is zero. If there is also a Item tax code, this will be entered on the order line, but not used to calculate the amount. If however, there is no Item tax amount the Item tax code will be used to calculate the tax value. When both columns are blank the product tax code is applied, but this will be overridden by the customer tax code if one is set. Every imported line will have a tax code set against it for VAT reporting purposes

Note: The import file must include either the Item tax code or the Item tax amount column, or it can include both.

This table summarises what VAT is applied under different circumstances:

Note: It is possible to tell in your sales order where the tax code has NOT been used to calculate the tax amount as the Tax amount field is shaded.

5. Common questions

Can I use the sales import for stock control?

Yes. But you do need to be very careful to ensure that every item you import matches an item that's already in Brightpearl with the same SKU.

Can Brightpearl collect a file via FTP from my Ecommerce Webstore?

No, sorry - you'll have to export it to Excel first. Some day soon we'll have an API for your ecommerce site to talk directly with Brightpearl.

What if my order export does not contain email addresses?

The email address is what's used to find the right customer. If you don't supply an email address, a new contact will be created for every single sale imported. Not ideal. If you have to, make up an email address like "email6798797@email.com" and ensure it's unique per customer.

Alternatively, and probably easier, you can import all the sales to a common customer, and make sure that you include the customer, delivery and billing fields so that each order is correct.