Importing One Stop Order Processing Orders
You can import
information that has been exported from One Stop Order Processing (using the
OSOPExport view) or another application that can export a single CSV order information
file (e.g. Excel).
If you tick the Update information in existing orders option on the main
page you can import information into existing orders. This can be useful for
adding order tracking or other information from external systems. See the Advanced
Import section for more details.
Import file
format
The import
file is a standard CSV (Comma Separated Value) file. The first line contains
the field names separated by a comma (,). Each subsequent line contains values
for those fields, each value being separated by a comma (,). Microsoft Excel
can export information as a CSV file. You can create a template (example CSV
file) by using the One Stop Order Processing OSOPExport view and exporting
an order.
The order import
file can contain any of the fields described below, in any order. If you do
not know any of the information for a field don't include the field in the file
to import. Any information not supplied is defaulted from the other information
supplied. This is described below.
Minimum requirements
for an import file
You must have at
least 1 of the red fields and 1 of the green
fields in your import file to create a valid One Stop Order Processing order.
Including orders with more than 1 item
If there is more than 1 item per order you must include a value for Order
ID (the
first value). Order lines with the same Order
ID are
imported into the same order. The first line in a group of order lines contains
the main order details. The subsequent order lines contain details for the other
order's items (and read from the Item ID, Item title, SKU, Item quantity,
Item price, Item tax, Item P&P, Item P&P tax, Item total, VAT rate, Item weight
fields).
|
Field
Name |
Description | Default
value If not included this value is used |
| Order ID | Unique Order number (e.g. 100006) | Invoice number |
| Transaction ID | Transaction ID (e.g. 72838485) | Order number |
| Invoice number | Invoice number (e.g. INT1001) | Automatically allocated from last sequential invoice number (setup in the import options), Order ID of not allocated. |
| Sales source | Where the order came from (e.g. MOTO) | OSOP |
| Listed on | Where the sale was listed (e.g. Website) | |
| Order number | Order number (e.g. 1023) | Order ID, then Invoice number |
| Sub-total | Total of all items sold (e.g. £85) | Total of all items in order |
| Discount | Discount applied to Sub-total | 0 |
| SubTotal tax | Total tax paid on all items sold (e.g. £15) | Total of all items' tax in order |
| Total P&P | Postage and Packing for all items (e.g. £10) | Total of all items' P&P |
| Total P&P tax | Tax on Postage and Packing (e.g. £1.75) | Total of all items' P&P Tax |
| Insurance | Insurance on whole order (e.g. £2) | |
| Insurance Tax | Insurance tax on whole order (e.g. £0.35) | If Tax rate given the required tax is split out from Insurance |
| Total Tax | Total of all Insurance, P&P and Items sold tax (e.g. £17.10) | Total of Insurance, P&P and Items tax |
| Grand total | Total of all items, Insurance and P&P and Taxes (e.g. £114.10) | Total of all Taxes, Insurance, P&P and Items |
| Fees | Any fees on sale (e.g. £5.70) | |
| Payment method | Payment type used for order (e.g. Paypal) | |
| Shipping method | Method used to send item (e.g. RoyalMail) | |
| Order date | Date order was placed (e.g. 16/09/2007 19:11:44) | Checkout Date, then Invoice Date and then Today's date |
| Invoice date | Invoice date. This is the Tax date/ point (e.g. 16/09/2007 19:11:44) | Order Date |
| Checkout date | Date checkout was completed (e.g. 16/09/2007 19:11:44) | Order Date |
| Payment date | Date payment was taken for order (e.g. 16/09/2007 19:11:44) | |
| Dispatch date | Date order was dispatched (e.g. 17/09/2007 16:00:00) | |
| Currency | Currency character used for monetary values (e.g. £) | Grand Total currency used |
| Paypal ID | Paypal transaction ID (e.g. 923849238928) | |
| PO num | Customer's Purchase Order number (e.g. OT412) | |
| VAT Number | Customer's VAT (TAX) code (e.g. 615 323 101) | |
| Total weight | Toal weight of order (e.g 1.02KG) | Total of all order items' weights |
| Delivery Instructions | Delivery instructions for courier (e.g. Leave in garage) | |
| Total items | Total number of items in the order (e.g. 2) | Total number of orders' items |
| Feedback left | Any feedback to give to customer (e.g. Good shopper) | |
| Notes | Notes on order. These can be amended or added to in the order's memo tab (e.g. Call customer when despatched) | |
| Feedback received | Comments received from customer (e.g. Excellent customer service) | |
| Invoice Customer | Customer name (e.g. John Symthe) | |
| Invoice Telephone | Customer Telephone number (e.g. 555 123 456) | |
| Invoice Fax | Customer Fax number (e.g. 555 654 321) | |
| Invoice Email | Customer's email address (e.g. support@tk.co.uk) | |
| Invoice Address line 1 | First line of address (e.g. Sparechton House) | |
| Invoice Address line 2 | Second line of address (e.g. 12 Parchdale Road) | |
| Invoice Address line 3 | Third line of address (e.g Farletontington) | |
| Invoice Address line 4 | Fourth line of address (e.g. Xexton) | |
| Invoice County | County (e.g. Wessex) | |
| Invoice Postcode | Postcode / Zip code (e.g. ZX2 5QC) | |
| Invoice Country | Country (e.g. United Kingdom) | |
| Invoice VAT number | Customer's VAT number (e.g. 615 323 101) | |
| Invoice Buyer ID | Buyers ID, username, login name or handle (e.g. filetpn63743) | Invoice Email, then Invoice Customer |
| Invoice Company | Company name (e.g. Aprelton Spares Ltd) | |
| Delivery Customer | Delivery customer name (e.g. Amy Smythe) | Invoice Customer |
| Delivery Telephone | Delivery customer Telephone number (e.g. 555 323 466) | Invoice Telephone |
| Delivery Fax | Delivery customer Fax number (e.g. 555 234 281) | Invoice Fax |
| Delivery Email | Delivery customer's email address (e.g. deliveries@tk.co.uk) | Invoice Email |
| Delivery Address line 1 | Delivery first line of address (e.g. Sparechton Warehouse) | Invoice Address line 1 |
| Delivery Address line 2 | Delivery second line of address (e.g. 14 Larch Close) | Invoice Address line 2 |
| Delivery Address line 3 | Delivery third line of address (e.g Fox Business Park) | Invoice Address line 3 |
| Delivery Address line 4 | Delivery fourth line of address (e.g. Grosgripe) | Invoice Address line 4 |
| Delivery County | Delivery county (e.g. Wessex) | Invoice County |
| Delivery Postcode | Delivery postcode / Zip code (e.g. ZS5 7IK) | Invoice Postcode |
| Delivery Country | Delivery country (e.g. United Kingdom) | Invoice Country |
| Delivery VAT Number | Delivery customer's VAT number (e.g. 615 323 101) | Invoice VAT Number |
| Delivery Buyer ID | Delivery buyers ID, username, login name or handle (e.g. filetpn63743) | Invoice Email, then Invoice Customer |
| Delivery Company | Delivery company name (e.g. Aprelton Spares Ltd) | Invoice Company |
| Item ID | Sales ID of item. This is normally unique (e.g. 832478325) | SKU |
| Item title | Description of item (e.g. Fluffy Dog in box) | SKU |
| SKU | Your own part number of item (e.g. IL1) | Item ID then Item title |
| Item quantity | Number of the item ordered (e.g. 1) | 1 |
| Item price | Cost of item. This can include tax (if you want to automatically split the tax out use the adjust orders to split VAT out in the import options) (e.g. £4.50) | Item total / Item quantity |
| Item tax | Tax on item (e.g. 0.79) | Can be calculated from Item price if you use the adjust orders to split VAT out in the import options. The tax (VAT) rate is taken from the VAT rate, unless this is blank. If it is the VAT rate used in the import options is used. |
| Item P&P | This item's postage and packing. The total P&P is the total of all the items' P&P, unless this field is left empty and the main P&P field is used (e.g. 0.55) | |
| Item P&P tax | This item's postage and packing tax (e.g. 0.10) | Can be calculated from Item price if you use the adjust orders to split VAT out in the import options. The tax (VAT) rate is taken from the VAT rate used in the import options is used. |
| Item total | Total cost of item(s), quantity * Item price (e.g. £50) | Item price * Item quantity |
| VAT rate | VAT rate specific to this product (e.g. 5%) | VAT rate given in Adjust orders to split VAT value in main import options. |
| Item weight | All the item's in this orderline's weight (e.g. 2KG) | |
| Shipped Qty | Number of these item's shipped (e.g. 2) | Item quantity (if item has been shipped) |
| Email Sent #1 | Has the first email setup been sent? (e.g. 1) | |
| Email Sent #2 | Has the second email setup been sent? (e.g. 0) | |
| Email Sent #3 | Has the third email setup been sent? (e.g. 1) | |
| Email Sent #4 | Has the fourth email setup been sent? (e.g. 0) | |
| Custom:Entered val. 1 | These are values that a specific to your business or from the another application. These values appear on the Custom Value tab on each order. | |
| Custom:Entered val. 2 | ||
| Custom:Entered val. 3 | ||
| Custom:Entered val. 4 | ||
| Custom:Entered val. 5 | ||
| Custom:Entered val. 6 | ||
| Custom:Entered val. 7 | ||
| Custom:Entered val. 8 | ||
| Custom:Entered val. 9 | ||
| Custom:Entered val. 10 | ||
| Custom:Entered val. 11 | ||
| Custom:Entered val. 12 | ||
| Custom:Entered val. 13 | ||
| Custom:Entered val. 14 | ||
| Custom:Entered val. 15 | ||
| Custom:Entered val. 16 | ||
| Custom:Entered val. 17 | ||
| Custom:Entered val. 18 | ||
| Custom:Entered val. 19 | ||
| Custom:Entered val. 20 | ||
| Custom:Entered val. 21 | ||
| Custom:Entered val. 22 | ||
| Custom:Entered val. 23 | ||
| Custom:Entered val. 24 | ||
| Custom:Entered val. 25 | ||
| Custom:Entered val. 26 | ||
| Custom:Entered val. 27 | ||
| Custom:Entered val. 28 | ||
| Custom:Entered val. 29 | ||
| Custom:Entered val. 30 | ||
| Custom:Entered val. 31 | ||
| Custom:Entered val. 32 | ||
| Custom:Entered val. 33 | ||
| Custom:Entered val. 34 | ||
| Custom:Entered val. 35 | ||
| Custom:Entered val. 36 | ||
| Custom:Entered val. 37 | ||
| Custom:Entered val. 38 | ||
| Custom:Entered val. 39 | ||
| Custom:Entered val. 40 | ||
| Custom:Entered val. 41 | ||
| Custom:Entered val. 42 | ||
| Custom:Entered val. 43 | ||
| Custom:Entered val. 44 | ||
| Custom:Entered val. 45 | ||
| Custom:Entered val. 46 | ||
| Custom:Entered val. 47 | ||
| Custom:Entered val. 48 | ||
| Custom:Entered val. 49 | ||
| Custom:Entered val. 50 |
Advanced Import
To import information into existing orders you only need to include the Order
ID and the required information (e.g. Delivery Instructions). You
can not include an order's item details (e.g. product codes), but you can include
all other order details (including Custom Entered values). An example of a CSV
is shown below. This can be edited in Microsoft Excel and exported as a CSV
file.
Order ID,Delivery Instructions,Custom:Entered val. 1
426-8234341-9441161,Leave with neighbour if out,JJ98324928
283-61188984-5408319,Leave with neighbour if out,TY342334
402-48239431-2828328,Leave with neighbour if out,KL123123
129-11112346-3745908,Leave with neighbour if out,DS823748
277-8758482-2751516,Leave with neighbour if out,RB819283