Posted: Sat Sep 13, 2008 7:41 pm Post subject: Import MS-Excel file directly into SAP Table
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.
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.
