Upload Excel file to internal table

There are a lot of SAP standard functions to upload Excel spreadsheet file into ABAP internal table.

They have some differences, for example: maximum length of returned cell value, transfer of data with or without clipboard, transfer of hidden columns, opening or not an instance of Excel application, etc.

This sample program should help you to test which function best suits your needs and show you how to call the functions and take over the returned data.

Attention: all these functions have one thing common: they are not released for customers and SAP doesn’t provide you support. Read the OSS note 933420. Use on your own risk.

You can use the sample program to test following functions:

  • ALSM_EXCEL_TO_INTERNAL_TABLE
  • FILE_READ_AND_CONVERT_SAP_DATA
  • IMPORT_FROM_SPREADSHEET
  • KCD_EXCEL_OLE_TO_INT_CONVERT
  • TEXT_CONVERT_XLS_TO_SAP
  • UPLOAD_XLS_FILE_2_ITAB
And here object oriented approach with SAP Office Integration (package SOFFICEINTEGRATION):
  • OBJECT ORIENTED

*&---------------------------------------------------------------------*
*& Report  ZS_EXCEL
*&
*&---------------------------------------------------------------------*
REPORT zs_excel LINE-SIZE 512.

TYPES:
  tv_data(256)  TYPE c,

  BEGIN OF ts_data,
    value_0001 TYPE tv_data,
    value_0002 TYPE tv_data,
    value_0003 TYPE tv_data,
    value_0004 TYPE tv_data,
    value_0005 TYPE tv_data,
    value_0006 TYPE tv_data,
    value_0007 TYPE tv_data,
    value_0008 TYPE tv_data,
    value_0009 TYPE tv_data,
    value_0010 TYPE tv_data,
    value_0011 TYPE tv_data,
    value_0012 TYPE tv_data,
    value_0013 TYPE tv_data,
    value_0014 TYPE tv_data,
    value_0015 TYPE tv_data,
    value_0016 TYPE tv_data,
    value_0017 TYPE tv_data,
    value_0018 TYPE tv_data,
    value_0019 TYPE tv_data,
    value_0020 TYPE tv_data,
  END OF ts_data,

  tt_data     TYPE TABLE OF ts_data,
  tv_index(4) TYPE n.

CONSTANTS:
  co_max_col TYPE i VALUE 20,
  co_max_row TYPE i VALUE 9999.

*&---------------------------------------------------------------------*
*&      SELECTION-SCREEN
*&---------------------------------------------------------------------*
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_fname FOR FIELD pa_fname.
PARAMETERS pa_fname TYPE localfile OBLIGATORY LOWER CASE MEMORY ID fnm.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN SKIP.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (83) tx_read0.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read1 FOR FIELD pa_read1.
PARAMETERS pa_read1 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read2 FOR FIELD pa_read2.
PARAMETERS pa_read2 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read3 FOR FIELD pa_read3.
PARAMETERS pa_read3 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read4 FOR FIELD pa_read4.
PARAMETERS pa_read4 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read5 FOR FIELD pa_read5.
PARAMETERS pa_read5 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read6 FOR FIELD pa_read6.
PARAMETERS pa_read6 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN SKIP.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read7 FOR FIELD pa_read7.
PARAMETERS pa_read7 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.

AT SELECTION-SCREEN OUTPUT.
  tx_fname = 'Path and name of Excel to open'.
  tx_read0 = 'Function:'.
  tx_read1 = 'ALSM_EXCEL_TO_INTERNAL_TABLE'.
  tx_read2 = 'FILE_READ_AND_CONVERT_SAP_DATA'.
  tx_read3 = 'IMPORT_FROM_SPREADSHEET'.
  tx_read4 = 'KCD_EXCEL_OLE_TO_INT_CONVERT'.
  tx_read5 = 'TEXT_CONVERT_XLS_TO_SAP'.
  tx_read6 = 'UPLOAD_XLS_FILE_2_ITAB'.
  tx_read7 = 'OBJECT ORIENTED'.

