topic BDC using ExcelSheet in Application Development and Automation Discussions

BDC using ExcelSheet

Former Member — Fri, 15 Feb 2008 04:58:40 GMT

Sir,

I like to know how upload data from excelsheet (not copying data in notepad or text file) to SAP using BDC. plz , explain with example.

Re: BDC using ExcelSheet

Former Member — Fri, 15 Feb 2008 05:08:05 GMT

Please go though the following lines of code:

 ************************************************************************

D A T A D E C L A R A T I O N * 
************************************************************************

TABLES: ANEP,
BKPF.

TYPES: BEGIN OF TY_TABDATA,
MANDT LIKE SY-MANDT, " Client
ZSLNUM LIKE ZSHIFTDEPN-ZSLNUM, " Serial Number
ZASSET LIKE ZSHIFTDEPN-ZASSET, " Original asset that was transferred
ZYEAR LIKE ZSHIFTDEPN-ZYEAR, " Fiscal Year
ZPERIOD LIKE ZSHIFTDEPN-ZPERIOD, " Fiscal Period
ZSHIFT1 LIKE ZSHIFTDEPN-ZSHIFT1, " Shift No. 1
ZSHIFT2 LIKE ZSHIFTDEPN-ZSHIFT1, " Shift No. 2
ZSHIFT3 LIKE ZSHIFTDEPN-ZSHIFT1, " Shift No. 3
END OF TY_TABDATA.

*-------------------------------------------------------------------------------------

Declaration of the Internal Table with Header Line comprising of the uploaded data. 
*-------------------------------------------------------------------------------------
DATA: BEGIN OF IT_FILE_UPLOAD OCCURS 0.
INCLUDE STRUCTURE ALSMEX_TABLINE. " Rows for Table with Excel Data
DATA: END OF IT_FILE_UPLOAD.

************************************************************************

S E L E C T I O N - S C R E E N * 
************************************************************************
SELECTION-SCREEN: BEGIN OF BLOCK B1 WITH FRAME,
BEGIN OF BLOCK B2 WITH FRAME.

PARAMETERS: P_FNAME LIKE RLGRAP-FILENAME OBLIGATORY.

SELECTION-SCREEN: END OF BLOCK B2,
END OF BLOCK B1.

************************************************************************


E V E N T : AT S E L E C T I O N - S C R E E N * 
************************************************************************
AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FNAME.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
EXPORTING

PROGRAM_NAME = SYST-REPID 
DYNPRO_NUMBER = SYST-DYNNR 
FIELD_NAME = ' ' 
STATIC = 'X'

MASK = '*.*' 
CHANGING
FILE_NAME = P_FNAME

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.

************************************************************************


E V E N T : S T A R T - O F - S E L E C T I O N * 
************************************************************************
START-OF-SELECTION.

*



--------------------------------------------------------------------------------

Upload Excel file into Internal Table. 
*

--------------------------------------------------------------------------------
PERFORM UPLOAD_EXCEL_FILE.

*

--------------------------------------------------------------------------------

Organize the uploaded data into another Internal Table. 
*

--------------------------------------------------------------------------------
PERFORM ORGANIZE_UPLOADED_DATA.

************************************************************************

E V E N T : E N D - O F - S E L E C T I O N * 
************************************************************************
END-OF-SELECTION.

&---------------------------------------------------------------------
*& Form UPLOAD_EXCEL_FILE
&---------------------------------------------------------------------


text


--------------------------------------------------------------------------------

--> p1 text 
<-- p2 text
--------------------------------------------------------------------------------
 FORM UPLOAD_EXCEL_FILE .

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
FILENAME = P_FNAME
I_BEGIN_COL = 1
I_BEGIN_ROW = 3
I_END_COL = 7
I_END_ROW = 32000
TABLES
INTERN = IT_FILE_UPLOAD
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.

ENDFORM. " UPLOAD_EXCEL_FILE

&---------------------------------------------------------------------
*& Form ORGANIZE_UPLOADED_DATA
&---------------------------------------------------------------------

text


--------------------------------------------------------------------------------

--> p1 text 
<-- p2 text
--------------------------------------------------------------------------------
 FORM ORGANIZE_UPLOADED_DATA .

SORT IT_FILE_UPLOAD BY ROW
COL.

LOOP AT IT_FILE_UPLOAD.

CASE IT_FILE_UPLOAD-COL.

