Technical Articles
if( !TLDR){ Upload .XLSX file to ZTable(Custom) on S4HANA Server }
Hello Everyone,
The blog is inspired from a real life challenge I faced while trying to upload a excel to the custom Z Table.
At the start, considering the vast amount of ABAP content, this seemed like a straight forward job. I wanted to finish it in 30 minutes. It took me 3 days.
Caveat: I am not a Pro-ABAPer. I m one who knows ABAP but works on UI/UX.
As a developer, my first step towards writing code:
I Googled It.
The results were many, which immediately boosted by confidence of accomplishing the task. However, I found that most of solutions which were proposed, were not relevant to S4HANA(1709) system, which was the one I was working on.
The function module : ALSM_EXCEL_TO_INTERNAL_TABLE does not exist
The function module : TEXT_CONVERT_XLS_TO_SAP does not exist
So, final resort, GUI_UPLOAD .
The code approach is very simple.
Solution:
- Convert the .XLSX to TXT(Tab delimited file).
HOW ? – Open the .XLSX file and choose option File-Save As and choose the file type as Text(Tab-Delimited) (*.txt)
2. Ensure that there is no header line ex: Column names are removed and only data is retained in the TXT file.
3. Create a executable program in SE38.
4. Call the function module – GUI_UPLOAD using Pattern option.
5. To the function module, pass the following parameters:
a. Path to the file in your local disk – FILENAME
b. field separator “#” – HAS_FIELD_SEPARATOR
c. An internal table of the type of the actual ZTable. ex: DATA: ZINTERNAL TYPE TABLE OF ZCUSTOMTABLE.
6. (Optional) Sit back and watch the data load into the internal table in debug mode. 🙂
7. Finally, look through internal table, and insert into the actual ZTable using the temporary work area.
DATA: ZINTERNALTABLE TYPE TABLE OF ZCUSTOMTABLE.
DATA: WA_ZINTERNALTABLE TYPE ZCUSTOMTABLE.
PARAMETERS: P_FILE TYPE STRING DEFAULT '<Path to your actual text file>' OBLIGATORY. " File Name
CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
FILENAME = P_FILE
HAS_FIELD_SEPARATOR = '#'
TABLES
DATA_TAB = ZINTERNALTABLE.
IF SY-SUBRC EQ 0.
LOOP AT ZINTERNALTABLE INTO WA_ZINTERNALTABLE.
INSERT ZCUSTOMTABLE FROM WA_ZINTERNALTABLE.
ENDLOOP.
ENDIF.
Thats it.
Hopefully, its not TLDR.
Hope it helps.
Disclaimer: The opinions expressed in this blog are purely personal and has to relevance to anything/anyone else.
You didn’t google enough: the advice of “most influential people” is to do anything Excel via abap2xlsx (works in both directions).
Hi Sandra,
My Bad that I didn't explore it more. However, when I checked this report, the option is to create a custom report and copy the file code - https://docs.abapgit.org/guide-install.html.
Considering that the scenario to upload data from excel is once in a while, mostly for testing and once in production the approach with abap2xlsx does not seem the fastest way out.
Maybe, the blame for not using it falls on my developer - impatience. 🙂
Regards,
Sharath
I just checked the two function modules ALSM_EXCEL_TO_INTERNAL_TABLE and TEXT_CONVERT_XLS_TO_SAP, they still exist on my same (apparently) system S/4HANA 1709 on premise (S4CORE 102).
Hi Sandra,
I am no expert, but I checked again with my ABAP team and I was told that these FMs are obsolete in S4HANA .
As shown in screenshots, they don't exist on our system at least.
Regards,
Sharath
Hi Sharath,
What version of NW are you on? Were you in core SAP? Or add in module - CRM I believe is still separate, HR... That would make a difference of the function modules. 😉
ABAP2EXCEL is a perfect option in standard SAP. Downloading and installing it is easy - see Sandra's comment. However I believe it does use "TEXT_CONVERT_XLS_TO_SAP", or something similar - I can't remember. If you aren't on the core system, you might not have them.
With that said, this is a nice work around. I love that you used a "Real Life" example. I hope you enjoy reading the comments as they almost always enhance the Blog itself. You get to learn something by reading them. Very cool!
Michelle
The comments are the best part and I always look forward to it.
That's the best way to learn and to be sure that I am not alone in this island 🙂
TEXT_CONVERT_XLS_TO_SAP isn't part of the "basis only" stack. (I just checked).
Thanks for the confirmation and the information 🙂
Little misunderstanding but I guess we agree: they exist and can be used, but are tagged obsolete.
Agree to agree. . 🙂
This process was pretty much the standard for many years. It's simple and quick. I've you've got a halfway competent developer you can cope with header lines in the txt or csv file.
As well as abap2xlsx, which is the best solution to use, if you need to read actual xlsx files there are methods using transformations. There are blogs about it on here. I've written one solution where the transformation is generated and deleted on the fly, which, coupled with RTTS, allows for generic upload of xlsx files.
Great.
Matthew Billinghamif you can share the link to the blog, it could be very helpful..
I would suggest to put in abap2xlsx in the SAP Community search at https://cse.google.com/cse?cx=013447253335410278659:k8ob9ipscwg
Hi Gregor, Thanks for pointing it out. As I told, I did find this and wanted to use it.
But, my scenario was simple. I wanted to upload data to a custom table.. and requirement was not to even create a report for it. So, the time provided to me for this activitiy was limited.
Abap2xlsx in my view is very good when the project involves handling excel files. But, mine was an implicit need. So, found this to be simpler.
Out of curiosity I entered "SAP upload xlsx" in Google and this SCN blog was the first result. Even though the blog itself is yet from another SCN member who apparently had trouble finding ABAP2XLSX, the very first comment (mine :)) mentions ABAP2XLSX. And then Paul Hardy nicely elaborates on the subject.
I feel something needs to be done to prop up ABAP2XLSX SEO score...
Hi Jelena Perfiljeva ,
I am not complaining about the SEO.. :), yet.