*&---------------------------------------------------------------------*
*&      START-OF-SELECTION
*&---------------------------------------------------------------------*
START-OF-SELECTION.

  DATA:
    lt_data  TYPE tt_data.

  IF pa_read1 = 'X'.
    WRITE: / 'Function ALSM_EXCEL_TO_INTERNAL_TABLE'.
    SKIP.
    PERFORM excel_read1 CHANGING lt_data.

  ELSEIF pa_read2 = 'X'.
    WRITE: / 'Function FILE_READ_AND_CONVERT_SAP_DATA'.
    SKIP.
    PERFORM excel_read2 CHANGING lt_data.

  ELSEIF pa_read3 = 'X'.
    WRITE: / 'Function IMPORT_FROM_SPREADSHEET'.
    SKIP.
    PERFORM excel_read3 CHANGING lt_data.

  ELSEIF pa_read4 = 'X'.
    WRITE: / 'Function KCD_EXCEL_OLE_TO_INT_CONVERT'.
    SKIP.
    PERFORM excel_read4 CHANGING lt_data.

  ELSEIF pa_read5 = 'X'.
    WRITE: / 'Function TEXT_CONVERT_XLS_TO_SAP'.
    SKIP.
    PERFORM excel_read5 CHANGING lt_data.

  ELSEIF pa_read6 = 'X'.
    WRITE: / 'Function UPLOAD_XLS_FILE_2_ITAB'.
    SKIP.
    PERFORM excel_read6 CHANGING lt_data.

  ELSEIF pa_read7 = 'X'.
    WRITE: / 'OBJECT ORIENTED'.
    SKIP.
    PERFORM excel_read7 CHANGING lt_data.
  ENDIF.

  PERFORM itab_display USING lt_data.

*&---------------------------------------------------------------------*
*&      Form  excel_read1
*&---------------------------------------------------------------------*
FORM excel_read1 CHANGING pt_data TYPE tt_data.

  DATA:
    lt_excel TYPE STANDARD TABLE OF alsmex_tabline,
    ls_excel TYPE alsmex_tabline,
    lv_data  TYPE tv_data,
    lv_error TYPE string.

  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = pa_fname
      i_begin_col             = 1
      i_begin_row             = 1
      i_end_col               = co_max_col
      i_end_row               = co_max_row
    TABLES
      intern                  = lt_excel
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.

  IF sy-subrc <> 0.
    WRITE: / 'SY-SUBRC = ', sy-subrc.
    MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
          WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_error.
    WRITE: / lv_error.
    STOP.
  ENDIF.

  LOOP AT lt_excel INTO ls_excel.
    lv_data = ls_excel-value.
    PERFORM itab_insert_value USING ls_excel-col ls_excel-row lv_data
                              CHANGING pt_data.
  ENDLOOP.
ENDFORM.                    "excel_read1

*&---------------------------------------------------------------------*
*&      Form  excel_read2
*&---------------------------------------------------------------------*
FORM excel_read2 CHANGING pt_data TYPE tt_data.

  DATA:
    lv_fname TYPE filename-fileintern,
    lv_error TYPE string.

  lv_fname = pa_fname.
  CALL FUNCTION 'FILE_READ_AND_CONVERT_SAP_DATA'
    EXPORTING
      i_filename           = lv_fname
      i_servertyp          = 'OLE2'
      i_fileformat         = 'XLS'
*     I_FIELD_SEPERATOR    =
*     I_LINE_HEADER        =
    TABLES
      i_tab_receiver       = pt_data
    EXCEPTIONS
      file_not_found       = 1
      close_failed         = 2
      authorization_failed = 3
      open_failed          = 4
      conversion_failed    = 5
      OTHERS               = 6.

  IF sy-subrc <> 0.
    WRITE: / 'SY-SUBRC = ', sy-subrc.
    MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
          WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_error.
    WRITE: / lv_error.
    STOP.
  ENDIF.
ENDFORM.                    "excel_read2

*&---------------------------------------------------------------------*
*&      Form  excel_read3
*&---------------------------------------------------------------------*
FORM excel_read3 CHANGING pt_data TYPE tt_data.

  DATA:
    lv_url(256) TYPE c,
    ls_range    TYPE soi_dimension_item,
    lt_range    TYPE soi_dimension_table,
    lt_excel    TYPE soi_generic_table,
    ls_excel    TYPE soi_generic_item,
    lv_data     TYPE tv_data,
    lv_col      TYPE tv_index,
    lv_row      TYPE tv_index.

  CONCATENATE 'file://' pa_fname INTO lv_url.

  ls_range-row     = 1.
  ls_range-column  = 1.
  ls_range-rows    = co_max_row.
  ls_range-columns = co_max_col.
  APPEND ls_range TO lt_range.

  CALL FUNCTION 'IMPORT_FROM_SPREADSHEET'
    EXPORTING
      item_url      = lv_url
      document_type = 'Excel.Sheet'
    TABLES
      data_table    = lt_excel
      ranges        = lt_range.

  LOOP AT lt_excel INTO ls_excel.
    lv_data = ls_excel-value.
    lv_col = ls_excel-column.
    lv_row = ls_excel-row.
    PERFORM itab_insert_value USING lv_col lv_row lv_data
                              CHANGING pt_data.
  ENDLOOP.
