How to Handle CSV and TSV Files in ABAP

Dear SCN,

My name is George Drakos and I decided to write a blog about handling of TSV and CSV files. First we will see what TSV and CSV files really are and then we will develop code methods to convert these files. I would like to thank my colleague and dear friend Dimitris Valouxis for his support and his contribution to the current article. Without him this blog would not exist.

In this blog, we will share code snippets to handle CSV and TSV files. It’s a daily task to convert these files to Internal Tables (and vice versa) and it comes very handy and time saving when we have a ready-to-go code available.

If you don’t know what CSV file is you can have a look in the following link. In a nutshell, CSV is a simple text file in which data and information are separated using a separator (in most case the separator is a comma). There are two mains points to keep in mind. Separator is used  to separate two fields (values) while delimiter is used to set the limits. Often double quote is used as a delimiter.

A brief explanation about TSV files follows. So, tab delimited format stores information from a database or spreadsheet in the format of a tabular structure. Each entry takes one line in the text file and the various fields are separated by tabs. It is widely used, as data between different systems can be easily transferred via this format. In order to create a TSV.txt file, you can simply save an excel worksheet as text tab delimited. Follow this link for more information.

We use to code in methods of global class in order to be available anytime we need to
use them. So all the codes provided are in form of method but it will be an easy task if you want to
convert them to a local report or function module. Check the importing and exporting parameters to
understand the types used. We tried to keep codes as generic as possible in order to be sure that they will work in every case and that’s why we pass ‘ANY TABLE’ as a table type.

1)CSV to Internal Table

Lets start with the conversion of CSV file to Internal Table. Using cl_rsda_csv_converted to separate the comma value to columns our work is so much easier. First, we need to create an instance of the class and pass the parameters of separator and delimeter to ‘CREATE’ method. Then, simply upload the CSV file to raw data and then separate the line values into columns by looping into the table and using our class.

* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCLASS->READ_CSV_FILE
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_DELIMITER                   TYPE        CL_RSDA_CSV_CONVERTER=>CHAR (default =C_DEFAULT_DELIMITER)
* | [--->] IM_FIELD_SEPARATOR             TYPE        CL_RSDA_CSV_CONVERTER=>CHAR (default =C_DEFAULT_SEPARATOR)
* | [--->] IM_FILEPATH                    TYPE        STRING
* | [<---] EX_TABLE                       TYPE        ANY TABLE
* +--------------------------------------------------------------------------------------</SIGNATURE>   

   DATA: lt_raw_data  TYPE  truxs_t_text_data,
         lv_dataset_line TYPE string,
         ref_wa          TYPE REF TO data.

    FIELD-SYMBOLS: <fs_itab>      TYPE ANY TABLE,
                   <fs_wa>        TYPE any.

    DATA(lo_csv_converter) =  cl_rsda_csv_converter=>create( i_delimiter = im_delimiter i_separator = 
    im_field_separator ).

    "CREATE A DYNAMIC TABLE WITH THE SAME STRUCTURE AS TARGETED TABLE
    ASSIGN ex_table  TO <fs_itab>.

    "CREATE A DYNAMIC STRUCTURE
    CREATE DATA ref_wa LIKE LINE OF <fs_itab>.
    ASSIGN ref_wa->* TO <fs_wa>.
    
    "UPLOAD CSV FILE
    CALL FUNCTION 'GUI_UPLOAD'
      EXPORTING
        filename = im_filepath
        filetype = 'ASC'
      TABLES
        data_tab = lt_raw_data.
    
    "SEPARATE VALUES AND APPEND THEM INTO TARGET TABLE
    LOOP AT lt_raw_data INTO DATA(ls_csv_line).

      CALL METHOD lo_csv_converter->csv_to_structure
        EXPORTING
          i_data   = ls_csv_line
        IMPORTING
          e_s_data = <fs_wa>.

      INSERT <fs_wa> INTO TABLE ex_table.

    ENDLOOP.

2)Internal Table to CSV

Download Internal Table as CSV to Presentation Server. First, we have to create the target table based on the value of the main table. Loop at the main table and for each row, separate the values using commas and then append lines to target string table. Finally, download the table using GUI_DOWNLOAD. It is exact the opposite procedure of uploading CSV file.

* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCLASS->SAVE_CSV_FILE
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_FILEPATH                    TYPE        STRING
* | [--->] IM_FIELD_SEPARATOR             TYPE        CL_RSDA_CSV_CONVERTER=>CHAR (default =C_DEFAULT_SEPARATOR)
* | [--->] IM_TABLE                       TYPE        ANY TABLE
* +--------------------------------------------------------------------------------------</SIGNATURE>    
    DATA: lt_csv TYPE TABLE OF string,
          lv_row TYPE string,
          lv_string  TYPE string.

    "CONSTRUCT THE TARGET TABLE FOR DOWNLOAD.SEPARATE VALUE WITH COMMAS
    LOOP AT im_table ASSIGNING FIELD-SYMBOL(<fs_line>).

      CLEAR xout.
      DO.
        ASSIGN COMPONENT sy-index OF STRUCTURE <fs_line> TO FIELD-SYMBOL(<fs_value>).
        IF sy-subrc NE 0.
          EXIT.
        ENDIF.
        IF sy-index EQ 1.
          lv_row = <fs_value>.
        ELSE.
          lv_string = <fs_value>.
          CONDENSE lv_string.
          CONCATENATE lv_row lv_string INTO lv_row SEPARATED BY im_field_separator.
        ENDIF.
      ENDDO.

      APPEND lv_row TO lt_csv.

    ENDLOOP.

    "DOWNLOAD THE TABLE INTO CSV FILE
    CALL FUNCTION 'GUI_DOWNLOAD'
      EXPORTING
        filename                = im_filepath
      TABLES
        data_tab                = lt_csv
      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
        OTHERS                  = 22.

3)Tab Delimited to Internal Table

This one is simple. Just use GUI_UPLOAD with filetype ‘ASC’ and the table will be ready. With ‘ASC’ the table is transferred as text. The conversion exits are also carried out.

* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCLASS->READ_TSV_FILE
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_FILEPATH                    TYPE        STRING
* | [<---] EX_TABLE                       TYPE        ANY TABLE
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD read_tsv_file.

  CALL METHOD cl_gui_frontend_services=>gui_upload
    EXPORTING
      filename            = im_filepath
      filetype            = 'ASC'
      has_field_separator = abap_true
    CHANGING
      data_tab            = ex_table.

ENDMETHOD.

4)Internal Table to Tab Delimited

Now for Tab Delimited Τable download we use the exact same process with CSV, but instead of comma we use the horizontal tab in order to split the fields into columns. Here is the tricky part.

You must NOT use # symbol hardcoded but instead you must use the attribute cl_abap_char_utilities=>horizontal_tab. In short, there is a difference in hexadecimal value between # as a normal character and # as a tab character and so ABAP treats them differently in each case. You can read an awesome article here which clarifies the case and explains why you should be very cautious when dealing with the horizontal tab. We strongly suggest you to take a look.

Finally, after forming the table, we use GUI_DOWNLOAD with filetype ‘DAT’ to save our file. DAT is used for Column-by-column transfer. With this format, the data is transferred as with ASC text. However, no conversion exists are carried out and the columns are separated by tab characters

* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCLASS->SAVE_TSV_FILE
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_TABLE                       TYPE        ANY TABLE
* | [--->] IM_FILEPATH                    TYPE        STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD save_tsv_file.

    DATA: lt_tsv   TYPE TABLE OF string,
          lv_line  TYPE string,
          lv_value TYPE string.

    LOOP AT im_table ASSIGNING FIELD-SYMBOL(<fs_line>).

      CLEAR lv_line.
      DO.
        ASSIGN COMPONENT sy-index OF STRUCTURE <fs_line> TO FIELD-SYMBOL(<fs_value>).
        IF sy-subrc NE 0.
          EXIT.
        ENDIF.
        IF sy-index EQ 1.
          lv_line = <fs_value>.
        ELSE.
          lv_value = <fs_value>.
          CONDENSE lv_value.
          CONCATENATE lv_line lv_value INTO lv_line SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
        ENDIF.
      ENDDO.

      APPEND lv_line TO lt_tsv.

    ENDLOOP.

    cl_gui_frontend_services=>gui_download(
       EXPORTING
         filename                  = im_filepath
         filetype                  = 'DAT'
       CHANGING
         data_tab                  = lt_tsv
       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
         not_supported_by_gui      = 22
         error_no_gui              = 23
         OTHERS                    = 24 ).

  ENDMETHOD.

Wrap up

So, here is all the different methods and we use in order to convert CSV and TSV files. If you approach the handling with a different method feel free to comment it.