how to upload data from excel sheet for BDC

[Former Member]

hi,

i want to know how to upload data from excel sheet for BDC.

i even want to know how to downaload the excel file to desktop.

urgent

thanks and regards

vijender

Edited by: VIJENDER SRIRAMOJU on Jun 2, 2008 12:30 PM

[Former Member]

use fm

ALSM_EXCEL_TO_INTERNAL_TABLE

[Former Member]

Hi,

use the FM ALSM_EXCEL_TO_INTERNAL_TABLE.

codePARAMETERS:

P_INFL like RLGRAP-FILENAME.

DATA:

BEGIN OF T_DATA1 OCCURS 0,

RESOURCE(25) TYPE C,

DATE(10) TYPE C,

DURATION TYPE P DECIMALS 2,

ACTIVITY(25) TYPE C,

B_NBILL(1) TYPE C,

END OF T_DATA1,

T_DATA TYPE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE,

BEGIN OF T_FINAL OCCURS 0,

RESOURCE(25) TYPE C,

DATE(10) TYPE C,

DURATION(15) TYPE C,

ACTIVITY(25) TYPE C,

B_NBILL(10) TYPE C,

END OF T_FINAL.

DATA : HEADER TYPE XSTRING.

Work Variables Declaration.

CONSTANTS:

W_Y TYPE C VALUE 'Y',

W_N TYPE C VALUE 'N'.

Work area.

DATA:

WA_DATA LIKE T_FINAL.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_INFL.

PERFORM GET_FILENAME CHANGING P_INFL.

-

---

START-OF-SELECTION.

-

---

PERFORM UPLOAD_DATA_FROMEXCEL.

FORM UPLOAD_DATA_FROMEXCEL.

Downloading the data from presentation server

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = p_infl

I_BEGIN_COL = 1

I_BEGIN_ROW = 2

I_END_COL = 8

I_END_ROW = 1000

TABLES

INTERN = T_DATA

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_data_fromexcel

&----

---

*& Form process_data

&----

---

text

-

---

FORM PROCESS_DATA .

T_FINAL-RESOURCE = 'Resource'.

T_FINAL-DATE = 'Date'.

T_FINAL-DURATION = 'Duration'.

T_FINAL-ACTIVITY = 'Activity'.

T_FINAL-B_NBILL = 'Billable'.

APPEND T_FINAL.

SORT T_DATA BY ROW COL.

LOOP AT T_DATA.

CASE T_DATA-COL.

WHEN 3.

T_DATA1-RESOURCE = T_DATA-VALUE.

WHEN 4.

T_DATA1-DATE = T_DATA-VALUE.

WHEN 5.

T_DATA1-DURATION = T_DATA-VALUE.

WHEN 6.

t_data1-activity = t_data-value.

WHEN 7.

T_DATA1-B_NBILL = T_DATA-VALUE.

ENDCASE.

AT END OF ROW.

COLLECT T_DATA1.

ENDAT.

ENDLOOP.

LOOP AT T_DATA1.

T_FINAL-RESOURCE = T_DATA1-RESOURCE.

T_FINAL-DATE = T_DATA1-DATE.

T_FINAL-DURATION = T_DATA1-DURATION.

T_FINAL-ACTIVITY = T_DATA1-ACTIVITY.

T_FINAL-B_NBILL = T_DATA1-B_NBILL.

APPEND T_FINAL.

ENDLOOP.

ENDFORM. " process_data

&----

---

*& Form get_filename

&----

---

FORM GET_FILENAME CHANGING P_FILENAME.

CALL FUNCTION 'WS_FILENAME_GET'

EXPORTING

DEF_FILENAME = SPACE

DEF_PATH = P_FILENAME

MASK = ',. ,..'

MODE = 'O' " O = Open, S = Save

TITLE = BOX_TITLE

IMPORTING

FILENAME = P_FILENAME

EXCEPTIONS

INV_WINSYS = 1

