Preserve Downloaded data formatting in Excel

When we download the data from the SAP using FMs like GUI_DOWNLOAD or WA_DOWNLOAD (absolute); or the method of class CL_GUI_FRONTEND_SERVICES=>GUI_DOWNLOAD and open that file in the Microsoft Excel, we loose some important information like leading zero, long numbers will come as exponents. Most of the times we don't want this because we will use this File as input of some program or we will use it for future analsys.

When we use the .xls extension to save the file and open it directly in the Excel, the file will look like this:


We will see how to stop Excel removing our precise information which we have downloaded from SAP. We need to follow this Steps:
1. We will save our file with .txt extension instead of the .xls extension.

2. Start Excel. Start > Run > Enter Excel.

3. Open the downloaded file by Selecting it from the Open File dialog box.

4. Once we open the file, excel will bring "Text to column" wizard.

5. Select the "Delimilated" option in the first screen. Move to the 3rd Step of the wizard by pressing the Next button two times.


6. In the 3rd step, select all the columns. To select all the columns do like: Click on first column, Hold the shift key, scroll to last column, click on last column. This way all the columns gets selected. The colums which got selected will have black background. Now, select the Option "Text" in the format.


7. Finish the Wizard.

Our file after opening in Excel will look like this:



This is the code snippet I have used to generate the test file.

Code Snippet

*&---------------------------------------------------------------------* *& Report will show how to use the GUI_DOWNLOAD to preserve the *& formatting of the data *&---------------------------------------------------------------------* REPORT ztest_download. * TYPES: BEGIN OF ty_data, vbeln TYPE vbeln, amt TYPE dmbtr, long TYPE char30, text TYPE char20, END OF ty_data. * DATA: it_data TYPE STANDARD TABLE OF ty_data, wa_data TYPE ty_data. * START-OF-SELECTION. * Fill the test table DO 5 TIMES. wa_data-vbeln = sy-index. UNPACK wa_data-vbeln TO wa_data-vbeln. wa_data-amt = '12345.67' * sy-index. wa_data-long = '12345678901234567890'. wa_data-text = sy-abcde. APPEND wa_data TO it_data. CLEAR wa_data. ENDDO. * * Download file DATA: l_file TYPE string. * * here we will pass the .txt instead of the .xls l_file = 'C:\temp\test_data.txt'. * * In the Filetype, we still pass the DAT because we want * our output in the tabular format. CALL FUNCTION 'GUI_DOWNLOAD' EXPORTING filename = l_file filetype = 'DAT' TABLES data_tab = it_data EXCEPTIONS file_write_error = 1 no_batch = 2 gui_refuse_filetransfer = 3 invalid_type = 4 no_authority = 5 unknown_error = 6 header_not_allowed = 7 separator_not_allowed = 8 filesize_not_allowed = 9 header_too_long = 10 dp_error_create = 11 dp_error_send = 12 dp_error_write = 13 unknown_dp_error = 14 access_denied = 15 dp_out_of_memory = 16 disk_full = 17 dp_timeout = 18 file_not_found = 19 dataprovider_exception = 20 control_flush_error = 21.