The purpose of this exercise is to access the data to & from Excel to SAP HANA. This will demonstrate the bidirectional transfer of the data without using import/export options in SAP HANA Studio.

Live data can be viewed whenever we open the excel file.

STEP 1: Creating a DB table in SAP HANA:

I have created a simple table with 2 field using SAP RIVER Code. Alternately we can also use the table which is created by .HDBTABLE file

@OData

application Rivermohas98.HelloExcelApp{

  export entity exceldata {

  key element employ: String(10);

         element skill: String(10);

       }

}

       

STEP 2: Creating .XSODATA:


I haven’t used the OData created by SAP RIVER.  The Excel file has difficulty to read the OData created by SAP RIVER. So I have alternately created an OData using .XSODATA with name rivertab.xsodata

service namespace “rivermohas98.services” {

“Rivermohas98″.”Rivermohas98::HelloExcelApp.exceldata”

as “Excel”;

}


Testing of OData:


I have inserted a record in the table using Insert SQL.


Table.jpg

  oData Test.txt.jpg


STEP 3: Retrieve SAP HANA DB from Excel:


Now I have an OData which can be accessed. I have chosen an .XSLM file, considering Macros/VBA script can be saved in .XSLM files.


Calling Odata using Power Query:

    1. My next step is to access the OData in this Excel File. I am sure there must be multiple ways to access it, I tried using the Excel Add-in PowerPivot.

excel odata feeds.jpg

          2. A pop-up will be displayed for the OData Feed. I have entered the OData which I have created using .XSODATA

/wp-content/uploads/2014/07/odata2_502562.jpg

          3. Query Editor will be opened. Check the feed and Click on Apply & Close

/wp-content/uploads/2014/07/odata3_502563.jpg

          4. You will promted for User ID and Password. Alternately you can set the Credentials here.

/wp-content/uploads/2014/07/odata4_502564.jpg

          5. The below screenshot will help to set the OData Refresh settings. Also you can see the data has been retrieved from SAP HANA DB.

/wp-content/uploads/2014/07/odata5_502567.jpg


STEP 4:Sending Data from Excel to SAP HANA DB:

Now lets see how to post the data. Data will be posted to SAP by Python code.


  1. Installation of Python and XLRD library:

I have installed Python33. The next important step is to install XLRD. XLRD is a library to read .XSLS, .XSLM files in python. I have downloaded the package “xlrd-0.9.3.tar.gz”.

Once downloaded, Unzip, go to Command line (RUN->CMD), Navigate to the folder and run “python setup.py install” in command line. Follow other sources to install python and xlrd.

    2.  Code in VBA:

A code has been written to trigger the Python File in Excel. Go to ALT+F11 and use the below code to trigger your Python file.

Write your code in Worksheet, this will have multiple options when to run this code. I want to trigger the Python code on Saving of the Excel file.

/wp-content/uploads/2014/07/vba_502572.jpg

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

Dim pyPrgm As String, pyScript As String

pyPrgm = “C:\Python33\python.exe “

pyScript = “C:\pyt\LoadExcel.py”

Call Shell(pyPrgm & pyScript, vbMaximizedFocus)

End Sub

Explanation for the Highlighted in RED

pyPrgm = “C:\Python33\python.exe ” -> This is the path where Python is installed.

pyScript = “C:\pyt\LoadExcel.py” -> This is where my python code stored to trigger OData.

     3.  Python Code:

Python code is in LoadExcel.py stored in the above mentioned path.

#! /usr/bin/python

# All import files may not require

import os

import requests

import json

import csv

import sys

import xlrd

# OData URL. I am using the OData Created by SAP RIVER. Give the full address of the server.

appURL = ‘http://v………..8008/Rivermohas98/odata/Rivermohas98.HelloExcelApp

# Credentials

auth = ‘userid’,’password’

s = requests.Session()

  1. s.headers.update({‘Connection’: ‘keep-alive’})

headers = {‘X-CSRF-TOKEN’: ‘Fetch’}

r = s.get(url=appURL, headers=headers, auth=auth)

CSRFtoken = r.headers[‘x-csrf-token’]

print(“CSRFToken: ” + CSRFtoken)

headers = {‘X-CSRF-TOKEN’: CSRFtoken}

url = appURL + “/exceldata”

# Your file has to be in this location. This is the path which we have given in Excel (VBA code). Full path is required

workbook = xlrd.open_workbook(‘C:\pyt\employ.xlsm’)

# Give the worksheet  name

worksheet = workbook.sheet_by_name(‘Sheet1’)

num_rows = worksheet.nrows – 1

num_cells = worksheet.ncols – 1

curr_row = 0

while curr_row < num_rows:

                curr_row += 1

                row = worksheet.row(curr_row)

                print(  row[0], row[1])

                print(  row[0], row[1])

                data = ‘{“employ”: ” ‘ + str(row[0].value) + ‘ “, “skill”: ” ‘ + str(row[1].value) + ‘ ” }’

                # print(“Check Data: ” + data)

                r = s.post(url, data=data, headers=headers)

     4.  Test your Python Code:

You can test your Python Code from command line. Below screenshot is for reference.

python test.jpg

STEP 5: Post the data to SAP HANA DB from Excel:

     1. Now add an entry in Excel:

add entry.jpg

     2. Check the result in SAP HANA:


Now we have two records.

excel to sap.jpg

STEP 6: Post the data to SAP HANA DB from Excel:


Now create one more record in SAP HANA table and check whether it is updating the Excel. As Similar to Step 2 and check the result in Excel.

insert 3rd rec.jpg

/wp-content/uploads/2014/07/final_502592.jpg

Currently I have only used OData ‘POST’ operation. I believe further operations can be used like PUT & DELETE or triggering the Python Code for GET operation instead of using Excel OData feeds.

Thanks to Philip MUGGLESTONE for his video on Python @ SAP HANA Academy

Thanks for reading.

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

Leave a Reply