 |
|
QuickTips: Importing Inventory Items
Your company may have data on the Internet, your own Intranet, or in other software that you need to enter into QuickBooks. Some companies choose to re-enter the data in QuickBooks. Your company may want to transfer the information into QuickBooks.
For instance, you may have customer names or product (inventory) names you need in QuickBooks. This QuickTip explains how to import Inventory Items into QuickBooks. The steps to import other lists into QuickBooks are similar to importing inventory into the Item List. Use the chart below to determine the format for your imports and exports based on the version of QuickBooks you use.
Users of QuickBooks 2002 and later can check the QuickBooks Solutions Marketplace to find applications that may help. A third-party developer may have written a QuickBooks add-on application that can transfer data to and from QuickBooks. To search for applications now, visit the QuickBooks Solutions Marketplace at marketplace.intuit.com/ .
| Importing Lists into QuickBooks |
.iif (Intuit Interchange Format) |
QBSM (QuickBooks Solutions Marketplace) |
.csv (Comma Separated Value) |
.xls (MS Excel) |
| 1999-2001 |
Yes |
No |
No |
No |
| 2002-2003 |
Yes |
Yes |
No |
No |
| 2004-2005 |
Yes |
Yes |
Yes |
Yes |
| Note: QuickBooks 2004-2005 users can use the .iif method or the .cvs/.xls method (Question 1). The .cvs/.xls method provides QuickBooks error handling and troubleshooting to ensure you correctly import your data. However, if you need to import Inventory Assembly Items you must use the .iif method as explained in Question 2. |
Question 1: How do I import Inventory Items into QuickBooks 2004-2005?
Question 2: How do I import Inventory Items into QuickBooks 99-2003?
Q: |
How do I import Inventory Items into QuickBooks 2004-2005? |
|
A: |
Part I. Prepare the .csv or .xls file in Excel.
|
A: |
Part II. Map and import the .csv or .xls file into QuickBooks 2004-2005.
IMPORTANT: QuickBooks 2004-2005 does not import Inventory Assembly Items via the .csv/.xls method. If you need to import Inventory Assembly Items you must use the .iif method as described for QuickBooks 2003 in Question 2.
A. Part I. Prepare the .csv or .xls file in Excel.
Start Microsoft® Excel '97 or later.
Choose File > Open.
Select and open the file that has your inventory items (either a .csv or .xls).
Go to cell A1.
Choose Insert > Rows to make a blank row for text labels that match QuickBooks New/Edit Item text labels.
Choose Insert > Columns to make a blank Type column.
Type 'Type' in cell A1.
Type 'Inventory Part' into cell A2.
Copy Inventory Part into each cell in column A that has an inventory item (for instance, if you have 10 items copy 'Inventory Part' into cells A3 through A11).
Go back to the first row.
Type 'Item Name' in cell B1, C1, or whichever column includes your item name.
Your Excel file probably will not have three required accounts. Move to the first open column in the first row.
- Type the title 'Income Account' (required in C1 or the next blank cell).
- Type the title 'COGS Account' (required in D1 or the next blank cell).
- Type the title 'Inventory Account' (required in E1 or the next blank cell).
- Type the name of your QuickBooks Income account (for example, 'Sales' in C2).
- Type the name of your QuickBooks COGS account (for example, 'COGS' in D2).
- Type the name of your QuickBooks Inventory account (for example, 'Inventory' in E2).
- Copy C2 through E2 down for each cell with an inventory item (for instance, if you have 10 items copy to cells C3 through E11).
If your file has any of the following data, type the text labels in row 1 above the appropriate column:
- Purchase Description (optional)
- Sales Description (optional)
- Cost (optional)
- Sales Price (optional)
- Preferred Vendor (optional)
Choose File > Save > ImportMyItems.xls Save.
Choose File > Close.
|
| Back to Top |
|
A. Part II. Map and import the .csv or .xls file into QuickBooks 2004-2005.
In QuickBooks 2004 and 2005, choose File > Back Up > OK > OK.
Choose Lists > Item List.
Choose Item > Import from Excel.
Choose Browse.
Change to the directory with your .xls file.
Select ImportMyItems.xls and choose Open.
Click on 'Select a sheet' and choose Sheet 1. If you named your sheet in Excel, select that name.
Click on 'Add or Select a Mapping.'
Choose < Add New >.
Type 'Import Inventory Items' as the Mapping name.
Click on '< Select an import type >'.
Choose Item.
Click on the dropdown arrow next to Type.
Select Type.
Click on the dropdown arrow next to Name.
Select Item Name.
Click on the dropdown arrow next to Account/Income Account.
Select Income Account.
Click on the dropdown arrow next to Expense/COGS Account.
Select COGS Account.
Click on the dropdown arrow next to Asset Account.
Select Inventory Account.
Click and select any optional mappings you set up in step 13.
Choose Save to close the Mappings window.
Choose Preview.
Review any red ERROR codes to see if there are changes you need to make in Excel.
- If you need to make changes in Excel, return to Part I.
- Then continue with Part II, step 17 in QuickBooks.
Choose 'Do not import rows with errors.'
Choose Import.
Choose Yes because you have already backed up the file.
The QuickBooks Message tells you how many records have been imported and the number of record with errors. Choose OK to close the message.
Review the Item List to see if all the items were imported.
If you have errors:
- Choose Save to keep the error log.
- Type ImportInventoryErrors.csv as the error log. IMPORTANT: note the folder location.
- Start Excel and choose File > Open.
- Change to the folder in step (b).
- Change 'Files of type' to Text Files.
- Open ImportInventoryErrors.csv.
- Move to the column titled 'Error.'
- Scroll down until you see text such as "An income account must be specified" or "Account/Income Account: The specified Account does not exist in the list. | Error Record: User selected to Skip."
- Go back to steps Part I to fix the error(s) in Excel.
- Continue with Part II in QuickBooks.
|
| Back to Top |
Q: |
How do I import Inventory Items into my QuickBooks 99-2003? |
|
A: |
Part I. Export one QuickBooks Inventory Item as an .iif file.
|
A: |
Part II. In Excel, add Inventory Items to the .iif file.
|
A: |
Part III. Import the .iif file into QuickBooks.
|
|
A: Part I. Export one QuickBooks Inventory Item as an .iif file.
In QuickBooks 99, 2000, 2001, 2002 and 2003, choose File > New Company.
Choose Next twice in the EasyStep Interview.
Choose Skip Interview.
In Company Name, enter Template Co. > Next.
In Creating New Company, choose (No Type) > Next.
Choose Save to create Template Co. QBW.
Choose No on the "Documentation" window.
Choose Edit > Preferences > Company Preferences. In QuickBooks 99 choose File > Preferences > Company Preferences.
Scroll down and select Purchases & Vendors.
Select "Inventory and purchase orders are active."
Choose OK twice to close Preferences.
Choose Lists > Item List. In QuickBooks 99 choose Lists > Items.
Choose Item > New > Inventory Part.
In New Item:
- Tab to Item Name/Number and enter 'widget.'
- Tab to Description on Purchase transactions and enter 'green'
- Tab to Cost and enter '1.00.'
- Tab to Sales Price and enter '2.00.'
- Tab to Income Account and enter the name of your account (for instance, 'Sales').
- Press Tab and Account Not Found displays.
- Choose Set Up > OK to close the New Account.
- Choose OK to close New Item.
Choose File > Utilities > Export. In QuickBooks 99 choose File > Export.
Choose Item List > OK.
Name the file ImportInventoryItems.iif. Note the location of the file.
Choose Save.
QuickBooks Information displays "Your data has been exported successfully."
Choose OK to close the message.
|
| Back to Top |
|
A: Part II. In Excel, add Inventory Items to the .iif file.
Start Microsoft® Excel '97 or later.
Choose File > Open.
Change to the QuickBooks folder (usually C:\Program Files\Intuit\QuickBooksXXX\).
In File name type *.iif and press tab.
Select ImportInventoryItems.iif.
Choose Open.
On Step 1 of 3 in the Text Import Wizard, choose Next. (Delimited should be selected by default).
On Step 2 of 3 in the Text Import Wizard, choose Next (Tab should be the only checkmark).
Choose Finish on Step 3 of 3.
Open the file with the inventory list (such as a spreadsheet, document or Web page).
Select the item name you want to copy and choose Edit > Copy.
In Excel, move to the blank cell B13. Note B11 title for this column is NAME.
Choose Edit > Paste in the empty cell. Your inventory item displays in cell B13.
Repeat steps 31-34 for each inventory item. Note the cell you copy to increments with each item (B14, B15, and so on).
After you have pasted all inventory items in the B column, you need to repeat steps 31-34 for other data you want to import. For instance, if you want to import:
- DESC (column F) paste your Sales Description in F13.
- PURCHASEDESC (column G) paste your Purchase Description in G13.
- PRICE (column M) paste your Price amount in M13.
- COST (column N) paste your Cost amount in N13.
QuickBooks requires text in certain fields in the .iif. You need to copy text as follows:
- Go to cell A12 that contains 'INVITEM' and choose Edit > Copy.
- Go to the empty cell A13 and press the Shift key. Press the down arrow until you reach your last inventory item.
- Press Enter to paste 'INVITEM' in column A.
- Repeat steps a-c for 'INVENTORY' (column E, starting with cell E12).
- Select the words Sales, Inventory Asset, and Cost of Goods Sold in H12, I12, and J12.
- Choose Edit > Copy.
- Move to cell H13 and press the Shift key.
- Press the down arrow until you reach your last inventory item.
- Press the Enter to paste the three account names.
Choose File > Save.
Choose Yes to leave the file in text .iif format.
Choose File > Close.
Choose No because you don't want to convert the text .iif to and Excel .xls file.
|
| Back to Top |
|
A: Part III. Import the .iif file into QuickBooks.
In QuickBooks 99, 2000, 2001, 2002 and 2003, open Template Co. QBW.
Choose File > Back Up.
- In QuickBooks 99 and 2000, choose Save > Yes.
- In QuickBooks 2001 and 2002, choose Back Up > Yes.
- In QuickBooks 2003, choose OK > Yes.
Choose OK to close the successful backup message.
Choose Lists > Item List. In QuickBooks 99 choose Lists > Items.
Choose File.
- In QuickBooks 99 choose Import.
- In QuickBooks 2000, 2001, and 2002, choose Utilities > Import.
- In QuickBooks 2003, choose Utilities > Import > Import IIF Files.
In File name, select ImportInventoryItems.iif.
Choose Open.
QuickBooks Information displays "Your data has been imported."
Choose OK to close the message.
Review the Item List to see if all the items were imported.
If an item was not imported, repeat Part II in Excel to fix the item(s) that did not import.
Repeat steps 44-51 in QuickBooks.
After all items import into the Template Co.qbw, repeat Part III to import the items into your 'real' QuickBooks company .qbw file.
|
Back to Top
|
|
|
|
