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.


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.


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!

Share This Article


  • My son has Invoice Manager that I thought would be able to be used to import his invoices into my QB Pro 2008 program. I never have figured out how to do that even though we were told at time of purchase that it would be possible. Another sales rep uses Excel to create her invoices. Can I import those easily without having to program something as you state above?

    • To be honest, I’ve never worked with Invoice Manager, so I don’t know what it is capable of. Most of the methods that I talk about above are designed to work with Pro, Premier and Enterprise, and I’m not sure if any of the third party products I talk about would work with Invoice Manager or not. I don’t know what kind of file it creates. You probably should try asking in the Intuit Community forum (I don’t even have a copy of Invoice Manager so I can’t try it) for invoice manager at

  • I would like to be able import vendor bills into QuickBooks. The bills consist of the vendor name, the date, a standard pay rate, number of hours worked on job and the customer:job.

    It sounds like the excel import should be the easiest. Do you agree? Do you have any suggestions that would prove beneficial to me?

    • James: Unfortunately, Excel imports in QuickBooks are limited to “list” entries, not transactions. You may want to look into some third party products – either Big Red Consulting (I’ve used their products with success, or Bay State Consulting (I’ve not yet had a chance to use any of their products yet).

  • I’m currently using QB Premier 2008 and would like to create very customized report. Will that be possible if I use ODBC?

    • Erly: Yes, you can purchase the ODBC driver at and use it to generate your own reports using an ODBC compliant tool. Microsoft Access and Crystal Reports are two examples. Microsoft Excel can use it but it is more limited as to what you can do with ODBC.

      Of course, this depends on your ability to understand the database structure, ODBC, and the tool you use…

  • I would like to know why it does not seem possible to show a total price for all items that make up a group item? Quickbooks make’s it almost impossible to find a relationship between group item titles and the items that relate to them. I have tried to export csv but again, no ID or relationship evident. How does QB actually know what item is linked to a group item?

    • Shaun, when you add the group to an invoice it should show the total price.

      As for imports and exports via Excel, QuickBooks doesn’t handle group items very well. This feature is not well developed. You can get the group, and its components, if you use an access method like ODBC or the programming toolkit.

  • Charlie,
    Thank you for your reply. I happened to stumble across this site after a few days of various trial and error. I tried the access/odbc method, but there is nothing that allows you to join the two tables, Item or ItemGroup, together with the way that quickbooks has setup the table structure. I guess what I’m asking if this can really be done or has it been done before so I know there is some hope or to stop wasting time.

    • Shaun, without knowing what exactly you are trying to accomplish (or your skill as a programmer) it is hard to give a specific answer. The Intuit table structures for the items in the item list are, shall I say, not “intuitive”. Or perhaps I should say they are “Intuit-tive” because this is typical of their internal structure. I don’t use ODBC myself, I use the programming SDK and build my own tables. I create a table of all items, and can create sub tables that have the group item components (or assembly item components) to work with in my applications. I’m not an ODBC expert, but I’m told that you have to do multiple queries to build your own item table and then try to make your relations from that. Group items and assemblies are complicated to work with since you can have components from each of the item type tables. It is complicated.

  • Basically I’m trying to get the total of a group item, by adding up each item that makes it a whole. I know the invoice can do it but I need to export the data to import/update my shopping cart for group items that have imported with 0.00 prices. I’ve got three tables that seem to have some kind of relationship between eachother yet I can’t figure out if it’s beyond the scope of sql to get me the answer.

    I’m shooting for this as a result, based off the three tables below. One group comprised of two items, will show me the group title, but, show the total price for each item linked to that group.

    Item Price
    Chocolate Milk 23
    ListID SalesPrice
    8000001E-1233090196 8 -milk
    8000001F-1233090225 15 -chocolate

    group title and ID = itemgroup

    ListID Name
    80000020-1233090256 Chocolate Milk

    ListID Name ItemGroupLineItemRefListID
    80000020-1233090256 chocolate milk 8000001E-1233090196 milk
    80000020-1233090256 chocolate milk 8000001F-1233090225 chocolate

  • Charlie,
    I need help on importing data to QB Pro 08. My customer wants to start downloading data from his bank online into Quicken. (His bank only suports Quicken) I am his bookkeeper and I use QB Pro 08. I already have an existing data file for him in QB Pro 08. I understand from an intuit techie that I need to use quicken 08 or earlier but he did not specify which type of quicken product. Will all or any of th the different quicken 08 products move data to an existing QB Pro 08 company?

    I am trying to avoid purchasing the wrong one, again. We bought quicken 09 and it will not work. So we are trying to get some help but no one at intuit can answer this question. Thanks for your help. Terri S.

    • Terri, this is a fairly complicated issue, because there are many variables. There are several ways that banks can export online banking information. Are you downloading a file, or trying to connect directly? If you download a file (or if that is an option), what is the “type” of the file?

  • Charlie:

    I have to tell you – you are a true godsend – I am transitioning from OB-OnLine to QB Desktop Pro 3 (I have always used desktop – after having used on-line for 9 months I hate it more every day). I just wanted to let you know that I came across your site when looking for an answer in QB Community. You have now solved, what I hope is, my fourth and final problem with my conversion. I don’t even bother going to the Inuit community for answers anymore – I come straight here!!! I downloaded from BigRed IFF creator and List Importer trials – work soo good that I am purchasing full version! Again thank you for all your help with every aspect of QB.

  • Charlie:

    I hope you can shed some light on my issue. The company I work for uses QB Pro 2008. We started using it Feb 2008. We have had no problems until about a month ago. We have always been able to import payroll items from our time clock manager into QB until last month. When we contact support they say there is no way that could have happened but we did have one support tech tell us it is possible but they can not tell us how to do it. At this point in order to get our data import we have to manually tell QB the payroll item for each employee before it imports any information. This takes hours. Any help would be greatly appreciated!

    • Mandie: Have you talked to the company that makes your time clock manager? I don’t know what method you (or they) are using to import to QuickBooks, so there isn’t much here for me to comment on.

  • Yes we have talked to our time clock manager and all the information is exporting correctly. The information is being exported to an .iif

    • Mandie: My apologies, but I don’t work with IIF files (it is an older format and is not being updated by Intuit). If I ever have to work with IIF I use tools from Big Red Consulting (

  • An .iif is the way our system was originally set up. What is the best way to import from a time clock manager to QB and receive all the data including payroll items? Would excel work? Thanks

  • I have been trying to find products that allow me to post sales transactions from Visual Foxpro into QB. I really was considering ODBC but was hearing much about SDK. Can anyone advise?

    • Patrick: It depends on your abilities as a programmer and what kind of tools you want to use. Without knowing much about you or your situation, if this is for your own use, I’d suggest working with the ODBC driver. It takes care of a lot of the mundane steps you have to take to work with the database. The SDK has a lot of quirks you have to get used to. The ODBC driver can do pretty much whatever the SDK can do for the kind of thing you are talking about.

  • Thanks Charlie. None of my applications ever had to ‘talk’ to others until now so I never had to ODBC any of them. Its not for my own use but for a client who’s very much interested in posting Rx transactions from my app to QB POS. I run VFP 6 but I just got a hold of 9. Well, it looks like I will have to break out the coffee and start with ODBC basics.

  • I am searching for a way (the best way) to setup a Data Base to import/export information. Currently I have a excell file that contains 45 fields of information, some of which is duplicated in my Quickbooks customer file. The company I work for repairs Fire Apparatus, we keep a detail record of each truck and what it has on it as far as engine, chassis, pump, aerial. I need to be able to print forms containing information from both files, which currently forces me to maintain two files with some duplicate information. Is there an easy way to do this &/or any program that you could suggest.

    • Sherry: I don’t have a simple answer. A program could be written by a custom developer who either uses the QuickBooks programming toolkit, or the ODBC driver, that could synchronize the information. You can also use off the shelf products that would import data from the other database – but that would be a manual process to some degree. I can’t really give you a more detailed explanation than this because I would need to know a lot more about how you are creating the data, and what you want to do with it.

  • We are having a web-based delivery ticket system created by a software developer. We will create delivery tickets online then have the data imported into Quickbooks when it comes time to invoice. The developer is asking for a full Quickbooks backup so he can extract the data he needs. Although we fully understand why he needs the information, we do not wish to hand over our financial information. Is there a way to give him what he needs without handing over our sensitive financial information?

    • Jill, that would depend on WHAT information they need. If it is list info, then you can export lists in a variety of ways in QB alone. If they need transaction info, then you can get a third party export program to extract the info. So it would depend on what they need, and what format they can accept it in.

  • We run 2009 QB Pro in our front office. Currently our secretary manually enters estimates written by the technicians into QB and emails them to the customer. We want to have the technicians enter the estimates directly into a program such as Excel and import it into QB. What would be the easiest way to accomplish this?

    According to the QB website we can import invoices and estimates directly into QB but we have not seen a way to do it.

    • There are several ways of handling that, but all involve a third party product. Look at products from Big Red Consulting ( to extract information from Excel to an IIF import file (I’ve used their products successfully). Look at products from Baystate Consulting ( for a similar approach, but it uses a different import method (I’ve used one of their products succesfully, but I have tried the import product). You can use a PDF file import approach by using Adobe Acrobat Pro 9, which would let you create a PDF form for the remote data entry person that can be imported to QuickBooks. This is what the QB web site is probably referring to. You have to buy Adobe Acrobat Pro and do some work to set things up – it can be a bit complicated sometimes. See my overview article on this at

  • I’ve set up this company with four users to have their own log-in ID’s with their own e-mail and Quickbook accounts.

    Their on a network with two pc’s I want one user on his pc under his account in Quickbooks to be able to view a receint invoice entered by the other user on his PC under his account in Quickbooks.

    How to do this, rite now he can’t view it

    • John: I can’t really answer that kind of question through a blog like this. I’d have to be able to connect to your system to see if it is set up properly. I don’t understand the details of what you are saying, but my first impression is that you don’t have the computers networked properly and sharing the same database. Open QB with each user, press the F2 key, and look for the address of the data file. If they aren’t all pointing to the exact same file (and not just pointing to a file in your “my documents” folder, which isn’t shared), then things aren’t set up right.

  • I am trying to use the importing facility in Quickbooks via excel. I have created the mapping et al but I keep on getting errors in QuickbookField Type The error against this field is Invalid Item Type.

    Please advise how I can correct this and import.

    Thanks Tunde

    • Tunde: As the article states, you must have the “type” field spelled exactly the way QuickBooks expects. If you don’t use the spellings that I list above, it won’t work. In addition, certain types cannot be imported, such as Inventory Assembly.

  • Hi,

    Is there an API for importing time and attendance information into QuickBooks?

  • Hi,

    I am investigating using Adobe Acrobat Pro to import invoices from an Access database to QB Pro 2009. I have also used QODBC in the past to connect to and post to the QB Invoice table.

    Cam you compare Adobe Acrobat Pro to QODBC in terms of extracting transactions from a database and then inserting those transactions in QB?

    I have read your article and the information provided by Adobe but I am still not clear on whether invoices must be manually entered into an Adobe Acrobat “form” to be able to post to QB or if this form could be populated by an import from a database.


    • David, if you are looking to import from Access into QuickBooks I wouldn’t look at Acrobat Pro. The primary reason to use Acrobat Pro is if you have people who want to type invoices into a form that you can then gather and import to QB.

      To import into QB from Access you can use ODBC, the Synergration Access tools (which I haven’t used myself), or the transaction importer from BayState Consulting (

  • We have Quicken Home & Business 99. We are considering purchasing Quickbooks Pro 2009. Can we easily import our Quicken records from numerous years into Quickbooks without much trouble? Can you give us a resource for step by step procedures?

    • Glenda, I don’t work with Quicken, so I’m not qualified to talk about that. Looking in the QuickBooks help file, they say the following:
      You can convert to QuickBooks directly from Quicken 98-2009 and Quicken Home and Business 98-2009. Older versions of Quicken require a preliminary conversion before this one. The step by step procedure is in the help file.

      Some people have success, some have some problems. The best I can say is that if you buy your QuickBooks from Intuit you should have a 30 day return guarantee, so if it doesn’t work then you can return your product for a refund.

  • I have qb08 pro and also a third party program developed to work in conjuction with qb. The problem I have is that I did import over 8 months worth of invoices aprox 1500 but they were imported without the tax when 99% are taxable. Is there any way qb could calculate the tax of each invoice in batch without going one by one. Thanks

    • If the import utility didn’t mark the items as taxable, you have to go through each line and mark it taxable. If the import utility didn’t set what the sales tax rate/item is, you have to go through individually and set that. There isn’t a way for QuicKBooks to know what the tax status and rates are in this case, you have to tell it.

      What third party utility did you use? Can you restore to a backup from before the import, and have the import do things correctly?

      Otherwise you are out of luck (without a custom program written to fix things, or exporting, fixing, and reimporting).

  • We just upgraded from quickbooks pro 2005 to 2010 for mac and for some reason we can’t enter employee pay rates. Do we need to buy the payroll service component? Plus, when we try to prepare a professional services invoice, we can’t seem to enter hourly rates, which include burden and mark-ups (which we don’t want customers to see. Any ideas?

    • David, I only work with the Windows versions – I don’t know much about the Mac version and how it differs from the Windows version. In the windows version there are ways to enable a stand alone payroll feature, but it is a bit tricky to set up. In the invoice you would use the rate field for hourly rates, but you don’t have a way to alter that significantly to include a calculation based on other fields.

  • We use QB08 Pro and are trying to import an excel file that has a list of all of the products we purchase from one of our vendors. We want it to go into the items list, but we want it to be set up with subaccounts. When I try to use QuickBooks’ import feature, it doesn’t give the option to create sub accounts. Do you know of a way to do this?

  • Ashleigh, when you say “subaccounts” do you mean the “subitem of” feature, where an item can be a subitem of another item? If that is the case, map the item name with a colon. That is, if item “DEF” is a subitem of item “ABC”, then add item ID as “ABC:DEF”

    Note that the item “ABC” must ALREADY be in the list when you add “ABC:DEF”, and that the item “type” of “ABC” must be the same as “DEF”

  • Will I have to use an IIF to do that? When I go to put it into the Excel import template, I get an error for putting the colon in Product Name column.

  • Ashleigh, it works in the Advanced Excel Import just fine. I just double checked in my copy of Premier 2010 and it worked as I said.

    What specific error message did you get? There may be some other issue.

    If you add an item to your item list manually (in the “add item” window) that has a subitem, then Export it to Excel, you can see how QB handles this…

  • We use QB 2008 but the new accountant uses QB 2009 is there a conversion tool available to be able to take the data from QB 2009 back to QB 2008? I really can’t afford to upgrade right now.

  • Dana, you can try using the Data Transfer Utility (, but that would be a very lousy way to deal with it. There isn’t a good way to “downgrade” the file effectively on a regular basis.

    The better approach would be if the Accountant were to use the 2009 Accountants Edition – and exchange information with you using the Accountant’s Copy procedure. This is the proper way to deal with these kinds of exchanges. If they have the 2009 Accountants Edition they can work with companies that have a 2008 or 2009 file without forcing the client to upgrade to 2008. Also, if you have an accountant who is part of the ProAdvisor program, they would have copies of each year of QuickBooks so they can work with you using the same version.

  • I am looking into updating from Quickbooks 2006 to a newer version for my boss. I am wondering if I can transfer or integrate the information in 2006 to the newer version easily, or if it is just not worth the trouble to upgrade?


    • Char: In most cases, moving your data from 2006 to a newer version is simple. It isn’t an “import” issue – you install the new version of QB and open the older company file, QB should convert the file to the new format easily and automatically. It sometimes doesn’t work that way – if you are making a really big jump in years or if your data has some accumulated damage internally, it might not convert. There are people that can help if you run into that situation.

  • Hi Charlie,

    Thanks for writing this article. It’s very informative.

    As you have mentioned various ways of importing data into QuickBooks, one of them is-

    [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”..]

    I would like to know about “QuickBooks Web Connector (QBWC)”

    Could you please answer my below questions?

    1. Is it a programming interface?
    2. Does is it support importing list of invoices?

    Thanks & Regards,

    • Yes, you can use the QBSDK and the web connector to write programs that will import invoices. Not necessarily easy, but you can do it. I use the QBSDK to work with invoices, sales orders and estimates myself (I don’t use the web connector because I write desktop products).

      The ODBC driver is built on top of the QBSDK.

  • Hi Charlie,

    Thanks for your reply for my last query.

    I want to integrate my Ruby on Rails web application with QuickBook Online Edition. I want to create customized template for time and expense invoice, like an invoice from Rails app. After that i want to import all entries from my rails app to QuickBook Online Account.

    Please help me about to do the same. I want to know, How I will achieve this and how QuickBook SDK can support? Or Do you know any alternate ways to do it?

    Thanks & Regards,

    • Manohar, I don’t work with QB Online, so I can’t help much there. Your options for interacting with QuickBooks are very limited, you have to use their SDK or possibly the IPP interface (I don’t know if that works with QBOE or not). Your best bet is to go to the Intuit Developer Network site and see what you can find there, both in the documentation (the SDK is free) and the support forums.

  • My company made the move from MAS90 to QB 2010 Premier. The initial choice was to recreate the customer records. Now they want to import the data.

    I can use IIF to transfer most of the information via a VFP program but we retain the customer CC information on file to make it easier to bill for orders. IIF does not have a format to import this to the customer record.

    I downloaded the SDK8 and am looking at several sample programs but not sure how to go about adding the customers yet. I need to add new customers and possibly try and match to existing customers via Custom Field value.

    Am I wasting my time or is this a possibility? Suggestions wanted.

    Jon Black
    Syncom Pharmaceuticals

    Visual Foxpro 9
    QB 2010 Pre – multiuser – network install
    QB SDK 8

  • I used a program that I found online that takes any pdf bill, order or invoice and allows you to seamlessly import it into your quickbooks. I couldnt believe how easy it was! but it worked by filling out all the required fields automatically and I cant do business with out it now. you should check it out.

    they even give you a demo that you can download for free and use with your quickbooks. i seriously recommend it.


      • I took a look as well and Chimpkey claims that they can also seamlessly import all your historical data and all of its information captured in your Simply Accounting application, and put it into Quickbooks immediately.

        something that I have never seen done before. it would be interesting to try it out.

        • Yes, when you call Intuit and ask them about switching from Sage 50/Simply they recommend ChimpKey. They seem to be the leader in the market for this.

          I did it in Nov 2013 and it was fine. I’m now on Quickbooks and its working well.

  • Just want to say your article is as astonishing.
    The clearness in your post is simply cool and i could assume you are an expert
    on this subject. Well with your permission let me to grab
    your feed to keep updated with forthcoming post. Thanks a million and please
    carry on the enjoyable work.

  • We are developing Bio metric attendance system which will ultimately communicate with Quickbooks. I was searching the ways to export our attendance data into Quickbooks. Thanks for your article, I am now able to figure out different ways to resolve the issue.

    Thanks for sharing valuable knowledge with us.