ENDFORM.                    "excel_read3

*&---------------------------------------------------------------------*
*&      Form  excel_read4
*&---------------------------------------------------------------------*
FORM excel_read4 CHANGING pt_data TYPE tt_data.

  DATA:
    lt_excel TYPE kcde_intern,
    ls_excel TYPE kcde_intern_struc,
    lv_data  TYPE tv_data,
    lv_error TYPE string.

  CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'
    EXPORTING
      filename                = pa_fname
      i_begin_col             = 1
      i_begin_row             = 1
      i_end_col               = co_max_col
      i_end_row               = co_max_row
    TABLES
      intern                  = lt_excel
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.

  IF sy-subrc <> 0.
    WRITE: / 'SY-SUBRC = ', sy-subrc.
    MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
          WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_error.
    WRITE: / lv_error.
    STOP.
  ENDIF.

  LOOP AT lt_excel INTO ls_excel.
    lv_data = ls_excel-value.
    PERFORM itab_insert_value USING ls_excel-col ls_excel-row lv_data
                              CHANGING pt_data.
  ENDLOOP.
ENDFORM.                    "excel_read4

*&---------------------------------------------------------------------*
*&      Form  excel_read5
*&---------------------------------------------------------------------*
FORM excel_read5 CHANGING pt_data TYPE tt_data.

  DATA:
    lt_raw_data(4096) TYPE c OCCURS 0,
    lt_data           TYPE STANDARD TABLE OF string,
    lv_error          TYPE string.

  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
*     I_FIELD_SEPERATOR    =
*     I_LINE_HEADER        =
      i_tab_raw_data       = lt_raw_data
      i_filename           = pa_fname
    TABLES
      i_tab_converted_data = pt_data
    EXCEPTIONS
      conversion_failed    = 1
      OTHERS               = 2.

  IF sy-subrc <> 0.
    WRITE: / 'SY-SUBRC = ', sy-subrc.
    MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
          WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_error.
    WRITE: / lv_error.
    STOP.
  ENDIF.
ENDFORM.                    "excel_read5

*&---------------------------------------------------------------------*
*&      Form  excel_read6
*&---------------------------------------------------------------------*
FORM excel_read6 CHANGING pt_data TYPE tt_data.

  DATA:
    lv_error          TYPE string.

  CALL FUNCTION 'UPLOAD_XLS_FILE_2_ITAB'
    EXPORTING
      i_filename = pa_fname
    TABLES
      e_itab     = pt_data
    EXCEPTIONS
      file_error = 1
      OTHERS     = 2.

  IF sy-subrc <> 0.
    WRITE: / 'SY-SUBRC = ', sy-subrc.
    MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
          WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_error.
    WRITE: / lv_error.
    STOP.
  ENDIF.
ENDFORM.                    "excel_read6

*&---------------------------------------------------------------------*
*&      Form  excel_read7
*&---------------------------------------------------------------------*
FORM excel_read7 CHANGING pt_data TYPE tt_data.

  DATA:
    lo_control     TYPE REF TO i_oi_container_control,
    lo_error       TYPE REF TO i_oi_error,
    lo_container   TYPE REF TO cl_gui_custom_container,
    lo_document    TYPE REF TO i_oi_document_proxy,
    lo_spreadsheet TYPE REF TO i_oi_spreadsheet,
    lv_url         TYPE char256,
    lv_has_sheet   TYPE i.

* 1. control get
  CALL METHOD c_oi_container_control_creator=>get_container_control
    IMPORTING
      control = lo_control
      error   = lo_error.

  IF lo_error->has_failed = 'X'.
    CALL METHOD lo_error->raise_message( type = 'E' ).
  ENDIF.

* 2. container create
  CREATE OBJECT lo_container
    EXPORTING
*     parent                      =
      container_name              = 'CONTAINER'
