Migrating from QuickBooks

 

joshuak's picture

Howdy All,

John Rogelstad posted in another topic about the Most Wanted features of the API. In passing he mentioned demand to migrate data from QuickBooks. Since that will most likely be a very popular topic, I decided to start it here.

Migrating from QuickBooks would be an excellent value-add for resellers. A while back I looked into what it'd take to do such a thing (data export from QB). At that point, the only way to get the data was to purchase a third-party "adapter" that provided an ODBC connection to the QuickBooks database. The information you got depended on how much you wanted to pay; if I remember correctly, $99 got you the vendors and customers, a bit more also got you the inventory, and even more got you vendors, customers, inventory, and invoice/PO/Quotes.

Perhaps today QB is more lenient about what they'll let you export for free, but I'm not sure.

Another pertinent question: If a company migrates between accounting applications mid-year, what do they want to migrate? It might be tricky to attempt to import all PO's, checks, invoices, etc. and have the books balance at the end. Instead, a company could migrate everything else, close one month out, and then start the new month on the new package.

Thoughts?
--J

 
cacycleworks's picture
Offline
Joined: 04/14/2009
qodbc...

We're currently using quickbooks pro 2006. We tried to hack together our own erp with qodbc. It is painfully slow at times, however, for $99 and unlimited reads, is absolutely worthy for use of extracting data from QuickBooks. Even though this is possible, when we switch to our next implementation, we'll start from scratch.

fwiw: we're using qodbc driver with scripts to download customer information from our website and also create a text summary of credit card charges for import to our credit card software. I do not recommend qb or qodbc for companies with over 3000 customers or 10 invoices per day.

chris

 
loyeyoung's picture
Offline
Joined: 06/01/2006
ODBC connector to QB

I may be covering old ground here, but has anyone tried reading the QB database using any of the Sybase ODBC drivers? Intuit embedded Sybase into Quickbooks starting with QB 2006.

 
loyeyoung's picture
Offline
Joined: 06/01/2006
Open source Ruby implementation of the QuickBooks SDK
 
jrogelstad's picture
Offline
Joined: 12/10/2008
RE: Migrating from QuickBooks

Quickbooks integration is definately a hot topic. We're getting queries almost daily asking where the tool is to import from Quickbooks.

While there's some work to do to make getting the data in PostBooks easier (see API "Most Wanted" List) actually defining an open source mechanism to connect directly to QuickBooks may be the bigger challenge. We'll need to find a coder out there willing to take on that challenge. There are several proprietary products that have pulled it off:

http://www.qodbc.com/
http://www.hcit.co.uk/LinkBook/
http://www.atandra.com/Prod_DFM.htm
http://www.synergration.com/accessBooksRT/

Any open source ODBC experts out there who want to take a look?

 
joshuak's picture
Offline
Joined: 08/01/2007
QB SDK is free!

I see that Intuit will let you download a QuickBooks developer SDK at no charge when you register with their site. I haven't taken a good look at it as I don't have any Windows boxes around that don't belong to my employer - but this might be easier than using ODBC, if we can simply use the SDK to pull the data out and directly insert it into PostBooks...

http://developer.intuit.com/QuickBooksSDK/qbsdk.asp

I wonder if any part of their license agreement prohibits such use of the SDK.

Cheers,
-Josh

 
jmahon's picture
Offline
Joined: 08/17/2007
Sql-Ledger + BOMs

We are presently using SQL-Ledger. I don't believe we are alone in wanting to move away from this
package. However, the problem of importing all our data into a new package is non trivial. At the
very least we would want to import
1) the customer list
2) the vendor list
3) the parts list + BOMs
4) the accounts list
Of course there maybe some translation required but Postbooks must keep most if not all the
same data in some form. With these four imports, we could at least start the new financial year
with your package.

If we could import the sales and purchase orders etc that would really be cool. Then we could convert
at any time.

Cheers
John

 
szuke's picture
Offline
Joined: 12/01/2002
QB SDK

We actually use the QuickBooks SDK and have found it fairly capable...although not very open. The datastore isn't really what most of us would think of as a database, it's proprietary and not ODBC friendly. We currently use the SDK to synchronize items and customers between QuickBooks and a MySQL database. The SDK is definitely tilted toward Windows development and we actually use VBScript with it in order to make it easy/quick to adapt.

It's been awhile since we wrote these integrations, but I'd be happy to be involved.

