Sales orders can be imported into Brightpearl using an Excel spreadsheet. They can be imported as unpaid, or they can be marked as paid during the same process.
Note: Orders can only be created using a spreadsheet - they cannot be updated!
Customers will be created during the import process, or existing customers can be matched by email address or account code.
How to import orders
Creating a data map
A data map is used to read the spreadsheet. It is a list of all the columns that are included in the spreadsheet and the order they appear in.
Only fields supported by Brightpearl can be imported - where a matching field is not available, consider creating a custom field or removing it from your spreadsheet.
Once a data map has been created it can be used every time a file using the same mapping is imported. Data maps can be copied and edited for variations of import files.
To add a new data map for your import go to Settings > Data/Import, and select the relevant import routine - in this case, 'Import orders'.
- Click the Add a new data map button. A modal window will appear.
- Enter a name for your new data map.
- The left-hand panel defines the data map.
Chose fields from the right-hand side and add them to the left-hand side by clicking the + icon or by dragging them across. Ensure they are in the same order as they are in the import file. - Scroll to the bottom of the window and save the data map.
Importing a file
- Click 'Import...' next to the relevant data map to begin importing your data.
This will take you to the import screen. - Before attempting the import of the data, doing a test run to check for any errors is recommended. Leave the first box unchecked to perform a test import to check for errors without creating or amending any data.
- If you wish to receive an email report of the import (including errors, number of records read/created etc.), check the 'Email me a report' box.
-
The rest of the fields are as follows:
Field Description Skip orders already imported Select if the file has been partially imported to avoid duplicating sales. Order type Choose between:
- Sales order
- Purchase order
- Sales credit
- Purchase credit
Set order status Choose the status to create the orders on Set channel Set a miscellaneous channel to assign to the orders Set warehouse Select the warehouse to assign to the orders - Browse for and select your saved file.
- Click the 'Upload XLS file' button.
Any errors will be shown at the top of the screen and in the email report. If the same problem occurs for every line, the error will be repeated for each one. No data will be created. - When you are happy, repeat the process but check the first box to perform the import.
To see your imported orders go to Sales > Recent sales.
Preparing an import file
The imported file can contain only fields supported by Brightpearl (as listed below) - where additional fields are included consider creating a custom field in Brightpearl or removing it from the spreadsheet.
- Column headings
Column headings must match the name of the Brightpearl field, as listed in the table below. - Cells of data
Cells should contain only the data relevant to a particular field.
For example, a full address cannot be entered into a single cell it must be split across multiple columns for "Street", "Suburb", "City" etc. - Multiple line items
Each line item of an order (i.e. each product on an order) must be recorded as a separate row in the spreadsheet.
An order reference is required in order to group each row into a single order in Brightpearl - apply the same reference to each row. - First column
The first column must always be "Order ref" or the import will fail.
The order reference must be unique for each order in the spreadsheet as well as across orders that already exist in Brightpearl.
Formatting an import file
An import file must be saved as an .xls file type - Excel 97-2003.
What data can be imported?
This table describes each field that can be imported on a sales order:
Field | Description | Required |
---|---|---|
Order ref |
This must be in the first column. It is used to group multiple order rows onto a single order record. |
Yes - must be the first column |
Customer email |
The email is used to look for existing customer records. Where a match is found, that customer record will be used. Where no match is found, a new customer will be created (provided you have included the customer's name in your file). |
Yes |
Item SKU |
Where a matching SKU is found in Brightpearl, the product will be added to the order. If this field is left blank, a miscellaneous line item (free text) will be added. |
|
Item name |
If no SKU is provided, this field will be entered as a miscellaneous line item. If a SKU is provided, this will override the product name in Brightpearl on the order row. |
Yes |
Item qty | Quantity of the item. | Yes |
Item net |
This is used as the item price without tax. Note that this value is used with the tax code to determine the tax and gross amounts. |
Yes - if gross not provided |
Item gross |
This is used as the item price including tax. Note that this value is used with the tax code to determine the net and tax amounts. 'Item gross' should not be used in your data map if also including 'item tax amount'. |
Yes - if net not provided |
Shipping (net) |
This will add an additional line item called "Shipping". Use either shipping net or gross. If both are added one will be ignored. The shipping charge applicable to the whole order must be entered against a single row. If multiple rows for the same order have a shipping charge entered only the first value listed is used. |
|
Shipping (gross) |
||
Item tax code |
Include one or both of these columns to apply tax amounts and codes to line items. Learn how tax is applied / calculated on order imports below. |
At least one of these |
Item tax amount | ||
Order total | This will be used to display the order total for the entire order. | |
Date time |
This will be used as the creation date and time as well as the tax date. It must be formatted as a date field to display: YYYY-MM-DD hh:mm:ss If not included, the date and time the import is performed will be used. |
|
Date |
This will be used as both the creation and tax date. It must be formatted as a date field to display: YYYY-MM-DD If not included, the date the import is performed will be used. |
|
Comments | This entry will be added as a comment in the notes and payment history on the order. | |
Customer name |
These details will be used to create a new customer record where no existing record can be found (by matching on email address). If an existing customer record is matched by email address, the order will be added to the customer account, but these address details will be applied to the order. The customer record will not be updated. Customer country must be the country name, not ISO country code. |
|
Customer company |
||
Customer telephone |
||
Customer mobile |
||
Customer street |
||
Customer suburb |
||
Customer city |
||
Customer state |
||
Customer postcode |
||
Customer country |
||
Delivery name |
These details will always be used as the delivery name and address for the order even if an existing customer record is matched by email with a different delivery address. An existing customer record will not be updated with a new delivery address. If these fields are not included, or are included but left blank the customer details (as above) will be used. Delivery country must be the country name, not ISO country code. |
|
Delivery company |
||
Delivery street |
||
Delivery suburb |
||
Delivery city |
||
Delivery state |
||
Delivery postcode |
||
Delivery country |
||
Billing name |
These details will always be used as the billing name and address for the order even if an existing customer record is matched by email with a different delivery address. An existing customer record will not be updated with a new delivery address. If these fields are not included, or are included but left blank the customer details (as above) will be used. Billing country must be the country name, not ISO country code. |
|
Billing company |
||
Billing suburb |
||
Billing city |
||
Billing state |
||
Billing postcode |
||
Billing country |
||
Billing telephone |
||
Billing Email |
||
Payment amount |
All four of these columns must be included to receipt a payment against the order. Enter the amount to be posted in the base currency. The date can be entered as DD/MM/YYYY or MM/DD/YYYY (depending on what you have specified in Settings > Company > Other options). Enter a reference for the payment and the payment method code which should be used to post the payment (it must exist in your payment method list). Note that payments can only be imported at the same time as the order. |
|
Payment date |
||
Payment ref |
||
Payment method |
||
PCF_* |
Custom fields can be populated on sales orders where they pre-exist in Brightpearl. The column heading must begin "PCF_" followed by the custom field code, e.g. "PCF_CODE". Dates must be formatted as a date field to display: YYYY-MM-DD |
Applying tax to imported orders
The spreadsheet for import must included at least one of:
- Item tax code
- Item tax amount
... or it can include both.
The rules below explain what tax amount and code will be applied, depending on the tax mode:
Sales Tax (USA) rules
Item tax amount column included? | Item tax code column included? | Tax amount applied: | Tax code applied: |
---|---|---|---|
Yes | Yes |
Item tax amount in spreadsheet (if blank tax code used to calculate) |
- Not rated |
Yes | No | Item tax amount applied to sale. | - Not rated |
No | Yes | Item tax code used to calculate tax amount (must exist in Brightpearl) | Tax code in spreadsheet |
No | No | The whole import will fail. |
VAT (UK) rules
Item tax amount entered? | Item tax code entered? | Product tax code set? | Customer tax code set? | Tax amount applied: | Tax code applied: |
---|---|---|---|---|---|
Yes | Yes | Yes | n/a | Item tax amount in spreadsheet | Item tax code in spreadsheet |
Yes | No | Yes | n/a | Item tax amount in spreadsheet | T9 Not Rated |
No | Yes | Yes | n/a | Item tax code in spreadsheet | Item tax code in spreadsheet |
No | No | Yes | No | Product tax code | Product tax code |
No | No | Yes | Yes | Customer tax code | Customer tax code |
Note: When the tax amount has been applied by the "Item tax amount field" and not calculated from a tax code it is represented as manual tax visible as a tax amount field shaded yellow on the sales order row.
Video
Learn how to import orders from a spreadsheet with Brightpearl.