QXL for QuickBooks

by

Vendor Corner

I first became aware of QXL late last year, when I was doing some research on the QODBC website. QXL is a relatively new product from our friends at Flexquarters, the makers of QODBC.

QXL provides a super easy way to export QuickBooks Data, most likely into Excel. It comes in two versions – one that works with QuickBooks Desktop and another that works with QuickBooks Online.

I demonstrated QXL for QuickBooks Online in my Data Utilities for QuickBooks Online course at “Scaling New Heights" this past June. I also demonstrated QXL for QuickBooks Desktop during a recent webinar sponsored by Qbox. 

In this article we’ll be looking at the version for QB Desktop, because I easily adapted my PowerPoint slides into graphics for this article.

With the exception of the “connection routine” and a few ‘behind the scenes’ techno-things, the two versions of the product work almost identically.

QXL uses the Flexquarters QODBC driver for reading QuickBooks which means that it works with every year of QuickBooks since 2002. 

The product makes use of standard SQL commands that have been automated into QXL.  In other words, you don’t have to learn structured query language with QXL like you needed to when using QODBC directly.

Set-up and Configuration of QXL

Once you’ve downloaded QXL, you must install and configure it. Let's take a few moments to examine configuring QXL. Just click the wrench in the bottom right of the QXL window to open the options window.

If you purchased the product, you’ll want to enter your registration credentials (sent via email), so that you can fully activate the product. You probably have internet access, so you’ll use the standard activation process. 

Select the Activate/Deactivate tab, and then click the Activate button to enter your CD key (code)

A pop-up will appear for you to enter your code. Click the Activate button on the right for on-line automatic activation. If you don’t have internet, click the Manual button on the right (below the Activate button).

The manual process requires you to enter additional information associated with your order documentation.

Once your activation is completed, QXL displays the Activation successful message. Just click OK to proceed with using the software.

This Activate/Deactivate tab also is where you “Deactivate” your currently registered version of QXL for the transfer of the product to another machine. This process will take 24 to 48 hours before your CD Key account is credited with the deactivated license. 

You can reactivate the license on a different machine. If internet access is not available from the registered machine, you can use the manual option to generate a deactivation code from a machine that has internet access.

