Skip to Content

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:

2013-06-14 18-27-21_Download table as csv.png

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.

typepools: abap.

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

   public section.

     classmethods:
       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 symandt
                                  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 sysysid ‘-‘ sydatum ‘-‘ 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 sysubrc <> 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 <> symandt.
       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.

     fieldsymbols:
        <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 sytabix = 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.

     fieldsymbols<fs_field> type data.

     do.
       assign component syindex of structure is_line to <fs_field>.
       if sysubrc <> 0.
         exit.
       endif.

       move <fs_field> to l_field_content.
       condense l_field_content.

*     Change target client for download
       if syindex = 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 syindex = 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 textb00.
parameters:
    table   type dd02ltabname,
    file    type string lower case.
selection-screen end of block b00.

selection-screen begin of block b01 with frame title textb01.
parameters:
     headincl  type abap_bool   default abap_true as checkbox,
     separatr  type c length default ‘;’,
     enclosng  type c length default ,
     fileclnt  type mandt       default symandt,
     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 sydbcnt = 0.
     message ‘Table does not exist’ type ‘E’.
     clear table.
   endif.

*———————————————————————-*
*
*———————————————————————-*
*
*———————————————————————-*

startofselection.

   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( ).

To report this post you need to login first.

12 Comments

You must be Logged on to comment or reply to a post.

  1. Fabian Krüger

    Thanks Karsten for sharing.

    There is one issue I always had when exporting data from microsoft sql server as .csv:

    If the seperator is ; and any cell contains a ; the import will fail.

    Thats why we enclose the content by ” (for example).

    But enclosing does not really solve the problem directly: If a cell contains a “, the import will fail, unless we escape all ” characters…

    As far as I can see, you don’t escape the enclosing character, do you?

    Surround each field with enclosing

          if m_enclosing is not initial.

            concatenate m_enclosing l_string m_enclosing into l_string.

          endif.

    Microsoft SQL Server escapes it as “”, but HANA needs \” so my suggestion is to replace m_enclosing by \m_enclosing first…

    I hope you get the point.

    Kind Regards,

    Fabian

    (0) 
    1. Karsten Kötter

      Hello Fabian,

      Thanks, good point, you are right!

      I’ll fix this (might take some time due to my soon coming vacation).

      Thanks & Best regards,

      Karsten

      (0) 
        1. Henrique Pinto

          Are you worrying about character encoding?

          Usually this something to worry about when exporting from an unicode database, you need to understand in which encoding have the special chars been encoded in, and export in an encoding the application that will consume the data is expecting.

          I’d suggest to add an “encoding” parameter for the desired encoding of the output.

          (0) 
          1. Karsten K&amp;#246;tter Post author

            Hello Henrique,

            nice to hear from you again!

            Encoding is a good point, I added an parameter as you suggested and updated the document and the coding.

            Thanks a lot & best regards,

            Karsten

            (0) 
    1. Karsten K&amp;#246;tter Post author

      Hello Fernando,

      execution in background means that the download could not be done to the client, but the file needs to be downloaded to server filesystem.

      Therefore you have to replace (or enhance) the coding with GUI_DOWNLOAD and browse_local_filesystem and use OPEN DATASET & Co.

      Maybe I’ll implement this, but don’t expect this to happen soon…

      Best regards,

      Karsten

      (0) 

Leave a Reply