[QODBC-Desktop] How to use QODBC with Alteryx Designer

Problem Description:

I want to export the QuickBooks data using Alteryx Designer Studio?

How can I use Alteryx Designer with the QODBC?

Solution: 

Alteryx Designer Studio is a 64-Bit application. You need to use QRemote 64-Bit and configure it to use ODBC Compatibility 3.8.

64 and 32-bit worlds are kept totally separate in the Microsoft Windows Operating System. QuickBooks is a 32-bit application, so in order to communicate with it, a 32-bit ODBC driver is required.

Since a 64-bit application cannot talk to a 32-bit driver, we have to do a bit of magic to get the worlds to connect, we call it QRemote. QRemote works as a bridge between 64-bit Applications and the 32-bit QODBC Driver by communicating data calls between the two worlds using TCP/IP protocol. QRemote makes it possible for 64-bit Web Servers and 64-bit applications to talk to QuickBooks live for the first time via the QODBC Driver.

QRemote has two components “QRemote Client Driver for QODBC” and “ QRemote Server for QODBC”

QRemote Client Driver: Allows 64-bit Applications to connect to QRemote Server and sends the Request to QRemote Server for Processing.

QRemote Server: Processes requested instruction from QRemote Client to QuickBooks using QODBC DSN Driver.

Setting Up a New QRemote Connection.

Start “QRemote Server for QODBC” from the start menu.

Double click on the QRemote Server icon from the system tray.

QRemote Server Main screen will appear that shows the IP address, and Port allows the QRemote Client to connect to.

Make a note of the IP address and port number listed on the QRemote Server "Event Logs window".

Create a new DSN entry

o Before creating A DSN entry, verify you have created a DSN entry for QODBC, which can connect to QuickBooks. 

Configure the default 64-bit DSN/or Create a new 64-bit DSN

Navigate to Start Menu, QODBC Drivers for Use with QuickBooks, and click on

"Configure QODBC Data Source 64-bit" 

Navigate to System DSN and select "QuickBooks Data 64-bit QRemote" DSN and click on Configure.

Note: "QuickBooks Data 64-bit QRemote" is the default DSN created when installing QODBC.

o Under the "QuickBooks Data 64-bit QRemote"- Client DSN configuration screen, Please fill in the following information

· Data Source Name: Name of your DSN entry

· Server: IP address displayed on QRemote Server

· Port: Port displayed on QRemote Server

· Encryption Key: encryption key ( keep this empty )

For the Alteryx Designer application, you need to set compatibility 3.8 in QRemote Client.

Select 3.8 from the list and click on "Ok".

Remote DSN: Select the QODBC DSN from the list. (Make sure QRemote Server is running)

After filling in all the details, click "Test Connection" to test the QRemote connection.

Test connection is successful.

Open Alteryx Designer Studio and drag the input/output connection and drop in the workflow screen.

Select Input connection and click on "Connect a file or Database".

From the Data Souce list, select "Generic ODBC connection".

From the Data Source name, select "QuickBooks Data 64-bit QRemote" DSN and click on "Ok".

Navigate to Tables tab and select any table from the list and click on "Ok" button.

Click on "Output Data" connection and select "Write to File or Database".

You can select any type of file connection from the screen.

Navigate to "Files" and select the "Microsoft Excel 1997-2003 .Xls"

In the dialog box, provide the file name and click on "Save".

Now, the input/output connection is ready.

Click on "Run" button.

After running the workflow successfully, the customer data will be exported to the targeted MS Excel file.

Open your MS Excel file.

Tags: Dynamic IP Address, QRemote IP Address changes, Random QRemote IP Address,