Skip to Content
Author's profile photo Former Member

How to get excel data into internal table in SAP SRM

In SRM we cannot use many functional modules which we have used in ECC ( GUI_UPLOAD / ALSM_EXCEL …) to read excel data to internal table .

I will show the coding where we can pass excel date to internal table by reading column by column and row by row .

DATA : gv_fname1 TYPE string.

CONSTANTS: tabu TYPE x VALUE 9.
FIELD-SYMBOLS: <pointer>.
DATA: sep(1).
DATA : v_len TYPE i,
v_uscore
TYPE c VALUE ‘_’,
v_bukrs_chk
TYPE string,
v_bukrs_len
TYPE string,
v_cat_strlen
TYPE i,
cnt TYPE i VALUE 0,
v_char
TYPE c,
v_flag
TYPE i,
v_delimit
(50) TYPE c VALUE ‘~!@#$%^&*()_+-={}|[]\:”/;<>?,.’,
v_deliv_pad
(10) TYPE n,
v_asset
(8) TYPE n,
v_sep
(1) TYPE c VALUE ‘/’,
v_message
TYPE string,
v_temp
TYPE sytabix,
lx_user
TYPE usr02,
lv_htype
LIKE dd01vdatatype.

SELECTION-SCREEN  BEGIN OF BLOCK b1 WITH FRAME TITLE text001.
SELECTION-SCREEN SKIP 1.
PARAMETERSp_file1 LIKE rlgrapfilename OBLIGATORY.
SELECTION-SCREEN  END   OF BLOCK b1.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file1.
PERFORM get_filename USING p_file1.

START-OF-SELECTION.
IF NOT p_file1 IS INITIAL.
gv_fname1 
= p_file1.
PERFORM call_excel TABLES lt_data USING gv_fname1 1 1 9999 100.
ENDIF.

*&———————————————————————*
*&      Form  GET_FILENAME
*&———————————————————————*
*       text
*———————————————————————-*
*      –>P_P_FILE1  text
*———————————————————————-*
FORM get_filename  USING    lv_file . “p_p_file1.

CALL FUNCTION ‘WS_FILENAME_GET’
EXPORTING
def_filename    
= space
def_path        
= space
mask             = ‘,*.*,*.*.’
mode             = space
title            = space
IMPORTING
filename        
= lv_file
*          RC               =
EXCEPTIONS
inv_winsys      
= 1
no_batch        
= 2
selection_cancel
= 3
selection_error 
= 4
OTHERS           = 5.
ENDFORM.                    ” GET_FILENAME

*&———————————————————————*
*&      Form  CALL_EXCEL
*&———————————————————————*
*       text
*———————————————————————-*
*      –>P_LT_DATA  text
*      –>P_GV_FNAME1  text
*      –>P_1      text
*      –>P_1      text
*      –>P_9999   text
*      –>P_100    text
*———————————————————————-*
FORM call_excel  TABLES   lt_data
USING value(arch)
value(fila_1)
value(col_1)
value(fila_2)
value(col_2).

DATA: excel_line(4096) OCCURS 10 WITH HEADER LINE.
DATA: app TYPE obj_record,
workbook
TYPE obj_record,
worksheet
TYPE obj_record,
cell_1 
TYPE obj_record,
cell_2
TYPE obj_record,
range
TYPE obj_record.

CREATE OBJECT app ‘EXCEL.APPLICATION’.
IF sysubrc NE 0.
” error.
EXIT.
ENDIF.

CALL METHOD OF app ‘WORKBOOKS’ = workbook.
CALL METHOD OF workbook ‘OPEN’ EXPORTING #1 = arch.

IF sysubrc NE 0.
WRITE:/ ‘Error en excel’.
ENDIF.

GET PROPERTY OF app ‘ACTIVESHEET’ = worksheet.

CALL METHOD OF worksheet ‘Cells’ = cell_1
EXPORTING
#1
= fila_1
#2
= col_1.

CALL METHOD OF worksheet ‘Cells’ = cell_2
EXPORTING
#1
= fila_2
#2
= col_2.

CALL METHOD OF worksheet ‘RANGE’ = range
EXPORTING
#1
= cell_1
#2
= cell_2.

CALL METHOD OF range ‘SELECT’.
CALL METHOD OF range ‘COPY’.

CALL FUNCTION ‘CLPB_IMPORT’
TABLES
data_tab  
= excel_line
EXCEPTIONS
clpb_error
= 1
OTHERS     = 2.
ASSIGN sep TO <pointer> TYPE ‘X’.
<pointer>
= tabu.

PERFORM delimit_columns TABLES excel_line lt_data USING sep.

SET PROPERTY OF app ‘CutCopyMode’ = 0.

CALL METHOD OF app ‘QUIT’.
FREE OBJECT app.

ENDFORM.                    ” CALL_EXCEL
*&———————————————————————*
*&      Form  DELIMIT_COLUMNS
*&———————————————————————*
*       text
*———————————————————————-*
*      –>P_EXCEL_LINE  text
*      –>P_LT_DATA  text
*      –>P_SEP  text
*———————————————————————-*
FORM delimit_columns   TABLES excel_line itab USING sep.
DATA: BEGIN OF xls OCCURS 5,
value(1024),
END OF xls.
DATA: len TYPE i.
LOOP AT excel_line.
SHIFT excel_line LEFT DELETING LEADING sep.
SHIFT excel_line LEFT DELETING LEADING ‘ ‘.
CONDENSE excel_line.
len
= STRLEN( excel_line ).

IF len EQ 0.
CONTINUE.
ENDIF.

SPLIT excel_line AT sep INTO TABLE xls.
LOOP AT xls.
ASSIGN COMPONENT sytabix OF STRUCTURE itab TO <pointer>.
<pointer>
= xlsvalue.
ENDLOOP.
APPEND itab.
CLEAR itab.
REFRESH xls.
*  ENDIF.
ENDLOOP.

ENDFORM.                    ” DELIMIT_COLUMNS

Assigned tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.