To import sales orders (and many other transaction types) from a CSV / Excel file into QuickBooks Premier or Enterprise, you can use a terrific tool called ZED AXIS 11 which is great for this scenario. First lets look at the sample excel file I will be working with:
Notice, I simplified the columns… normally, if you are getting a spreadsheet like this produced from some other software or shopping cart system, you will probably have a whole bunch of columns you probably do not need, I cleaned it to only look at the ones we are interested in importing, containing:
- Customer Name
- Order Number
- Order Date
- Item Name
- QTY
- Price per Unit
- Total
Keep in mind you can also bring in address, memos, item descriptions, etc… I just left them put for this example. Once you know your spreadsheet is clean and ready for import. Now, after you launch ZED AXIS, you must create a connection with your QuickBooks File, and you need to click on CONNECT:
And QuickBooks will request to approve the connection:
After the connection is established, ZED AXIS will confirm the connection by displaying the QuickBooks Company File Name:
The next step is to go to the IMPORT tab, and choose the Excel file to import by clocking on BROWSE:
And choose the worksheet, if your got multiple worksheets:
And then click on MORE OPTIONS from the Import Options section on the right side, so set the presets for ZED to use when a new item is created from the import:
After setting up your default type and accounts for new items, click on OK… Now we need to setup the mapping:
For the first time importing that spreadsheet format, you must create a New Mapping that matches your spreadsheet columns with the data fields that QuickBooks should be importing them into:
After selecting the transaction import type, you need to make sure that every line relevant to the import is “mapped”, and the Data Preview is great to get a quick glance on the type of info that will be imported:
Once the mapping is all set, you can execute the import after you preview the new mapped spreadsheet:
And you will get a confirmation of what imported and what didn’t (if applies):
And you can switch back to QuickBooks to see what imported:
One important thing to notice is that multiple lines with the same reference number will go into the same transaction:
In this example, reference order 8001 has 3 items:
Why do I like ZED AXIS? it is a time fee of $199, no need to pay a monthly subscription and it works with unlimited number of companies. And the best part, they have a fully functional 30-day trial, so you can test you data imports before buying it!
I also have another post about: 3rd party programs that do import/export with Quickbooks
