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

1. Open File Dialog – It will be used to open the excel file from your machine
2. Add username & password in input parameters of automation
3. Encode String : In this activity encode your credentials ( We will use them during service call )
4. 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
5. Open Excel Instance : Just drag and drop this activity.
6. 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                                                                                                                                                           
7. Log Message:  Display Excel return values using Log Message activity for confirmation if this is working fine .                                                                                                                                                                                                                                                                              Data looks good here 🙂                                                                                                       
8. 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.
9. 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
10. Release excel instance: Add this activity to release excel instance
11. 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