bdc:upload excel sheet data into database through bdc

[Former Member]

Hi,

i would like to upload Excel sheet data into database through bdc recording..

pls help me

[Former Member]

Please SEARCH in SCN before posting and be clear with your requirement. Thats why your previous thread was deleted by the mods

[Former Member]

Hi,

Please check before posting as it has already been posted earlier.

Though you can refer below.

parameter p_file type localfile default 'c:\text' obligatory.

DATA: TXT_FILENAME LIKE RLGRAP-FILENAME, " Create structure equivalent to Excel file

GT_FILE TYPE ALSMEX_TABLINE OCCURS 0 WITH HEADER line.

start-of-selection.

TXT_FILENAME = p_file.

• Function Module call to Upload Excel file Data in an Internal table GT_FILE FORM UPLOAD_LGL_ACC_DATA .

REFRESH GT_FILE.

CLEAR GT_FILE.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = TXT_FILENAME

i_begin_col = '1'

i_begin_row = '1'

i_end_col = '200'

i_end_row = '5000'

tables

intern = GT_FILE

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 GT_FILE[] IS NOT INITIAL.

LOOP AT GT_FILE.

CASE GT_FILE-COL.

WHEN '0001'.

"fill ur structure field here

WHEN '0002'.

"fill ur structure field here

WHEN '0003'.

"fill ur structure field here

ENDCASE.

and then you can transfer the data to respective field through BDC Recording and update the database table.

If you have any doubt, please let me know

Please appreciate if your problem get resloved .

Regards

Sachin

[Former Member]

Hi Sachin,

Thank u So much....

I have done all the steps..finally got runtime error like this " bdc_insert & is_invalis' while execute my prg..

but ther is no syntax error...

have add my sample coding along with this pls do help..

TYPE-POOLS:TRUXS.

TYPES:BEGIN OF TP_EMP,

EMP_NO TYPE ZEMPD_NO,

NAME TYPE ZEMPD_NAME,

DOB TYPE ZEMPD_DOB,

QUAL TYPE ZEMPD_QUAL,

END OF TP_EMP.

DATA:IG_EMP TYPE STANDARD TABLE OF TP_EMP,

WG_EMP TYPE TP_EMP.

TYPES:BEGIN OF TP_BDCDATA.

INCLUDE STRUCTURE BDCDATA.

TYPES:END OF TP_BDCDATA.

DATA:IG_BDCDATA TYPE STANDARD TABLE OF TP_BDCDATA,

WG_BDCDATA TYPE TP_BDCDATA.

DATA:I_RAW TYPE TRUXS_T_TEXT_DATA.

PARAMETERS:P_FILE TYPE RLGRAP-FILENAME.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

• PROGRAM_NAME = SYST-CPROG

• DYNPRO_NUMBER = SYST-DYNNR

FIELD_NAME = 'P_FILE '

IMPORTING

FILE_NAME = P_FILE.

*include bdcrecx1.

start-of-selection.

PERFORM READ_FLAT_FILE.

PERFORM OPEN_FROUP.

LOOP AT IG_EMP INTO WG_EMP.

*perform open_group.

perform bdc_dynpro using 'ZSCR_DATA_HANDLING' '9000'.

perform bdc_field using 'BDC_OKCODE'

'=SHOW'.

perform bdc_field using 'BDC_CURSOR'

'RB_CREATE'.

perform bdc_field using 'RB_CREATE'

'X'.

perform bdc_dynpro using 'ZSCR_DATA_HANDLING' '9100'.

perform bdc_field using 'BDC_OKCODE'

'=SAVE'.

perform bdc_field using 'BDC_CURSOR'

'ZEMPLOYEE_DETAIL-QUAL(01)'.

perform bdc_field using 'ZEMPLOYEE_DETAIL-EMP_NO(01)'

WG_EMP-EMP_NO.

perform bdc_field using 'ZEMPLOYEE_DETAIL-NAME(01)'

WG_EMP-NAME.

perform bdc_field using 'ZEMPLOYEE_DETAIL-DOB(01)'

WG_EMP-DOB.

perform bdc_field using 'ZEMPLOYEE_DETAIL-QUAL(01)'

WG_EMP-QUAL.

perform bdc_dynpro using 'ZSCR_DATA_HANDLING' '9100'.

perform bdc_field using 'BDC_OKCODE'

'=BACK'.

perform bdc_field using 'BDC_CURSOR'

'LBLS_HEAD'.

perform bdc_dynpro using 'ZSCR_DATA_HANDLING' '9000'.

