SAP ABAP - Uploading Large Data From Excel Sheet To Internal Table Using FM ALSM_EXCEL_TO_INTERNAL_TABLE.

To upload data from excel sheet having more than 65,356 records into an internal table.
In the sample code below a loop has been made on the function module to read
10000 records from excel sheet at a time.

SAMPLE PROGRAM:

REPORT zp2p_test_sample1.

TABLES:mvke,s001,s004,knvv.

TYPES : BEGIN OF t_final,
spwoc TYPE spwoc,
vkorg TYPE vkorg,
vtweg TYPE vtweg,
spart TYPE spart,
matnr TYPE matnr,
kunnr TYPE vbak-kunnr,
vbeln TYPE vbeln,
posnr TYPE posnr,
fkdat(10) TYPE c,
fkart TYPE fkart,
ummenge TYPE s001-ummenge,
umnetwr(15),
END OF t_final.

DATA: i_final TYPE TABLE OF t_final,
wa_final TYPE t_final,
wa_xcl_upd TYPE t_final.

DATA: i_excel TYPE TABLE OF alsmex_tabline WITH HEADER LINE.
DATA: wa_excel TYPE alsmex_tabline.


DATA: v_file TYPE rlgrap-filename.
DATA: w_total_rows TYPE i,
w_row_num TYPE i.

DATA: lv_counter_low TYPE i, "Bottom range of the rows to scan
lv_counter_hgh TYPE i, "Top range of the rows to scan
lv_increment TYPE i, "Size of the range we will scan
lv_max TYPE i. "This is the number of rows we plan to scan in the Excel file

lv_max = '200000'.
lv_counter_low = '1'. "Reading from first row
lv_increment = '9999'. "Increment by 9999 each loop
lv_counter_hgh = lv_counter_low + lv_increment.

CONSTANTS: c_vrsn(3) TYPE n VALUE '000'.

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
PARAMETERS : p_vkorg TYPE mvke-vkorg OBLIGATORY,
p_vrsio TYPE s001-vrsio OBLIGATORY,
p_excel TYPE rlgrap-filename OBLIGATORY.
SELECTION-SCREEN END OF BLOCK b1.

AT SELECTION-SCREEN.

AUTHORITY-CHECK OBJECT 'ZP2P_SORG'
ID 'VKORG' FIELD p_vkorg
ID 'ACTVT' FIELD '02'
ID 'ACTVT' FIELD '03'.

IF sy-subrc <> 0.
MESSAGE e919(zp2p) WITH p_vkorg. "No Authorization for &
ENDIF.

IF NOT p_vkorg IS INITIAL. "Validating SORG
SELECT COUNT(*) FROM mvke WHERE vkorg = p_vkorg.

IF sy-subrc NE 0.
MESSAGE 'Sales Org. Does Not Exit' TYPE 'E'.
ENDIF.

ENDIF.

IF p_vrsio = c_vrsn.
MESSAGE 'Version "000" is not allowed.' TYPE 'E'.
ENDIF.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_excel.

* GET THE EXCEL FILE FROM DESKTOP
CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
EXPORTING
program_name = syst-repid
dynpro_number = syst-dynnr
CHANGING
file_name = p_excel
EXCEPTIONS
mask_too_long = 1
OTHERS = 2.

IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.

START-OF-SELECTION.
PERFORM f_upload_excel.


*&---------------------------------------------------------------------*
*& Form F_UPLOAD_EXCEL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*

FORM f_upload_excel .
v_file = p_excel.

WHILE lv_counter_hgh <= lv_max AND lv_counter_low <= lv_max.

* UPLOAD EXCEL FILE DATA INTO INTERNAL TABLE.
 REFRESH: i_excel.
 CLEAR i_excel .

 CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
 EXPORTING
 filename = v_file
 i_begin_col = 1
 i_begin_row = lv_counter_low
 i_end_col = 11
 i_end_row = lv_counter_hgh
 TABLES
 intern = i_excel
 EXCEPTIONS
 inconsistent_parameters = 1
 upload_ole = 2
 OTHERS = 3.

 IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.

IF i_excel IS INITIAL.
MESSAGE 'The excel sheet is empty.' TYPE 'E'.
ENDIF.

* GET FIRST ROW RETRIEVED
READ TABLE i_excel INDEX 1.

* SET FIRST ROW RETRIEVED TO CURRENT ROW
w_row_num = i_excel-row.

* MOVE DATA FROM EXCEL FILE TO CORRESPONDING FIELD OF STRUCTURE
IF sy-subrc EQ 0.

LOOP AT i_excel INTO wa_excel.
* RESET WORKAREA

IF i_excel-row NE w_row_num.
APPEND wa_excel TO i_excel.
CLEAR wa_excel.
w_row_num = i_excel-row.
ENDIF.

CASE wa_excel-col.

WHEN 0001.
MOVE wa_excel-value TO wa_final-vkorg.

WHEN 0002.
MOVE wa_excel-value TO wa_final-vtweg.

WHEN 0003.
MOVE wa_excel-value TO wa_final-spart.

WHEN 0004.
MOVE wa_excel-value TO wa_final-matnr.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
EXPORTING
input = wa_final-matnr
IMPORTING
output = wa_final-matnr.

WHEN 0005.
MOVE wa_excel-value TO wa_final-kunnr.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
EXPORTING
input = wa_final-kunnr
IMPORTING
output = wa_final-kunnr.

WHEN 0006.
MOVE wa_excel-value TO wa_final-vbeln.

WHEN 0007.
MOVE wa_excel-value TO wa_final-posnr.

WHEN 0008.
MOVE wa_excel-value TO wa_final-fkdat.

WHEN 0009.
MOVE wa_excel-value TO wa_final-fkart.

WHEN 0010.
MOVE wa_excel-value TO wa_final-ummenge.

WHEN 0011.
MOVE wa_excel-value TO wa_final-umnetwr.

ENDCASE.

AT END OF row.
APPEND wa_final TO i_final.
CLEAR wa_final.
ENDAT.

ENDLOOP.
ENDIF.

* INCREMENT OUR RANGE
lv_counter_low = lv_counter_hgh + 1.
lv_counter_hgh = lv_counter_low + lv_increment.

* We SHOULD NOT scan any more than our max, so set our top end of the range
* to the total number of rows to scan if the w_Counter_hgh exceeds the max.

IF lv_counter_hgh > lv_max.
lv_counter_hgh = lv_max.
ENDIF.

ENDWHILE.

ENDFORM. "F_UPLOAD_EXCEL



MORE PROGRAMS ON REPORT PROGRAMMING:


- Upload Excel Sheet Data Into Internal Table Using FM ALSM_EXCEL_TO_INTERNAL_TABLE.

- Send an excel sheet to the concerned person in an email format via SAP System.

- Upload the financial documents from a legacy system (excel file) into SAP using transaction code FB01.

- Edit / Create Customer Specific Message / Text On Login Screen Via SE61.

- MM - Determine Shelf Life Of Materials.

- ...Back To Index On Report Programming.



Your suggestions and comments are welcome in this section.


Please mail all your contributions to administrator@abapmadeeasy.com We request you to mention your Name, Designation, Experience & Organization you are working for. Your posts will be verified and posted in this site with your name.

No comments:

Post a Comment