How to pipe SAP SuccessFactors Data to CSV in PowerShell

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

Creating a Connection to Your SAP SuccessFactors Data

You can authenticate to SAP Success Factors using Basic authentication or OAuth with SAML assertion.

Basic Authentication

You must provide values for the following properties to successfully authenticate to SAP Success Factors. Note that the provider will reuse the session opened by SAP Success Factors using cookies. Which means that your credentials will be used only on the first request to open the session. After that, cookies returned from SAP Success Factors will be used for authentication.

• Url: set this to the URL of the server hosting Success Factors. Some of the servers are listed in the SAP support documentation (external link).
• User: set this to the username of your account.
• Password: set this to the password of your account.
• CompanyId: set this to the unique identifier of your company.

OAuth Authentication

You must provide values for the following properties, which will be used to get the access token.

• Url: set this to the URL of the server hosting Success Factors. Some of the servers are listed in the SAP support documentation (external link).
• User: set this to the username of your account.
• CompanyId: set this to the unique identifier of your company.
• OAuthClientId: set this to the API Key that was generated in API Center.
• OAuthClientSecret: the X.509 private key used to sign SAML assertion. The private key can be found in the certificate you downloaded in Registering your OAuth Client Application.
• InitiateOAuth: set this to GETANDREFRESH.

$conn = Connect-SAPSuccessFactors  -User "$User" -Password "$Password" -CompanyId "$CompanyId" -Url "$Url"

Selecting Data

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

Select-SAPSuccessFactors -Connection $conn -Table ExtAddressInfo | Select -Property * -ExcludeProperty Connection,Table,Columns | Export-Csv -Path c:\myExtAddressInfoData.csv -NoTypeInformation

You will notice that we piped the results from Select-SAPSuccessFactors 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-SAPSuccessFactors -Connection $conn -Table ExtAddressInfo -Where "city = Springfield" | Remove-SAPSuccessFactors

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

Import-Csv -Path C:\MyExtAddressInfoUpdates.csv | %{
  $record = Select-SAPSuccessFactors -Connection $SAPSuccessFactors -Table ExtAddressInfo -Where ("Id = `'"+$_.Id+"`'")
  if($record){
    Update-SAPSuccessFactors -Connection $sapsuccessfactors -Table ExtAddressInfo -Columns ("address1","zipCode") -Values ($_.address1, $_.zipCode) -Where ("Id = `'"+$_.Id+"`'")
  }else{
    Add-SAPSuccessFactors -Connection $sapsuccessfactors -Table ExtAddressInfo -Columns ("address1","zipCode") -Values ($_.address1, $_.zipCode)
  }
}

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!