topic Re: Upload data in excel sheet through BDC in Application Development and Automation Discussions

Upload data in excel sheet through BDC

Former Member — Wed, 04 Jun 2008 05:50:05 GMT

Dear all,

How do we upload data in excel sheet through BDC?

Thanks in advance.

Regards,

Sandra.

Re: Upload data in excel sheet through BDC

Former Member — Wed, 04 Jun 2008 05:52:28 GMT

Hi,

The sample code is as given below:

REPORT upload_supply_area.

*include for dispaying icons in error log

INCLUDE <icon>.

*Declaration of structure.

TYPES:BEGIN OF x_struct,

werks TYPE v_pvbe-werks, "Plant

prvbe TYPE v_pvbe-prvbe, "Supply Area

pvbtx TYPE v_pvbe-pvbtx, "Production supply area description

lgort TYPE v_pvbe-lgort, "Storage Location

rgver TYPE v_pvbe-rgver, "Person responsible for one or more supply areas

END OF x_struct.

TYPES:BEGIN OF x_messages,

msgtyp(1) type c,

werks TYPE v_pvbe-werks, "Plant

prvbe TYPE v_pvbe-prvbe, "Supply Area

message(120) type c,

END OF x_messages.

DATA: it_messages TYPE STANDARD TABLE OF x_messages .

DATA: wa_messages TYPE x_messages.

DATA:it_msgtab TYPE STANDARD TABLE OF bdcmsgcoll,

wa_msgtab TYPE bdcmsgcoll.

*internal table for BDC

DATA: it_bdcdata TYPE STANDARD TABLE OF bdcdata.

DATA: wa_bdcdata TYPE bdcdata.

DATA:it_file TYPE STANDARD TABLE OF x_struct. "internal table which has same structure as file

DATA:wa_file TYPE x_struct. "work area which has same structure as file

DATA: it_excel TYPE STANDARD TABLE OF alsmex_tabline,

wa_excel TYPE alsmex_tabline.

DATA: x_ctuprms TYPE ctu_params.

DATA:nodata TYPE c VALUE '/'.

data:con(50) type c.

data:con1(50) type c.

*selection screen

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

*Enter file name on presentation server

PARAMETERS: p_file TYPE rlgrap-filename OBLIGATORY.

SELECTION-SCREEN END OF BLOCK b1.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

*Function which enables the user to browse the files on hard disk

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

program_name = syst-repid

static = 'X'

CHANGING

file_name = p_file

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.

START-OF-SELECTION.

*Subroutine to upload excel file and read it

PERFORM upload.

*Subroutine to upload supply area data

PERFORM fill.

&----

*& Form bdc_dynpro

&----

Fill the BDC table

----

FORM bdc_dynpro USING program dynpro. "#EC *

CLEAR wa_bdcdata.

wa_bdcdata-program = program.

wa_bdcdata-dynpro = dynpro.

wa_bdcdata-dynbegin = 'X'.

APPEND wa_bdcdata TO it_bdcdata.

ENDFORM. "BDC_DYNPRO

&----

*& Form bdc_field

&----

Fill the BDC table

----

FORM bdc_field USING fnam fval. "#EC *

IF fval <> nodata.

CLEAR wa_bdcdata.

wa_bdcdata-fnam = fnam.

wa_bdcdata-fval = fval.

APPEND wa_bdcdata TO it_bdcdata. "#EC

ENDIF.

ENDFORM. "BDC_FIELD

&----

*& Form collect_messages

&----

Collect the messages from transaction

----

FORM collect_messages . "#EC *

DATA: w_msg(100).

LOOP AT it_msgtab INTO wa_msgtab.

CALL FUNCTION 'FORMAT_MESSAGE'

EXPORTING

id = wa_msgtab-msgid

lang = wa_msgtab-msgspra

no = wa_msgtab-msgnr

v1 = wa_msgtab-msgv1

v2 = wa_msgtab-msgv2

IMPORTING

msg = w_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.

CONDENSE w_msg.

CLEAR wa_messages.

wa_messages-msgtyp = wa_msgtab-msgtyp.

wa_messages-message = w_msg.

wa_messages-werks = wa_file-werks.

wa_messages-prvbe = wa_file-prvbe.

if wa_messages-message eq 'Formatting error in the field V_PVBE-RGVER; see next message'.

wa_messages-message = 'Invalid name of the person responsible'.

endif.

if wa_messages-message eq 'Formatting error in the field V_PVBE-LGORT; see next message'.

wa_messages-message = 'Enter the storage location'.

endif.

APPEND wa_messages TO it_messages .

ENDLOOP.

REFRESH it_msgtab.

ENDFORM. "collect_messages

&----

*& Form write_messages

&----

Display the messages

----

FORM write_messages .

DELETE ADJACENT DUPLICATES FROM it_messages COMPARING werks prvbe.

LOOP AT it_messages INTO wa_messages .

WRITE:/1 sy-vline.

IF wa_messages-msgtyp = 'S'.

WRITE: 10 icon_green_light.

ELSEIF wa_messages-msgtyp = 'E'.

WRITE: 10 icon_red_light.

ELSEIF wa_messages-msgtyp = 'W'.

WRITE: 10 icon_yellow_light.

ENDIF.

WRITE: 20 sy-vline.

WRITE : 30 'Plant-', wa_messages-werks . "#EC NOTEXT

WRITE: 48 sy-vline.

WRITE : 49 'Supply Area-', wa_messages-prvbe . "#EC NOTEXT

WRITE: 79 sy-vline.

WRITE : 80 wa_messages-message .