.................................................... 
WHEN 1.
WA_TABDATA-ZSLNUM = IT_FILE_UPLOAD-VALUE.
WHEN 2.
WA_TABDATA-ZASSET = IT_FILE_UPLOAD-VALUE.
WHEN 3.
WA_TABDATA-ZYEAR = IT_FILE_UPLOAD-VALUE.
WHEN 4.
WA_TABDATA-ZPERIOD = IT_FILE_UPLOAD-VALUE.
WHEN 5.
WA_TABDATA-ZSHIFT1 = IT_FILE_UPLOAD-VALUE.
WHEN 6.
WA_TABDATA-ZSHIFT2 = IT_FILE_UPLOAD-VALUE.
WHEN 7.
WA_TABDATA-ZSHIFT3 = IT_FILE_UPLOAD-VALUE.

.................................................... 
ENDCASE.

AT END OF ROW.
WA_TABDATA-MANDT = SY-MANDT.
APPEND WA_TABDATA TO IT_TABDATA.
CLEAR: WA_TABDATA.
ENDAT.

ENDLOOP.

ENDFORM. " ORGANIZE_UPLOADED_DATA

In the subroutine --> ORGANIZE_UPLOADED_DATA, data are organized as per the structure declared above.

Check these sample programs which uses your FM.

http://www.sap-img.com/abap/upload-direct-excel.htm

http://www.sapdevelopment.co.uk/file/file_upexcel2.htm

Reward if useful

Re: BDC using ExcelSheet

Former Member — Fri, 15 Feb 2008 05:08:06 GMT

Hi,

check out this code.

REPORT zbdc_rfi

NO STANDARD PAGE HEADING LINE-SIZE 255.

TYPE-POOLS: truxs.

TABLES: t100.

TYPES: BEGIN OF bdc.

INCLUDE TYPE bdcdata.

TYPES: END   OF bdc.

TYPES: BEGIN OF ty_itab,

project_id TYPE dpr_project-project_id,

aufnr TYPE aufk-aufnr,

vbeln TYPE vbak-vbeln,

auart TYPE vbak-auart,

matnr TYPE vbap-matnr,

zieme TYPE vbap-zieme,

kschl TYPE konv-kschl,

kbetr(13),

bemot   TYPE vbap-bemot,

zmeng(13),

mvgr5   TYPE vbap-mvgr5,

zzpernr TYPE vbap-zzpernr,

htext1(72),

htext2(72),

END   OF ty_itab.

TYPES: BEGIN OF ty_item,

matnr TYPE vbap-matnr,

zmeng(13),

zieme TYPE vbap-zieme,

mvgr5   TYPE vbap-mvgr5,

aufnr TYPE aufk-aufnr,

zzpernr TYPE vbap-zzpernr,

bemot   TYPE vbap-bemot,

arktx   TYPE vbap-arktx,

kschl   TYPE konv-kschl,

kbetr(13),

END  OF ty_item.

TYPES: BEGIN OF ty_pa0001,

pernr    TYPE persno,

ename    TYPE pa0001-ename,

END   OF ty_pa0001.

DATA: it_pa0001 TYPE TABLE OF ty_pa0001,

wa_pa0001 TYPE ty_pa0001.

DATA: itab     TYPE STANDARD TABLE OF ty_itab,

item     TYPE TABLE OF ty_item,

wa_item  TYPE ty_item,

wa_itab  TYPE ty_itab.

DATA: bdcdata  TYPE TABLE OF bdc,

wa_bdc   TYPE bdc.

DATA: mode TYPE c.

DATA: message TYPE TABLE OF bdcmsgcoll,

wa_message TYPE bdcmsgcoll.

DATA : count(2) TYPE n.

DATA : index(2) TYPE n.

DATA: it_raw    TYPE truxs_t_text_data.

DATA: lv_werks  TYPE vbap-werks.

DATA: lv_vbeln(10)  TYPE n.

DATA: l_mstring(480).

DATA: l_pernr(10) TYPE c.

FIELD-SYMBOLS : <fs_itab> TYPE ty_itab.

SELECTION-SCREEN: BEGIN OF BLOCK b1 WITH FRAME.

PARAMETERS: p_file TYPE rlgrap-filename,

rb1 RADIOBUTTON GROUP g1,

rb3 RADIOBUTTON GROUP g1.

SELECTION-SCREEN: SKIP 2,

COMMENT 1(79) text-001.

SELECTION-SCREEN: END OF BLOCK b1.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

field_name = 'P_FILE'

IMPORTING

file_name  = p_file.

START-OF-SELECTION.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

i_line_header        = 'X'

i_tab_raw_data       = it_raw       " WORK TABLE

i_filename           = p_file

