bdc

[Former Member]

Hi,

How to load data from MS Excel sheet to SAP by using BDC method ?

thanx in advance.

[Former Member]

Mayank,

dont forget reward.

{REPORT zarinterface

NO STANDARD PAGE HEADING LINE-SIZE 255.

*set screen 0103.

INCLUDE bdcrecx1.

TYPE-POOLS: truxs , slis.

SELECTION-SCREEN BEGIN OF BLOCK a WITH FRAME TITLE text-001.

PARAMETER: p_kunnr LIKE knb1-kunnr , "knb1-kunnr

p_budat LIKE bkpf-budat OBLIGATORY,

p_konto LIKE rf05a-konto OBLIGATORY,

p_wrbtr LIKE bseg-wrbtr OBLIGATORY,

p_bukrs LIKE bseg-bukrs OBLIGATORY,

p_prctr LIKE bseg-prctr OBLIGATORY,

p_waers LIKE bkpf-waers OBLIGATORY,

p_kostl LIKE cobl-kostl,

p_sgtxt LIKE bseg-sgtxt,

p_spesw LIKE rf05a-spesw,

p_agkon LIKE rf05a-agkon.

SELECTION-SCREEN END OF BLOCK a.

SELECTION-SCREEN BEGIN OF BLOCK b WITH FRAME TITLE text-002.

PARAMETER: pfile LIKE rlgrap-filename OBLIGATORY.

SELECTION-SCREEN END OF BLOCK b.

DATA: it_raw TYPE truxs_t_text_data.

DATA: BEGIN OF it_excel OCCURS 0,

belnr(10),

xblnr(16),

bktxt(25),

bldat(10),

zuonr(18),

oac(1),

dmbtr(16),

pszah(15),

wrbtr(16),

kunnr(10),

budat(10),

konto(16),

bukrs(04),

waers(05),

agkon(16),

prctr(10),

wrbtr1(16),

spesw(15),

kostl(10),

sgtxt(50),

END OF it_excel.

DATA:BEGIN OF item OCCURS 0,

belnr LIKE bseg-belnr,

pszah(15),

END OF item.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR pfile.

PERFORM sub_browse_file. "Get file name

START-OF-SELECTION.

PERFORM sub_data_load. " Transfer excel into internal table.

PERFORM sub_calc_excel.

PERFORM open_group.

LOOP AT it_excel.

PERFORM bdc_dynpro USING 'SAPMF05A' '0103'.

PERFORM bdc_field USING 'BDC_CURSOR'

'RF05A-XPOS1(03)'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=PA'.

PERFORM bdc_field USING 'BKPF-BLDAT'

it_excel-bldat."'06022008'.param

PERFORM bdc_field USING 'BKPF-BLART'

'dz'.

PERFORM bdc_field USING 'BKPF-BUKRS'

it_excel-bukrs."'3404'.param

PERFORM bdc_field USING 'BKPF-BUDAT'

it_excel-budat."'06022008'.Param

PERFORM bdc_field USING 'BKPF-MONAT'

'6'.

PERFORM bdc_field USING 'BKPF-WAERS'

it_excel-waers."'eur'.Parameter

PERFORM bdc_field USING 'BKPF-XBLNR'

it_excel-xblnr."'testar'.Parameter

PERFORM bdc_field USING 'BKPF-BKTXT'

it_excel-bktxt."'arclear check'.Parameter

PERFORM bdc_field USING 'RF05A-KONTO'

it_excel-konto."'11320000'.Parameter

PERFORM bdc_field USING 'BSEG-WRBTR'

it_excel-wrbtr."'310'."Parameter

PERFORM bdc_field USING 'RF05A-SPESW'

it_excel-spesw. "'10'.

PERFORM bdc_field USING 'RF05A-AGKON'

it_excel-agkon."'1356'.param

PERFORM bdc_field USING 'RF05A-AGKOA'

'D'.

PERFORM bdc_field USING 'RF05A-XNOPS'

'X'.

PERFORM bdc_field USING 'RF05A-XPOS1(01)'

''.

PERFORM bdc_field USING 'RF05A-XPOS1(03)'

'X'.

• PERFORM bdc_field USING 'BSEG-WRBTR'

• it_excel-dmbtr."Amount in LC

PERFORM bdc_dynpro USING 'SAPMF05A' '0611'.

PERFORM bdc_field USING 'BDC_CURSOR'

'BSEG-MWSKZ'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=GO'.

PERFORM bdc_field USING 'BSEG-WRBTR'

it_excel-spesw."'10.00'.parambank charge

PERFORM bdc_field USING 'BSEG-MWSKZ'

'i0'.

PERFORM bdc_dynpro USING 'SAPMF05A' '0731'.

PERFORM bdc_field USING 'BDC_CURSOR'

'RF05A-SEL01(02)'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=PA'.

• DATA :FNAM(20) TYPE C,

• IDX TYPE C.

• MOVE 1 TO IDX.

• LOOP AT item.

• CONCATENATE 'RF05A-SEL01(' IDX ')' INTO FNAM.

• perform bdc_field using FNAM

• item-belnr.

PERFORM bdc_field USING 'RF05A-SEL01(01)'

it_excel-belnr."''."'18000002'.

• PERFORM bdc_field USING 'RF05A-SEL01(02)'

• it_excel-belnr."''."'18000003'.

PERFORM bdc_dynpro USING 'SAPDF05X' '3100'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=PART'.

PERFORM bdc_field USING 'BDC_CURSOR'

'DF05B-PSSKT(01)'.

PERFORM bdc_field USING 'RF05A-ABPOS'

'1'.

PERFORM bdc_dynpro USING 'SAPDF05X' '3100'.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

• PERFORM bdc_field USING 'BDC_CURSOR'

• 'DF05B-PSZAH(02)'.

PERFORM bdc_field USING 'RF05A-ABPOS'

'1'.

• CONCATENATE 'DF05B-PSZAH(' IDX ')' INTO FNAM.

• perform bdc_field using FNAM

• item-pszah.

PERFORM bdc_field USING 'DF05B-PSZAH(01)'

it_excel-pszah. ""'80'.

• PERFORM bdc_field USING 'DF05B-PSZAH(02)'

• it_excel-pszah. "'100'..

• IDX = IDX + 1.

• ENDLOOP.

PERFORM bdc_dynpro USING 'SAPDF05X' '3100'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=KMD'.

• PERFORM bdc_field USING 'BDC_CURSOR'

• 'DF05B-PSZAH(02)'.

PERFORM bdc_field USING 'RF05A-ABPOS'

'1'.

PERFORM bdc_dynpro USING 'SAPMF05A' '0700'.

PERFORM bdc_field USING 'BDC_CURSOR'

'RF05A-NEWKO'.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

PERFORM bdc_field USING 'BKPF-XBLNR'

it_excel-xblnr."'TESTAR'.

PERFORM bdc_field USING 'BKPF-BKTXT'

it_excel-bktxt."'arclear check'.

IF it_excel-oac = 'O'.

PERFORM bdc_field USING 'RF05A-NEWBS'

'01'.

ELSE.

PERFORM bdc_field USING 'RF05A-NEWBS'

'11'.

ENDIF.

PERFORM bdc_field USING 'RF05A-NEWKO'

it_excel-agkon. " '1356'.

PERFORM bdc_dynpro USING 'SAPMF05A' '0301'.

PERFORM bdc_field USING 'BDC_CURSOR'

'RF05A-NEWKO'.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

PERFORM bdc_field USING 'BSEG-WRBTR'

it_excel-wrbtr. "'100'.

PERFORM bdc_field USING 'BSEG-ZFBDT'

it_excel-budat.""'06/02/2008'.

PERFORM bdc_field USING 'BSEG-ZUONR'

it_excel-zuonr."'15987984'.

IF it_excel-oac = 'O'.

PERFORM bdc_field USING 'BSEG-SGTXT'

'over charge'.

PERFORM bdc_field USING 'RF05A-NEWBS'

'01'."11

ELSE.

PERFORM bdc_field USING 'BSEG-SGTXT'

'under charge'.

PERFORM bdc_field USING 'RF05A-NEWBS'

'11'.

ENDIF.

PERFORM bdc_field USING 'RF05A-NEWKO'

it_excel-agkon. " '1356'.

PERFORM bdc_dynpro USING 'SAPMF05A' '0301'.

PERFORM bdc_field USING 'BDC_CURSOR'

'RF05A-NEWKO'.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

PERFORM bdc_field USING 'BSEG-WRBTR'

it_excel-wrbtr. "'100'.

PERFORM bdc_field USING 'BSEG-ZFBDT'

it_excel-bldat."'06/02/2008'.

PERFORM bdc_field USING 'BSEG-ZUONR'

it_excel-zuonr."'15987985'.

IF it_excel-oac = 'O'.

PERFORM bdc_field USING 'BSEG-SGTXT'

'overcharge'.

PERFORM bdc_field USING 'RF05A-NEWBS'

'01'.

ELSE.

PERFORM bdc_field USING 'BSEG-SGTXT'

'under charge'.

PERFORM bdc_field USING 'RF05A-NEWBS'

'11'.

ENDIF.

PERFORM bdc_field USING 'RF05A-NEWKO' "CUST Param

it_excel-agkon. "'1356'.

PERFORM bdc_dynpro USING 'SAPMF05A' '0301'.

PERFORM bdc_field USING 'BDC_CURSOR'

it_excel-sgtxt."'BSEG-SGTXT'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=PA'.

PERFORM bdc_field USING 'BSEG-WRBTR'

it_excel-wrbtr. "'50'.

PERFORM bdc_field USING 'BSEG-ZTERM'

'NT30'.

PERFORM bdc_field USING 'BSEG-ZBD1T'

'30'.

PERFORM bdc_field USING 'BSEG-ZFBDT'

it_excel-bldat."'06/02/2008'.

PERFORM bdc_field USING 'BSEG-ZUONR'

it_excel-zuonr."'under'.

PERFORM bdc_field USING 'BSEG-SGTXT'

it_excel-sgtxt."'undercharge'.

• PERFORM bdc_dynpro USING 'SAPDF05X' '3100'.

• LOOP AT item.

• PERFORM bdc_field USING 'BDC_OKCODE'

• '/00'.

• PERFORM bdc_field USING 'BDC_CURSOR'

• 'DF05B-PSZAH(02)'.

• PERFORM bdc_field USING 'RF05A-ABPOS'

• '1'.

• PERFORM bdc_field USING 'DF05B-PSZAH(02)'

• it_excel-pszah. "'110.00'."item-pszah.".

• PERFORM bdc_dynpro USING 'SAPDF05X' '3100'.

• PERFORM bdc_field USING 'BDC_OKCODE'

• '/00'.

• PERFORM bdc_field USING 'BDC_CURSOR'

• 'DF05B-PSZAH(02)'.

• PERFORM bdc_field USING 'RF05A-ABPOS'

• '1'.

• PERFORM bdc_field USING 'DF05B-PSZAH(02)'

• it_excel-pszah. "'90'." item-pszah."

• PERFORM bdc_dynpro USING 'SAPDF05X' '3100'.

• PERFORM bdc_field USING 'BDC_OKCODE'

• '=BU'.

• PERFORM bdc_field USING 'BDC_CURSOR'

• 'DF05B-PSZAH(02)'.

• PERFORM bdc_field USING 'RF05A-ABPOS'

• '1'.

• PERFORM bdc_field USING 'DF05B-PSZAH(02)'

• it_excel-pszah. "'90'." item-pszah."

• ENDLOOP.

PERFORM bdc_dynpro USING 'SAPDF05X' '3100'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=PART'.

PERFORM bdc_field USING 'BDC_CURSOR'

'DF05B-PSSKT(01)'.

PERFORM bdc_field USING 'RF05A-ABPOS'

'1'.

PERFORM bdc_dynpro USING 'SAPDF05X' '3100'.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

PERFORM bdc_field USING 'BDC_CURSOR'

'DF05B-PSZAH(02)'.

PERFORM bdc_field USING 'RF05A-ABPOS'

'1'.

PERFORM bdc_field USING 'DF05B-PSZAH(01)'

it_excel-pszah."' 80'.

PERFORM bdc_field USING 'DF05B-PSZAH(02)'

it_excel-pszah."' 100'.

PERFORM bdc_field USING 'DF05B-PSZAH(03)'

it_excel-pszah."' 100'.

PERFORM bdc_dynpro USING 'SAPDF05X' '3100'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=KMD'.

PERFORM bdc_field USING 'BDC_CURSOR'

'DF05B-PSZAH(02)'.

PERFORM bdc_field USING 'RF05A-ABPOS'

'1'.

****

PERFORM bdc_dynpro USING 'SAPMF05A' '0700'.

PERFORM bdc_field USING 'BDC_CURSOR'

'RF05A-AZEI1(01)'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=PI'.

PERFORM bdc_field USING 'BKPF-XBLNR'

it_excel-xblnr." 'TESTAR'.

PERFORM bdc_field USING 'BKPF-BKTXT'

it_excel-bktxt."'arclear check'.

PERFORM bdc_dynpro USING 'SAPMF05A' '0300'.

PERFORM bdc_field USING 'BDC_CURSOR'

'BSEG-SGTXT'.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

PERFORM bdc_field USING 'BSEG-WRBTR'

it_excel-wrbtr."'10.00'. &param

PERFORM bdc_field USING 'BSEG-MWSKZ'

'I0'.

PERFORM bdc_field USING 'BSEG-ZUONR'

it_excel-zuonr."'taxes'.

PERFORM bdc_field USING 'BSEG-SGTXT'

it_excel-sgtxt."'taxes on bank charges'.

PERFORM bdc_field USING 'DKACB-FMORE'

'X'.

PERFORM bdc_dynpro USING 'SAPLKACB' '0002'.

PERFORM bdc_field USING 'BDC_CURSOR'

'COBL-KOSTL'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=ENTE'.

PERFORM bdc_field USING 'COBL-KOSTL'

it_excel-kostl."'340412010'.

PERFORM bdc_dynpro USING 'SAPMF05A' '0300'.

PERFORM bdc_field USING 'BDC_CURSOR'

'BSEG-WRBTR'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=BU'.

PERFORM bdc_field USING 'BSEG-WRBTR'

it_excel-wrbtr. "'10.00'.

PERFORM bdc_field USING 'BSEG-MWSKZ'

'I0'.

PERFORM bdc_field USING 'BSEG-ZUONR'

it_excel-zuonr."'taxes'.

PERFORM bdc_field USING 'BSEG-SGTXT'

it_excel-sgtxt.".'taxes on bank charges'.

PERFORM bdc_field USING 'DKACB-FMORE'

'X'.

PERFORM bdc_dynpro USING 'SAPLKACB' '0002'.

PERFORM bdc_field USING 'BDC_CURSOR'

'COBL-KOSTL'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=ENTE'.

PERFORM bdc_field USING 'COBL-KOSTL'

it_excel-kostl." '340412010'.

PERFORM bdc_transaction USING 'F-28'.

REFRESH:bdcdata,messtab.

ENDLOOP.

REFRESH:bdcdata.

PERFORM close_group.

&----

---

*& Form sub_browse_file

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

FORM sub_browse_file .

CALL FUNCTION 'F4_FILENAME'

EXPORTING

program_name = syst-cprog

dynpro_number = syst-dynnr

• FIELD_NAME = ' '

IMPORTING

file_name = pfile.

ENDFORM. " sub_browse_file

&----

---

*& Form sub_data_load

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

FORM sub_data_load .

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

• I_FIELD_SEPERATOR =

i_line_header = 'X'

i_tab_raw_data = it_raw

i_filename = pfile

TABLES

i_tab_converted_data = it_excel[]

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

ENDFORM. " sub_data_load

&----

---

*& Form sub_calc_excel

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

FORM sub_calc_excel.

DATA: idate TYPE sy-datum,

tdat8 TYPE string.

LOOP AT it_excel.

item-belnr = it_excel-belnr.

item-pszah = it_excel-pszah.

idate = p_budat.

it_excel-kunnr = p_kunnr.

it_excel-budat = p_budat.

it_excel-konto = p_konto .

it_excel-wrbtr = p_wrbtr.

it_excel-prctr = p_prctr .

it_excel-bukrs = p_bukrs .

it_excel-waers = p_waers .

it_excel-agkon = p_agkon .

it_excel-spesw = p_spesw.

it_excel-kostl = p_kostl.

it_excel-sgtxt = p_sgtxt.

APPEND item.

CLEAR item.

CALL FUNCTION 'DATUMSAUFBEREITUNG'

EXPORTING

• FLAGM = ' '

• FLAGW = ' '

idate = idate

• IMONT = ' '

• IWEEK = ' '

IMPORTING

• MDAT4 =

• MDAT6 =

• TDAT4 =

• TDAT6 =

tdat8 = tdat8

• WDAT4 =

• WDAT6 =

• EXCEPTIONS

• DATFM_UNGUELTIG = 1

• DATUM_UNGUELTIG = 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.

it_excel-budat = tdat8.

MODIFY it_excel.

CLEAR it_excel.

ENDLOOP.

ENDFORM. " sub_calc_excel}

Amit.

[Former Member]

Hi,

do the following steps.

1. Get the file name with path through selection screen.

2. File Data should be converted to Internal table.

3. loop the internal table and convert the data to sap format.

4. if BDC Sesstion use the method BDC_OPEN,BDC_INSERT and BDC_CLOSE.

5. if Call transaction, loop the internal table and call the call transaction using.

Refer the below links for more info.

[http://www.thespot4sap.com/Articles/Download_to_excel.asp|http://www.thespot4sap.com/Articles/Download_to_excel.asp]

[|]

Reward if found helpful.

Regards,

Boobalan Suburaj.

[Former Member]

Hi,

KCD_EXCEL_OLE_TO_INT_CONVERT even takes care of blank cells and is available in older versions of SAP

• Add values to internal table

SORT t_cells BY row col.

LOOP AT t_cells INTO wa_cells.

MOVE : wa_cells-col TO l_index.

ASSIGN COMPONENT l_index OF STRUCTURE itab TO .

MOVE : wa_cells-value TO .

AT END OF row.

APPEND itab

CLEAR itab.

ENDAT.

ENDLOOP.

reward if helpful

preet