SAP ABAP - Report Program To Upload Excel Sheet Data Into Internal Table Using FM ALSM_EXCEL_TO_INTERNAL_TABLE.

This function module is used to transfer data from excel sheet to internal table. The only problem with this FM is it transfers all the excel data as char type and hence type conversion is needed to be done.

For Example, IDATE is of type DATS - Date field (YYYYMMDD) stored as char(8). In excel sheet the date format was : 01.01.2011. hence data conversion has to take place to save the date in internal table in the format YYYYMMDD.


Again PRICE is of type CURR of length 13 & decimals 2. Since the FM retrieves the data from excel in CHAR type, hence type conversion has to be done to convert CHAR type to CURR type. PRICE in excel sheet is saved as 23,864.00 .


SAMPLE PROGRAM:

REPORT z_zvgaq_update.

TYPES: BEGIN OF x_zvgaq,
mandt LIKE zvgaq-mandt,
werks LIKE zvgaq-werks,
matnr LIKE zvgaq-matnr,
idate LIKE zvgaq-idate,
drate LIKE zvgaq-drate,
price LIKE zvgaq-price,
udate LIKE zvgaq-udate,
END OF x_zvgaq.

DATA: it_zvgaq TYPE STANDARD TABLE OF x_zvgaq,
wa_zvgaq TYPE x_zvgaq.

DATA: it_zvgaq1 TYPE STANDARD TABLE OF x_zvgaq.

DATA:price_tmp(17) TYPE C,
idate_tmp(10) TYPE C,
udate_tmp(10) TYPE C.

DATA : dd(2) TYPE C,
mm(2) TYPE C,
yy(4) TYPE C,
dat(8) TYPE C.

DATA: it_intern LIKE STANDARD TABLE OF alsmex_tabline,
wa_intern LIKE alsmex_tabline.

SELECTION-SCREEN SKIP
SELECTION SCREEN BEGIN OF BLOCK block1 WITH FRAME title text-001.
SELECTION-SCREEN SKIP.
PARAMETERS: p_file TYPE rlgrap-filename OBLIGATORY.
SELECTION-SCREEN SKIP.
SELECTION-SCREEN END OF BLOCK block1.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

CALL FUNCTION 'F4_FILENAME'
EXPORTING
program_name = syst-cprog
dynpro_number = syst-dynnr
field_name = ' '
IMPORTING
file_name = p_file.

START-OF-SELECTION.
PERFORM upload.

*&---------------------------------------------------------------------*
*& Form UPLOAD
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM upload.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = p_file
i_begin_col = 1
i_begin_row = 2
i_end_col = 10
i_end_row = 65536
TABLES
intern = it_intern
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.

CLEAR wa_zvgaq .
LOOP AT it_intern INTO wa_intern.

CASE wa_intern-col .
WHEN '1'.
wa_zvgaq-mandt = wa_intern-value.

WHEN '2'.
IF wa_intern-value CP 'E*'.
wa_zvgaq-werks = wa_intern-value+3(04).
ELSE.
wa_zvgaq-werks = wa_intern-value.
ENDIF.

WHEN '3'.
wa_zvgaq-matnr = wa_intern-value.

WHEN '4'.
idate_tmp = wa_intern-value.
REPLACE '.' WITH '' INTO idate_tmp.
REPLACE '.' WITH '' INTO idate_tmp.
CONDENSE idate_tmp NO-GAPS.
dd = idate_tmp(2).
mm = idate_tmp+2(2).
yy = idate_tmp+4(4).
CONCATENATE yy mm dd INTO dat.
MOVE dat TO wa_zvgaq-idate.
CLEAR:idate_tmp,dd,mm,yy,dat.

WHEN '5'.
wa_zvgaq-drate = wa_intern-value.

WHEN '6'.
price_tmp = wa_intern-value.
REPLACE ',' WITH '' INTO price_tmp.
CONDENSE price_tmp NO-GAPS.
MOVE price_tmp TO wa_zvgaq-price.
CLEAR price_tmp.

WHEN '7'.
udate_tmp = wa_intern-value.
REPLACE '.' WITH '' INTO udate_tmp.
REPLACE '.' WITH '' INTO udate_tmp.
CONDENSE udate_tmp NO-GAPS.
dd = udate_tmp(2).
mm = udate_tmp+2(2).
yy = udate_tmp+4(4).
CONCATENATE yy mm dd INTO dat.
MOVE dat TO wa_zvgaq-udate.
CLEAR:udate_tmp,dd,mm,yy,dat.
ENDCASE.

AT END OF row.
APPEND wa_zvgaq TO it_zvgaq.
CLEAR wa_zvgaq .
ENDAT.
ENDLOOP.

DELETE ADJACENT DUPLICATES FROM it_zvgaq COMPARING ALL FIELDS.

ENDFORM. "upload


THE EXCEL SHEET FORMAT & THE INPUT SCREEN LOOKS LIKE

MORE PROGRAMS ON REPORT PROGRAMMING:

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

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

- BDC To Create Condition Types & Amount For Tcode MEK1.

- Type Conversion ( Char To Curr Type).

- Type Conversion ( Curr To Char Type).

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