TABLES

i_tab_converted_data = itab[]    "ACTUAL DATA

EXCEPTIONS

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

*  CALL FUNCTION 'WS_UPLOAD'

*   EXPORTING

**   CODEPAGE                      = ' '

*     filename                      = p_file

*     filetype                      = 'DAT'

**   HEADLEN                       = ' '

**   LINE_EXIT                     = ' '

**   TRUNCLEN                      = ' '

**   USER_FORM                     = ' '

**   USER_PROG                     = ' '

**   DAT_D_FORMAT                  = ' '

** IMPORTING

**   FILELENGTH                    =

*    TABLES

*      data_tab                      = itab[]

*   EXCEPTIONS

*     conversion_error              = 1

*     file_open_error               = 2

*     file_read_error               = 3

*     invalid_type                  = 4

*     no_batch                      = 5

*     unknown_error                 = 6

*     invalid_table_width           = 7

*     gui_refuse_filetransfer       = 8

*     customer_error                = 9

*     no_authority                  = 10

*     OTHERS                        = 11

*            .

*  IF sy-subrc <> 0.

*    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

*            WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

*  ENDIF.

**  PERFORM open_group.

CLEAR :lv_werks,lv_vbeln.

READ TABLE itab INTO wa_itab INDEX 1.

IF sy-subrc = 0.

lv_vbeln = wa_itab-vbeln.

SELECT SINGLE werks

FROM   vbap

INTO   lv_werks

WHERE  vbeln = lv_vbeln.

ENDIF.

IF itab[] IS NOT INITIAL.

REFRESH: it_pa0001.

SELECT pernr ename

FROM  pa0001

INTO  TABLE it_pa0001

FOR   ALL ENTRIES IN itab

WHERE pernr = itab-zzpernr.

*           AND   begda <= sy-datum

*           AND   endda >= sy-datum.

ENDIF.

LOOP AT itab INTO wa_itab.

MOVE-CORRESPONDING wa_itab TO wa_item.

CLEAR wa_pa0001.

READ TABLE it_pa0001 INTO wa_pa0001 WITH KEY

pernr = wa_item-zzpernr.

IF sy-subrc = 0.

wa_item-arktx = wa_pa0001-ename.

ENDIF.

APPEND wa_item TO item.

CLEAR wa_item.

ENDLOOP.

READ TABLE itab INTO wa_itab INDEX 1.

PERFORM bdc_dynpro      USING 'SAPMV45A' '0101'.

PERFORM bdc_field       USING 'BDC_CURSOR'

'VBAK-AUART'.

PERFORM bdc_field       USING 'BDC_OKCODE'

'=COPY'.

PERFORM bdc_field       USING 'VBAK-AUART'

wa_itab-auart.

PERFORM bdc_dynpro      USING 'SAPLV45C' '0100'.

PERFORM bdc_field       USING 'BDC_OKCODE'

'=REF1'.

PERFORM bdc_field       USING 'BDC_CURSOR'

'LV45C-VBELN'.

PERFORM bdc_field       USING 'LV45C-VBELN'

wa_itab-vbeln.

PERFORM bdc_dynpro      USING 'SAPLV45C' '0100'.

PERFORM bdc_field       USING 'BDC_OKCODE'

'=REF1'.

PERFORM bdc_field       USING 'BDC_CURSOR'

'LV45C-VBELN'.

PERFORM bdc_field       USING 'LV45C-VBELN'

wa_itab-vbeln.

PERFORM bdc_dynpro      USING 'SAPLV45C' '0100'.

PERFORM bdc_field       USING 'BDC_OKCODE'

'=UEBR'.

PERFORM bdc_dynpro      USING 'SAPMV45A' '4001'.

PERFORM bdc_field       USING 'BDC_OKCODE'

'=KTEX_SUB'.

PERFORM bdc_dynpro      USING 'SAPMV45A' '4002'.

PERFORM bdc_field       USING 'BDC_OKCODE'

'=TP_DELETE'.

PERFORM bdc_dynpro      USING 'SAPMV45A' '4002'.

PERFORM bdc_field       USING 'BDC_OKCODE'

'=TP_CREATE'.

PERFORM bdc_field       USING 'LV70T-SPRAS'

sy-langu.

PERFORM bdc_dynpro      USING 'SAPMV45A' '4002'.

PERFORM bdc_field       USING 'BDC_OKCODE'

'=TP_DETAIL'.

PERFORM bdc_field       USING 'LV70T-SPRAS'

sy-langu.