*     style                       =
*     lifetime                    = lifetime_default
*     repid                       =
*     dynnr                       =
*     no_autodef_progid_dynnr     =
    EXCEPTIONS
      cntl_error                  = 1
      cntl_system_error           = 2
      create_error                = 3
      lifetime_error              = 4
      lifetime_dynpro_dynpro_link = 5
      OTHERS                      = 6.

  ASSERT sy-subrc = 0.

* 3. control init with container
  CALL METHOD lo_control->init_control
    EXPORTING
*     dynpro_nr                = SY-DYNNR
*     gui_container            = ' '
      inplace_enabled          = 'X'
*     inplace_mode             = 0
*     inplace_resize_documents = ' '
      inplace_scroll_documents = 'X'
*     inplace_show_toolbars    = 'X'
*     no_flush                 = ' '
*     parent_id                = cl_gui_cfw=>dynpro_0
      r3_application_name      = 'Excel'
*     register_on_close_event  = ' '
*     register_on_custom_event = ' '
*     rep_id                   = SY-REPID
*     shell_style              = 1384185856
      parent                   = lo_container
*     name                     =
*     autoalign                = 'x'
    IMPORTING
      error                    = lo_error
    EXCEPTIONS
      javabeannotsupported     = 1
      OTHERS                   = 2.

  ASSERT sy-subrc = 0.

  IF lo_error->has_failed = 'X'.
    CALL METHOD lo_error->raise_message( type = 'E' ).
  ENDIF.

* 4. control get proxy
  CALL METHOD lo_control->get_document_proxy
    EXPORTING
*     document_format    = 'NATIVE'
      document_type      = soi_doctype_excel_sheet " 'Excel.Sheet'
*     no_flush           = ' '
*     register_container = ' '
    IMPORTING
      document_proxy     = lo_document
      error              = lo_error.

  IF lo_error->has_failed = 'X'.
    CALL METHOD lo_error->raise_message( type = 'E' ).
  ENDIF.

* 5. document open
  CONCATENATE 'FILE://' pa_fname INTO lv_url.

  CALL METHOD lo_document->open_document
    EXPORTING
*     document_title   = ' '
      document_url     = lv_url
*     no_flush         = ' '
      open_inplace     = 'X'
      open_readonly    = 'X'
*     protect_document = ' '
*     onsave_macro     = ' '
*     startup_macro    = ''
*     user_info        =
    IMPORTING
      error            = lo_error.

  IF lo_error->has_failed = 'X'.
    CALL METHOD lo_error->raise_message( type = 'E' ).
  ENDIF.

* 6. spreadsheet check exists
  CALL METHOD lo_document->has_spreadsheet_interface
*   EXPORTING
*     no_flush     = ' '
    IMPORTING
      error        = lo_error
      is_available = lv_has_sheet.

  IF lo_error->has_failed = 'X'.
    CALL METHOD lo_error->raise_message( type = 'E' ).
  ENDIF.

  IF NOT lv_has_sheet IS INITIAL.

*   7. spreadsheet get
    CALL METHOD lo_document->get_spreadsheet_interface
      EXPORTING
        no_flush        = ' '
      IMPORTING
        error           = lo_error
        sheet_interface = lo_spreadsheet.

    IF lo_error->has_failed = 'X'.
      CALL METHOD lo_error->raise_message( type = 'E' ).
    ENDIF.

*   8. data read
    DATA:
      lv_row_start TYPE i value 1,   " first row
      lv_row_block TYPE i value 100, " number of rows read in one block (range)
      lv_row_empty TYPE i value 0,   " count of empty rows at the end of block
      lt_value     TYPE soi_generic_table,
      ls_value     TYPE soi_generic_item,
      lt_rangesdef TYPE soi_dimension_table,
      ls_rangesdef TYPE soi_dimension_item,
      lt_ranges    TYPE soi_range_list,
      ls_data      TYPE ts_data,
      lv_col_index TYPE tv_index,
      lv_fieldname TYPE string.

    FIELD-SYMBOLS:
      <fs_value> TYPE tv_data.

    WHILE lv_row_empty < 5. " max empty rows

*     range create
      CLEAR ls_rangesdef.
      CLEAR lt_rangesdef.
      ls_rangesdef-row     = lv_row_start.
      ls_rangesdef-column  = 1.
      ls_rangesdef-rows    = lv_row_block.
      ls_rangesdef-columns = co_max_col.
      INSERT ls_rangesdef INTO TABLE lt_rangesdef.

