Personal Insights
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.
The first post regarding generation of Excel files in a long time... 😉
P.S.
The generated file isn't XLS.
Did you execute this code ? I am getting data to an excel file only.
This is a CSV-like file which can be opened by Microsoft Excel.
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.
Hi Anuja!
Try with filetype = 'DBF' and write_field_separator = abap_false.
You will get an Excel compatible file instead of a CSV.
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.
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 😉
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.
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 🙂
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.
Thank you for bringing this to my attention.
This is wonderful, just wonderful. All my Christmases have come at once.
And be sure not to have any FORM routines , let alone methods of classes.
Also better use CL_GUI_FRONTEND_SERVICES methods: FILE_SAVE_DIALOG and GUI_DOWNLOAD.
At least one should give you the hint to check out the abap2xlsx Project.
it worked, thank you
But it ain't Excel. It's a CSV file. Try opening it in notepad and you'll see.
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
Alexandre Langer For information
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!)
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
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
Better solution: