Automate Xero Integration Tasks from PowerShell

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

ADO.NET

Insert Xero Data

PowerShell

ADO.NET

Delete Xero Data

PowerShell

ADO.NET