Tools for Transferring QuickBooks Inventory Data

By Tim Grant, Inventory & Manufacturing Software Consultant for QuickBooks Users

The concept of moving data seems so simple – I want to move my data from one software program to another. But the reality is a lot more complicated. When transferring QuickBooks inventory data (either into or out of QuickBooks), you are usually restricted by the ‘fields’ associated with the item list, either in QuickBooks or the program you are trying to exchange data with.

In this article I’ll present the different tools, either internally (residing in QuickBooks) or externally (third-party products) that can be used for transferring QuickBooks inventory data. Some transfer some of the data, others more. You need to know the limitations of all of them before attempting this kind of transfer.

Transferring QuickBooks Inventory Data with Internal Tools

Internal tools are those that exist within QuickBooks itself.

Add/Edit Multiple List Entries

You will find this by selecting Lists => Add/Edit Multiple List Entries.

This function allows the inventory user to add Inventory, Non-Inventory and Assembly items in a cut and paste fashion from a spreadsheet. It has all of the fields available in the Item Edit screens, so it makes item creation quick and easy. However, you cannot add the components to the Bill of Materials of an assembly on this screen. You have to add the assembly ‘shell,’ then go back to each assembly’s Edit Item screen to add the components.

By clicking on the Customize Columns button, you can select which fields will appear and the corresponding order.

Note: When using the Advanced Inventory add-on and the client has serial or lot numbers, do not fill in the field for ‘Serial Numbers’ or ‘Lot Numbers.’ QuickBooks does not track the numbers for this field (oddly enough). To add serial or lot numbers use only the Adjust Quantity/Value on Hand transaction window available under the Inventory menu, and select Serial Number or Lot Number as the adjustment type to add or take away the numbers. You can also use a third-party program to import adjustments. The quantity is normally added in Item Receipts and taken out in sales transactions (invoices and sales receipts).

Excel

Exporting: You can export your item list from QuickBooks to Excel by using the Export=>Export All Items option that is shown at the bottom of the Item List. Note that for assemblies this only exports the ‘shell’ of the assembly, not the components making up the Bill of Materials. The only way to export the BOMs using Excel is, while in the Item Edit screen for the assembly, click on Print, and choose, Print To => File => Comma Delimited File. The file can then be opened in Excel and will appear in a similar format to printing it as a document.

Importing: QuickBooks offers two ways to import with Excel. Go to File => Utilities => Import => Excel Files. The first is a simple copy and paste:

Selecting, Products I Sell opens a simple spreadsheet where the user can enter limited data to populate their item list. It does include an ‘error checking’ to verify the fields prior to import. However, honestly, since Intuit added the,Add/Edit Multiple List Entries feature, I’m not sure why people would still use this method. You are limited to the fields shown below:

The other method is by using the Advanced Import button on the prior screen. This allows the user to create their own spreadsheet with many fields, then ‘map’ those fields to QuickBooks fields. To import a file, select the location of the Excel file and the worksheet where it resides. Then, choose to add a new mapping. This opens a screen where the user can direct the corresponding fields to QuickBooks.

Intuit Interchange Files (IIF)

Items and assemblies can be imported in or exported out through the File => Utilities => Import => IIF Files orFile=>Utilities => Export => Lists to IIF Files wizards.

The first 20 or so rows of an IIF file pertain to header information and can be left alone. The list starts with the, ‘!INVITEM’ designator. The items are fairly straight-forward.

To add to the list, insert rows. The required fields are column A [INVITEM], which can just be copied down, the item name [NAME], the item type [INVITEMTYPE], the revenue account [ACCNT], the inventory asset account on the Balance Sheet [ASSETACCNT], and the cost-of-goods sold account [COGSACCNT]. The other fields are not required, but should be considered. For example, cost and price might be important to include.

Note: The, ‘Quantity on Hand’ field is ignored on import. Entering a value in this field has no effect. To change quantities, you must enter or import an Adjust Quantity/Value on Hand transaction once the items have been imported.

The assembly export in an IIF file is similar, but with more fields and an extra line to designate the assembly type. Below it are the components that make up the assembly.

The required fields are the same as with the inventory items, but with each of the component’s item type broken out and the required quantity identified.

Special care should be taken if trying to import assemblies this way. The formatting must match exactly, and all of the components must either be on the list or already in the QuickBooks file, otherwise it will be rejected.

Adjust Quantity/Value on Hand

