Function module to convert excel file into flat file

Former Member — Wed, 13 Aug 2008 09:55:41 GMT

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

Re: Function module to convert excel file into flat file

Former Member — Wed, 13 Aug 2008 09:59:22 GMT

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

Re: Function module to convert excel file into flat file

Former Member — Wed, 13 Aug 2008 10:28:16 GMT

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

Re: Function module to convert excel file into flat file

Former Member — Thu, 14 Aug 2008 08:02:10 GMT

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

Re: Function module to convert excel file into flat file

Former Member — Thu, 14 Aug 2008 08:02:35 GMT

regards

topic Re: Function module to convert excel file into flat file in Application Development and Automation Discussions

Function module to convert excel file into flat file

Re: Function module to convert excel file into flat file

Re: Function module to convert excel file into flat file

Re: Function module to convert excel file into flat file

Re: Function module to convert excel file into flat file