- How can I import orders from a spreadsheet?
- What format/schema is required?
APT University: Order Importing in AdvancePro
Preset notes: Use pre-filled sheet
(Show Title Slide)
Hello and welcome to AdvancePro University. Today we’ll be discussing excel order importing – This is a powerful technology that can be used to link advancepro with other technologies you might be using to take a customer or vendor order. Our focus will be on customer orders, since that is much more common.
So to begin with, you will need to be able to get an excel report with the information displayed here, you can include many more fields – check our knowledgebase for the complete list, but these are the most common ones.
So Today I’d like to walk you through the details of this excel sheet and the process of doing a manual order import. You might do this to test a an export so that it can be set import automatically in the future, or you might do this on a regular basis to bring in orders from other sources.
The first thing I want you to notice is that a single order can take up more than one line in the excel file, in fact it will take up one line for each SKU on the order, each line item. If a field applies to the order as a whole, then it needs to be duplicated on each line of items that go on that order, so in this case our order 4456 has two line items on it.
Now you’ll see the first required field here is Company name – this refers to your customer in this case, now if the customer name doesn’t match one in your AdvancePro – importing this order will create a customer with this name – when you look at the full schema you’ll see that you can include fields for both delivery and shipping addresses and all of those are saved to a new customer or updated on an existing customer if the addresses are different.
Next we have the order number, and order date, these are absolutely mandatory for the import.
Then we’re getting into the line item details, so you’ll see we have the SKU, quantitity of the SKU on each line, and the selling price. There are optional import fields for the Tax Term to be applied, shipping charge on the order, shipping method, payment method, and custom fields.
Finally, we have some more fields at the end for your Payment terms on the order, and for Order_notes – if the customer has any special requests for handling.
Now in order for this to import properly, make sure that this is all on the first sheet of the excel file, and that that sheet is named sheet 1 – also make sure that there are no spaces in the headers.
In order to import this into advancepro, you will need to make sure excel has closed the file, then navigate to Admin > Utilities > Order import > Load import tools.
Now you can save a schema or preset that tells advancepro exactly what information is in each column, make sure you check off and sort these headers so that they are in the exact same order as the columns in your file. You’ll also want to choose the file extension – xls, csv or txt. If there’s a column you won’t be using, use the add blank fields here. Once you’re happy with the schema, give it a name and save it.
Once you have a schema, click the ‘ready to import’ tab. Now choose your import type, file and schema.
When you click import, Advancepro will attempt to read the file, if it’s successful you’lll see this preview screen appear, this is where you can verify the orders so you’ll see we have the customer details in the order header, and we can click each customer to see their order details and their address information.
Now if we click continue, those orders will be brought into advancepro exactly as displayed here.
In this case they will be brought in as open orders, so we can process and pick these orders as normal now.
This concludes our lesson today. Next week we’ll be looking at options to automate this import process. if you have any questions please feel free to enter them in the questions tab on your gotowebinar control panel, otherwise feel free to reach out to your AdvancePro Advisor.
Question 1: Would I use this process to import orders from my online shopping cart?
Yes – you would use your shopping cart’s reporting capabilities to get a report that matches the excel file that we started with today.
Question 2: Can I also Import Vendor orders this way?
Yes – The process is identical, except that the company would refer to a vendor, the SKUs must already be assigned to a vendor and the vendor must already exist in advancePro, and the price would be the cost price, not the sale price.