Upload data into SAP S/4HANA Cloud CBO from MS Excel
In this blog, we’ll build on the $batch OData feature introduced in the first blog in order to load data into a SAP S/4HANA Custom Business Object (CBO) from Microsoft Excel using a macro written in Visual Basic for Applications (VBA). The same CBO from the previous blog will be used in this blog to upload the data.
This scenario would allow a user to create a spreadsheet of data for a CBO with the required fields and then click a button to trigger a macro which would upload and create the records in S/4HC using VBA.
Essentially, we will use VBA to create the same payload as we used in Postman in the first blog. There are many ways to accomplish this task in VBA and this is one example.
The macro enabled file can be set up as follows:
Metadata Tab
On this tab, capture the technical fields that will be used to call the CBO via OData. Namely, the URL, CBO Name, and Service User.
I also set up logic to allow the user to input the number of records that you want to process in one OData call (i.e. number of records per batch). SAP recommends no more than 50 per batch. In the example below, if I had 38 fields, there would be 4 update calls made to the CBO (10,10,10,8).
CBO Data Tab
This tab contains the field names (matching exactly to how they appear in the OData metadata including case sensitivity) and the actual data.
VBA Code
The next step is to write the VBA code to form the $batch requests.
There are three main pieces to the VBA.
The UploadDataToCBO function is the main function. It determines the number of batches, makes calls to generate the batch payload using function CBOPayload and then calls the PostCBOData to submit the batch request to the S/4HANA Cloud system.
UploadDataToCBO Function
Function determines number fields, how many rows, whether to use multiple batches, asks user for the service user password, makes call for batch payload and ultimately calls the OData CBO API to post the data.
Sub UploadDataToCBO()
'
' UploadDataToCBO Macro
'
ClearResults
containsError = False
Dim resultRow As Integer
resultRow = 2
' Get the Number of Columns to set up the field names into a string array
Dim lastColumn As Long
lastColumn = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column
Dim fieldNames() As String
ReDim fieldNames(1 To lastColumn) As String
For d = 1 To lastColumn Step 1
fieldNames(d) = ActiveWorkbook.Sheets(SHEET_TITLE_CBO).Cells(1, d).Value
Next d
Dim CBOName As String
CBOName = ActiveWorkbook.Sheets(SHEET_TITLE_META).Cells(3, 2).Value
Dim numOfRows As Integer
numOfRows = ActiveWorkbook.Worksheets(SHEET_TITLE_CBO).Cells(Worksheets(SHEET_TITLE_CBO).Rows.Count, "A").End(xlUp).Row - 1
Dim cboCount As Integer
Dim batchAmount As Integer
Dim batchCallNum As Integer
Dim response As String
batchCallNum = 1
batchAmount = Sheets(SHEET_TITLE_META).Cells(5, 2).Value
'set user data
Dim strPass As String
Dim strUser As String
' Set up Data to call
strUsr = ActiveWorkbook.Sheets(SHEET_TITLE_META).Cells(4, 2).Value
strPass = InputBox("Enter Password for user " & strUsr)
strURL = Sheets(SHEET_TITLE_META).Cells(2, 2).Value & "/$batch"
Dim arr
Dim doneProcCBO As Boolean
Dim startRow As Integer
Dim endRow As Integer
Dim currRow As Integer
Dim totalBatches As Integer
currRow = 2
startRow = 2
doneProcCBO = False
totalBatches = numOfRows / batchAmount
If numOfRows Mod batchAmount > 0 Then
totalBatches = totalBatches + 1
End If
If MsgBox("Application will now process " & numOfRows & " records in " & totalBatches & " total batches." & vbCrLf & "Please be patient and check the status bar for progress." & vbCrLf & "Click Yes to proceed.", vbYesNo) = vbYes Then
If numOfRows > batchAmount Then
endRow = batchAmount + startRow - 1
Do
Application.StatusBar = "Processing: Batch " & batchCallNum & " - " & Format(batchCallNum / totalBatches, "Percent")
payload = CBOPayload(currRow, endRow, numOfRows, fieldNames, CBOName, lastColumn)
If debugMode = True Then
ActiveWorkbook.Worksheets(SHEET_TITLE_PAYLOAD).Shapes("Textbox " & batchCallNum).TextFrame.Characters.Text = payload
End If
response = PostCBOData(strURL, payload, strUsr, strPass, batchCallNum)
batchCallNum = batchCallNum + 1
payload = vbNullString
If currRow > numOfRows Then
doneProcCBO = True
End If
startRow = currRow
endRow = endRow + batchAmount
Loop While doneProcCBO = False
Else
' send all rows in the payload
endRow = numOfRows + 1
'generate Payload
payload = CBOPayload(currRow, endRow, numOfRows, fieldNames, CBOName, lastColumn)
response = PostCBOData(strURL, payload, strUsr, strPass, batchCallNum)
End If
Application.StatusBar = False
' PostCBOData(strURL,payload,strPass,batchCallNum,resultRow)
Else
'user clicked No button
End If
End Sub
CBOPayload Function
This function generates the batch payload.
Function CBOPayload(currRow, endRow, numOfRows, fieldNames, CBOName, lastColumn) As String
Dim payload As String
payload = "--batch_mybatch" & vbCrLf & "Content-Type: multipart/mixed; boundary=changeset_mychangeset1"
For e = currRow To endRow
If currRow <= numOfRows + 1 Then
payload = payload & vbCrLf & vbCrLf & "--changeset_mychangeset1" & vbCrLf & "Content-Type: application/http" & vbCrLf & "Content -Transfer - Encoding: binary" & vbCrLf & vbCrLf
payload = payload & "POST " & CBOName & " HTTP/1.1" & vbCrLf & "Content-Type: application/json" & vbCrLf & vbCrLf & "{" & vbCrLf
For f = 1 To lastColumn Step 1
payload = payload & Chr(34) & fieldNames(f) & Chr(34) & ": " & Chr(34) & ActiveWorkbook.Sheets(SHEET_TITLE_CBO).Cells(e, f).Value & Chr(34)
If f = lastColumn Then
payload = payload & vbCrLf & "}" & vbCrLf
Else
payload = payload & "," & vbCrLf
End If
Next f
End If
currRow = currRow + 1
Next e
payload = payload & vbCrLf & vbCrLf & vbCrLf & "--changeset_mychangeset1--" & vbCrLf & vbCrLf & vbCrLf & "--batch_mybatch--" & vbCrLf
CBOPayload = payload
End Function
PostCBOData Function
In this function, notice the first call to fetch the x-csrf-token before posting the data followed by the POST to update the CBO.
Function PostCBOData(strURL, strPostData, strUser, strPass, batchCallNum) As String
Set objWinHttp = CreateObject("Msxml2.XMLHTTP.6.0")
objWinHttp.Open strMethod, strURL, False, strUser, strPass
objWinHttp.SetRequestHeader "x-csrf-token", "Fetch"
objWinHttp.SetRequestHeader "Cache-Control", "no-cache,max-age=0"
objWinHttp.SetRequestHeader "pragma", "no-cache"
objWinHttp.send
strToken = objWinHttp.getResponseHeader("x-csrf-token")
If Len(strToken) = 0 Then
MsgBox "Error: Most likely the password is not correct."
containsError = True
Else
objWinHttp.Open "POST", strURL, False, strUser, strPass
objWinHttp.SetRequestHeader "x-csrf-token", strToken
objWinHttp.SetRequestHeader "Content-Type", "multipart/mixed; boundary=batch_mybatch"
objWinHttp.send (strPostData)
PostCBOData = objWinHttp.ResponseText
If debugMode = True Then
ActiveWorkbook.Worksheets(SHEET_TITLE_RESULTS).Cells(1, 15).Value = PostCBOData
End If
'ActiveWorkbook.Worksheets(SHEET_TITLE_RESULTS).Cells(1, 15 + batchCallNum).Value = PostCBOData
ActiveWorkbook.Worksheets(SHEET_TITLE_RESULTS).Cells(batchCallNum, 1).Value = "Batch " & batchCallNum
If InStr(PostCBOData, "HTTP/1.1 400 Bad Request") > 0 Then
ActiveWorkbook.Worksheets(SHEET_TITLE_RESULTS).Cells(batchCallNum, 2).Value = "ERROR"
ActiveWorkbook.Worksheets(SHEET_TITLE_RESULTS).Cells(batchCallNum, 3).Value = PostCBOData
Else
ActiveWorkbook.Worksheets(SHEET_TITLE_RESULTS).Cells(batchCallNum, 2).Value = "SUCCESS"
End If
Exit Function
badPassword: MsgBox "The password is not correct."
End Function
Execution and Results
Now it’s time to execute the code. I created a control sheet in a different tab with buttons to upload data and/or reset data. Clicking on the “Upload New Data” button will call the UploadDataToCBO function.
Enter service password.
Confirm one last time that you want to upload the data
And then on the results tab we see the output of each batch. If there was an error, the payload is logged in Column C and you can see what the problem was with the batch (for example, duplicate key).
And we can see the data in S/4HC
I hope you found this blog helpful.
Best Regards,
Marty
very cool!
Thanks for this
thanks for valuable information buddy really loved ur stuff
https://dynamoinfotech.com/sap-s4hana-cloud/
Awesome stuff put together Marty
Thumbs up !
Great Blog Marty!
Hello Marty,
I am unable to fetch x-csrf-token in the response, please guide me .
Raju Mammula
Hi Raju
I updated the calling method, I think an Excel patched changed the way we have to get the x-csrf-token. Can you try with the Msxml2.XMLHTTP.6.0 object as updated in the blog and let me know? Several statements change in that section...
Thanks,
Marty
Hi Marty McCormick
First of all, thank you very much for sharing this information with us!
I have try with the Msxml2.XMLHTTP.6.0 object, but I am not able to get the token in the response.
Do you have any idea why this is happening?
Thank you very much in advance for your comments,
Best regards,
Jorge
Hi Jorge. Were you able to get the token in the response?
Regards
Rosaria
Hi Marty,
I also cannot get the token value, could you please give some ideas for me.
Thanks.
Best Regards,
Ranson
Hello Marty ,
Good article …would it be possible to upload the data through Excel to the standard Objects ( let’s say Purchase requistion) using ODATA API? ( It has the dedicated Sevice URL API)
RegardS
Naresh J
Hi Naresh
Yes, any OData API could follow this approach.
Thanks,
Marty
Hello Marty,
I also cannot get token, could you please help to give some suggestions for me.
Thanks.
Best Regards,
Ranson
Hello Marty ,
How do we handle date to be uploaded ?
If i provide the date in any formats like "2020-08-12 " or "2020/08/12" it is throwing conversion error
If i provide the date in EDM format "2020-08-12T00:00:00" or /13456745676/" it is throwing some offset error stating it has Invalid value .
Can you help if you have any idea on this .
Regards
Raju Mammula
Ecxellent post and so helpful! Thank you a lot!
Johannes
Hi Marty,
I tried with the Msxml2.XMLHTTP.6.0 object, but I am not able to get the token in the response.
Regards
Rosaria
Hello Marty,
Very Helpful and Excellent post.
Is there any way that we can update existing entries in CBO using MS Excel? Via PUT Method?
Thanks,
Ricardo
Hi Ricardo
Yes, same logic could be used with PATCH method to update existing entities using the UUID for the record.
Thanks,
Marty
Thanks Marty,
Appreciated your response.
Changing 'POST' to 'PATCH' seems not working. How is UUID used to update rows? Is it possible to use the Key fields?
Thanks,
Ricardo
Hi Marty,
Thanks for your effort and detailed explanation...!
Can you please share the excel file for us to refer and create our own as per our requirement?
Thank you in advance.