Technical Articles
Send XL to SAP using iRPA + OData
Hi ,
Let’s see how you can automate your processes easily by leveraging the powerful iRPA and a very strong backend technology like SAP OData.
Problem Statement:
- Send your Excel data to SAP System – Just Store it somewhere in custom table Simple
Solution Proposed:
- Develop one RPA bot and integrate OData Endpoint and Make a POST Call to Update/Insert/Modify Information in SAP System.
- Once your data is in SAP system you will get the chance to play and process your information based on the need -> Here I am trying to explain the possibility of integration and development with iRPA and OData
A Simple Architecture:
Architecture
- Upload your excel file from desktop to iRPA
- Collect your data in some variable
- Make a POST call to backend SAP System and Update your custom table
Let’s start…
- Get your excel ready with 4-5 Simple columns
- Create a new project upload_excel in Cloud Studio of RPA and create new automation for your project
Add following Activities one by in RPA Automation
- Open File Dialog – It will be used to open the excel file from your machine
- Add username & password in input parameters of automation
- Encode String : In this activity encode your credentials ( We will use them during service call )
- Custom Script : Add one input parameter name it as Credentials type string / One Output parameter name it as Token Note: We will Add the Service call later – Let’s finish the RPA Piece first
- Open Excel Instance : Just drag and drop this activity.
- Excel cloud link: Drag and drop this activity and make few changes – Drag and drop your excel file here and create form data for your columns – Just add all the columns in form data – this will be your schema for the data which you are trying to upload through your sheet. Map the path with workbook path from previous step
- Log Message: Display Excel return values using Log Message activity for confirmation if this is working fine . Data looks good here 🙂
- Lets create one backend OData Service which take same structure as we have excel data and make a post call to the service to update this information in backend.
- I assume you have services ready for your data – Now this is the time to call 2 services first one is to get x-csrf-token second to post your data
- Release excel instance: Add this activity to release excel instance
- Custom Script: Add custom script and name it POST_DATA for posting excel data to SAP
Fetch Token
async function fetchToken() {
const token = {
resolveBodyOnly : false,
method: 'GET',
url: 'your Odata Service URL',
headers: {
'Accept' : 'application/json',
'Content-Type': 'application/json',
'Authorization' : 'Basic ' + credentials,
'x-csrf-token': 'fetch'
}
};
try {
const response = await irpa_core.request.call(token);
return response;
} catch (error) {
const csrfToken = error.response;
return error;
}
}
let response = await fetchToken();
return response.headers['x-csrf-token'];
POST CALL
- Everything will be same as GET – Make this small change – Add body for your Data .
body:JSON.stringify(myObject)
We got the token here now move to next call – POST Call – Your post call should look like this.
Execute your RPA Script and hope you will get the Data in backend like this –
Notes:
- Few things to notice – once we get the data in backend – you can insert this into your custom table of anything do whatever is required
- Make your OData Service – Make sure to follow the column naming convention – As it is
- In RPA – Add your User Name and Password which will be used in your service calls
RPA Should Look like this:
In the end – Keep things simple 🙂
Your feedback / Suggestions / Question-Comments are most welcome 🙂
Thanks,
Shivam
That is not actually all that simple.
Anyway you probably are not aware that every month for at least the last ten years there has been one or two blogs posted on the SAP Community Site regarding th best way to upload/download Excel data to/from an ABAP system.
Every time the poster is pointed at ABAP2XLSX.
https://github.com/abap2xlsx/abap2xlsx
I understand that this blog is more about RPA than any specific use case, but I just thought I would mention ABAP2XLSX.
Cheersy Cheers
Paul
Hi Paul ,
yes that’s totally fine — but in this I just thought to share the possibility of getting the excel data in backend using RPA — the use which we implemented was a bit different but this small piece I just added and thought to share with community. And more to it Is using ODATA integration as well with RPA in case someone is looking for.
thanks ,
shivam