Skip to Content
Personal Insights
Author's profile photo Anuja Kawadiwale

How to Download Data from Internal Table to an Excel File

This blog post is explaining all about how to download data from internal table to an excel file.

Requirement: On selection screen user will give the input as country name and file name in which user want to download the data to an excel file based on the given country name.

Step 1: Design Selection Screen. On selection screen declare country and file name as parameter.

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME.
  PARAMETERS:p_land TYPE kna1-land1,
             p_file TYPE rlgrap-filename.
SELECTION-SCREEN END OF BLOCK b1.

Step 2: Declare structure, internal table and work area.

TYPES:
  BEGIN OF ty_kna1,
    kunnr TYPE kunnr,
    name1 TYPE name1,
    land1 TYPE land1,
    ort01 TYPE ort01,
  END OF ty_kna1.

DATA:
  it_kna1 TYPE TABLE OF ty_kna1,
  wa_kna1 TYPE ty_kna1.

Step 3: Declare variables and internal table for heading.

DATA: g_str1 TYPE string VALUE '.xls',
      g_str  TYPE string,
      g_str2 TYPE string.

DATA : BEGIN OF it_header OCCURS 0,
         line(50) TYPE c,
       END OF it_header.

Step 4: Use function module KD_GET_FILENAME_ON_F4 for F4 help for file name.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
    EXPORTING
      static    = 'X'
    CHANGING
      file_name = P_file.

Step 5: Append all the heading for each field into internal table.

START-OF-SELECTION.

  it_header-line = 'Customer Number'.
  APPEND it_header.
  it_header-line = 'Customer Name'.
  APPEND it_header.
  it_header-line = 'Country'.
  APPEND it_header.
  it_header-line = 'City'.
  APPEND it_header.

Step 6: I want data in excel file so if user did not take F4 help then extension of file needs to append to file name. If user take F4 help and select excel file then no need to append extension.

IF p_file NS '.xls'.
    g_str = p_file.
    CONCATENATE g_str g_str1 INTO g_str2.
  ELSE.
    g_str2 = p_file.
  ENDIF.

Step 7: Write Select query to fetch data from database.

SELECT
    kunnr
    name1
    land1
    ort01
    FROM kna1
    INTO TABLE it_kna1
  WHERE land1 = p_land.

Step 8: Call the function module GUI_DOWNLOAD to download the data from the database into excel file.

CALL FUNCTION 'GUI_DOWNLOAD'
    EXPORTING
      filename              = g_str2
      filetype              = 'ASC'
      write_field_separator = 'X'

    TABLES
      data_tab              = it_kna1
      fieldnames            = it_header
    .

Output

Once you execute it file with name Customer excel file will be created and data will be downloaded.

If you want to download data into existing excel file Press F4 for file name.

Then Execute it, you will get data into the selected file.

