Skip to Content
Technical Articles
Author's profile photo Jacky Liu

Using Python to get data from s4 hana cloud by odata and use python pandas to handle data

In some situation , we need to extract data from s4hc and do some data handle work like we do  in SAP ECC or S4HAN OP system with abap . We can realize this with BTP sdk on BTP with JAVA. But today I want to try with python pandas .

 

refered blog :[Consuming OData service based on ODP extractor in Python | SAP Blogs](https://blogs.sap.com/2020/11/04/consuming-odata-service-based-on-odp-extractor-in-python/)

 

Prerequisite: communication arrangement for sales order and outbound delivery has been set in s4 hana cloud .

 

used dev tool: Pycharm

Install python library in cmd:

pip install requests

pip install pyodata

pip install pandas

pip install openpyxl

python code :

# This is a sample Python script.

import requests
import pyodata
import pandas as pd
import io
import json
from datetime import datetime

def handleData():
    # Use a breakpoint in the code line below to debug your script.
    SERVICE_URL = 'https://myxxxxx-api.saps4hanacloud.cn/sap/opu/odata/sap/API_OUTBOUND_DELIVERY_SRV;v=0002/A_OutbDeliveryItem/?$format=json'
    response = requests.get(SERVICE_URL,auth=('communication user', 'communication user password'), headers={"Prefer": "odata.maxpagesize=500", "Prefer": "odata.track-changes"})
    init_json = json.loads(response.content)
    length = len(init_json['d']['results'])
    print(length)
    # print(init_json['d']['results'])
    l_output = []
    l_record = []
    # l_record = ('DeliveryDocument','DeliveryDocumentItem','Plant','ReferenceSDDocumentCategory','ReferenceSDDocument','ReferenceSDDocumentItem','ActualDeliveredQtyInBaseUnit','BaseUnit')
    l_output.append(l_record)
    i=0
    while i < length:
        l_record = (init_json['d']['results'][i]['DeliveryDocument'],init_json['d']['results'][i]['DeliveryDocumentItem'],init_json['d']['results'][i]['Plant'],init_json['d']['results'][i]['ReferenceSDDocumentCategory'],init_json['d']['results'][i]['ReferenceSDDocument'],init_json['d']['results'][i]['ReferenceSDDocumentItem'],init_json['d']['results'][i]['ActualDeliveredQtyInBaseUnit'],init_json['d']['results'][i]['BaseUnit'])
        l_output.append(l_record)
        i=i+1
    # print(l_output)

    df_dn = pd.DataFrame(l_output,columns=['DeliveryDocument','DeliveryDocumentItem','Plant','ReferenceSDDocumentCategory','SalesOrder','SalesOrderItem','ActualDeliveredQtyInBaseUnit','BaseUnit'])
    # delete leading 0 from so item column
    df_dn['SalesOrderItem'] = df_dn['SalesOrderItem'].str.lstrip('0')
    # change data type from string to float for actual delivery quantity column
    df_dn[['ActualDeliveredQtyInBaseUnit']] = df_dn[['ActualDeliveredQtyInBaseUnit']].astype('float')
    #subtotal the columnn ActualDeliveredQtyInBaseUnit by SalesOrder,SalesOrderItem,BaseUnit
    df_dn = df_dn[['SalesOrder','SalesOrderItem','BaseUnit','ActualDeliveredQtyInBaseUnit']].groupby(['SalesOrder','SalesOrderItem','BaseUnit']).agg('sum')

    # get so item data from S4HC
    SERVICE_URL = 'https://myxxxxxx-api.saps4hanacloud.cn/sap/opu/odata/sap/API_SALES_ORDER_SRV/A_SalesOrderItem/?$format=json'
    response = requests.get(SERVICE_URL,auth=('communication user', 'communication user password'), headers={"Prefer": "odata.maxpagesize=500", "Prefer": "odata.track-changes"})
    init_json = json.loads(response.content)
    length = len(init_json['d']['results'])
    i=0
    l_output = []
    l_record = []
    while i<length:
        l_record = (
        init_json['d']['results'][i]['SalesOrder'], init_json['d']['results'][i]['SalesOrderItem'],
        init_json['d']['results'][i]['SalesOrderItemText'], init_json['d']['results'][i]['Material'],
        init_json['d']['results'][i]['OrderQuantityUnit'], init_json['d']['results'][i]['ConfdDelivQtyInOrderQtyUnit'],
        init_json['d']['results'][i]['TransactionCurrency'], init_json['d']['results'][i]['NetAmount'])
        l_output.append(l_record)
        i = i + 1

    df_so = pd.DataFrame(l_output, columns=[ 'SalesOrder','SalesOrderItem','SalesOrderItemText','Material', 'OrderQuantityUnit','ConfdDelivQtyInOrderQtyUnit','TransactionCurrency','NetAmount'])
    # change data type from string to float for ConfdDelivQtyInOrderQtyUnit column
    df_so[['ConfdDelivQtyInOrderQtyUnit']] = df_so[['ConfdDelivQtyInOrderQtyUnit']].astype('float')
    df_so[['NetAmount']] = df_so[['NetAmount']].astype('float')

    # join the so item table and dn item subtotal table
    df_so=pd.merge(df_so,df_dn,how='inner',on=['SalesOrder','SalesOrderItem'])
    print(df_so)
    file_name = 'so_' + str(datetime.now().strftime('%Y_%m_%d_%H_%M_%S')) + '.csv'
    # save the result in local file
    df_so.to_csv(file_name, index=True, header=True,encoding='utf-8')


# Press the green button in the gutter to run the script.
if __name__ == '__main__':
    handleData()

The result is easy to save as different format

Run program:

 

 

Best regards!

 

Jacky Liu

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.