Solved

Question about impoting data into QuickBooks

Posted on 2011-09-13
7
509 Views
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:

1

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?

2

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

3

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?

4

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,
Ak

0
Comment
Question by:akohan
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 28

Assisted Solution

by:Bill Bach
Bill Bach earned 300 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.
0
 
LVL 3

Expert Comment

by:ZillionTech
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: http://www.e-tech.ca/022-Quickbooks-SDK-Consulting-Services.asp
0
 

Author Comment

by:akohan
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.

Regards,
ak
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 11

Accepted Solution

by:
stlinae earned 200 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: http://accountant.intuit.com/support/document.aspx?product=QBAE&id=HOW12778
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 marketplace.intuit.com, 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 developer.intuit.com. 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 marketplace.intuit.com 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.
0
 
LVL 3

Expert Comment

by:ZillionTech
ID: 36537989
Stlinae,

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

0
 
LVL 28

Assisted Solution

by:Bill Bach
Bill Bach earned 300 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.
0
 

Author Closing Comment

by:akohan
ID: 36817290
Thanks
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

By Linda Saltz Customized item descriptions from bills DO import into a customer invoice!   This is a wonderful tip for companies that want to use generic items like Hardware, Software, Mileage, Travel in their item list which helps keep the i…
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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now