For those of you doing small business consulting whose clients use QuickBooks, what is your approach to QuickBooks integration?
I’ve been consulting for 20-plus years, and I know QBooks very well. I’ve even used Transaction Pro Importer (they have changed their name), and QODBC. So I’m extremely aware of the difficulties of extracting data from their database (which isn’t really a relational database).
Currently, I’m running reports, converting to Excel, and running macros to at least get the data in a readable, 2×2 format. If I need to unwrangle further, I do.
I’d be remiss if I didn’t ask you guys about your secrets to QuickBooks integration with Quantrix. Any tips other than Excel-based exports?
Incidentally, the techniques above take 10-15 minutes monthly per client. I just don’t like having to manually intervene in the process.
My experience has been exporting to a csv file from QBooks and having that file name the same each time, so the dataimport does not need to change in Quantrix. I then create a “mapping” file in the Quantrix model that maps the GL accounts from Quickbooks to certain lines of the PnL or BalanceSheet in Quantrix.
I have also used SQL server and pushed the data from QBooks to SQL Server then import into Quantrix by DataImport using a mapping matrix as well. One consideration is that if there are new accounts these need to be mapped to the correct line on the PnL etc, but you can create views and filters that help the end user identify “new” accounts very quickly upon update.
Hi mgandy, just couple of additions to excellent comments from Rich:
- You can automate this process of importing data from csv and pushing to Sql database if you have a Qloud. You can use automation feature and run import and push scripts periodically. Then you do not need to do the whole thing manually
- If your historical data in csv files are large, then it would be better to use import flat file to DB feature in MS SQL Management studio