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.
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.