Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Question about impoting data into QuickBooks

Posted on 2011-09-13
7
Medium Priority
?
548 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 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.
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
Independent Software Vendors: 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

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

Author Closing Comment

by:akohan
ID: 36817290
Thanks
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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…
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.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

971 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