topic Re: bdc:upload excel sheet data into database through bdc in Application Development and Automation Discussions

bdc:upload excel sheet data into database through bdc

Former Member — Tue, 27 Oct 2009 11:58:49 GMT

Hi,

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

pls help me

Re: bdc:upload excel sheet data into database through bdc

Former Member — Tue, 27 Oct 2009 12:03:48 GMT

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

Re: bdc:upload excel sheet data into database through bdc

Former Member — Tue, 27 Oct 2009 12:10:11 GMT

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

Re: bdc:upload excel sheet data into database through bdc

Former Member — Wed, 28 Oct 2009 06:51:08 GMT

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.

Re: bdc:upload excel sheet data into database through bdc

Former Member — Wed, 28 Oct 2009 06:57:10 GMT

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.