One of the most arduous tasks in setting up either a new QuickBooks file or a QB Point of Sale file is entering the Items. I find that many people spend an excessive amount of time entering each item individually. With either program, the easiest method for entering many items is to use the built-in Item template. There’s a trick to making this easy, but it’s a simple one.
Import Items in QuickBooks
First, enter at least one of each type of item that you’re going to use: inventory, non-inventory, service, and other charge. Leave any group items or assemblies items until later. Those are best entered directly into QuickBooks. Next, using the built-in utility, export your item list. The menu path is File/Utilities/Export/Items. This will export as an *.iif file readable in Excel.
Open the file in Excel. The Text Import Wizard will pop up. Don’t worry about understanding the instructions. Just click NEXT, NEXT, FINISH and you’ll be fine. Now just enter all of your items into this spreadsheet. Often you’ll already have your items accessible in another Excel file so you can just cut and paste into this file.
IMPORTANT: Don’t change anything in the column header row, or anything above the header row. In column “A” enter INVITEM for all newly entered items. Be sure to enter the Item Type and use the exported items as examples for the correct code. When saving the file, answer YES, you do want to keep the file in *.iif format.
Then simply import the entire file back into QuickBooks. File/Utilities/Import
So the trick is that by creating at least one of each type of item before exporting the template:
- You’re not starting from an empty template so there’s less confusion as to what each field represents.
- You can easily see which columns represent the fields you’ll typically use.
- You can copy the exact spelling of item types and column A text that QuickBooks expects to see when importing.
- You don’t have to worry about which excel fields to map to which QuickBooks fields because that can lead to errors and frustration.
Import Items in Point of Sale
For POS, the process used to be essentially the same as for QuickBooks until POS version 10.0 came out. Now the import process is a bit different. If you use the built-in template, you can’t export existing items within the template in order to see what fields you should use.
So you have two choices. One is to follow the steps I’ve described. Then, during the import process, select Custom File instead of Default Template and use the mapping feature to match up the spreadsheet columns with the POS fields. The other is to start at File/Utilities/Import, select Inventory Items/Default Template/Open Import Template, and enter all of your data into the spreadsheet. There won’t be any existing items for you to look at so just be sure to enter two required fields for each new item, Item Name and Item Type. I recommend that you DO NOT enter an item number (column A). This field is for POS to use in assigning a unique sequential number that it uses for its own purposes including bar code creation. This entire process needs to be performed on the POS server workstation in single user mode.
My final suggestion is that you try the whole process with just a couple of items until you’ve figured out any quirks and you feel comfortable with the process. From there it should be smooth sailing!