Assigned Tags

      20 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Shai Sinai
      Shai Sinai

      The first post regarding generation of Excel files in a long time... 😉

       

      P.S.

      The generated file isn't XLS.

      Author's profile photo Anuja Kawadiwale
      Anuja Kawadiwale
      Blog Post Author

      Did you execute this code ? I am getting data to an excel file only.

      Author's profile photo Shai Sinai
      Shai Sinai

      This is a CSV-like file which can be opened by Microsoft Excel.

      Author's profile photo Matthew Billingham
      Matthew Billingham

      Open an XLS or XLSX file generated from Excel in notepad.

      Open your XLS or XLSX file generated from your program in notepad.

      See the difference? Your file is just just a flat text file. With the fields separated by commas or semi-colons.

      Author's profile photo Federico Kovach
      Federico Kovach

      Hi Anuja!

      Try with filetype = 'DBF' and write_field_separator = abap_false.

      CALL FUNCTION 'GUI_DOWNLOAD'
          EXPORTING
            filename              = g_str2
            filetype              = 'ASC'
            write_field_separator = 'X'
      
          TABLES
            data_tab              = it_kna1
            fieldnames            = it_header
          .

      You will get an Excel compatible file instead of a CSV.

      Author's profile photo Gábor Márián
      Gábor Márián

      It is a misconception I've seen many times: the way to create an Excel file is to append .xls(x) extension to a flat file.

      Author's profile photo Andrea Borgia
      Andrea Borgia

      Anuja Kawadiwale please note that Excel is XLSX and here you're downloading a normal CSV text file. There are, of course, ways to generate real XLSX files from SAP and there have been numerous blog posts about it.

      Paul Hardy july is now done 😉

      Author's profile photo Anuja Kawadiwale
      Anuja Kawadiwale
      Blog Post Author

      Did you execute this code ? I am getting data to an excel file only.

      There is no blog post on getting data to an excel file using GUI_Download FM.

      Author's profile photo Andrea Borgia
      Andrea Borgia

      You're confusing the file format with the program used to read that file. Your code generates a text file in CSV format, which you then open with Excel, but it is NOT an Excel native file (XLSX extension, a zipped folder of XML files)

      There are 158 blog posts mentioning "gui download", I'd say this topic is well covered already 🙂

      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva
      1. There are so many blog posts about "download to Excel" that it has become a meme. This is the most recent one, I think, we had a good run: https://blogs.sap.com/2021/12/30/xlsc-upload-unified-approach-for-older-abap-version/
      2. You won't find many recent posts about using FM GUI_DOWNLOAD because it's been replaced by the class (mentioned here in the comments) long time ago.

      Edit: there is also a better way to offer F4 for a file name, this answer is from 2007. https://answers.sap.com/questions/2202117/f4-search-help-for-file-name.html

      Sorry to say but the knowledge shared in this post is seriously outdated. Surely you can do better.

      Author's profile photo Paul Hardy
      Paul Hardy

      Thank you for bringing this to my attention.

      This is wonderful, just wonderful. All my Christmases have come at once.

      Author's profile photo Paul Hardy
      Paul Hardy

      And be sure not to have any FORM routines , let alone methods of classes.

      Author's profile photo Sandra Rossi
      Sandra Rossi

      Also better use CL_GUI_FRONTEND_SERVICES methods: FILE_SAVE_DIALOG and GUI_DOWNLOAD.

      Author's profile photo Gregor Wolf
      Gregor Wolf

      At least one should give you the hint to check out the abap2xlsx Project.

      Author's profile photo Alexandre Langer
      Alexandre Langer

      it worked, thank you

      Author's profile photo Matthew Billingham
      Matthew Billingham

      But it ain't Excel. It's a CSV file. Try opening it in notepad and you'll see.

      Author's profile photo Sandra Rossi
      Sandra Rossi

      What people say about the generated file is that it's CSV format. If you choose the extension .XLS instead of .CSV, you will see this message in modern versions of Excel:

      Excel%20the%20file%20format%20and%20extension%20don%20t%20match

      Excel the file format and extension don t match

      Alexandre Langer For information

      Author's profile photo Bärbel Winkler
      Bärbel Winkler

      Another option is to simply create an ALV output and then use its standard functionality to create the Excel output from there. No need for GUI_DOWNLOAD at all then and it has the advantage that the user has a chance to first see which data - and how much! - will end up in a local file.

      Something like this (just with the proper variable names, obviously!)

        DATA lr_table type ref to cl_salv_table.
       
         TRY.
              cl_salv_table=>factory(
                IMPORTING
                  r_salv_table   = lr_table
                CHANGING
                  t_table        = lt_out ).
      
              "Activate ALV generic Functions 
              "Note: PF-Status STANDARD needs to be copied from FG SALV as SALV_STANDARD!
              lr_table->set_screen_status(
                pfstatus      = 'SALV_STANDARD'
                report        = sy-repid
                set_functions = lr_table->c_functions_all ).
      
              "Generate ALV-output
              lr_table->display( ).
      
            CATCH cx_salv_msg INTO zcx_salv_msg.
              lv_msg = zcx_salv_msg->get_text( ).
              WRITE :/ lv_msg.
          ENDTRY.

      I never much liked programs which simply write data to an output file without an option for the user to see what the result will be. "Flying blind" comes to mind.

      Cheers

      Bärbel

      Author's profile photo George Borghouts
      George Borghouts

      Hi Anuja,

      Thanks so much for your article. I built it here and works OK. In case however - as here with us - the end user wants to initiate the download themselves like at night of via automated tooling on their  windows client machine,  i had to resort to another solution.

      In our case we want a download from several tables for specific records on stock positions ( LQUA and MARA + MARC) every 30 minutes or whenever our Blueprism users need it. For that I wrote a tiny freeware ms-windows app that acts as a connector for SAP tables and the option to even filter those. It outputs to Excel xlsx and/or CSV  This then can be called from windows command prompt manually or automated to start the download. May help anyone facing same challenge. If you search for SAP table download or SAP table connector or ExtracTable you will see it popping up.

      Big advantage of your solution is that for bigger tables it is faster as mine goes through FM RFC_READ_TABLE. I wonder whether there is a way to instruct SAP from within a windows PC to trigger your abap (not using SAP GUI script as here we are Fiori only).

      Cheers,

      George

      Author's profile photo Evgeni Rovneiko
      Evgeni Rovneiko

      Better solution:

      DATA lo_salv TYPE REF TO cl_salv_table.
      DATA lo_ex TYPE REF TO cx_root.
      DATA lv_error TYPE string.
      DATA lv_xml TYPE xstring.
      DATA lt_bin TYPE solix_tab.
      DATA lv_len TYPE i.
      
      TRY.
          CALL METHOD cl_salv_table=>factory
            IMPORTING
              r_salv_table = lo_salv
            CHANGING
              t_table      = gt_data.
      
          CALL METHOD lo_salv->to_xml
            EXPORTING
              xml_type = if_salv_bs_xml=>c_type_xlsx
            RECEIVING
              xml      = lv_xml.
      
        CATCH cx_salv_error INTO lo_ex.
          lv_error = lo_ex->get_text( ).
          BREAK-POINT.
      ENDTRY.
      
      CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
        EXPORTING
          buffer        = lv_xml
        IMPORTING
          output_length = lv_len
        TABLES
          binary_tab    = lt_bin.
      
      CALL FUNCTION 'GUI_DOWNLOAD'
        EXPORTING
          bin_filesize = lv_len
          filename     = 'D:\test.xlsx'
          filetype     = 'BIN'
        TABLES
          data_tab     = lt_bin.