- Scott

 
jrogelstad's picture
Offline
Joined: 12/10/2008
Importing from SQL Ledger

We currently support the importing of customers and sales orders. All that would need to happen there is to define a routine for exporting the data from SQL-Ledger into a CSV format, then creating what we can an "atlas" which is a GUI defined map in our CSV Import tool. Have you taken a look at that?

As to the other data points, we are actively seeking parties who would be interested in sponsoring or contributing code to expand the current API functionality.

I will be working on a comprehensive white paper for the API this week.

John

 
loyeyoung's picture
Offline
Joined: 06/01/2006
Migration from Quickbooks

Using the QB SDK is the "right" way to do it, but perhaps an easy way is to have the user export the database from QB and then import into PB. The import will require parsing the .iif files (for exports of more recent QB versions) and .qif (for older versions).

.iif files are described here: http://support.quickbooks.intuit.com/ar ... iles2.html

The Quickbooks IIF Import Kit provides additional documentation of the .iif format: http://support.quickbooks.intuit.com/su ... d=bde7b06f

Here's documentation on the headers of the .iif files: http://support.quickbooks.intuit.com/su ... 7+10:37:17

The following describes the information that can be imported using .iif :
http://support.quickbooks.intuit.com/su ... 06_2_3_Fam

Of note, the foregoing link says
"The following cannot be imported or exported:
<li>Transactions from an existing QuickBooks company data file. However, you can create a report based on transactions and print the report to a file that can be read by a spreadsheet, database, or word processor program.
<li>QuickBooks timesheets, except through the Timer. (Time functions are not available in QuickBooks Standard and Basic versions.)
<li>Payroll items cannot be exported from QuickBooks."

This article recommends saving payroll items by generating a report that's saved to a delimited format: http://support.quickbooks.intuit.com/su ... 07+8:49:59

.qif files are no longer supported by Intuit, but older versions of QB use it. The format is documented here: http://web.intuit.com/support/quicken/2 ... /1178.html and here: http://web.intuit.com/support/quicken/docs/d_qif.html
Intuit abandoned the format in favor of OFX in 2005: http://web.intuit.com/personal/quicken/qif/

Intuit will eventually drop the .iif format because the data format has been opened. http://developer.intuit.com/Support/FAQs/?id=144
For an overview of the SDK, see the Technical Overview document: http://developer.intuit.com/qbSDK-curre ... erview.pdf
The Intuit Developers Network has available a great deal of additional information about moving information into and out of QB. http://developer.intuit.com/membership/?id=433

 
whispyyellow's picture
Offline
Joined: 07/16/2010
Cool, thanks for those tips

Cool, thanks for those tips man.

Todd-Sniper Rifle

 
salubrium's picture
Offline
Joined: 06/16/2005
It seems to me that the way

It seems to me that the way to go here would be using Talend by creating a Talend Connector to talk with Postbooks. Talend is an Open Source Master Data Management software that is able to integrate and exchange data from multiple systems and uses Postgresql as it's central datastore. There seems to be a Talend Connector that talks to Quickbooks already.

http://talendforge.org/exchange/tos/extension_view.php?eid=47

The obvious advantages of this would be that it opens up Postbooks to other ERP / CRM (or any data source) and so that once a connector is created for Postbooks it can be used to exchange data from/to multiple systems.

I have only ever had a bit of a play with Talend and never used it to complete a project so I am open to any thoughts on the matter.

 
tchelle's picture
Offline
Joined: 07/10/2008
Talend Quickbooks ETL component

Has anybody tried using the ETL tool Talend and the tQuickbooksAdvanced component.

The component is a community contrib here http://www.talendforge.org/exchange/tos/extension_view.php?eid=47

Curious to hear some feedback. Come to think of it...a xTuple component would be great and simultaneously give xTuple a fair amount of exposure.

Additionally I attached the Quickbooks 2008 schema for postgres in case it is of value to anybody.

tom

AttachmentSize
quickbooks_2008.sql 234.98 KB
 
adnan yousaf's picture
Offline
Joined: 08/03/2013
Quickbooks to xTuple PostBook Expert

Dear All

 

We are working with QuickBooks SDK from last 5 years and we have done more than 50 projects with different kind of data integration with QuickBooks and other open source and customized application we can help in exporting data from QuickBooks to xTuple please contact me on

 

consuspk@gmail.com

Skype :consuspk

 

 

Thanks

Regards

Adnan

Consus Solutions