Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

Author: Dennis
Submitted: 04 March 2008

Simple class to read a MS Excel file and import the data directly into an SAP table.

Be careful using this import functionality! There are no data checks (as yet) also the SAP table is not locked before the records
are created and/or changed.

This code snippet has 2 parts:

  1. Class ZCL_IMPORT_EXCEL - Does the actual import
  2. Program Z_IMPORT_XLS - Program used as an GUI to call class ZCL_IMPORT_EXCEL

Pre-requisites MS-Excel spreadsheet

  1. The data must be on the first worksheet
  2. Sequence of the columns in the MS-Excel spreadsheet must be equal to the field   sequence in SAP Table definition. Hidden columns in MS-Excel are NOT ignored during import!
  3. Date fields must have the format "Text" or "General" with mask MM.DD.YYYY

Tips & Tricks

  1. Hidden columns in MS-Excel are NOT ignored during import! You can however put additional columns at the end.

Class/Method definition

Class

 

Method

 

 

 

 

 

 


Source code

METHOD import_xls.

* declarations
* dynamic internal table
  FIELD-SYMBOLS: <tb_data> TYPE STANDARD TABLE.
* variables excel import
  TYPE-POOLS: truxs.
  DATA:
    tb_data TYPE REF TO data,
    it_raw TYPE truxs_t_text_data.

* create dynamic table from structure name
  CREATE DATA tb_data TYPE TABLE OF (i_tablename).
  ASSIGN tb_data->* TO <tb_data>.

* load excel file into internal table
  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
      i_line_header       = 'X'
      i_tab_raw_data     = it_raw       " work table
      i_filename           = i_filename
    TABLES
      i_tab_converted_data = <tb_data>    " excel data
    EXCEPTIONS
      conversion_failed  = 1
      OTHERS               = 2.
* error check
  IF sy-subrc <> 0.
    IF NOT sy-msgno IS INITIAL.
      MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
              WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 RAISING error_import_excel.
    ELSE.
      RAISE error_import_excel.
    ENDIF.
  ELSE.
*   update table
    MODIFY (i_tablename) FROM TABLE <tb_data>.
    IF sy-subrc <> 0.
*     error creating/changing records
      RAISE error_updating_table.
    ELSE.
*     return number of processed records
      e_processed = sy-dbcnt.
    ENDIF.
  ENDIF.

----

 REPORT z_import_xls.
*------------------------------------------------------------------------
* global declarations
*------------------------------------------------------------------------
TABLES: dd02l.
DATA:
  i_processed   TYPE sydbcnt.       "processed records

*------------------------------------------------------------------------
* selection screen
*------------------------------------------------------------------------
PARAMETERS: pa_file TYPE  rlgrap-filename OBLIGATORY.
SELECT-OPTIONS: so_tabl FOR dd02l-tabname OBLIGATORY NO INTERVALS NO-EXTENSION.

*------------------------------------------------------------------------
* at selection screen event
*------------------------------------------------------------------------
AT SELECTION-SCREEN ON VALUE-REQUEST FOR pa_file.
  CALL FUNCTION 'F4_FILENAME'
    EXPORTING
      field_name = 'PA_FILE'
    IMPORTING
      file_name  = pa_file.

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

* import ms-excel file into sap table
  CALL METHOD zcl_import_excel=>import_xls
    EXPORTING
      i_filename           = pa_file
      i_tablename          = so_tabl-low
    IMPORTING
      e_processed          = i_processed
    EXCEPTIONS
      error_import_excel   = 1
      error_updating_table = 2
      OTHERS               = 3.
  IF sy-subrc <> 0.
    IF NOT sy-msgno IS INITIAL.
      MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
      WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
    ELSE.
      MESSAGE ID 'ZCONK' TYPE 'E' NUMBER '000'
      WITH 'Unknow error importing MS-Excel data'(m01).
    ENDIF.
  ELSE.
*   show results on screen
    WRITE:/ 'Import table:'(t01), so_tabl-low.
    WRITE:/ 'Data source:'(t02), pa_file.
    WRITE:/ 'Number of created/changed records:'(t03), i_processed.
  ENDIF.
  • No labels

3 Comments

  1. I tried your code example, but I always get a program dump when it tries to process the date field in my spreadsheet.  I have defined the date as General.  In the spreadsheet it looks like this 12319999.  Is this correct?

  2. Guest

    Thanks for this helpful article but i have a question when i upload the file for the first time it is uploaded the data successfully but when i need to add new data it delete the previously inserted data and insert the new data . How i can add new data plus existing data in the table.

  3. 1) Some bugs
    2) Please provide code, that can be running also in a Basis System: no references to type-pools and function modules, that does not exist

    3) Use SAPLink