IMPORTANT NOTE: IMPORTING DATA INTO QUICKBOOKS DOES REQUIRE A VERY SPECIFIC SKILLSET AND EXPERIENCE, YOU CAN HIRE US TO HELP YOU WITH THIS PROCESS, EMAIL HECTOR@GARCIACPA.COM FOR MORE INFO
There are several techniques for importing items into QuickBooks Desktop (Pro, Premier, Premier Accountant, and/or Enterprise) from an excel/csv file:
- Simple Excel Import
- Advanced Excel Import
- IIF File
- Add/Edit Multiple List Entries
- Using a 3rd Party Software: Transaction Pro Importer
The importing process and data fields that can be imported vary across each tool, in this article we will list all the fields and provide a template for each type.
Simple Excel Import
Description: this tool is built into QuickBooks Desktop, accessed via File, Utilities, Import, Excel Files
Fields:
| Product Name * |
| Description |
| Sales Price |
| Mfr. Part Number |
| Tracked as Inventory? * |
Notes: You can only import Inventory Parts and Non-Inventory Parts. All the other item types: Service and Other Charges, cannot be imported via this tool
Advanced Excel Import
Description: this tool is built into QuickBooks Desktop, accessed via File, Utilities, Import, Excel Files
| Field | Description |
| TYPE* | Enter a QuickBooks item type. |
| NAME* | Enter the name of the item. Note: If you’re importing a child (or sub) entry for a parent (or main) entry, the parent entry must already exist in order for the child entry to be imported correctly. |
| REIMBURSABLE CHARGE | Enter “Yes” or “No.” Note: if the charge is for services performed by someone else, the item type should be “Service Item.” If the charge is for a reimbursable expense, the item type should be “Other charge.” |
| DESCRIPTION | Enter the description of the item. |
| TAX CODE | Enter a three character tax code. To view your tax codes, go to the Lists menu and click Sales Tax Code List. |
| ACCOUNT* | Enter a QuickBooks Account name. (Typically an Income Account) |
| EXPENSE/COGS Account | (Required for Inventory Parts) Enter an expense account name from your chart of accounts. To view the Chart of Accounts, go to the Lists menu and click Chart of Accounts. |
| ASSET ACCOUNT | (required for Inventory Parts) Enter an asset account name from your chart of accounts. To view the Chart of Accounts, go to the Lists menu and click Chart of Accounts. |
| DEPOSIT TO (ACCOUNT) | (For payment items only) Enter a bank account name from your chart of accounts. To view the Chart of Accounts, go to the Lists menu and click Chart of Accounts. |
| DESCRIPTION ON PURCHASE TRANSACTIONS | Enter a text description. |
| ON HAND | Enter an amount. |
| U/M | Use only if unit of measure is set to Single U/M Per Item mode. Enter the unit of measure name and abbreviation in the format name(abbreviation). |
| U/M Set | Use only if unit of measure is set to Multiple U/M Per Item mode. Enter the unit of measure set name. |
| COST | Enter an amount. |
| PREFERRED VENDOR | Enter a vendor’s name from QuickBooks. |
| TAX AGENCY | Enter the name of a tax agency from the Vendor list. To view it, click Vendor Center. |
| PRICE/AMOUNT or %/RATE | Enter a price or rate. Keep in mind that you can’t use percentages when setting a price for inventory items or items whose expense will be passed on to the customer. In those cases, use a dollar amount. |
| ITEM IS INACTIVE | Enter “Yes,” or “No,” “Active,” or “Not-Active.” |
| REORDER POINT | When your inventory item reaches this number, QuickBooks will remind you to reorder the item. |
| TOTAL VALUE | (For inventory items only) Enter the total value for this item, which is the item cost multiplied by the number of items on hand. |
| AS OF (DATE) | Enter a date. |
| PAYMENT METHOD | Enter a QuickBooks payment method. |
Notes: You can import the four main item types: Inventory Parts, Non-Inventory Parts, Service, and Other Charges. Sales Tax Items, Group Items, and Payroll Items, and Inventory Assemblies cannot be imported with this tool. The good news is that this tool can also UPDATE any field (except name and type) during an “import”; but when updating accounts, it will NOT retroactively change transactions with this item, will only change moving forward.
IIF File
Description: this tool is built into QuickBooks Desktop, accessed via File >Utilities > Import > IIF File
| Field | Description |
| NAME | (Required) The name of the invoice item. |
| TIMESTAMP | (Export files only) A unique number that identifies the company file from which you exported the Item list. |
| REFNUM | (Export files only) A unique number that identifies an entry in the list. |
| INVITEMTYPE | (Required) Indicates the type of invoice item. If you are creating an import file, use one of these keywords to indicate the item type. COMPTAX (Sales tax item), DISC (Discount item), GRP (Group item), INVENTORY (Inventory Part item), OTHC (Other charge item), PART (Non-inventory part item) , PMT (Payment item) , SERV (Service item), STAX (Sales tax group item), SUBT (Subtotal item) |
| DESC | A description of the item as you want it to appear in the Description column on invoices, credit memos, and sales receipts. |
| PURCHASEDESC | (Inventory part items only) A description of the item as you want it to appear on purchase orders. |
| ACCNT | (Required) The name of the income account you use to track sales of the item. The type of this account should be INC. |
| ASSETACCNT | (Inventory part items only) The name of the asset account you use to track the value of your inventory. The type of this account should be OASSET. |
| COGSACCNT | (Inventory part items only) The name of the account you use to track the cost of your sales. The type of this account should be COGS. |
| PRICE | (All item types except group, payment, and subtotal) The rate or price you charge for the item. If you are creating an import file, add a percent sign (%) if the amount is a percentage. |
| COST | (Inventory part items only) The unit cost of the item. |
| TAXABLE | (Discount, other charges, part, and service items only) Indicates whether the item is taxable. If you are creating an import file, enter one of these keywords in the TAXABLE field: Y / N |
| PAYMETH | (Payment items only) The payment method customers use (check, Visa, etc.). |
| TAXVEND | (Sales tax items only) The name of the agency to which you pay sales tax. |
| TAXDIST | (Sales tax items only) The name of your tax district. |
| TOPRINT | (Group items only) Indicates whether QuickBooks includes a list of the items in the group when you print an invoice, credit memo, or sales receipt. If you are creating an import file, enter one of these keywords in the TOPRINT field: Y/ N |
| PREFVEND | (Inventory part items only) The name of the vendor from whom you normally purchase the item. |
| REORDERPOINT | (Inventory part items only) The minimum quantity you want to keep in stock at any given time. When your inventory reaches this level, QuickBooks informs you that it is time to reorder the item. |
| EXTRA | Adds additional information about the invoice item. These keywords can appear in the EXTRA field: AUTOSTAX / REXPGROUP / REXPSUBTOT |
| CUSTFLD1 | The custom field entries for the item (you can have up to 5 custom field entries). Custom fields let you track special information about the item, such as color, unit or measure, or size. What you use custom fields for is entirely up to you. |
| CUSTFLD2 | The custom field entries for the item (you can have up to 5 custom field entries). Custom fields let you track special information about the item, such as color, unit or measure, or size. What you use custom fields for is entirely up to you. |
| CUSTFLD3 | The custom field entries for the item (you can have up to 5 custom field entries). Custom fields let you track special information about the item, such as color, unit or measure, or size. What you use custom fields for is entirely up to you. |
| CUSTFLD4 | The custom field entries for the item (you can have up to 5 custom field entries). Custom fields let you track special information about the item, such as color, unit or measure, or size. What you use custom fields for is entirely up to you. |
| CUSTFLD5 | The custom field entries for the item (you can have up to 5 custom field entries). Custom fields let you track special information about the item, such as color, unit or measure, or size. What you use custom fields for is entirely up to you. |
| DEP_TYPE | (Payment items only) Indicates how you want QuickBooks to handle deposits of the payment item. Values: 1 = You want QuickBooks to deposit the payment in the bank account of your choice when you record the payment. The payment does not go into the Undeposited Funds account, and you do not have to use the Make Deposits window to deposit the payment. 0 = You want QuickBooks to “hold” all the payments in a special account named UndepositedFunds. To move the payments to a bank account, you must use the Make Deposits window to group the payments into one deposit. |
| ISPASSEDTHRU | (Service, non-inventory part, and other charge items) Indicates whether you pass the item through as an expense to the customer. Values: Y = You pass the item through as an expense. N = You do not pass the item through as an expense. |
Resources:
Add/Edit Multiple List Entries
Description: this tool is built into QuickBooks Desktop, via List Menu > Add/Edit Multiple List Entries
Download ADD/EDIT ITEM TEMPLATE
| Field | Item Type |
| ITEM NAME* | Service, Inventory, Non-Inventory, and Assembly |
| SUBITEM OF | Service, Inventory, Non-Inventory, and Assembly |
| SALES PRICE | Service, Inventory, Non-Inventory, and Assembly |
| INCOME ACCOUNT* | Service, Inventory, Non-Inventory, and Assembly |
| SALES TAX CODE | Service, Inventory, Non-Inventory, and Assembly |
| PURCHASE DESCRIPTION | Service, Inventory, Non-Inventory, and Assembly |
| COST | Service, Inventory, Non-Inventory, and Assembly |
| PREFERRED VENDOR | Service, Inventory, Non-Inventory, and Assembly |
| EXPENSE ACCOUNT | Service, Inventory, Non-Inventory, and Assembly |
| SALES DESCRIPTION | Service, Inventory, Non-Inventory, and Assembly |
| INACTIVE | Service, Inventory, Non-Inventory, and Assembly |
| U/M | Service, Inventory, Non-Inventory, and Assembly |
| CUSTOMFIELD1 | Service, Inventory, Non-Inventory, and Assembly |
| CUSTOMFIELD2 | Service, Inventory, Non-Inventory, and Assembly |
| CUSTOMFIELD3 | Service, Inventory, Non-Inventory, and Assembly |
| CUSTOMFIELD4 | Service, Inventory, Non-Inventory, and Assembly |
| CUSTOMFIELD5 | Service, Inventory, Non-Inventory, and Assembly |
| MPN | Inventory, Non-Inventory, and Assembly |
| REORDER POINT / BUILD POINT (MIN) | Inventory and Assembly Only |
| REORDER POINT / BUILD POINT (MAX) | Inventory and Assembly Only |
| QTY ON HAND | Inventory and Assembly Only |
| AS OF DATE | Inventory and Assembly Only |
| ASSET ACCOUNT | Inventory and Assembly Only |
| USE BOM COST | Assembly Only |
Notes: You can import the four main item types: Inventory Parts, Non-Inventory Parts, Service, and Inventory Assemblies (with no BOMs). Other Charges, Sales Tax Items, Payroll Items, Payment Items, Group Items, and Sales Tax Items cannot be imported via this tool
Transaction Pro Importer 7.0
Description: $200 3rd party tool, purchase here
Notes: TPI can actually import ALL item types (except for Payroll Items)
