Simple csv table export in ABAP for HANA

[kkoetter]

As you all know the import of a table via csv file is easy and straightforward in SAP HANA Studio (see e.g. http://www.saphana.com/docs/DOC-2191 from SAP HANA Academy).

The export of a table from SAP Netweaver to a csv file is unfortunately not so straightforward. Therefore I wrote a little ABAP program which simplifies the table export as csv file. This is a screenshot of the selection screen:

You can specify the table and the csv filename. Furthermore you have the following options:

• Should the table header line be written
• Change the field separator
• Should the field content be enclosed (useful for fields which contain the field separator within the table content)
• Client in the csv file (useful if source client differs from target client in HANA)
• Code page (empty value means frontend codepage, other value might be applicable depending on special characters in table and expectations within HANA)

Here comes the source code. Please drop me a little comment below, if the program could help you.

*&---------------------------------------------------------------------*
*& Report  ZKK_TABLE_DOWNLOAD
*&
*&---------------------------------------------------------------------*
*&
*&   Karsten Kötter, 2013-03
*&   cbs Corporate Business Solutions Unternehmensberatung GmbH
*&   karsten.koetter@cbs-consulting.de
*&
*&---------------------------------------------------------------------*

program zkk_table_download.

type-pools: abap.


*----------------------------------------------------------------------*
*       CLASS lcl_table_download DEFINITION
*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
class lcl_table_download definition.

   public section.

     class-methods:
       browse_local_filesystem importing i_table       type tabname
                               returning value(e_file) type string.

     methods:
       initialize,
       set_parameter    importing i_table        type tabname
                                  i_file         type string
                                  i_separator    type char01      default ';'
                                  i_header       type abap_bool   default abap_true
                                  i_file_client  type mandt       default sy-mandt
                                  i_enclosing    type char01      optional
                                  i_codepage     type cpcodepage  optional,
       download_table.



   protected section.

     types: tyt_csv     type standard table of string with default key.

     data:
       m_table          type tabname,
       m_file           type string,
       m_header_incl    type abap_bool,
       m_change_client  type abap_bool,
       m_separator     type char01,
       m_enclosing      type char01,
       m_file_client    type mandt,
       m_codepage       type abap_encod,
       mt_dd03l         type standard table of dd03l.

     methods:
       get_header_line  returning value(es_csv) type string,
       download_block   changing  it_data type table,
       convert_to_csv   importing is_line type data
                        returning value(es_csv) type string.

endclass.                    "lcl_table_download DEFINITION


*----------------------------------------------------------------------*
*       CLASS lcl_table_download IMPLEMENTATION
*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
class lcl_table_download implementation.

*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
   method browse_local_filesystem.

     data:
      l_filename  type string,
      l_path      type string,
      l_file      type string.

     l_file = i_table.
     replace all occurrences of '/' in l_file with ''.
     concatenate sy-sysid '-' sy-datum '-' l_file '.csv' into l_file.

     cl_gui_frontend_services=>file_save_dialog(
       exporting
         window_title         = 'Save table as csv file'
         default_extension    = 'csv'
         default_file_name    = l_file
*       with_encoding        =
         file_filter          = '*.csv'
         initial_directory    = 'D:\UserData'
         prompt_on_overwrite  = 'X'
       changing
         filename             = l_filename
         path                 = l_path
         fullpath             = e_file
*       user_action          =
*       file_encoding        =
       exceptions
         cntl_error           = 1
         error_no_gui         = 2
         not_supported_by_gui = 3
         others               = 4 ).
     if sy-subrc <> 0.
       message 'File selection failed' type 'E'.
     endif.

   endmethod.                    "browse_local_filesystem


*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
   method initialize.

     clear:
       m_table,
       m_file,
       m_separator,
       m_header_incl,
       m_file_client,
       m_enclosing,
       mt_dd03l.

   endmethod.                    "initialize


*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
   method set_parameter.

     data:
       lrs_dd03l type ref to dd03l.


     m_table         = i_table.
     m_file          = i_file.
     m_separator     = i_separator.
     m_header_incl   = i_header.
     m_file_client   = i_file_client.
     m_enclosing     = i_enclosing.
     m_codepage      = i_codepage.


*   Get fields of table (reading DD03L seems simpler than RTTI, think of includes...)
     select * from dd03l
       into corresponding fields of table mt_dd03l
      where tabname  = m_table
        and comptype = 'E'
      order by position.

*   Change client in file, if table is not cross-client and target client differs
     read table mt_dd03l reference into lrs_dd03l index 1.
     if lrs_dd03l->rollname = 'MANDT' and
        m_file_client <> sy-mandt.
       m_change_client = abap_true.
     else.
       m_change_client = abap_false.
     endif.

   endmethod.                    "set_table


*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
   method download_table.

     data:
       lrt_data type ref to data,
       lrs_data type ref to data,
       lt_csv   type tyt_csv,
       ls_csv   type string.


     field-symbols:
        <fs_table> type standard table,
        <fs_line>  type data.


*   Create internal table dynamically
     create data lrt_data type standard table of (m_table).
     assign lrt_data->* to <fs_table>.

     create data lrs_data type (m_table).
     assign lrs_data->* to <fs_line>.


*   Select data from db
     select * from (m_table)
       into table <fs_table>.


*   Build header line
     if m_header_incl = abap_true.
       ls_csv = get_header_line( ).
       append ls_csv to lt_csv.
     endif.


*   Convert structured data to csv
     loop at <fs_table> into <fs_line>.
       ls_csv = convert_to_csv( <fs_line> ).
       append ls_csv to lt_csv.
     endloop.


*   save file
     download_block( changing it_data = lt_csv ).


   endmethod.                    "download_table


*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
   method get_header_line.

     data:
       lrs_dd03l type ref to dd03l.


*   Build header line
     loop at mt_dd03l reference into lrs_dd03l .
       if sy-tabix = 1.
         es_csv = lrs_dd03l->fieldname.
       else.
         concatenate es_csv m_separator lrs_dd03l->fieldname into es_csv.
       endif.
     endloop.

   endmethod.                    "get_header_line

*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
   method convert_to_csv.

     data:
       l_field_content  type string,
       l_enclosing_esc  type string.

     field-symbols:  <fs_field> type data.



     do.
       assign component sy-index of structure is_line to <fs_field>.
       if sy-subrc <> 0.
         exit.
       endif.

       move <fs_field> to l_field_content.
       condense l_field_content.


*     Change target client for download
       if sy-index = 1 and               " First field of structure?
          m_change_client = abap_true.   " Change target client?
         l_field_content = m_file_client.
       endif.


*     Surround each field with enclosing and escape enclosing
       concatenate '\' m_enclosing into l_enclosing_esc.
       if m_enclosing is not initial.
         replace all occurrences of m_enclosing in l_field_content with l_enclosing_esc.
         concatenate m_enclosing l_field_content m_enclosing into l_field_content.
       endif.


*     Concatenate the fields to one single line separated by separator
       if sy-index = 1.
         es_csv = l_field_content.
       else.
         concatenate es_csv l_field_content into es_csv separated by m_separator.
       endif.

     enddo.

   endmethod.                    "convert_to_csv


*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
   method download_block.

     call function 'GUI_DOWNLOAD'
       exporting
         filename = m_file
         codepage = m_codepage
       tables
         data_tab = it_data.

   endmethod.                    "download_block

endclass.                    "lcl_table_download IMPLEMENTATION




*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*

selection-screen begin of block b00 with frame title text-b00.
parameters:
    table   type dd02l-tabname,
    file    type string lower case.
selection-screen end of block b00.

selection-screen begin of block b01 with frame title text-b01.
parameters:
     headincl  type abap_bool   default abap_true as checkbox,
     separatr  type c length 1  default ';',
     enclosng  type c length 1  default '',
     fileclnt  type mandt       default sy-mandt,
     codepage  type cpcodepage  default space matchcode object h_tcp00.
selection-screen end of block b01.


*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
at selection-screen on value-request for file.

   file = lcl_table_download=>browse_local_filesystem( i_table = table ).


*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
at selection-screen on table.

   data: ls_dd02l  type dd02l.

   select single * from dd02l into ls_dd02l where tabname = table.
   if sy-dbcnt = 0.
     message 'Table does not exist' type 'E'.
     clear table.
   endif.


*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*

start-of-selection.

   data:
     lro_table_download type ref to lcl_table_download.


   create object lro_table_download.

   lro_table_download->initialize( ).
   lro_table_download->set_parameter( i_table       = table
                                      i_file        = file
                                      i_separator   = separatr
                                      i_header      = headincl
                                      i_enclosing   = enclosng
                                      i_file_client = fileclnt
                                      i_codepage    = codepage ).
   lro_table_download->download_table( ).