Purchase orders can be imported into Brightpearl from an Excel spreadsheet. To do this the supplier must already exist in Brightpearl. The imported orders will be matched to the supplier using an email address.
Note: If a customer email address is used, orders will be created as sales.
Preparing a file for import
The imported file can contain only fields supported by Brightpearl - 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. product) 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.
Format the file
The import file must be saved as an .xls file type - Excel 97-2003. Need help converting a file to .xls?
Create 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 that they are 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 of the same mapping is imported. Data maps can be copied and edited for variations of import files.
- Go to Settings > Data/import > Import orders.
- Click to add a new data map.
- Enter a name for this data map.
- Add "Order ref" as the first item, and ensure this is also the first column in your spreadsheet.
- Continue to build up the data map on the left-hand side of the window and ensure that it matches the columns headings and sequence of the spreadsheet.
- Scroll to the bottom of the window to save the data map. It will be listed, along with any existing data maps.
Test the import file
- If you have just created a data map you will be ready and in the right place, otherwise, go to Settings > Data/Import > Import orders.
- Locate the data map fitting the structure of the file to be imported and click the Import link.
- Leave the first box unchecked to perform a test run.
- If you wish to receive an email report of the import - includes errors, number of records read/created etc.
- 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.
Import the orders
- If you have just tested your file you will be ready and in the right place, otherwise, go to Settings > Data/Import > Import orders and click Import against the relevant data map.
- Check the first box Import values to go ahead with a live import.
- If you wish to receive an email report of the import - includes errors, number of records read/created etc.
- Leave the Skip orders already imported unchecked, unless some of the orders have already been created. If checked any orders which have the same order reference as an existing order in Brightpearl will be skipped. Note that if this is unchecked and a matching order reference is found in Brightpearl the whole import will fail.
- Browse for and select your saved file.
- Click the Upload XLS file button.
To see your imported orders go to Purchases > Recent purchase orders.
What data can be imported?
This table describes each field that can be imported on a purchase 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 |
Vendor/Supplier email | The email is used to look for existing vendor record. Where a match is found it will be used. Where no match is found a new vendor will be created, providing you have included the vendor'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 | This will be entered as the line item text. This will override the product name in Brightpearl where an SKU is matched. | 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) Shipping (gross) |
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. |
|
Item tax code Item tax amount |
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 |
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 and 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 the creation and tax date. It must be formatted as a date field to display: YYYY-MM-DD. If not included the date and time 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. | |
Vendor name Vendor company Vendor telephone Vendor mobile Vendor street Vendor suburb Vendor city Vendor state Vendor postcode Vendor country |
These details will be used to create a new v endor record where no existing record can be found (by matching email). If an existing vendor record is matched by email address the order will be added to the vendor account, but these address details will be applied to the order. The vendor record will not be updated. Vendor country must be the country name, not ISO country code. |
|
Delivery name Delivery company Delivery street Delivery suburb Delivery city Delivery state Delivery postcode Delivery country |
These details will always be used as the delivery name and address for the order even if an existing vendor record is matched by email with a different delivery address. An existing vendor record will not be updated with a new delivery address. If these fields are not included, or are included but left blank the vendor details (as above) will be used. Delivery country must be the country name, not ISO country code. |
|
Billing name Billing company Billing suburb Billing city Billing state Billing postcode Billing country Billing telephone Billing Email |
These details will always be used as the billing name and address for the order even if an existing vendor record is matched by email with a different delivery address. An existing vendor record will not be updated with a new delivery address. If these fields are not included, or are included but left blank the vendor details (as above) will be used. Billing country must be the country name, not ISO country code. |
|
Payment amount Payment date Payment ref Payment account |
Payments can only be imported with sales orders | |
PCF_* | Custom fields can be populated on purchase 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 include at least one of the columns "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 entered? | Item tax code entered? | Product taxable? | Vendor tax code set? | Tax amount applied: | Tax code applied: |
---|---|---|---|---|---|
Yes | Yes | n/a | n/a |
Item tax amount in spreadsheet |
Item tax code in spreadsheet |
No | Yes | n/a | n/a |
Calculated using item tax code in spreadsheet |
Item tax code in spreadsheet |
Yes | No |
n/a |
n/a |
Item tax amount in spreadsheet |
- Not rated |
No | No | n/a | Yes | Calculated using vendor tax code | Vendor tax code |
No | No | Yes | No | Calculated using system default tax rate | System default tax rate |
No | No | No | No | Tax amount is zero | - Not rated |
VAT (UK) rules
Item tax amount entered? | Item tax code entered? | Product tax code set? | Supplier 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 | Supplier tax code | Supplier 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.