Function module to convert excel file into flat file

[Former Member]

Hi all,

Is there any function module in ABAP which can conver a file in excel format(containing merged cells) into a flat file ?

regards

Debansu

[Former Member]

hii

use this code

FORM convert_data_to_file .
* Edit data
  LOOP AT <fs_tab> ASSIGNING <fs_line>.

    w_row = sy-tabix + 3.
    w_col = 1.

    CLEAR w_excel.

    LOOP AT it_dfies INTO w_dfies WHERE datatype <> 'CLNT'.

      ASSIGN COMPONENT w_dfies-fieldname
             OF STRUCTURE <fs_line> TO <fs_field>.

      CASE w_dfies-datatype.
        WHEN 'CURR' OR 'QUAN' OR 'DEC'
          OR 'INT1' OR 'INT2' OR 'INT4'.  " numeric
          WRITE <fs_field> TO w_value.
          SHIFT w_value RIGHT CIRCULAR.
          CONDENSE w_value NO-GAPS.

        WHEN 'DATS'.  " date
          CONCATENATE <fs_field>(4) <fs_field>+4(2) <fs_field>+6(2)
                      INTO w_value SEPARATED BY '/'.

        WHEN OTHERS.  " OTHERS
          WRITE <fs_field> TO w_value.
      ENDCASE.

      IF w_col = 1.
        w_excel = w_value.
      ELSE.
        CONCATENATE w_excel w_value INTO w_excel SEPARATED BY cos_tab.
      ENDIF.

      ADD 1 TO w_col.

    ENDLOOP.
    CONCATENATE cos_tab w_excel INTO w_excel.

    APPEND w_excel TO it_excel.

  ENDLOOP.


ENDFORM.                    " CONVERT_DATA_TO_FILE
*&---------------------------------------------------------------------*
*&      Form  DOWNLOAD_TABLE_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM download_table_data. "" using.  " dtabname like idictfilename[] .
*
  CLEAR: f_append.

* count internal table lines
  DESCRIBE TABLE it_excel LINES w_table_lines.


  DO.
    w_loop_start = ( sy-index - 1 ) * cos_dwnload_size + 1.
    w_loop_end = sy-index * cos_dwnload_size.

    REFRESH it_excel_part.

    LOOP AT it_excel INTO w_excel FROM w_loop_start TO w_loop_end.

      APPEND w_excel TO it_excel_part.

    ENDLOOP.
    REFRESH it_excel.

    CALL FUNCTION 'GUI_DOWNLOAD'
      EXPORTING
*       BIN_FILESIZE                  =
        filename                      = tablefilenamewithpath
        filetype                      = 'ASC'
        append                        = f_append
*       WRITE_FIELD_SEPARATOR         = ' '
*       HEADER                        = '00'
*       TRUNC_TRAILING_BLANKS         = ' '
*       WRITE_LF                      = 'X'
*       COL_SELECT                    = ' '
*       COL_SELECT_MASK               = ' '
*       DAT_MODE                      = ' '
*       CONFIRM_OVERWRITE             = ' '
*       NO_AUTH_CHECK                 = ' '
*       CODEPAGE                      = ' '
*       IGNORE_CERR                   = ABAP_TRUE
*       REPLACEMENT                   = '#'
*       WRITE_BOM                     = ' '
*     IMPORTING
*       FILELENGTH                    =
      TABLES
        data_tab                      = it_excel_part
      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.
*
    IF sy-subrc <> 0.
      MESSAGE s121(14) WITH 'GUI_DOWNLOAD' sy-subrc.
      STOP.
    ENDIF.

    f_append = 'X'.

    IF w_table_lines <= w_loop_end.
      EXIT.
    ENDIF.

  ENDDO.



ENDFORM.                    " DOWNLOAD_TABLE_DATA

[Former Member]

Hi..

use this FM

TEXT_CONVERT_XLS_TO_SAP

and take data in internal table and the dat data u can convert to flat file using FM

GUI_DOWNLOAD

regards

vivek

[Former Member]

Thanks Pardeep and Vivek.The code snippet and FM are working for normal excel but they are not working correctly if the excel contains merged cells.Please let me know if there are any FM or code snippet which can take care of conversion from excel containing merged cells to flat file.

regards

Debansu

[Former Member]

Thanks Pardeep and Vivek.The code snippet and FM are working for normal excel but they are not working correctly if the excel contains merged cells.Please let me know if there are any FM or code snippet which can take care of conversion from excel containing merged cells to flat file.

regards