[QODBC-Desktop] How to Use QODBC with MS Excel Power Query
Posted by Jack - QODBC Support on 30 March 2016 10:04 AM
How to Use QODBC with MS Excel Power Query
Note: QODBC allows you to quickly merge live QuickBooks data into the cells of your Microsoft Excel worksheets for quick calculations and graphs. The data will retain a live link to QuickBooks, and one-click will update the data in the worksheet to the most current data in the linked QuickBooks table.
Open the QODBC setup screen and change the QODBC Compatibility mode from “Default” to “3.8.”
To change, please follow the below steps:
Start >> All Programs >> QODBC Driver for QuickBooks >> Configure QODBC Data Source >>Go To "System DSN" Tab>> click the DSN "QuickBooks Data" >> click "Configure”>> Switch to "Advanced" tab>> Navigate to "QODBC Compatibility">> change to "3.8"
Similarly, change the DSN configuration for QRemote 32-Bit and QRemote 64-Bit.
Start QuickBooks and log in to the QuickBooks company file as QuickBooks user Admin.
Before using the MS Excel Power Query application, please make sure you have installed MS Excel Add-in named "Power Query".
Please select Power Query Window.
Please select the data input method via From Other Sources & click on From ODBC.
In connection string box write "DSN = QuickBooks Data" & In SQL Statement box write your SQL statement & click on "OK" button. If you have 64-bit MS Excel, then you need use write DSN = QuickBooks Data 64-bit QRemote in the connection string box
Now select "Windows" & "Use my current credentials" option and click on "OK" button.:
Now you will get data in Power Query, apply filter & other settings as per your requirement & click on "Close & Load" button.
If you are facing the error "ODBC: ERROR [HYC00] [Microsoft][ODBC Driver Manager] Driver does not support this parameter", please switch QODBC, QRemote 32-Bit DSN, and QRemote 64-Bit DSN to ODBC Compatibility 3.8
You can also use "From Microsoft Query" for fetching data from QuickBooks to Excel.