This simple window can make a lot of changes to the item list. Inventory=>Adjust Quantity/Value on Hand orVendor=>Inventory Activities=>Adjust Quantity/Value on Hand.

Inventory items can be adjusted by quantity, value, a combination of both, or by lot or serial number depending upon the preference checked under Advanced Inventory. Here, the lot and serial numbers can be entered or pasted to update existing inventory.

Transferring QuickBooks Inventory Data with External Tools

External tools are those that are developed by third-party developers, which you have to purchase.

Transaction Pro Importer

A common tool used by QuickBooks professionals for list and transaction imports is the Transaction Pro Importer(TPI) tool available from Baystate Consulting. It is more reliable than IIF files, and includes error checking and overrides for designated fields. You can easily import all types of items and assemblies with some limitations.

With TPI you set up your spreadsheet with fields listed below in the header line. Then you populate the rows with items for import. After opening Transaction Pro Importer you are prompted for the Excel file and the appropriate worksheet. Next, you select the import type (Item List or Item Assembly), and you will be directed to map the accounts in Excel to the available fields in QuickBooks which TPI will import.

Screen for item mapping:

Note: These are the only fields available for items.

The mapping requirement is similar with assemblies, except that multiple rows are used for each component. The typical spreadsheet will look like this:

You will then match the fields on the mapping screen in TPI:

As long as the assembly item name is the same and uninterrupted in the list, TPI assumes it to be a component of the assembly. Note: When importing assemblies, TPI does not import preferred vendors, manufacturer part numbers, or custom fields.

Transaction Pro Importer can also be used for importing inventory-related transactions, such as item receipts, inventory adjustments and inventory transfers.

Baystate Consulting also offers a Transaction Pro Exporter for pulling out transactions and lists.

Note regarding serial numbers: TPI allows for the import of serial numbers and inventory sites on item receipts, bills, checks, credit card charges, sales receipts, invoices and credit memos. With inventory transfers, it only imports the serial numbers, not the site (captured in the ‘To/From’ fields). Just the opposite is true for inventory adjustments: It imports the sites, but not the serial numbers. This would be important if you used a tool that only imported the items to a central location and you need to spread the items with corresponding serial numbers to other locations. You would have to do it manually in QuickBooks. One surprising issue is that serial number and site import are left off of vendor credits and credit card credits. If the user is setting up a file mid-year and first imports an item through a bill with a serial number, then returned the item, they will need to perform a separate inventory adjustment for serial numbers to take it out.

Data Transfer Utility

Like TPI, the Data Transfer Utility is a third-party software tool used by accounting professionals to transfer inventory data from one QuickBooks file to another. It is available from Karl Irvin, CPA.

In the List category, the user can transfer over all types of items – service, non-inventory, inventory, assembly, other charge, as well as inventory sites. For transactions, the user can send over inventory adjustments, build assemblies, item receipts and inventory transfers.

Date ranges can also be set for transactions. This is a sample screen shot:

Note that it includes separate ‘Inv. Adjustments’ and ‘Build Assembly’ reports upon import into the destination company for reconciling any changes to costs.

This tool is useful when building a new QuickBooks file, and the user wants to carry over the lists and a limited set of transactions from an old file. It can also be used to simply start a new file.

List Importer

To create an IIF-type file with an item list from an Excel spreadsheet, use the List Importer from Big Red Consulting. It works as an ‘add-in’ to an Excel file, and converts a worksheet to an ‘IIF’ file instantly. You can use it for all inventory types, except assemblies.

Lot Mop

One tool that takes heavy advantage of the Adjust Quantity/Value on Hand window is a software tool called Lot Mop. With this tool the user can import a spreadsheet with all of the current inventory quantities and corresponding serial or lot numbers into QuickBooks. The program uses the adjustment window to reverse out the existing data and replaces it with the current data. Hundreds of items can be adjusted in minutes, and the user ends up with an up-to-date inventory list. The product has not been widely distributed yet – contact me directly for more information.

Summary

Moving inventory data is not as easy as it would seem. If you want to do it within QuickBooks, use the Add List Items fields and add the rest of the data through other screens, or use the Advanced Import for Excel. If you need to go outside of QuickBooks, use a third-party tool like the Transaction Pro Importer for items, assemblies and serial numbers; the Data Transfer Utility for moving between QB files; the List Importer for simple, non-assembly type items; or the Lot Mop for updating items in inventory. These tools will save you many hours compared to doing it manually.