PERFORM bdc_dynpro      USING 'SAPLSTXX' '1100'.

PERFORM bdc_field       USING 'BDC_CURSOR'

'RSTXT-TXLINE(02)'.

PERFORM bdc_field       USING 'BDC_OKCODE'

'=TXVB'.

PERFORM bdc_field       USING 'RSTXT-TXPARGRAPH(02)'

'*'.

PERFORM bdc_field       USING 'RSTXT-TXPARGRAPH(03)'

'*'.

PERFORM bdc_field       USING 'RSTXT-TXLINE(02)'

wa_itab-htext1.

PERFORM bdc_field       USING 'RSTXT-TXLINE(03)'

wa_itab-htext2.

PERFORM bdc_dynpro      USING 'SAPLSTXX' '1100'.

PERFORM bdc_field       USING 'BDC_CURSOR'

'RSTXT-TXLINE(02)'.

PERFORM bdc_field       USING 'BDC_OKCODE'

'=TXBA'.

PERFORM bdc_dynpro      USING 'SAPMV45A' '4002'.

PERFORM bdc_field       USING 'BDC_OKCODE'

'/EBACK'.

CLEAR index.

DATA: line TYPE i.

DESCRIBE TABLE item LINES line.

LOOP AT item INTO wa_item.

index = index + 1.

PERFORM bdc_dynpro      USING 'SAPMV45A' '4001'.

IF index = 1.

PERFORM bdc_field       USING 'VBAP-ZMENG(01)'

wa_itab-zmeng.

PERFORM bdc_field       USING 'VBAP-ZIEME(01)'

wa_itab-zieme.

PERFORM bdc_field       USING 'VBAP-ARKTX(01)'

wa_item-arktx.

IF wa_itab-mvgr5 IS NOT INITIAL.

PERFORM bdc_field       USING 'VBAP-MVGR5(01)'

wa_itab-mvgr5.

ENDIF.

PERFORM bdc_field       USING 'VBAP-AUFNR(01)'

wa_itab-aufnr.

PERFORM bdc_field       USING 'VBAP-WERKS(01)'

lv_werks.

ELSE.

IF index < line.

PERFORM bdc_field       USING 'BDC_OKCODE'

'=POAN'.

ENDIF.

PERFORM bdc_field       USING 'RV45A-MABNR(02)'

wa_item-matnr.

PERFORM bdc_field       USING 'VBAP-ZMENG(02)'

wa_item-zmeng.

PERFORM bdc_field       USING 'VBAP-ZIEME(02)'

wa_item-zieme.

IF wa_item-mvgr5 IS NOT INITIAL.

PERFORM bdc_field       USING 'VBAP-MVGR5(02)'

wa_item-mvgr5.

ENDIF.

PERFORM bdc_field       USING 'VBAP-ARKTX(02)'

wa_item-arktx.

PERFORM bdc_field       USING 'VBAP-AUFNR(02)'

wa_item-aufnr.

PERFORM bdc_field       USING 'VBAP-WERKS(02)'

lv_werks.

ENDIF.

ENDLOOP.

PERFORM bdc_dynpro      USING 'SAPMV45A' '4001'.

PERFORM bdc_field       USING 'BDC_OKCODE'

'=ITEM'.

PERFORM bdc_field       USING 'BDC_CURSOR'

'RV45A-MABNR(02)'.

PERFORM bdc_dynpro      USING 'SAPMV45A' '4003'.

PERFORM bdc_field       USING 'BDC_OKCODE'

'=T\05'.

PERFORM bdc_dynpro      USING 'SAPMV45A' '5003'.

PERFORM bdc_field       USING 'BDC_OKCODE'

'=POS1'.

CLEAR index.

LOOP AT item INTO wa_item.

index = index + 1.

IF index = 1.

PERFORM bdc_field       USING 'BDC_CURSOR'

'KOMV-KSCHL(01)'.

PERFORM bdc_dynpro      USING 'SAPMV45A' '5003'.

PERFORM bdc_field       USING 'BDC_OKCODE'

'=POS+'.

PERFORM bdc_field       USING 'BDC_CURSOR'

'KOMV-KBETR(01)'.

PERFORM bdc_field       USING 'KOMV-KBETR(01)'

wa_item-kbetr.

ELSE.

PERFORM bdc_dynpro      USING 'SAPMV45A' '5003'.

PERFORM bdc_field       USING 'BDC_OKCODE'

'=V69A_KOAN'.

PERFORM bdc_field       USING 'BDC_CURSOR'

'KOMV-KSCHL(01)'.