*     data read from range
      CALL METHOD lo_spreadsheet->get_ranges_data
        EXPORTING
*         no_flush  = ' '
*         all       = ' '
*         updating  = -1
          rangesdef = lt_rangesdef
        IMPORTING
          contents  = lt_value
          error     = lo_error
        CHANGING
          ranges    = lt_ranges.

      IF lo_error->has_failed = 'X'.
        CALL METHOD lo_error->raise_message( type = 'E' ).
      ENDIF.

*     data takeover
      LOOP AT lt_value INTO ls_value.

        AT NEW row.
          CLEAR ls_data.
        ENDAT.

        lv_col_index = ls_value-column.
        CONCATENATE 'VALUE_' lv_col_index INTO lv_fieldname.
        ASSIGN COMPONENT lv_fieldname OF STRUCTURE ls_data TO <fs_value>.
        ASSERT sy-subrc = 0.
        <fs_value> = ls_value-value.

        AT END OF row.
          APPEND ls_data TO pt_data.

          IF ls_data IS INITIAL.
            ADD 1 TO lv_row_empty.
          ELSE.
            CLEAR lv_row_empty.
          ENDIF.
        ENDAT.
      ENDLOOP.

      ADD lv_row_block TO lv_row_start.
    ENDWHILE.

*   data delete empty rows at the end
    DATA:
      lv_index TYPE sy-tabix.

    lv_index = lines( pt_data ).

    WHILE lv_index > 0.
      READ TABLE pt_data INTO ls_data INDEX lv_index.
      IF ls_data IS INITIAL.
        DELETE pt_data INDEX lv_index.
      ELSE.
        EXIT.
      ENDIF.
      SUBTRACT 1 FROM lv_index.
    ENDWHILE.
  ELSE.
    MESSAGE e323(bf00) WITH pa_fname RAISING file_error.
  ENDIF.

* 9. final cleaning
  IF NOT lo_spreadsheet IS INITIAL.
    FREE lo_spreadsheet.
  ENDIF.

  IF NOT lo_document IS INITIAL.
    CALL METHOD lo_document->close_document.
    CALL METHOD lo_document->release_document.
    FREE lo_document.
  ENDIF.

  IF NOT lo_control IS INITIAL.
    CALL METHOD lo_control->destroy_control.
    FREE lo_control.
  ENDIF.
ENDFORM.                    "excel_read7

*&---------------------------------------------------------------------*
*&      Form  itab_insert_value
*&---------------------------------------------------------------------*
FORM itab_insert_value USING    pi_col   TYPE tv_index
                                pi_row   TYPE tv_index
                                pi_value TYPE tv_data
                       CHANGING pt_data  TYPE tt_data.

  DATA:
    lv_fieldname TYPE string.

  FIELD-SYMBOLS:
    <fs_data>  TYPE ts_data,
    <fs_value> TYPE tv_data.

  CHECK pi_value <> ''.

  WHILE pi_row > lines( pt_data ).
    INSERT INITIAL LINE INTO TABLE pt_data.
  ENDWHILE.

  READ TABLE pt_data ASSIGNING <fs_data> INDEX pi_row.
  ASSERT sy-subrc = 0.
  CONCATENATE 'VALUE_' pi_col INTO lv_fieldname.
  ASSIGN COMPONENT lv_fieldname OF STRUCTURE <fs_data> TO <fs_value>.
  ASSERT sy-subrc = 0.
  <fs_value> = pi_value.
ENDFORM.                    "itab_insert_value

*&---------------------------------------------------------------------*
*&      Form  itab_display
*&---------------------------------------------------------------------*
FORM itab_display USING pt_data  TYPE tt_data.

  DATA:
    lv_count TYPE i,
    ls_data  TYPE ts_data,
    lv_value TYPE tv_data.

  lv_count = lines( pt_data ).

  WRITE: / 'Number of lines', lv_count.
  SKIP.

  LOOP AT pt_data INTO ls_data.

    WRITE / ''. " sy-tabix.
    DO co_max_col TIMES VARYING lv_value FROM ls_data-value_0001
                                         NEXT ls_data-value_0002.
      WRITE: lv_value(10).
    ENDDO.
  ENDLOOP.
ENDFORM.                    "itab_display

See other related notes on my website:

Scroll to Top