So, now that you have registered our product, you can set up QXL to run the way you want, starting with the General tab.

  • The general tab displays the Output folder where QXL will create the files you export from QuickBooks. You also can change this location simply by clicking the “Browse” button and navigating to your desired location.
  • There are multiple formats that can be used when formatting your exported data, including XLSX, XLS, CSV or SQL Server. 
  • You can set a connection string when exporting data into SQL Server or ODBC Data Sources. (By the way, you’ll need the Pro Edition of QXL in order to export to SQL/ODBC.
  • You also can select how you want you data structured in terms of export, as a single file, or as separate files. This is one of the most important configuration options you set, in my way of thinking. The default is separate files, which means each QuickBooks table will be set up as a unique file. 
  • If you select the Single File option, you’ll only have one Excel Spreadsheet, and each QuickBooks table will appear as a separate “tab” within that spreadsheet.
  • Another critical selection is the Output File Read-only option. By default, QXL sets your files to prevent unwanted overwrite. But if you intend to simply update the output files each time you use QXL, you’ll want to set the Read-only status to No.
  • By default, QXL sets the QuickBooks source to the Company file you have open in QuickBooks using the “Currently Opened Company File.” Most of the time, I think this is the option that fits the use of this tool the best. But if you will only be using the tool for a single file, this option allows you to browse to the specific QuickBooks (*.QBW) file you want to export from on a regular basis.

Additional options for this tab are explained in the In-product Help, which is accessed from the Question Mark in the upper right corner of the tab.

The Advanced Tab is used to change the Optimizer and Message settings. You might guess from the “big red box” that the Optimizer is another key setting or feature of QXL.

When you select the option to Use Optimizer, then QXL will “cache some of your QuickBooks data” to increase the retrieval performance of future queries. When you access any table, QXL will gather and store data from QuickBooks in Optimizer file.

The next time you query the same table, QXL will request differential records, which have been added, modified and deleted from QuickBooks and will sync it to Optimizer file. This way, QXL will have to only get the differential records, instead of entire data set from QuickBooks.

You also can configure the optimizer file location how you want QXL to display messages and a few other file export options, including the color of table headers.

Because you may want to limit the data you export from QuickBooks, using QXL to a specific table or set of tables, the Table Settings tab gives you options related to this functionality.

The Custom Query tab gives you the ability to write your own custom query, and then export QuickBooks data according to your requirement. This feature is more like using true QODBC than QXL. One of the reasons you chose QXL as an export tool was so you didn't need to learn structured query language and all the table configurations.

With that said, the day may come when you want or need a tool that allows you to execute custom queries. QXL gives you that functionality, in addition to the automated way it retrieves your QuickBooks data.

The Report Settings tab allows you to add default, and even custom reports, to your QuickBooks export. Default reports are selected from the drop down list.

Based upon your select, QXL composes the proper XML command to query the appropriate stored procedure, providing access to information in the format that would essentially be a report in QuickBooks.

Once you’re skilled at it, you’ll be able to add custom report parameters by writing or modifying a report query and specifying a unique output report name.  

The Pro Edition tab gives you the ability to export QuickBooks Data via QXL into SQL Server and ODBC database applications. You can accomplish this task using either SQL Server’s Default Driver or by using SQL Server’s ODBC Driver. 

Connecting and Using QXL with QuickBooks Desktop

Now that we know a little about how to configure QXL, let’s look at connecting to QuickBooks.

The first time we connect, just as with any other SDK-based tool, we must be logged in to QuickBooks as the Company Administrator.

So, here I am in QuickBooks Enterprise 17 – the Sample Mark’s Atomic Graphic Design Company file, as Administrator. QuickBooks pops up a message about security approval for QXL (QODBC) to access your QuickBooks Company file. 

Be sure to select the "Yes, always, allow access even if QuickBooks is not running.” Next, select “Continue.” Make sure to also select the “Done” option at the bottom of the next window QuickBooks displays asking you to confirm access.

Immediately upon returning to QXL, you’ll notice the transfer of data is already taking place. The progress bar will turn green as it moves from left to right.

The amount of time it takes to transfer all of your QuickBooks data the first time is directly related to the size of your QuickBooks Company file. If you have a small QuickBooks Pro file, with say, 100 MB of data, it may take only a couple of minutes to export all the data. 

On the other hand, a Premier file with 500 MB of data may take several more minutes. A QuickBooks Enterprise file with 2 GB of data could take a long time (the first time). Your hardware (RAM, Processor, Hard-drive speed, etc.) can all impact the transfer of data in addition to file size. 

Remember, when you use the Optimizer, future exports of data will take less time then the initial export because only changed data is being updated to your export file.

The moment the green progress bar reaches the maximum, Excel will launch and display your query results (if you’re working with a single Excel file.)

If you have selected the option to have each QuickBooks table exported as a separate file, QXL will open a File Explorer window to show you all of the tables that have been exported into their respective Excel spreadsheets.

At the same time, QXL displays a “pop-up” message advising that my QB Data from all 150 tables has been exported successfully.

If you want to learn more about the specific table structures and the relationships that exist between the various tables in QuickBooks Desktop, there’s no better place to review this information then the QODBC technical reference website.

To download a trial or purchase QXL, you can go to this website. You’ll have the option of selecting either the QXL product for QuickBooks Desktop or QuickBooks Online. In addition, the same website has more information on the inner workings of QXL.

This is a valuable data utility that ProAdvisors and skilled QuickBooks users can rely upon for data extraction. Beyond the extraction, other tools may make use of the extracted data for reporting, analysis or archiving.

QXL even gives you a couple of sample reports that will work with your extracted data (when you extract “all tables”) if you enable the Excel macros built into those samples. This will give you ideas as to the possibilities of expanding upon the extracted data.

By the way, Flexquarters is working on a new restore feature called “QRestore” for its QXL for QuickBooks Online product. Preliminary indications are that you’ll be able to pay a one-time fee to restore your QXL data to QBO, rather than paying a higher monthly price that includes both download and restore functionality.

In other words, you’ll pay for the restore capability, only when you need it. That's pretty cool.