Skip to Content
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:

  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.

 

16 Comments
You must be Logged on to comment or reply to a post.
  • 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

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

  • 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…