Importing Information into QuickBooks

QuickBooks is a “closed” database, with limited ways to push information in. Today I’ll give you an overview of some of the methods that you can use to import information into your QuickBooks company file, with more details to come in future posts. I won’t be able to cover EVERY alternative, but I’ll hit the major ones.

This article was updated on September 29, 2009

What do I mean by closed database? The information isn’t totally open to you or any programmer, so someone can’t just go in and edit your company information. You have to go through one of the controlled methods that Intuit provides. This is a good thing – you don’t want just anyone to be able to jump into your important company financial information to change things as they want. You want the data to be protected and secure. On the other hand, this is a bad thing – you want to be able to easily change information when you need to, as well as being able to access it to generate the reports that QuickBooks doesn’t provide that are critical to your business.

So what we have is a compromise. You have ways of getting to your information, but it is tightly controlled .

We have two general methods of importing information, file imports and the programming interface. With a file import you create a file that contains the information that you want to push into QuickBooks and run a utility to import it. With a programming interface you have to use some sort of programming tool that will make the connection, format your information, and push it in. Each of these general methods have variations, with various advantages and disadvantages.

File Imports

Do you just want to add a few inventory parts to your item list, or post some invoices? Can’t you just enter your information in Excel and post it? Well, yes and no. It isn’t always that simple.

Let’s split QuickBooks information into two types. You have “lists”, like the item list or customer list. You have “transactions” that alter information, such as an invoice or purchase order. This distinction is critical, because some methods only work with one or the other data type.

Excel

If you have a copy of Microsoft Excel installed on your computer you can use it to import information. In general, however, this will only work with list information. You can import customer, vendor, account or item list information. You have to create a “map” to correlate the columns of your spreadsheet to match the fields in QuickBooks (with the 2008 release there are some “wizards” that can help you with this). One of the frustrations of this method is that with some of the imports you can’t work with all of the data fields that QuickBooks manages. A good feature is that this method does a fair job of checking your information for accuracy, and reporting any problems. I use this format quite a bit myself when working with the item list.

Rapid Data Entry

Starting with the 2010 release of QuickBooks you have the Rapid Data Entry feature, which can be used to quickly add a limited number of list items. See the article for more details.

IIF

This is an older format that QuickBooks still supports, to a degree. You can use Excel or other tools to create a file in this format. It is a highly structured format that is complex to set up on your own. See the IIF toolkit for details on this. There are several disadvantages to this approach: Error checking is minimal at best, the format is very complicated to figure out, and it is outdated. Intuit has stated that they aren’t going to advance this any further (although that could change), and newer features in QuickBooks aren’t supported. One big advantage, though, is that this format supports a variety of transactions such as invoices, unlike Excel. I generally don’t work with this format, but when I do I use a low cost tool that will take an Excel file and reformat it into the proper IIF format. There are several available, I use the IIF Transaction Creator from Big Red Consulting.

Specialty Files

There are a couple of other formats that can be used to import information into QuickBooks, but these are not typically created by users. They are more properly created by a software developer or financial institution. You may see “Web Connect” (QBO) files, and online banking service (OFX) files. These are beyond the scope of this article.

Programming Interface

Intuit publishes a programming interface that you can download from the Intuit Developer Network web site. You will see this referred to as the “SDK”. This is the method that I use when I write QuickBooks integrated programs for CCRSoftware. Most QuickBooks users won’t use this – you need to be able to write programs in a computer language like C# or Visual Basic. However, it is important to note that this format allows the programmer the most detailed access to the data files that is possible in QuickBooks. You can access almost all of the lists and transactions that are available (there are a few frustrating omissions, but far less than other methods). Why do I mention this? Because several enterprising companies have built THEIR tools on the SDK, and these tools are far more accessible.

One tool is the ODBC driver from QODBC. This is, again, a programming tool that takes a lot of getting used to. However, more people are familiar with ODBC (a standard database access language). In addition, you get programs like Microsoft Access and Microsoft Excel to work with ODBC, so you have a better chance of being able to work things out. Still, this is a fairly complicated method.

Moving to a higher level, you can find products from Synergration, such as AccessBooks and CoreObjX , programming tools that let you work with Microsoft Access or languages like Visual Basic. I’ve not used their products so I can’t offer an opinion. There are a number of other companies that provide products that work similarly.

Import Utilities

There are a number of programs that are built on the SDK and are listed at the Intuit Marketplace. I’ve not worked extensively with any of these, so I can’t offer any opinions (but we’ll be getting to them in the future). One example is the Transaction Pro Importer by Baystate Consulting. These tools provide a variety of ways of working with your source data to import them into QuickBooks. Since they are based on the SDK they should have access to a wide variety of lists and transactions.

Custom Programs

The final method that I’ll discuss is finding a custom software developer to create a custom program that fits your specific need. This approach may cost more out of pocket, BUT you’ll save money by not spending hours trying to figure out how to map information or write computer programs. If you have a specific import task that you are going to do repeatedly, you should consider this.

So what do I use?

The choice of what to use depends on what you are trying to do, what your skills are, what your budget is and how much time you have to spend on the task. If it is a simple list import, such as setting up your inventory list for a new company, you can work with the Excel import. If you have a complicated import from an internal order system that you will do repeatedly, you may want to consider a custom developer.

This is a quick overview, in the future I will be expanding on some of the options, and reviewing a few products. If there is a particular issue you would like to see discussed, let me know!