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
  • Using a 3rd Party Software:  Zed Axis 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

Download Template

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

Download Sample in CSV

Excel Example Item List

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

Download IIF Template

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

Description: $200 3rd party tool, purchase here

Resources:

Notes: TPI can actually import ALL item types (except for Payroll Items)

 

 

Zed Axis

Description: $200 3rd party tool, purchase here

Field  Description 
Name* The name of the inventory item as it appears in QuickBooks, name must be unique unless it has a Parent Ref. For example if the Item Full Name is Widgit:Green:Small then the Name = Small and the ParentFullName = Widgit:Green
IsActive Whether the item is currently enabled for use by QuickBooks. The default is true
ParentFullName The parent name of the inventory item name, referred to as Sub-item in QuickBooks item list, For example if the Item Full Name is Widgit:Green:Smallthen the parent name is Widgit:Green
Manufacturer Part Number The part number used by the manufacturer of the item
Unit of Measure Set Name The unit of measure set consists of a base unit and a number of related units
Is Tax Included Whether Prices includes tax or not. Only supported in CA, UK and AU versions of QuickBooks
Sales Tax Code Tax code for the item used when selling
Sales Desc Description used when the item is sold
Sales Price Price charged for the item
Income Account* Refers to the Income account used when the item is sold
Purchase Desc Description used for the item when bought
Purchase Price Price of the item when bought
Purchase Tax Code Tax code used when the item is bought
COGS Account* Refers to the account that QuickBooks uses to track the original cost of the goods when bought
Pref Vendor Name of the vendor as appear in QuickBooks where the item is usually purchased from
Asset Account* Asset account for the inventory item
Reorder Point Minimum quantity that stock falls to before item needs to be reordered
Quantity On Hand Quantity held in available stock
Total Value Total value of stock held
Inventory Date Date of valuation

Resources:

Share →

Leave a Reply

Your email address will not be published. Required fields are marked *



For More Information

Call us to 954-633-2718 or email: hector@quickbooks-training.net