NO_BATCH = 2

SELECTION_CANCEL = 3

SELECTION_ERROR = 4

OTHERS = 5.

*

CASE SY-SUBRC.

WHEN 1.

MESSAGE I999 WITH

'File selector not available on this windows system'(046).

WHEN 2.

MESSAGE E999 WITH

'Frontend function cannot be executed in background'(047).

WHEN 3.

MESSAGE I999 WITH 'Selection was cancelled'(048).

WHEN 4.

MESSAGE E999 WITH 'Communication error'(049).

WHEN 5.

MESSAGE E999 WITH 'Other error'(050).

ENDCASE.

ENDFORM.

[Former Member]

Hi .

refer this code for BDC

REPORT zmm01_bdc_finish

NO STANDARD PAGE HEADING LINE-SIZE 255.

• Data declaration

DATA: BEGIN OF record OCCURS 0,

matnr(18),

mbrsh(1),

mtart(4),

werks(4),

lgort(4),

vkorg(4),

vtweg(2),

maktx(40),

meins(3),

spart(2),

versg(1),

kondm(2),

bonus(2),

ktgrm(2),

mtpos(4),

mtvfp(2),

tragr(4),

ladgr(4),

prctr(10),

dismm(2),

dispo(3),

disls(2),

beskz(1),

fhori(3),

dzeit(3),

strgr(2),

vrmod(1),

vint2(3),

vint1(3),

altsl(1),

sbdkz(1),

fevor(3),

sfcpf(6),

bklas(4),

vprsv(1),

stprs(11),

END OF record.

**File Name

DATA: flname TYPE string.

DATA: BEGIN OF bdcdata OCCURS 0.

INCLUDE STRUCTURE bdcdata.

DATA END OF bdcdata.

• For massage

DATA: BEGIN OF messtab OCCURS 0.

INCLUDE STRUCTURE bdcmsgcoll.

DATA: matnr TYPE mara-matnr,

END OF messtab.

DATA: cnt1 TYPE i,

cnt2 TYPE i,

cnt3 TYPE i.

**For Sreach help to select the Flat file from Presentation server.

PARAMETERS: filename TYPE rlgrap-filename.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR filename.

CALL FUNCTION 'F4_FILENAME'

IMPORTING

file_name = filename.

**Satrt of selection.

START-OF-SELECTION.

flname = filename.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = flname

filetype = 'ASC'

has_field_separator = 'X'

• HEADER_LENGTH = 0

• READ_BY_LINE = 'X'

• DAT_MODE = ' '

• CODEPAGE = ' '

• IGNORE_CERR = ABAP_TRUE

• REPLACEMENT = '#'

• CHECK_BOM = ' '

• VIRUS_SCAN_PROFILE =

• NO_AUTH_CHECK = ' '

• IMPORTING

• FILELENGTH =

• HEADER =

TABLES

data_tab = record

EXCEPTIONS

file_open_error = 1

file_read_error = 2

no_batch = 3

gui_refuse_filetransfer = 4

invalid_type = 5

no_authority = 6

unknown_error = 7

bad_data_format = 8

header_not_allowed = 9

separator_not_allowed = 10

header_too_long = 11

unknown_dp_error = 12

access_denied = 13

dp_out_of_memory = 14

disk_full = 15

dp_timeout = 16

OTHERS = 17

.

IF sy-subrc <> 0.

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

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

ENDIF.

WRITE:sy-uline.

WRITE:/ 'ERROR MESSAGE:' COLOR 3.

WRITE:sy-uline.

LOOP AT record.

cnt1 = cnt1 + 1.

REFRESH bdcdata.

PERFORM bdc_dynpro USING 'SAPLMGMM' '0060'.

PERFORM bdc_field USING 'BDC_CURSOR'

'RMMG1-MTART'.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

PERFORM bdc_field USING 'RMMG1-MATNR'

• '122017M-f_rec'.

record-matnr.

