Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Question about impoting data into QuickBooks

Posted on 2011-09-13
Medium Priority
Last Modified: 2012-05-12

My client has switched from a custom app to QuickBooks and now he is asking me to transfer the data from its legacy database (MS-Access) to Quickbooks. I have few questions and I will appreciate it if you give me some insight into it:


I'm assuming if I read data from *.mdf files and store them into *.csv or Excel format QB can import them. Is this correct?


How can I map a specific table to the right column or field in QB tables/files?


This is a small manufacture and they don't deal with FirstName, LastName but instead Company name. How can I handle the First-Name or Last-Name and replace it with Company name?


my client has bought latest version of QB Point of Sale or POS. if I can do this on an older version e.g.  Standard Edition 2010 still would work on client's application ?

Thanks indeed for your help,

Question by:akohan
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
LVL 28

Assisted Solution

by:Bill Bach
Bill Bach earned 1200 total points
ID: 36536932
The solution you probably want is QODBC. This tool gives you read/write access to the QB database from any ODBC-compliant tool. We use this to import invoices and product data into QB, and also to export to an easily searchable web database.

Expert Comment

ID: 36537429
It is insanely difficult to do a conversion from a different data source to Quickbooks as any expert will testify. If you don't know what you are doing, get a professional service to do it for you. Otherwise you will just end up spinning your wheels and getting nowhere.

I highly recommend:

Author Comment

ID: 36537524

Thank you BillBach and ZillionTech.

I'm looking at QODBC (thanks to BillBach) which seems a database connectivity interface and so far seems I can read the data from a legacy system and export it as xml or a friendly format for QB.

1) Now, base on ZillionTech comment is that too complicated to understand QB file format? please give me some insight into this.

2) As an example, if I want to import customer information in QB, what file name and extension should I look at? and where can I find the rules to provide and create the importing file so that QB can understand it?

I will appreciate your feedback.

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 11

Accepted Solution

stlinae earned 800 total points
ID: 36537973
What sort of data are you looking to import? If it's just things like customer information or other list information, take the Access data and export it to Excel. Use the QB import from Excel utility. You'll have to either map to your Excel or copy/paste into QB template but either way is easy.

If you're looking to import transaction information, it can be time consuming, but doesn't have to be near as difficult as some would make it seem. (Some people will make it sound more difficult in order to sell their own products or services. You really need to look at all your options and decided your skill level first.)

The qODBC will allow you to connect directly to the QB database and import from an Access file using update queries. Upside - If you understand Access, this isn't to difficult. Downsides - qODBC is SLOW, especially if it's moving into a large QB file. Also, the qODBC, using Access, will not allow you to import multiple line transactions. (An invoice with 3 line items would become 3, single line invoices for example.)

You can use the .iif format to import List and Transaction information. Everything you should need to know about .iif's can be found here:
Upside - It's Free and fairly easy to set up and use although can be VERY time consuming. (There are utilities that can help you format that can be purchased from, search iif) Downside - IIF files bypass the normal QB checks and balances it preforms when entering transactions. If things are done perfectly, it can make more issues than it solves. (Always make backups and practice in dummy file copies before trying in your live file.) Another downside, the iif doesn't allow for transactions to be linked together. So if Customer A paid invoice 1 with payment 2, both transactions will be in the QB file, but Inv. 1 will still show open with an un-applied payment 2. The customer balance will still be correct however.)

The final ways I would suggest importing transactions are  ways I don't use, and that's using the QB SDK or IPP. You can find information about both at Using either of these does require some program understanding and knowledge. Using either of these ways will allow you to avoid the above issues, but again, is the one that requires the most knowledge. (I'd check out again for apps that may be able to help you with it.)

As far as your version question, Everything I gave is referring to the QB data file and there fore won't matter what version of POS is being used. POS is a whole different beast when discussing importing.)

The Name issue really isn't an issue. You can either leave the company field blank and QB will default to the first name, last name or simply enter the first/last name as the company name as well.

Hope this helps.

Expert Comment

ID: 36537989

AK mentioned that he is using both QB 2010 and POS. I don't see how this is a non-trivial import.

LVL 28

Assisted Solution

by:Bill Bach
Bill Bach earned 1200 total points
ID: 36538444
With QODBC, you are basically doing an INSERT statement from SQL.  Many fields can be either specified, or left null (which will insert blank entries).   Yes, you have to know a bit of SQL, but this is still a fairly basic concept, and the QODBC driver is only a few hundred dollars.  As for multi-line transactions, it may be true that MSAccess doesn't allow it, but a straight import certainly does.  We extract web order data from our storecart system and dump it into a special directory, and a tool, reads the web order files and creates invoices in QB and populates them with product lines and serial numbers.  I use a very simple VBScript to connect to the QODBC database, parse the data, and insert it, and this works really well (yes, it's a bit slow).

If you are only importing customer data, then this should be even easier, and a command line SQL import tool like our own SQLExec should do the job rather handily.  If you need to translate the data fields, then perhaps a Business integration tool should be used to provide the data mapping, such as Informatica, Pervasive Data Integrator, or open source tools like Pentaho or Jaspersoft.

Author Closing Comment

ID: 36817290

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You may need to view past transactions from previous QuickBooks files or other QuickBooks company files when:       1. you are working in another QuickBooks file       2. QuickBooks is closed QuickBooks users who would benefit from this artic…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question