Question about impoting data into QuickBooks

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,

Who is Participating?
stlinaeConnect With a Mentor Commented:
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.
Bill BachConnect With a Mentor PresidentCommented:
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.
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:
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

akohanAuthor Commented:

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.


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

Bill BachConnect With a Mentor PresidentCommented:
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.
akohanAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.