PERFORM bdc_field USING 'RMMG1-MBRSH'

• 'M'.

record-mbrsh.

PERFORM bdc_field USING 'RMMG1-MTART'

• 'FERT'.

record-mtart.

PERFORM bdc_dynpro USING 'SAPLMGMM' '0070'.

PERFORM bdc_field USING 'BDC_CURSOR'

'MSICHTAUSW-DYTXT(12)'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=ENTR'.

PERFORM bdc_field USING 'MSICHTAUSW-KZSEL(01)'

'X'.

PERFORM bdc_field USING 'MSICHTAUSW-KZSEL(02)'

'X'.

PERFORM bdc_field USING 'MSICHTAUSW-KZSEL(04)'

'X'.

PERFORM bdc_field USING 'MSICHTAUSW-KZSEL(05)'

'X'.

PERFORM bdc_field USING 'MSICHTAUSW-KZSEL(06)'

'X'.

PERFORM bdc_field USING 'MSICHTAUSW-KZSEL(09)'

'X'.

PERFORM bdc_field USING 'MSICHTAUSW-KZSEL(10)'

'X'.

PERFORM bdc_field USING 'MSICHTAUSW-KZSEL(11)'

'X'.

PERFORM bdc_field USING 'MSICHTAUSW-KZSEL(12)'

'X'.

PERFORM bdc_dynpro USING 'SAPLMGMM' '0080'.

PERFORM bdc_field USING 'BDC_CURSOR'

'RMMG1-VKORG'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=ENTR'.

PERFORM bdc_field USING 'RMMG1-WERKS'

record-werks.

PERFORM bdc_field USING 'RMMG1-LGORT'

record-lgort.

PERFORM bdc_field USING 'RMMG1-VKORG'

record-vkorg.

PERFORM bdc_field USING 'RMMG1-VTWEG'

record-vtweg.

PERFORM bdc_dynpro USING 'SAPLMGMM' '4004'.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

PERFORM bdc_field USING 'MAKT-MAKTX'

record-maktx.

PERFORM bdc_field USING 'BDC_CURSOR'

'MARA-SPART'.

PERFORM bdc_field USING 'MARA-MEINS'

record-meins.

PERFORM bdc_field USING 'MARA-SPART'

record-spart.

PERFORM bdc_field USING 'MARA-MTPOS_MARA'

record-mtpos.

PERFORM bdc_dynpro USING 'SAPLMGMM' '4004'.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

PERFORM bdc_field USING 'BDC_CURSOR'

'MAKT-MAKTX'.

PERFORM bdc_field USING 'MAKT-MAKTX'

record-maktx.

PERFORM bdc_dynpro USING 'SAPLMGMM' '4000'.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

PERFORM bdc_field USING 'BDC_CURSOR'

'MAKT-MAKTX'.

PERFORM bdc_field USING 'MAKT-MAKTX'

record-maktx.

PERFORM bdc_field USING 'MARA-MEINS'

record-meins.

PERFORM bdc_field USING 'MARA-SPART'

record-spart.

PERFORM bdc_dynpro USING 'SAPLMGMM' '4000'.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

PERFORM bdc_field USING 'MAKT-MAKTX'

record-maktx.

PERFORM bdc_field USING 'BDC_CURSOR'

'MVKE-VERSG'.

PERFORM bdc_field USING 'MVKE-VERSG'

record-versg.

PERFORM bdc_field USING 'MARA-MTPOS_MARA'

• 'NORM'.

record-mtpos.

PERFORM bdc_field USING 'MVKE-MTPOS'

record-mtpos.

PERFORM bdc_dynpro USING 'SAPLMGMM' '4000'.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

PERFORM bdc_field USING 'MAKT-MAKTX'

record-maktx.

PERFORM bdc_field USING 'MARA-MEINS'

record-meins.

PERFORM bdc_field USING 'BDC_CURSOR'

'MARC-PRCTR'.