perform bdc_field using 'BDC_OKCODE'

'=EXIT'.

perform bdc_field using 'BDC_CURSOR'

'TXT_HEAD'.

perform bdc_field using 'RB_CREATE'

'X'.

*perform bdc_transaction using 'ZMU_DH01'.

CALL FUNCTION 'BDC_INSERT'

EXPORTING

TCODE = 'zmu_dh01'

• POST_LOCAL = NOVBLOCAL

• PRINTING = NOPRINT

• SIMUBATCH = ' '

• CTUPARAMS = ' '

TABLES

dynprotab = ig_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.

refresh ig_bdcdata.

ENDLOOP.

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

&----

---

*& Form OPEN_FROUP

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

form OPEN_FROUP .

CALL FUNCTION 'BDC_OPEN_GROUP'

EXPORTING

CLIENT = SY-MANDT

• DEST = FILLER8

GROUP = 'MY_PONJ'

• HOLDDATE = FILLER8

KEEP = 'X'

USER = SY-UNAME

• RECORD = FILLER1

• PROG = SY-CPROG

• DCPFM = '%'

• DATFM = '%'

• IMPORTING

• QID =

EXCEPTIONS

CLIENT_INVALID = 1

DESTINATION_INVALID = 2

GROUP_INVALID = 3

GROUP_IS_LOCKED = 4

HOLDDATE_INVALID = 5

INTERNAL_ERROR = 6

QUEUE_ERROR = 7

RUNNING = 8

SYSTEM_LOCK_ERROR = 9

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

endform. " OPEN_FROUP

&----

---

*& Form READ_FLAT_FILE

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

form READ_FLAT_FILE .

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_FIELD_SEPERATOR ='X'

• I_LINE_HEADER =

i_tab_raw_data = I_RAW

i_filename = P_FILE

tables

i_tab_converted_data = IG_EMP[]

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

----

---

• Start new screen *

----

---

FORM BDC_DYNPRO USING PROGRAM DYNPRO.

CLEAR WG_BDCDATA.

WG_BDCDATA-PROGRAM = PROGRAM.

WG_BDCDATA-DYNPRO = DYNPRO.

WG_BDCDATA-DYNBEGIN = 'X'.

APPEND WG_BDCDATA TO IG_BDCDATA.

ENDFORM.

----

---

• Insert field *

----

---

FORM BDC_FIELD USING FNAM FVAL.

CLEAR WG_BDCDATA.

• IF FVAL <> NODATA.

CLEAR IG_BDCDATA.

WG_BDCDATA-FNAM = FNAM.

WG_BDCDATA-FVAL = FVAL.

APPEND WG_BDCDATA TO IG_BDCDATA.

• ENDIF.

[Former Member]

try this code

DATA : IT_FILE LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.

DATA FILE_PATH LIKE RLGRAP-FILENAME.

TYPES : BEGIN OF SBU_UPLOAD ,

BU TYPE c,

BU_DESC TYPE c,

DELETE TYPE C,

END OF SBU_UPLOAD .

DATA TBU_UPLOAD TYPE STANDARD TABLE OF SBU_UPLOAD WITH HEADER LINE.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

field_name = 'FILE_LOC' "Here file_LOC is the text Element name on Screen.

IMPORTING

file_name = FILE_PATH.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = FILE_PATH

I_BEGIN_COL = '1'

I_BEGIN_ROW = '3'

I_END_COL = '3'

I_END_ROW = '256'

TABLES

INTERN = IT_FILE

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3.

IF SY-SUBRC NE SPACE.

*leave PROGRAM.

ENDIF.

" Now I use the following Logic to convert that IT_FILE table into our own table format."

LOOP AT IT_FILE.

CASE IT_FILE-COL.

WHEN '0001'.

MOVE IT_FILE-VALUE TO TBU_UPLOAD-BU.

WHEN '0002'.

MOVE IT_FILE-VALUE TO TBU_UPLOAD-BU_DESC.

WHEN '0003'.

MOVE IT_FILE-VALUE TO TBU_UPLOAD-DELETE.

ENDCASE.

AT END OF ROW.

"At end of row TBU_UPLOAD is updated with the desired value i.e

When Row Column in table one converted from 1 to 2 , the first column in Table 2 is inserted. Which is the required format of us . "

APPEND TBU_UPLOAD.

CLEAR TBU_UPLOAD.

ENDAT.

• APPEND TBU_UPLOAD.

ENDLOOP.