Skip to end of metadata
Go to start of metadata

Upload Excel document into internal table

 ABAP code for uploading an Excel document into an internal table. See code below for structures. The
Code is base on uploading a simple Excel spreadsheet.
  
 
 
*..............................................................

*: Description                                                :

*: -----------                                                :

*: This is a simple example program to get data from an excel :

*: file and store it in an internal table.                    :

*:                                                            :

*: Author : www.sapdev.co.uk                                  :

*:                                                            :

*: SAP Version : 4.7                                          :

*:............................................................:

REPORT zupload_excel_to_itab NO STANDARD PAGE HEADING.

 
TYPES:   BEGIN OF t_datatab ,

         col1(25)  TYPE c,

         col2(30)  TYPE c,

         col3(30)  TYPE c,

         col4(30)  TYPE c,

         col5(30)  TYPE c,

         col6(30)  TYPE c,

         col7(30) TYPE c,

         col8(30)  TYPE c,

         col9(30)  TYPE c,

         col10(30)  TYPE c,

         col11(30)    TYPE c,

       END OF t_datatab.

DATA: it_datatab TYPE STANDARD TABLE OF t_datatab INITIAL SIZE 0,

      wa_datatab TYPE t_datatab.

 
DATA : gd_scol   TYPE i VALUE '1',

       gd_srow   TYPE i VALUE '1',

       gd_ecol   TYPE i VALUE '256',

       gd_erow   TYPE i VALUE '65536'.

 
DATA: it_tab TYPE filetable,

      gd_subrc TYPE i.

 
*Selection screen definition

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

PARAMETERS:  p_file LIKE rlgrap-filename

               DEFAULT 'c:\test.xls' OBLIGATORY.   " File Name

SELECTION-SCREEN END OF BLOCK b1.

 
 
***********************************************************************

  • AT SELECTION-SCREEN

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

  REFRESH: it_tab.
CALL METHOD cl_gui_frontend_services=>file_open_dialog
    EXPORTING
      window_title     = 'Select File'
      default_filename = '*.xls'
      multiselection   = ' '
    CHANGING
      file_table       = it_tab
      rc               = gd_subrc.
   LOOP AT it_tab INTO p_file.
*    so_fpath-sign = 'I'.
*    so_fpath-option = 'EQ'.
*    append so_fpath.
  ENDLOOP.
  ***********************************************************************

  • START-OF-SELECTION.
    START-OF-SELECTION.
      PERFORM upload_excel_file TABLES   it_datatab
                                 USING   p_file
                                         gd_scol
                                         gd_srow
                                         gd_ecol
                                         gd_erow.
      ***********************************************************************
  • END-OF-SELECTION.
    END-OF-SELECTION.
      LOOP AT it_datatab INTO wa_datatab.
        WRITE:/ wa_datatab-col1,
                wa_datatab-col2,
                wa_datatab-col3,
                wa_datatab-col4,
                wa_datatab-col5,
                wa_datatab-col6,
                wa_datatab-col7,
                wa_datatab-col8,
                wa_datatab-col9,
                wa_datatab-col10,
                wa_datatab-col11.
      ENDLOOP.
      &--------------------------------------------------------------------
    *&      Form  UPLOAD_EXCEL_FILE
    &--------------------------------------------------------------------
    *       upload excel spreadsheet into internal table
    ---------------------------------------------------------------------
    *      -->P_TABLE    Table to return excel data into
    *      -->P_FILE     file name and path
    *      -->P_SCOL     start column
    *      -->P_SROW     start row
    *      -->P_ECOL     end column

 


*      -->P_EROW     end row

---------------------------------------------------------------------

FORM upload_excel_file TABLES   p_table

                       USING    p_file

                                p_scol

                                p_srow

                                p_ecol

                                p_erow.

 
  DATA : lt_intern TYPE  kcde_cells OCCURS 0 WITH HEADER LINE.

 
 
 
 

  • Has the following format:

*             Row number   | Colum Number   |   Value

*             ---------------------------------------

*      i.e.     1                 1             Name1

*               2                 1             Joe

 
  DATA : ld_index TYPE i.

  FIELD-SYMBOLS : .

 

  • Note: Alternative function module - 'ALSM_EXCEL_TO_INTERNAL_TABLE'

  CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'

    EXPORTING

      filename                = p_file

      i_begin_col             = p_scol

      i_begin_row             = p_srow

      i_end_col               = p_ecol

      i_end_row               = p_erow

    TABLES

      intern                  = lt_intern

    EXCEPTIONS

      inconsistent_parameters = 1

      upload_ole              = 2

      OTHERS                  = 3.

  IF sy-subrc <> 0.

    FORMAT COLOR COL_BACKGROUND INTENSIFIED.

    WRITE:/ 'Error Uploading file'.

    EXIT.

  ENDIF.

 
  IF lt_intern[] IS INITIAL.

    FORMAT COLOR COL_BACKGROUND INTENSIFIED.

    WRITE:/ 'No Data Uploaded'.

    EXIT.

  ELSE.

    SORT lt_intern BY row col.

    LOOP AT lt_intern.

      MOVE lt_intern-col TO ld_index.

      ASSIGN COMPONENT ld_index OF STRUCTURE p_table TO .

      MOVE lt_intern-value TO .

      AT END OF row.

        APPEND p_table.

CLEAR p_table.

      ENDAT.

    ENDLOOP.

  ENDIF.

ENDFORM.                    "UPLOAD_EXCEL_FILE

PROGRAM CODE 2

 FORM upload_file_into_table.
* Data Declarations.......................................
  DATA : L_INTERN TYPE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.
  DATA : L_INDEX TYPE I.
  DATA : L_START_COL TYPE I VALUE '1',
         L_START_ROW TYPE I VALUE '1',
         L_END_COL TYPE I VALUE '256',
         L_END_ROW TYPE I VALUE '65536'.
* Field Symbols...........................................
  FIELD-SYMBOLS : <FS>.
  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
       EXPORTING
            FILENAME = P_PCFILE
            I_BEGIN_COL = L_START_COL
            I_BEGIN_ROW = L_START_ROW
            I_END_COL = L_END_COL
            I_END_ROW = L_END_ROW
       TABLES
            INTERN = L_INTERN
       EXCEPTIONS
            INCONSISTENT_PARAMETERS = 1
            UPLOAD_OLE = 2
            OTHERS = 3.
  IF sy-subrc > 0.
    message i000 with 'Error opening PC file ... RC=' sy-subrc.
    EXIT.
  ENDIF.
  IF L_INTERN[] IS INITIAL.
    message i051 with 'No data uploaded!'.
    EXIT.
  ELSE.
    SORT L_INTERN BY ROW COL.
    LOOP AT L_INTERN.
      MOVE L_INTERN-COL TO L_INDEX.
      ASSIGN COMPONENT L_INDEX OF STRUCTURE inrec TO <FS>.
      if sy-subrc = 0. " Incase there are more xls columns than fields
        MOVE L_INTERN-VALUE TO <FS>.
      endif.
      AT END OF ROW.
        APPEND inrec.
        CLEAR inrec.
      ENDAT.
    ENDLOOP.
  ENDIF.
ENDFORM. " upload_file_into_table
          

  • No labels

5 Comments

  1. Unknown User (103mk8cnt)

    Hi there, I would like to know if there is a way to upload excel document into internal table executing the program in background mode

  2. Unknown User (102zg33hx)

    Your second program is beautyful and simple. Thanks.

  3. Former Member

     

    Thanks for the pgm. Works good.

  4. Former Member

    WHAT IS inrec in program 2?

  5. Former Member

    @sophanith vong - inrec is a structure, like a line of table p_table (which is one of the parameters in FORM upload_excel_file).

    When it's called, you can see that table it_datatab is passed in, which is typed against t_datatab, declared at the very top of program zupload_excel_to_itab.

    The structures and tables in program 2 don't correspond to those used further up, presumably because they're actually samples from two different programs.

     

    @mansi asnani - I can't see your field symbol definitions or assignments in the first program.

    I thought it might be my browser interpreting them as HTML tags, but they don't appear to be in the source code either, and the entries in the second program look fine.

    I presume they should just be displaying

    &lt;FS&gt;

    Is this just down to the copy / paste process when uploading the code?