How to pipe Avalara AvaTax Data to CSV in PowerShell

The CData Cmdlets Module for Avalara AvaTax is a standard PowerShell module offering straightforward integration with Avalara AvaTax. Below, you will find examples of using our AvalaraAvatax Cmdlets with native PowerShell cmdlets.

Creating a Connection to Your Avalara AvaTax Data

The primary method for performing basic authentication is to provide your login credentials, as follows:

• User: Set this to your username.
• Password: Set this to your password.

Optionally, if you are making use of a sandbox environment, set the following:

• UseSandbox: Set this to true if you are authenticating with a sandbox account.

Authenticating Using Account Number and License Key

Alternatively, you can authenticate using your account number and license key. Connect to data using the following:

• AccountId: Set this to your Account Id. The Account Id is listed in the upper right hand corner of the admin console.
• LicenseKey: Set this to your Avalara Avatax license key. You can generate a license key by logging into Avalara Avatax as an account administrator and navigating to Settings -> Reset License Key.

$conn = Connect-AvalaraAvatax  -User "$User" -Password "$Password"

Selecting Data

Follow the steps below to retrieve data from the Transactions table and pipe the result into to a CSV file:

Select-AvalaraAvatax -Connection $conn -Table Transactions | Select -Property * -ExcludeProperty Connection,Table,Columns | Export-Csv -Path c:\myTransactionsData.csv -NoTypeInformation

You will notice that we piped the results from Select-AvalaraAvatax into a Select-Object cmdlet and excluded some properties before piping them into an Export-Csv cmdlet. We do this because the CData Cmdlets append Connection, Table, and Columns information onto each "row" in the result set, and we do not necessarily want that information in our CSV file.

Deleting Data

The following line deletes any records that match the criteria:

Select-AvalaraAvatax -Connection $conn -Table Transactions -Where "Code = 051349" | Remove-AvalaraAvatax

Inserting and Updating Data

The cmdlets make data transformation easy as well as data cleansing. The following example loads data from a CSV file into Avalara AvaTax, checking first whether a record already exists and needs to be updated instead of inserted.

Import-Csv -Path C:\MyTransactionsUpdates.csv | %{
  $record = Select-AvalaraAvatax -Connection $AvalaraAvatax -Table Transactions -Where ("Id = `'"+$_.Id+"`'")
  if($record){
    Update-AvalaraAvatax -Connection $avalaraavatax -Table Transactions -Columns ("Id","TotalTax") -Values ($_.Id, $_.TotalTax) -Where ("Id = `'"+$_.Id+"`'")
  }else{
    Add-AvalaraAvatax -Connection $avalaraavatax -Table Transactions -Columns ("Id","TotalTax") -Values ($_.Id, $_.TotalTax)
  }
}

As always, our goal is to simplify the way you connect to data. With cmdlets users can install a data module, set the connection properties, and start building. Download Cmdlets and start working with your data in PowerShell today!