PERFORM bdc_field USING 'MARC-PRCTR'

record-prctr.

PERFORM bdc_dynpro USING 'SAPLMGMM' '4000'.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

PERFORM bdc_field USING 'MAKT-MAKTX'

record-maktx.

PERFORM bdc_field USING 'MARA-MEINS'

record-meins.

PERFORM bdc_field USING 'BDC_CURSOR'

'MARC-DISPO'.

PERFORM bdc_field USING 'MARC-DISMM'

record-dismm.

PERFORM bdc_field USING 'MARC-DISPO'

record-dispo.

PERFORM bdc_field USING 'MARC-DISLS'

record-disls.

PERFORM bdc_dynpro USING 'SAPLMGMM' '4000'.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

PERFORM bdc_field USING 'MAKT-MAKTX'

record-maktx.

PERFORM bdc_field USING 'MARC-BESKZ'

record-beskz.

PERFORM bdc_field USING 'BDC_CURSOR'

'MARC-PLIFZ'.

PERFORM bdc_field USING 'MARC-DZEIT'

record-dzeit.

PERFORM bdc_field USING 'MARC-FHORI'

record-fhori.

PERFORM bdc_dynpro USING 'SAPLMGMM' '4000'.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

PERFORM bdc_field USING 'BDC_CURSOR'

'MAKT-MAKTX'.

PERFORM bdc_field USING 'MAKT-MAKTX'

record-maktx.

PERFORM bdc_dynpro USING 'SAPLMGMM' '4000'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=SP17'.

PERFORM bdc_field USING 'BDC_CURSOR'

'MAKT-MAKTX'.

PERFORM bdc_field USING 'MAKT-MAKTX'

record-maktx.

PERFORM bdc_dynpro USING 'SAPLMGMM' '4000'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=SP24'.

PERFORM bdc_field USING 'BDC_CURSOR'

'MAKT-MAKTX'.

PERFORM bdc_field USING 'MAKT-MAKTX'

record-maktx.

PERFORM bdc_field USING 'MARA-MEINS'

record-meins.

PERFORM bdc_field USING 'MARC-DZEIT'

record-dzeit.

PERFORM bdc_dynpro USING 'SAPLMGMM' '4000'.

PERFORM bdc_field USING 'BDC_OKCODE'

'SP25'.

PERFORM bdc_field USING 'BDC_CURSOR'

'MAKT-MAKTX'.

PERFORM bdc_field USING 'MAKT-MAKTX'

record-maktx.

PERFORM bdc_field USING 'MARA-MEINS'

record-meins.

PERFORM bdc_field USING 'MARA-SPART'

record-spart.

PERFORM bdc_field USING 'MBEW-BKLAS'

record-bklas.

PERFORM bdc_field USING 'MBEW-VPRSV'

record-vprsv.

PERFORM bdc_dynpro USING 'SAPLMGMM' '4000'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=SP26'.

PERFORM bdc_field USING 'BDC_CURSOR'

'MAKT-MAKTX'.

PERFORM bdc_field USING 'MAKT-MAKTX'

record-maktx.

PERFORM bdc_dynpro USING 'SAPLMGMM' '4000'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=SP27'.

PERFORM bdc_field USING 'BDC_CURSOR'

'MAKT-MAKTX'.

PERFORM bdc_field USING 'MAKT-MAKTX'

record-maktx.

PERFORM bdc_field USING 'MARA-MEINS'

record-meins.

PERFORM bdc_field USING 'MARC-PRCTR'

record-prctr.

PERFORM bdc_dynpro USING 'SAPLMGMM' '4000'.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

PERFORM bdc_field USING 'BDC_CURSOR'

'MAKT-MAKTX'.

PERFORM bdc_field USING 'MAKT-MAKTX'

record-maktx.

PERFORM bdc_field USING 'MBEW-VPRSV'

record-vprsv.

