Automate Xero Integration Tasks from PowerShell

Ready to get started?

Download for a free trial:

Download Now

Learn more:

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.



Are you looking for a quick and easy way to access Xero data from PowerShell? We show how to use the Cmdlets for Xero and the CData ADO.NET Provider for Xero to connect to Xero data and synchronize, automate, download, 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.

Cmdlets or ADO.NET?

The cmdlets are not only a PowerShell interface to the Xero API, 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.

After obtaining the needed connection properties, accessing Xero data in PowerShell consists of three basic 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 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 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 System.Data.CData.Xero.XeroParameter("@myId","001d000000YBRseAAH")) $cmd.ExecuteNonQuery()