Preserve Downloaded data formatting in Excel

By | September 25, 2008 | Formatting | 8,187 | 1

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.
*
TYPESBEGIN OF ty_data,
       vbeln TYPE vbeln,
       amt   TYPE dmbtr,
       long  TYPE char30,
       text  TYPE char20,
       END   OF ty_data.
*
DATAit_data TYPE STANDARD TABLE OF ty_data,
      wa_data TYPE ty_data.
*
START-OF-SELECTION.
* Fill the test table
  DO 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
  DATAl_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.

Like It? Share!!

Don't miss an Update

Get notified of the new post, right into your inbox

Naimesh Patel{272 articles}

I'm SAP ABAP Consultant for more than a decade. I like to experiment with ABAP especially OO. I have been SDN Top Contributor.
Follow :

Explore all of his 272 articles.

Load comments

1 Comment

  • Anonymous

    Hi,

    Most of the times, the excel file will be downloaded, converted to tab delimited text file & again uploaded into SAP program by the End user.The end user will not try to do the above steps.

    If you can post any code for excel macros(office 2007 or older verions) to retain/rest the excel precision limit or any other solution, it would be great.

    I liked the Marbles game very much.Displaying the ALV output in the same selection screen is also good. Please keep up the good work.

    Thanks,
    vijaymarella@gmail.com

Comments on this Post are now closed. If you have something important to share, you can always contact me.

You seem to be new here. Subscribe to stay connected.