WRITE: 180 sy-vline.

WRITE:/1 sy-vline.

ULINE 1(180).

ENDLOOP.

ENDFORM. " write_m

&----

*& Form fill_params

&----

Processing mode for the transaction

----

FORM fill_params .

x_ctuprms-dismode = 'N'.

x_ctuprms-updmode = 'A'.

x_ctuprms-defsize = 'X'.

ENDFORM. "fill_params

&----

*& Form upload

&----

Upload the excel file and read the data

----

FORM upload .

*Function to upload excel file

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = p_file

i_begin_col = 1

i_begin_row = 2

i_end_col = 5

i_end_row = 9999

TABLES

intern = it_excel

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.

CLEAR wa_file.

*Read the file row-wise

LOOP AT it_excel INTO wa_excel.

CASE wa_excel-col .

*Read plant

WHEN '1'.

wa_file-werks = wa_excel-value.

*Read supply area

WHEN '2'.

wa_file-prvbe = wa_excel-value.

*Read decription

WHEN '3'.

wa_file-pvbtx = wa_excel-value.

*Read storage location

WHEN '4'.

wa_file-lgort = wa_excel-value.

*Read Person responsible

WHEN '5'.

IF STRLEN( wa_excel-value ) = 1.

CONCATENATE '00' wa_excel-value INTO con.

wa_file-rgver = con.

ELSEIF STRLEN( wa_excel-value ) = 2.

CONCATENATE '0' wa_excel-value INTO con1.

wa_file-rgver = con1.

ELSE.

wa_file-rgver = wa_excel-value.

ENDIF.

ENDCASE.

AT END OF row.

CONDENSE:wa_file-werks,wa_file-prvbe,wa_file-pvbtx,wa_file-lgort,wa_file-rgver.

APPEND wa_file TO it_file.

CLEAR wa_file.

ENDAT .

ENDLOOP.

ENDFORM. " upload

&----

*& Form fill

&----

Call the transaction 'PK05'

----

FORM fill .

*Upload the data through transaction 'PK05'

PERFORM fill_params.

LOOP AT it_file INTO wa_file.

PERFORM bdc_dynpro USING 'SAPLSVIX' '0100'.

PERFORM bdc_field USING 'BDC_CURSOR'

'D0100_FIELD_TAB-LOWER_LIMIT(01)'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=OKAY'.

PERFORM bdc_dynpro USING 'SAPL0PK1' '0020'.

PERFORM bdc_field USING 'BDC_CURSOR'

'V_PVBE-PVBTX(01)'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=NEWL'.

PERFORM bdc_dynpro USING 'SAPL0PK1' '0021'.

PERFORM bdc_field USING 'BDC_CURSOR'

'V_PVBE-RGVER'.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

PERFORM bdc_field USING 'V_PVBE-WERKS'

wa_file-werks.

PERFORM bdc_field USING 'V_PVBE-PRVBE'

wa_file-prvbe.

PERFORM bdc_field USING 'V_PVBE-PVBTX'

wa_file-pvbtx.

PERFORM bdc_field USING 'V_PVBE-LGORT'

wa_file-lgort.

PERFORM bdc_field USING 'V_PVBE-RGVER'

wa_file-rgver.

PERFORM bdc_dynpro USING 'SAPL0PK1' '0021'.

PERFORM bdc_field USING 'BDC_CURSOR'

'V_PVBE-WERKS'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=SAVE'.

PERFORM bdc_field USING 'V_PVBE-WERKS'

wa_file-werks.

PERFORM bdc_field USING 'V_PVBE-PRVBE'

wa_file-prvbe.

PERFORM bdc_field USING 'V_PVBE-PVBTX'

wa_file-pvbtx.

PERFORM bdc_field USING 'V_PVBE-LGORT'

wa_file-lgort.

PERFORM bdc_field USING 'V_PVBE-RGVER'

wa_file-rgver.

CALL TRANSACTION 'PK05'

USING it_bdcdata

OPTIONS FROM x_ctuprms

MESSAGES INTO it_msgtab.

REFRESH it_bdcdata.

PERFORM collect_messages.

CLEAR wa_file.

ENDLOOP.

PERFORM write_messages.

ENDFORM. " fill

Re: Upload data in excel sheet through BDC

Former Member — Wed, 04 Jun 2008 05:54:49 GMT

what u can do is use ALSM_EXCEL_TO_INTERNAL_TABLE function module to upload data.

i will provide you with a sample code. hope it helps!!

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.

u2022 Work Variables Declaration.

CONSTANTS:

W_Y TYPE C VALUE 'Y',

W_N TYPE C VALUE 'N'.

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

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

u2022 MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

u2022 WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

ENDFORM. " upload_data_fromexcel

&----

*& Form process_data

&----

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

u2022 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

u2022 TITLE = BOX_TITLE

IMPORTING

FILENAME = P_FILENAME

EXCEPTIONS

INV_WINSYS = 1

NO_BATCH = 2

SELECTION_CANCEL = 3

SELECTION_ERROR = 4

OTHERS = 5.

u2022 CASE SY-SUBRC.

u2022 WHEN 1.

u2022 MESSAGE I999 WITH

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

u2022 WHEN 2.

u2022 MESSAGE E999 WITH

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

u2022 WHEN 3.

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

u2022 WHEN 4.

u2022 MESSAGE E999 WITH 'Communication error'(049).

u2022 WHEN 5.

u2022 MESSAGE E999 WITH 'Other error'(050).

u2022 ENDCASE.

ENDFORM. " get_filename[/code]

Regards

Puneet Chadha