Automate Xero Integration Tasks from PowerShell



Are you in search of a quick and easy way to access Xero data from PowerShell? This article demonstrates how to utilize the Xero Cmdlets for tasks like connecting to Xero data, automating operations, downloading data, and more.

The CData Cmdlets for Xero are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to Xero.

About Xero Data Integration

Accessing and integrating live data from Xero has never been easier with CData. Customers rely on CData connectivity to:

  • Connect to Xero Accounts and both US and Australian Payroll APIs.
  • Read, write, update, and delete Xero objects like Customers, Transactions, Invoices, Sales Receipts and more.
  • Use SQL stored procedures for actions like adding items to a cart, submitting orders, and downloading attachments.
  • Work with accounting, payroll, file, fixed asset, and project data.

Customers regularly integrate their Xero data with preferred tools, like Tableau, Qlik Sense, or Excel, and integrate Xero data into their database or data warehouse.


Getting Started


PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to Xero, but also an SQL interface; this tutorial shows how to use both to create, retrieve, update, and delete Xero data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for Xero. To access Xero data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for Xero.

Once you have acquired the necessary connection properties, accessing Xero data in PowerShell can be enabled in three steps.

To connect, set the Schema connection property in addition to any authentication values. Xero offers authentication for private applications, public applications, and partner applications. You will need to set the XeroAppAuthentication property to PUBLIC, PRIVATE, or PARTNER, depending on the type of application configured. To connect from a private application, you will additionally need to set the OAuthAccessToken, OAuthClientId, OAuthClientSecret, CertificateStoreType, CertificateStore, and CertificateStorePassword.

To connect from a public or partner application, you can use the embedded OAuthClientId, OAuthClientSecret, and CallbackURL, or you can register an app to obtain your own OAuth values.

See the "Getting Started" chapter of the help documentation for a guide to authenticating to Xero.

PowerShell

  1. Install the module:

    Install-Module XeroCmdlets
  2. Connect:

    $xero = Connect-Xero
  3. Search for and retrieve data:

    $name = "Golf balls - white single" $items = Select-Xero -Connection $xero -Table "Items" -Where "Name = `'$Name`'" $items

    You can also use the Invoke-Xero cmdlet to execute SQL commands:

    $items = Invoke-Xero -Connection $xero -Query 'SELECT * FROM Items WHERE Name = @Name' -Params @{'@Name'='Golf balls - white single'}

ADO.NET

  1. Load the provider's assembly:

    [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for Xero\lib\System.Data.CData.Xero.dll")
  2. Connect to Xero:

    $conn= New-Object System.Data.CData.Xero.XeroConnection("InitiateOAuth=GETANDREFRESH") $conn.Open()
  3. Instantiate the XeroDataAdapter, execute an SQL query, and output the results:

    $sql="SELECT Name, QuantityOnHand from Items" $da= New-Object System.Data.CData.Xero.XeroDataAdapter($sql, $conn) $dt= New-Object System.Data.DataTable $da.Fill($dt) $dt.Rows | foreach { Write-Host $_.name $_.quantityonhand }

Update Xero Data

PowerShell

Update-Xero -Connection $Xero -Columns @('Name','QuantityOnHand') -Values @('MyName', 'MyQuantityOnHand') -Table Items -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.Xero.XeroCommand("UPDATE Items SET Name='Golf balls - white single' WHERE Id = @myId", $conn) $cmd.Parameters.Add((New-Object System.Data.CData.Xero.XeroParameter("@myId","10456255-0015501366"))) $cmd.ExecuteNonQuery()

Insert Xero Data

PowerShell

Add-Xero -Connection $Xero -Table Items -Columns @("Name", "QuantityOnHand") -Values @("MyName", "MyQuantityOnHand")

ADO.NET

$cmd = New-Object System.Data.CData.Xero.XeroCommand("INSERT INTO Items (Name) VALUES (@myName)", $conn) $cmd.Parameters.Add((New-Object System.Data.CData.Xero.XeroParameter("@myName","Golf balls - white single"))) $cmd.ExecuteNonQuery()

Delete Xero Data

PowerShell

Remove-Xero -Connection $Xero -Table "Items" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.Xero.XeroCommand("DELETE FROM Items WHERE Id=@myId", $conn) $cmd.Parameters.Add((New-Object System.Data.CData.Xero.XeroParameter("@myId","001d000000YBRseAAH"))) $cmd.ExecuteNonQuery()

Ready to get started?

Download a free trial of the Xero Data Provider to get started:

 Download Now

Learn more:

Xero Icon Xero ADO.NET Provider

Complete read-write access to Xero accounting enables developers to search (Customers, Transactions, Invoices, Sales Receipts, etc.), update items, edit customers, and more, from any .NET application.