How to Download Data from Internal Table to an Exc...

anujawani2426 · ‎2022 Jul 05

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.

shais · ‎2022 Jul 05

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

P.S.

The generated file isn't XLS.

abo · ‎2022 Jul 05

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

bfeeb8ed7fa64a7d95efc21f74a8c135 july is now done 😉

hardyp180 · ‎2022 Jul 05

Thank you for bringing this to my attention.

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

anujawani2426 · ‎2022 Jul 05

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.

anujawani2426 · ‎2022 Jul 05

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

hardyp180 · ‎2022 Jul 05

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

abo · ‎2022 Jul 05

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 🙂

shais · ‎2022 Jul 05

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

gabmarian · ‎2022 Jul 05

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.

Sandra_Rossi · ‎2022 Jul 05

Also better use CL_GUI_FRONTEND_SERVICES methods: FILE_SAVE_DIALOG and GUI_DOWNLOAD.

gregorw · ‎2022 Jul 05

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

alexandrelanger · ‎2022 Jul 05

it worked, thank you

Sandra_Rossi · ‎2022 Jul 05

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 the file format and extension don t match

alexandrelanger For information

BaerbelWinkler · ‎2022 Jul 05

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

Jelena_Perfiljeva · ‎2022 Jul 05

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

matt · ‎2022 Jul 13

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

matt · ‎2022 Jul 13

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.

former_member527418 · ‎2022 Sep 09

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

fkovach · ‎2022 Sep 14

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.

erovneiko · ‎2023 Oct 27

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.

How to Download Data from Internal Table to an Excel File

Output

Get Started with the ABAP Development Tools for SAP NetWeaver

Become an ABAP in Eclipse Feature Explorer and earn the Explorer Badge

Six kinds of debugging tips to find the source code where the message is raised