Skip to Content
Technical Articles
Author's profile photo Sharath M G

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:

  1. 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: DATAZINTERNAL 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.

 

Assigned Tags

      16 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Sandra Rossi
      Sandra Rossi

      You didn’t google enough: the advice of “most influential people” is to do anything Excel via abap2xlsx (works in both directions).

       

      Author's profile photo Sharath M G
      Sharath M G
      Blog Post Author

      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

      Author's profile photo Sandra Rossi
      Sandra Rossi

      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).

      Author's profile photo Sharath M G
      Sharath M G
      Blog Post Author

      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

       

      Author's profile photo Michelle Crapo
      Michelle Crapo

      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

      Author's profile photo Sharath M G
      Sharath M G
      Blog Post Author

      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 🙂

      Author's profile photo Matthew Billingham
      Matthew Billingham

      TEXT_CONVERT_XLS_TO_SAP isn't part of the "basis only" stack. (I just checked).

      Author's profile photo Sharath M G
      Sharath M G
      Blog Post Author

      Thanks for the confirmation and the information 🙂

      Author's profile photo Sandra Rossi
      Sandra Rossi

      Little misunderstanding but I guess we agree: they exist and can be used, but are tagged obsolete.

      Author's profile photo Sharath M G
      Sharath M G
      Blog Post Author

      Agree to agree. .  🙂

      Author's profile photo Matthew Billingham
      Matthew Billingham

      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.

      Author's profile photo Sharath M G
      Sharath M G
      Blog Post Author

      Great.

      Matthew Billinghamif you can share the link to the blog, it could be very helpful..

      Author's profile photo Gregor Wolf
      Gregor Wolf

      I would suggest to put in abap2xlsx in the SAP Community search at https://cse.google.com/cse?cx=013447253335410278659:k8ob9ipscwg

      Author's profile photo Sharath M G
      Sharath M G
      Blog Post Author

      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.

      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva

      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...

       

      Author's profile photo Sharath M G
      Sharath M G
      Blog Post Author

      Hi Jelena Perfiljeva ,

      I am not complaining about the SEO.. :), yet.