How to update SAP Netweaver Gateway from Excel

The CData Excel Add-In for SAP Netweaver Gateway enables you to edit and save SAP Netweaver Gateway data directly from Excel. This article explains how to transfer data from Excel to SAP Netweaver Gateway. This technique is useful if you want to work on SAP Netweaver Gateway data in Excel and update changes, or if you have a whole spreadsheet you want to import into SAP Netweaver Gateway. In this example, you will use the SalesOrderLineItems table; however, the same process will work for any table that can be retrieved by the CData Excel Add-In.

Establish a Connection

If you have not already done so, create a new SAP Netweaver Gateway connection by clicking From SAP Netweaver Gateway on the ribbon.

SAP Gateway allows both basic and OAuth 2.0 authentication. You can use basic authentication to connect to your own account, or you can use OAuth to enable other users to retrieve data from your service with their accounts. In addition to authenticating, set the following connection properties to access SAP Gateway tables.

• Url: Set this to the URL of your environment, or to the full URL of the service. For example, the full URL might appear as: https://sapes5.sapdevcenter.com/sap/opu/odata/IWBEP/GWSAMPLE_BASIC/. In this example, the environment URL would just be: https://sapes5.sapdevcenter.com.
• Namespace: Set the appropriate Service Namespace. In the example above, IWBEP is the namespace. It is optional if the full URL to the service is specified.
• Service: Set this to the service you want to retrieve data from. In the example above, the service is GWSAMPLE_BASIC. It is not required if the full URL is specified.

Authenticate via Basic Authentication

In basic authentication, you use your login credentials to connect. Set the following properties:

• User: This is the username you use to log in to SAP Gateway.
• Password: This is the password you use to log in to SAP Gateway.

Authenticate via OAuth Authentication

You can connect to SAP Gateway using the embedded OAuth connectivity (without setting any additional authentication connection properties). When you connect, the OAuth endpoint opens in your browser. Log in and grant permissions to complete the OAuth process. See the OAuth section in the online Help documentation for more information on other OAuth authentication flows.

Retrieve Data from SAP Netweaver Gateway

To insert data into SAP Netweaver Gateway, you will first need to retrieve data from the SAP Netweaver Gateway table you want to add to. This links the Excel spreadsheet to the SAP Netweaver Gateway table selected: After you retrieve data, any changes you make to the data are highlighted in red.

1. Click the From SAP Netweaver Gateway button on the CData ribbon. The Data Selection wizard is displayed.
2. In the Table or View menu, select the SalesOrderLineItems table.
3. In the Maximum Rows menu, select the number of rows you want to retrieve. If you want to insert rows, you need to retrieve only one row. The Query box will then display the SQL query that corresponds to your request.
4. In the Sheet Name box, enter the name for the sheet that will be populated. By default the add-in will create a new sheet with the name of the table.

Insert Rows to SAP Netweaver Gateway

After retrieving data, you can add data from an existing spreadsheet in Excel.

1. In a cell after the last row, enter a formula referencing the corresponding cell from the other spreadsheet; for example, . 👁 A local copy of a table. One row will be inserted. (QuickBooks is shown.)
   
2. After using a formula to reference the cells you want to add to SAP Netweaver Gateway, select the cells that you are inserting data into and drag the formula down as far as needed. The referenced values you want to add will be displayed on the SalesOrderLineItems sheet. 👁 The range of changes to update. (QuickBooks is shown.)
   
3. Highlight the rows you want to insert and click the Update Rows button.

As each row is inserted, the Id value will appear in the Id column and the row's text will change to black, indicating that the record has been inserted.