DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Data
  4. Import QuickBooks Online Data to QuickBooks Desktop in PowerShell

Import QuickBooks Online Data to QuickBooks Desktop in PowerShell

Import a saved QBXML file of QuickBooks Online data into your QuickBooks Desktop Company using the SendQBXMLFile stored procedure.

Jerod Johnson user avatar by
Jerod Johnson
·
Dec. 17, 18 · Tutorial
Like (5)
Save
Tweet
Share
8.53K Views

Join the DZone community and get the full member experience.

Join For Free

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.

Data (computing) Desktop (word processor) PowerShell

Published at DZone with permission of Jerod Johnson, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • How To Check Docker Images for Vulnerabilities
  • Event Driven 2.0
  • Stream Processing vs. Batch Processing: What to Know
  • Quick Pattern-Matching Queries in PostgreSQL and YugabyteDB

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: