SAP R/3 форум ABAP консультантов
Russian ABAP Developer's Club

Home - FAQ - Search - Memberlist - Usergroups - Profile - Log in to check your private messages - Register - Log in - English
Blogs - Weblogs News

Import MS-Excel file directly into SAP Table



 
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> OLE2, Excel, WinWord
View previous topic :: View next topic  
Author Message
admin
Администратор
Администратор



Joined: 01 Sep 2007
Posts: 1636

PostPosted: Sat Sep 13, 2008 7:41 pm    Post subject: Import MS-Excel file directly into SAP Table Reply with quote

Author: Dennis

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:
Class ZCL_IMPORT_EXCEL - Does the actual import
Program Z_IMPORT_XLS - Program used as an GUI to call class ZCL_IMPORT_EXCEL

Pre-requisites MS-Excel spreadsheet
The data must be on the first worksheet
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!
Date fields must have the format "Text" or "General" with mask MM.DD.YYYY

Tips & Tricks
Hidden columns in MS-Excel are NOT ignored during import! You can however put additional columns at the end.
Class/Method definition
Class


Method

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.


Code:
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.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> OLE2, Excel, WinWord All times are GMT + 4 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You can download files in this forum


All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG.
SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG.
Every effort is made to ensure content integrity. Use information on this site at your own risk.