PERFORM bdc_dynpro USING 'SAPLSPO1' '0300'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=YES'.

CALL TRANSACTION 'MM01' USING bdcdata

MODE 'N'

UPDATE 'S'

MESSAGES INTO messtab.

IF sy-subrc NE 0.

PERFORM error.

ENDIF.

ENDLOOP.

WRITE:sy-uline.

&----

---

*& Form OPEN_GROUP

&----

---

• text

----

---

FORM open_group.

CALL FUNCTION 'BDC_OPEN_GROUP'

EXPORTING

client = sy-mandt

group = 'MM01'

user = sy-uname

keep = 'X'.

ENDFORM. "OPEN_GROUP

&----

---

*& Form BDC_INSERT

&----

---

• text

----

---

FORM bdc_insert.

CALL FUNCTION 'BDC_INSERT'

EXPORTING

tcode = 'MM01'

• POST_LOCAL = NOVBLOCAL

• PRINTING = NOPRINT

• SIMUBATCH = ' '

• CTUPARAMS = ' '

TABLES

dynprotab = bdcdata

EXCEPTIONS

internal_error = 1

not_open = 2

queue_error = 3

tcode_invalid = 4

printing_invalid = 5

posting_invalid = 6

OTHERS = 7

.

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

END-OF-SELECTION.

*perform close_dataset using dataset.

&----

---

*& Form close_group

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

FORM close_group .

CALL FUNCTION 'BDC_CLOSE_GROUP'

EXCEPTIONS

not_open = 1

queue_error = 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. " close_group

&----

---

*& Form bdc_dynpro

&----

---

• text

----

---

• -->P_0270 text

• -->P_0271 text

----

---

FORM bdc_dynpro USING program dynpro.

CLEAR bdcdata.

bdcdata-program = program.

bdcdata-dynpro = dynpro.

bdcdata-dynbegin = 'X'.

APPEND bdcdata.

ENDFORM. " bdc_dynpro

&----

---

*& Form bdc_field

&----

---

• text

----

---

• -->P_0275 text

• -->P_0276 text

----

---

FORM bdc_field USING fnam fval.

IF fval <> ' '.

CLEAR bdcdata.

bdcdata-fnam = fnam.

bdcdata-fval = fval.

APPEND bdcdata.

ENDIF.

ENDFORM. " bdc_field

*perform close_group.

FORM error .

DATA: err_msg(250).

READ TABLE messtab WITH KEY msgtyp = 'E'.

IF sy-subrc = 0.

CALL FUNCTION 'FORMAT_MESSAGE'

EXPORTING

id = sy-msgid

lang = 'EN'

no = sy-msgno

v1 = sy-msgv1

v2 = sy-msgv2

v3 = sy-msgv3

v4 = sy-msgv4

IMPORTING

msg = err_msg

EXCEPTIONS

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

messtab-matnr = record-matnr.

MODIFY messtab INDEX cnt1 FROM messtab TRANSPORTING matnr.

WRITE:/1(20) messtab-matnr COLOR 5,21(60) err_msg.

ENDIF.

ENDFORM. " ERROR

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

Function module GUI_DOWNLOAD for SAP to desttop

Reward all helpfull answers.

Regards.

Jay

[Former Member]

hi,

enter the data in a excel sheet and save it.

note : while saving it , select the file type as tab delimited and then save it in desktop.

put a parameter to get the path of ur file , which is in desktop. while doing paramerter the file type should be rlgrap. now use FM gui_upload.

1) the FM gui_upload will not accept the 'rlgrap' type, so after getting ur flat file. convert the rlgrap type to string type.

2) simply pass ur f_file to str_file string type.

3) i.e str_file = f_file.

4) now pass ur str_file to ur gui_upload FM, because it wil accept string type only..

reward if helpful

regards

mano

[Former Member]

hi there,

[Former Member]

Hi,

You can also use FM TEXT_CONVERT_XLS_TO_SAP.

Regards,

Kannan