SAP Commissions – Smart Data Integration[SDI] – Pa...

yogananda · ‎05-23-2023

Dear All,

This article is intended for database admins, consultants, customers & partners to enable the Excel Adapter & assuming data will be pushed to your local file path from customer to load into your SDI Project - Virtual table

In this blog, a Microsoft Excel file with all of the data in rows will be consumed or you can define the parameter the rows with header or without header in Remote Source.

The Excel formats (.xls and.xlsx) are supported.

Reason why ExcelAdapter : Few customers may not connect to their own local database (source) due to privacy & security.

Prepare a file for upload

Download this sample file into the default workspace. Convert the downloaded data to excel format.

The default workspace is located in <<ROOT DIRECTORY>>\workspace, for example, \usr\sap\dpa\excel

Once you enable the Adapter, excel folder will be created.. place all data in the required filename defined ( Refer below)

Configure an access token

From a command prompt, use the following command to set an access token.

agentcli --setSecureProperty

Set a token and make a note of it. example you can use FileToken

or you can enter your own choice but you should be remembering token you entered which will be needed in last Step configuring the Remote source

Press Enter and quit the configuration tool.

Adapter Registration

agentcli --configAdapters

Register an adapter using option 8

From a command prompt, use the following command to set an access token.

Use ExcelAdapter when prompted. Use Quit to exit the configuration agent.
Check once if ExcelAdapter is enabled with showing "Yes"
The datafiles being sent to the DP Agent needs to end with .xls or xlsx.
This is how it should look like in your directory for file to process.

Create a Remote Source

Go back to the Database Explorer. You will see the adapter under Catalog -> Remote Source

Right-click on Remote Sources. Choose Add Remote Source

Here you can define the Source Name (arbitary), the Adapter will be the ExcelAdapter

Also you can CREATE REMOTE SOURCE using below statement in your webIDE

CREATE REMOTE SOURCE "ExcelAdapter_testing_yoga" ADAPTER "ExcelAdapter" AT LOCATION AGENT "Localfiledump2process"

CONFIGURATION

'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<ConnectionProperties name="configurations">

<PropertyEntry name="FileSourceType">File System</PropertyEntry>

<PropertyGroup name="File System" displayName="File System">

	<PropertyEntry name="folder" displayName="Folder"></PropertyEntry>

</PropertyGroup>

<PropertyGroup name="Table" displayName="Table">

	<PropertyEntry name="firstRowAsHeader" displayName="First Row as Header">true</PropertyEntry>

	<PropertyEntry name="rowMin" displayName="rowMin">2</PropertyEntry>

	<PropertyEntry name="showHiddenColRow" displayName="Show Hidden Column and Rows">false</PropertyEntry>

</PropertyGroup>

</ConnectionProperties>

' WITH CREDENTIAL TYPE 'PASSWORD' USING

'<CredentialEntry name="usertoken">

<password>XXXXXXXX</password>

</CredentialEntry>';

Create a Virtual Table - Excel - XLS Format

you will have to create a Virtual table by connecting to your remote source to load the data into table.

CREATE virtual table ext.VT_ExcelAdapter_testing_yoga 

AT "ExcelAdapter_testing_yoga"."<NULL>"."<NULL>"."salarydata.xls";

select * from ext.VT_ExcelAdapter_testing_yoga;

Create a Virtual Table - Excel - XLSX Format

you will have to create a Virtual table by connecting to your remote source to load the data into table.

CREATE virtual table ext.VT_ExcelAdapter_testing_yoga_xlsx 

AT "ExcelAdapter_testing_yoga"."<NULL>"."<NULL>"."salarydata.xlsx";

select * from ext.VT_ExcelAdapter_testing_yoga_xlsx;

you can preview your data if that’s coming from local file.. File will be decrypted and you can see csv file shown in your file path

References