Import QuickBooks Online Data to QuickBooks Desktop in PowerShell

Exporting QuickBooks Online data to a QuickBooks Desktop company can be an involved process. With the CData Cmdlets for QuickBooks, you can easily import a saved QBXML file of QuickBooks Online data into your QuickBooks Desktop Company using the  SendQBXMLFile  stored procedure.

In this article, we walk through downloading your QuickBooks Online data, discuss the  SendQBXMLFile  stored procedure, and walk through PowerShell scripting to import the file into a QuickBooks Desktop Company.

Exporting QuickBooks Online Data

To start, download your QuickBooks Online data as a QBXML file: log into QuickBooks Online and click Export Data from the Settings menu (). Note that you can currently only export data as a QBXML file from Internet Explorer.

In the Export Data wizard, follow the first four steps:

• Step 1: Prepare to export
• Step 2: Select your data
• Step 3: Request your data - when the download is ready, click Continue to Download
• Step 4: Create a QuickBooks for Windows company file - click Download

At this point, the native export process becomes tedious and involved. Instead, we stop the process here and use the CData Cmdlets for QuickBooks to import the QBXML file.

SendQBXMLFile Stored Procedure

The  SendQBXMLFile  stored procedure allows you to import a QBXMLFile into a QuickBooks Desktop Company. The main use case for the stored procedure is performing mass inserts, making it a viable way to import an entire QuickBooks Online company as a QBXML file into QuickBooks Desktop.

Note that executing the stored procedure overwrites the exist company file data.

Input Parameters

The stored procedure has the following inputs:

• File (String): A URL or path to the QBXML file to submit to QuickBooks.
• ContinueOnError (String): Whether or not to continue executing the insert after an error has been reached. The default value is 'true'.
• IncludeInfoMessages (String): Whether or not info messages are printed in the output. The default value is 'false,' which means you only get information about failures. When set to 'true,' the results also include information about successful requests.

To call the stored procedure using the PowerShell cmdlets, use the  Invoke-QB  cmdlet. Note the double-escaping for the back slashes.

Invoke-QB -Connection $connection -Query "EXEC SendQBXMLFile @File='C:\\\\Path\\\\To\\\\QBXMLFile.qbxml', @ContinueOnError='true'"

The stored procedure returns the output parameters described below.

Output Parameters

The store procedure has the following output parameters, which are only available for failures by default:

• RequestId (String): The Request Id associated with the message.
• Severity (String): The severity of the message.
• StatusCode (String): The status code of the output message.
• StatusMessage (String): The status message giving details about what happened.

The  Invoke-QB  cmdlet returns a QueryResult PSObject when used to call a stored procedure. For the example above, the result is displayed as a table, with columns for the above parameters and a row for each result of the mass insert:

RequestId Severity SatusCode StatusMessage
--------- -------- --------- -------------
2         Error    3100      The name "Opening Balance Equity" of the list element is already in use.
3         Error    3100      The name "Checking" of the list element is already in use.
4         Error    3100      The name "Savings" of the list element is already in use.
...

Import the QBXML File to QuickBooks Desktop in PowerShell

With the QBXML file downloaded, you are ready to import the data into a QuickBooks Company. To do so, install the CData Cmdlets for QuickBooks on the machine where you saved the QBXML file and install the Remote Connector (included in the installer for the Cmdlets) on the machine hosting QuickBooks Desktop.

Once you install the Cmdlets and Remote Connector on the appropriate machines, you can import the QBXML file to QuickBooks Desktop. Below is a sample PowerShell script that will import your QBXML file. Download and run our interactive PowerShell script (ImportQBXMLFile.ps1) with prompts for the needed variables to accomplish the same task.

# $user = Remote Connector user name
# $password = Remote Connector password
# $server = Remote Connector name/address
# $port = Remote Connector port (default 8166)
# $QBXMLFile = full path to the QBXML file, with back slashes double-escaped

#Build the URL parameter for Connect-QB
$URL = $server + ':' + $port

#Connect to QuickBooks Desktop
$qbConn = Connect-QB -URL $URL -User $user -Password $password

#Construct the query to call the Stored Procedure
$query = "EXEC SendQBXMLFile @File='$QBXMLFile', @ContinueOnError='true'"

#Call the stored procedure and store the results
$results = Invoke-QB -Connection $qbConn -Query $query

At this point,  $results  contains a table of the import results, with columns for the request ID, severity, status code, and status message of any failed imports, as described above. To manage the failed imports, edit your QuickBooks Desktop company and try again, edit the QBXML file with corrections, or add the updates manually.

To work with your Quickbooks data in Powershell, download a 30-day free trial of the CData Cmdlets for QuickBooks and our sample PowerShell script today. If you have any questions, contact our support team.