Excel Power Query

Does Excel Power Query allow you do directly access a Quick Books database & export (into Excel) the contents of any Quick Books table?

If so, can you provide a like to a "how to" on doing this?

Thanks
Richard KortsBusiness Owner / Chief DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
do you have the Enterprise edition of Quick Books ?

here is a link with steps:
http://www.cdata.com/kb/templates/quickbooks-cloud-power-query.rst
LajuanTaylorCommented:
@Richard Korts - If you don't have a way to export data using a Quickbooks interface, you will need to install an ODBC driver that will allow Excel to query the Quickbooks data files.

Are you using Quickbooks on a local workstation, server, or hosted cloud solution?  

Once you get the data into into Excel, you can even perform analysis using PowerBI Desktop from Microsoft. It's also a free download: https://powerbi.microsoft.com/en-us/desktop
Richard KortsBusiness Owner / Chief DeveloperAuthor Commented:
LajuanTaylor

QB is on a local server; it is NOT cloud based.

I believe there is an ODBC driver on the server; how can I check?

Thanks
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

LajuanTaylorCommented:
@Richard Korts - Depending on which version of Quickbooks product that you have will help you to determine which ODBC driver you have installed.

If you are a QuickBooks Enterprise Solutions (ES) customer, the Intuit ODBC driver is built into the program. However, there's also a FLEXquarters ODBC driver that can be configured for desktop or server use.

There should be a Quickbooks configuration utility to check for the driver presence. Sometimes you can also check if there's a System DSN defined in Windows. Click path: Control Panel\System and Security\Administrative Tools\Data Sources (ODBC)

Here's two key Quickbooks articles to review before moving forward. The page load times are slow.
Differences between the Intuit ODBC driver and the FLEXquarters ODBC driver
Valid link as of: 11/21/2015 http://support.quickbooks.intuit.com/support/Articles/INF12219

ODBC driver frequently asked questions
Valid link as of: 11/21/2015 http://support.quickbooks.intuit.com/support/articles/INF12836
Richard KortsBusiness Owner / Chief DeveloperAuthor Commented:
To LajuanTaylor

I was able to export the customer file based on the links you provided; I exported as CSV.

But there are a lot of missing columns in the export, in particular ListID, which I need.

Is there more than one customer table now in QB?

Thanks
LajuanTaylorCommented:
@Richard Korts - Unfortunately, you have to use the QB SDK in order to access the listid. It's an internal value that isn't exported.

Some customers use a custom I'd in their export, but that can become problematic if you need to insert the back into QB.
LajuanTaylorCommented:
@Richard Korts - I took a look at the SDK and I'm not sure if you want to go that route or have time to read through all the documentation...

The second option is to try the 30 trial from QODBC. It's an install with a point and click interface.

I provided links to both options for you:
SDK
Valid link as of: 11/23/2015
https://developer.intuit.com/docs/0250_qb/0010_get_oriented/0020_programming_skills

QODBC
Valid link as of: 11/23/2015 http://www.qodbc.com/qodbcexcel.htm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richard KortsBusiness Owner / Chief DeveloperAuthor Commented:
LajuanTaylor

I'm going to try the 30 day trail of QODBC; haven't had a chance yet.

Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.