Pipe Sage 50 UK Data to CSV in PowerShell

The CData Cmdlets Module for Sage 50 UK is a standard PowerShell module offering straightforward integration with Sage 50 UK. Below, you will find examples of using our Sage50UK Cmdlets with native PowerShell cmdlets.

Creating a Connection to Your Sage 50 UK Data

Note: Only Sage 50 UK 2012 and above are supported.

The User and Password properties, under the Connection section, must be set to valid Sage 50 UK user credentials. These values will be the same used to log in to the Sage 50 UK software.

Additionally, the URL property, under the Connection section, will need to be set to the address of the company dataset desired. To obtain the address, do the following:

1. If you have not already done so, open the Sage 50 UK software.
2. Click Tools -> Internet Options.
3. Select the SData Settings tab.
4. Click the Details button next to Sage 50 Accounts. A window is displayed containing a list of company names along with the address to their corresponding datasets.
5. Set the URL property to the value in the address field next to the company desired.

$conn = Connect-Sage50UK  -URL "$URL" -User "$User"

Selecting Data

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

Select-Sage50UK -Connection $conn -Table TradingAccounts | Select -Property * -ExcludeProperty Connection,Table,Columns | Export-Csv -Path c:\myTradingAccountsData.csv -NoTypeInformation

You will notice that we piped the results from Select-Sage50UK 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-Sage50UK -Connection $conn -Table TradingAccounts -Where "TradingAccountUUID = c2ef66a5-a545-413b-9312-79a53caadbc4" | Remove-Sage50UK

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 Sage 50 UK, checking first whether a record already exists and needs to be updated instead of inserted.

Import-Csv -Path C:\MyTradingAccountsUpdates.csv | %{
  $record = Select-Sage50UK -Connection $Sage50UK -Table TradingAccounts -Where ("Id = `'"+$_.Id+"`'")
  if($record){
    Update-Sage50UK -Connection $sage50uk -Table TradingAccounts -Columns ("Name","FinanceBalance") -Values ($_.Name, $_.FinanceBalance) -Where ("Id = `'"+$_.Id+"`'")
  }else{
    Add-Sage50UK -Connection $sage50uk -Table TradingAccounts -Columns ("Name","FinanceBalance") -Values ($_.Name, $_.FinanceBalance)
  }
}

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!