SAP mass update via Excel made easy – SAP Gateway for Microsoft
SAP Gateway for Microsoft — Service Pack 01
What is the first thing that comes to your mind when you have to work with lots of data? When you need not only to update one entry, but ten or hundred?
Most likely you will think of Microsoft Excel. In the past, what you probably did was dump the data from your SAP system into an Excel sheet, work on the data from there and then somehow try to import this data back into your SAP system.
The process to do this has quite a few limitation:
- as soon as you dump the data into Excel — it is decoupled. All of a sudden you have two data sources to work on. The real, live data in your SAP system and an outdated snapshot in your Excel.
- Now you need to work on the data and you know that for some properties only certain values are allowed. Well, so you have to go back to the SAP system and check what are these allowed values (lets say for simplicity the allowed Country Codes). Only then can you go back to your Excel and can continue to work on the data — and hope that you have no typos
- You might have data / time fields (or some other defined data-types). Well, the problem is that when dumping your data into Excel all these data-types are lost. So you have to remind yourself, that you really keep track on what data goes where
- And finally — after you have updated all the data in Excel — you are ready to import the data back into SAP. The only problem is, that you do not know if someone else also updated the data in the meantime. So your import might overwrite whatever has been already updated…
You can see that a process which seems to be quite simple can be extremely complicated once you decouple data from the original datasource.
This is exactly what we are addressing in the latest Service Pack 01 for SAP Gateway for Microsoft.
As you probably have read we released SAP Gateway for Microsoft about a month ago. The response that we got was simply amazing.
Unlike the previous integration in Visual Studio which comes with great templates for Outlook, SP01 focuses on an end-user story for Microsoft Excel. End-users can use the SAP Gateway for Microsoft Excel add-on to easily bind their Excel sheets to an existing OData service.
Now this Excel sheet/”template” can be shared with end-users who have no idea about OData services, NetWeaver Gateway systems or SAP backend systems. They can simply open up the Excel file and interact with SAP.
Updates can be made directly from within Excel. Data can be refreshed anytime to see new changes in the backend system. Value help information in the SAP system can be made available as drop-downs in Excel and a conflict resolution can help the end-user to easily identify updates that were made by other users while the data was stored in the Excel sheet.
This latest Service Pack brings the power of SAP Gateway for Microsoft to Microsoft Excel. Now updating data from Excel is extremely simple and only requires the knowledge how to use Excel. Of course all the communication can be done via in an enterprise ready way, via a secure channel (e.g. HTTPS) and the users can be authenticated via Single Sign On, so that all corporate auditing and tracing functionalities are kept in place.
Scenarios range from mass data updates (e.g. updating your bank information with the new SEPA information like most of us in Europe currently experienced), maintaining your timesheet once a week / month in Excel (e.g. like several customers in consulting or employess on off-shore installations in the Oil & Gas industries), verifying and working on purchase orders before they are getting released or simply keeping track of OSS notes that have to be implemented across your SAP system landscape)
Even though we only released SP01 today we already have quite a few requirements from SAP internal stakeholders/colleagues to work with the SAP Gateway for Microsoft Excel add-on. I am sure you will also find lots of usecases that can make your life easier to work with data.
Go ahead and download it from the service marketplace, at http://service.sap.com/patches -> Support Packages and Patches – G” GWPAM” GWPAM 1.0 -> GWPAM EXCEL ADD ON 1.0
Great to know its out for customers. This should really make the life easy for everyone working on excel and mass data upload.
After installation of GWPAM, I am facing an issue in the User Logon step. After entering User Host, Server Port, Username, Password, I get the following error - "The underlying connection was closed. An unexpected error occurred on a send". Please let me know how to fix this issue.
can you send me the log files after getting this error message? If you have not changed the configuration, you should find the logs here: %temp%\GWPAMExcel-Logs
Hi Holger - I have sent the log file to you. Please advice.
Hi Holger ,
Yesterday I have posted one question on GWPAM. Posting the same here.
I want to create Purchase order data from excel . I have PO header and Item as entity in gateway services . I have associated PO Header with PO Items and declared navigation in SEGW. I have implemented method CREATE_DEEP_ENTITY.
While binding the gateway services, it just bind the header fields and i don't have provision to pass on the item fields . My requirement is to bind PO Header and Items together in excel and call the create_deep_entity method so that PO with item is created together in single payload.
In my case i am able to bind either header or Item data and it search for create_entity method and throw error.
Does excel support Deep Insert ? Can we create Hierarchical data using GWPAM excel.
I provided an answer in the forum, SAP Gateway for Microsoft excel
Hope it helps,
As of now I can see only a Windows 64 bit installation for the Excel Add-on. Is there a 32 bit (for Windows at least) available by any chance or plans to release in near future.
you should be able to use this version as well to install on a 32bit Windows.
I am interested in using the Excel add-on to generally view and update data in SAP ECC. I imagine the Excel add-on to be an Excel frontend to SAP ECC's [sm30] transaction - I enter the table name, the add-on will download data from the table; I click an update button, the add-on will update data in ECC with conflict resolution.
Is that how the add-on works? Can I use the Excel add-on to update any table? Or does the add-on only bind to available OData services - e.g. if there is a vendor master data view/update service, then the add-on can bind and update; but if there is no service for a custom z-table, then, the add-on cannot update?
If the add-on needs to bind to an OData service, is there an out-of-the-box service for generic table data download and upload?
Thanks and best regards,
you need to have the underlyning Odata service available. We do not connect to any evailable tablename. However, my colleagues Alexander had published a nice concept which outlines how to use a generic Odata service to access any table: Prototyping with Generic Table Reader
Thanks! I will read the other blog as well.
Thanks and best regards,
The tool is quite interesting. I viewed a number of training materials, and now got a few more questions on Excel add-in.
Question 1: SAP Gateway for Microsoft: Excel Conflict Resolution self-pace training (SAP Gateway for Microsoft: Excel Conflict Resolution - YouTube) at time 4:35 to 4:47 says enabling conflict resolution in OData Service after creation of GWPAM Excel Add-in require re-creation of the add-ins.
What does "re-creation of the add-ins" mean? Do I need to re-create the add-in from Visual Studio, or re-install the pre-packaged add-in installation file from SAP in my local desktop, or just rebind to the same service, or does it mean something else?
Question 2: In the same video at time 5:50, the video showed 2 Excel windows opened side-by-side. The left window does not have "Bind" button. I assume the left window is deployed to users so they can't just bind the way they want. How to hide the button?
Question 3: Conflict resolution with E-tag. In the same video at time 2:48, the video showed us just one ETag column per entity. The video recommended people to choose EPM Date Time structure. This works for the table in the demonstration. For many other SAP tables, the time stamp is separated into a date field and a time field. Is it possible to implement conflict resolution with ETag for a date field and a time field?
Question 4: Does the add-in support modifying tables where modification will generate transports?
Question 5: I see in the training videos that a user needs to enter user ID and password to bind excel with OData service. I don't remember seeing user entering credentials when uploading data changes.
When a business expert configured the excel add-in and then distribute the excel file to a business user, and then the business user users the file to upload data into SAP, whose user ID actually changed the data in SAP?
Thanks and best regards,
wow! You really took your time watching the videos -- thank you very much for that! I hope they were valuable to you!
To answer your questions:
1) We are just talking about re-binding the data in the Excel add-in (at least for the BPX scenario) [see second question]. In order to be aware of the if-match features in the OData service, the Add-in has to re-read the $metadata information. This is not done during runtime, so a new binding is required.
2) In the video we wanted to show two scenarios: on the right hand side you can see the out of the box, ready to use Excel BPX scenario. On the left hand side you can see an add-in that was developed using the Gateway for Microsoft Excel templates that is available for Visual Studio. In this development scenario you can be very flexible in the ribbons, features, icons, ... that you want to present in Excel (and when the OData services changes, you also have to rebuild the add-in)
3) Yes, you can define an etag from multiple properties. However, this cannot be done with the Service Builder, but has to be developed in the MPC. Alternatively you could create a property that calculates a timestamp from the date and time field and use that as the etag property.
4) Not sure I understand your question. Usually you do not transport data from one system to the other. Can you provide more information on your scenario?
5) There is a difference between the initial binding / browsing the catalog service and the actual end-user screen. All Gateway for Microsoft based "flavors" support Single Sign-on. So The power-user might user his/her basic login credentials to bind the Excel sheet to an Odata service, but the end-user can then use SSO (Kerberos, SAML2.0, X.509, ...) to connect to the backend system. This also makes sure that the end-user's user-id is always used when writing data back to SAP. This not only helps solving a lot of security issues, but also makes monitoring / auditing ("who updated what") much easier.
Thanks for the detailed explanation. That was very helpful.
For #4, I was just thinking of potential use cases. There are standard SAP SD transportable configurations that require lots of entries. Examples would be route determination and shipping point determination. It will be helpful if users can maintain them en masse with an excel tool. But a potential issue is that maintaining these configurations result in creating a transport. I was wondering how the excel add-in can manage transports. But given the backend OData service is eventually the one that is managing table updates, I guess some code in backend system has to handle the transport.
Thanks and best regards,
Hello Holger ,
Can we restrict binding gateway services from SAP to specific users. Only few users are allowed to bind service to excel and others are only allowed to perform CRUD operations from GWPAM excel.
Do we have specific roles which can be assigned to different business users.
SAP Gateway for Microsoft does not add an additional security layer on top (we "only" do Single Sign-On). You could try to restrict access to services on a pure SAP / SAP Gateway layer and by this decide which users can bind and which users can perform CRUD operations.
If this is not good enough for you, then the Gateway for Microsoft Visual Studio Excel template allows you to custom build your solution. There you could have a fixed binding and only allow end-users to perform CRUD operations -- without any binding at all.
the first step that you need to do is expose your data via an OData service (using SAP NetWeaver Gateway). Then you can consume this OData service via the SAP Gateway for Microsoft Excel add-in. That should be it 🙂
Hi Holger ,
Is it that there are existing services available with some latest versions of IWBEP addon for the material update or is it that we need to develop our own service for the CRUD operations ?
How the F4 helps are handled in excel?Is that each field in the excel which has an f4 help will have to trigger service call each time to fetch data from the backend ?
Thanks and Regards
you most likely will have to develop your own OData services for material updates. However, using the SAP Gateway Service Builder that should be fairly simple.
There are also plenty of other OData services available which might alrready be suitable for you which you can take as a starting point (e.g. I am thinking of the over 500 Fiori apps and their underlying OData services):
The F4 helps are handled as "normal" navigation attributes. Maybe you can also take a look at this discussion GWPAM - Input help is not enabled
Hi Holger ,
Thanks a lot for your reply.
Regarding the F4 help the main concern i have is that ,in case of mass create and Update of material for all the fields which supports F4 if the odata has to be triggered each time it would not be user friendly .Is there any way the data once fetched can be stored locally ?
We use the GWPAM 2 version.
Thanks and Regards
yes, that is possible. You can cache the F4 value helps in Excel so that you do not have to look it up everytime.
Currently we are facing some issues with logging.When i am executing my Excel from visual studio i could see if there are any errors its getting logged .But once we create a setup file and then try to run the excel with some error scenarios its not generating the logs in other systems.It would be great if you could guide on what could be the possible issue here .
Thanks and Regards
Can you please reply to the below.
You mean it is not generating logs in Gateway System?
What are the error scenarios you are running?
In which Transaction you are checking the logs?
I am not mentioning about the logs in gateway system.I am mentioning about the Gwpamlogs which we check in the device manager of you local machine.When the program is executed via visual studio,when the excel is executed and encounters an error a log gets generated in the system ,we can see the logs under the device manager application logs.But when we create a set up file and then try to execute the excel and when there are errors no errors get logged .
Errors gets logged in gateway system.But i am not mentioning abot the gateway error log.
Thanks and Regards
1. What is the ideal way to raise E/W/I messages in GWM addin?
Currently I see only exceptions as an option, since GWM addin do not support message handler in response header; though Gateway itself supports it.
Due to above, when Gateway service's Changeset_process method tries to process multiple instance, raising an exception for error case will even affect valid data.
2. Where can I find documentation on how to implement F4 helps in gateway service? Though there are few thread discussing in problems faced, I did not find any documentation
for #1 I unfortunately don't think there is much you can do with the End-user Excel add-in. As you probably know we also have an GWM Excel template for our Visual Studio add-in from which you should be able to raise any messages you like.
for #2 I can recommend this video which talks about the F4 integration.
Hope this helps!
We have tried Excel add-in with SAP provided sample service ZGWSAMPLE_SRV >> BusinessPartnerCollection and we are very excited.
Problem is that we are facing issues - may be because
we are having many Odata service using "Table type inputs". Many cases we have header and line item scenarios - e.g deep insert, fetch.
Can you please help - How can we use the GWM excel add-in for such complex service? If there is any similar example we can check.
Please feel free to share if there is a particular way to write such service to be compatible with excel add-in. Our abap developers looking for guidance on this.
Thanks and Regards,