How to pipe SAP Ariba Source Data to CSV in PowerShell

The CData Cmdlets Module for SAP Ariba Source is a standard PowerShell module offering straightforward integration with SAP Ariba Source. Below, you will find examples of using our SAPAribaSource Cmdlets with native PowerShell cmdlets.

Creating a Connection to Your SAP Ariba Source Data

In order to connect with SAP Ariba Source, set the following:

• API: Specify which API you would like the provider to retrieve SAP Ariba data from. Select the Supplier, Sourcing Project Management, or Contract API based on your business role (possible values are SupplierDataAPIWithPaginationV4, SourcingProjectManagementAPIV2, or ContractAPIV1).
• DataCenter: The data center where your account's data is hosted.
• Realm: The name of the site you want to access.
• Environment: Indicate whether you are connecting to a test or production environment (possible values are TEST or PRODUCTION).

If you are connecting to the Supplier Data API or the Contract API, additionally set the following:

• User: Id of the user on whose behalf API calls are invoked.
• PasswordAdapter: The password associated with the authenticating User.

If you're connecting to the Supplier API, set ProjectId to the Id of the sourcing project you want to retrieve data from.

Authenticating with OAuth

After setting connection properties, you need to configure OAuth connectivity to authenticate.

• Set AuthScheme to OAuthClient.
• Register an application with the service to obtain the APIKey, OAuthClientId and OAuthClientSecret.

  For more information on creating an OAuth application, refer to the Help documentation.

Automatic OAuth

After setting the following, you are ready to connect:

APIKey: The Application key in your app settings. OAuthClientId: The OAuth Client Id in your app settings. OAuthClientSecret: The OAuth Secret in your app settings.

When you connect, the provider automatically completes the OAuth process:

1. The provider obtains an access token from SAP Ariba and uses it to request data.
2. The provider refreshes the access token automatically when it expires.
3. The OAuth values are saved in memory relative to the location specified in OAuthSettingsLocation.

$conn = Connect-SAPAribaSource -API "$API" -APIKey "$APIKey" -Environment "$Environment" -Realm "$Realm" -AuthScheme "$AuthScheme" -InitiateOAuth "$InitiateOAuth"

Selecting Data

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

Select-SAPAribaSource -Connection $conn -Table Vendors | Select -Property * -ExcludeProperty Connection,Table,Columns | Export-Csv -Path c:\myVendorsData.csv -NoTypeInformation

You will notice that we piped the results from Select-SAPAribaSource 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.

The Connection, Table, and Columns are appended to the results in order to facilitate piping results from one of the CData Cmdlets directly into another one.

Deleting Data

The following line deletes any records that match the criteria:

Select-SAPAribaSource -Connection $conn -Table Vendors -Where "Region = USA" | Remove-SAPAribaSource

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

Import-Csv -Path C:\MyVendorsUpdates.csv | %{
 $record = Select-SAPAribaSource -Connection $SAPAribaSource -Table Vendors -Where ("Id = `'"+$_.Id+"`'")
 if($record){
 Update-SAPAribaSource -Connection $saparibasource -Table Vendors -Columns ("SMVendorID","Category") -Values ($_.SMVendorID, $_.Category) -Where ("Id = `'"+$_.Id+"`'")
 }else{
 Add-SAPAribaSource -Connection $saparibasource -Table Vendors -Columns ("SMVendorID","Category") -Values ($_.SMVendorID, $_.Category)
 }
}

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!