PERFORM bdc_dynpro      USING 'SAPMV45A' '5003'.

PERFORM bdc_field       USING 'BDC_OKCODE'

'=POS+'.

PERFORM bdc_field       USING 'BDC_CURSOR'

'KOMV-KBETR(02)'.

PERFORM bdc_field       USING 'KOMV-KSCHL(02)'

wa_item-kschl.

PERFORM bdc_field       USING 'KOMV-KBETR(02)'

wa_item-kbetr.

ENDIF.

ENDLOOP.

PERFORM bdc_dynpro      USING 'SAPMV45A' '5003'.

PERFORM bdc_field       USING 'BDC_OKCODE'

'/EBACK'.

PERFORM bdc_field       USING 'BDC_CURSOR'

'KOMV-KSCHL(03)'.

PERFORM bdc_dynpro      USING 'SAPMV45A' '4001'.

PERFORM bdc_field       USING 'BDC_OKCODE'

'=FEAZ'.

LOOP AT item INTO wa_item.

PERFORM bdc_dynpro      USING 'SAPLSLVC_FULLSCREEN' '0500'.

PERFORM bdc_field       USING 'BDC_OKCODE'

'=FEBE'.

PERFORM bdc_dynpro      USING 'SAPMV45A' '4003'.

*    PERFORM bdc_field       USING 'BDC_OKCODE'

*                                  '=FEWE'.

PERFORM bdc_field       USING 'BDC_CURSOR'

'VBAP-BEMOT'.

CLEAR l_pernr.

l_pernr = wa_item-zzpernr.

CONDENSE l_pernr.

PERFORM bdc_field       USING 'VBAP-ZZPERNR'

wa_item-zzpernr.

PERFORM bdc_field       USING 'VBAP-BEMOT'

wa_item-bemot.

PERFORM bdc_dynpro      USING 'SAPMV45A' '4003'.

PERFORM bdc_field       USING 'BDC_OKCODE'

'=FEWE'.

ENDLOOP.

PERFORM bdc_dynpro      USING 'SAPMV45A' '4001'.

PERFORM bdc_field       USING 'BDC_OKCODE'

'=SICH'.

IF rb1 = 'X'.

mode = 'A'.

ELSEIF rb3 = 'X'.

mode = 'E'.

ENDIF.

CALL TRANSACTION 'VA01' USING bdcdata

MODE mode

UPDATE 'A'

MESSAGES INTO message.

LOOP AT message INTO wa_message.

SELECT SINGLE * FROM t100 WHERE sprsl = wa_message-msgspra

AND   arbgb = wa_message-msgid

AND   msgnr = wa_message-msgnr.

IF sy-subrc = 0.

l_mstring = t100-text.

IF l_mstring CS '&1'.

REPLACE '&1' WITH wa_message-msgv1 INTO l_mstring.

REPLACE '&2' WITH wa_message-msgv2 INTO l_mstring.

REPLACE '&3' WITH wa_message-msgv3 INTO l_mstring.

REPLACE '&4' WITH wa_message-msgv4 INTO l_mstring.

ELSE.

REPLACE '&' WITH wa_message-msgv1 INTO l_mstring.

REPLACE '&' WITH wa_message-msgv2 INTO l_mstring.

REPLACE '&' WITH wa_message-msgv3 INTO l_mstring.

REPLACE '&' WITH wa_message-msgv4 INTO l_mstring.

ENDIF.

CONDENSE l_mstring.

WRITE: / wa_message-msgtyp, l_mstring(250).

ELSE.

WRITE: / wa_message.

ENDIF.

ENDLOOP.

*&---------------------------------------------------------------------*

*&      Form  bdc_dynpro

*&---------------------------------------------------------------------*

*       text

*----------------------------------------------------------------------*

*      -->PROGRAM    text

*      -->DYNPRO     text

*----------------------------------------------------------------------*

FORM bdc_dynpro  USING    program dynpro.

CLEAR wa_bdc.

wa_bdc-program = program.

wa_bdc-dynpro  = dynpro.

wa_bdc-dynbegin = 'X'.

APPEND wa_bdc TO bdcdata.

ENDFORM.                    " bdc_dynpro

*&---------------------------------------------------------------------*

*&      Form  bdc_field

*&---------------------------------------------------------------------*

FORM bdc_field  USING    fnam fval.

CLEAR wa_bdc.

wa_bdc-fnam = fnam.

wa_bdc-fval  = fval.

APPEND wa_bdc TO bdcdata.

ENDFORM.                    " bdc_field

regards,

Santosh Thorat