topic Uploadding data from excel in Application Development and Automation Discussions

Uploadding data from excel

Former Member — Tue, 24 Jul 2007 07:22:54 GMT

Hii

i had a different senario to upload the data into the SAP system .

There is header data of 7 fields and line items of 32 fields

and the excel is like

Control Indicator : Header data.....

Control Indicator : Line item data......

I want to take into internal table first header data then lineitem data in one go then next header data .

But it is taking as first header data and then in next line of internal table next line item data .

Please give some raw code to accomplish this

Waitin 4 replies

Regards

Hitesh

Re: Uploadding data from excel

Former Member — Tue, 24 Jul 2007 07:28:08 GMT

See the sample BDC code for populating Header and Item tables data for a PR Transaction and do accordingly

REPORT zmm_pr_upload_mat

NO STANDARD PAGE HEADING

LINE-SIZE 255.

Standard Include for Selection Screen

INCLUDE bdcrecx1.

Internal Table for Upload Data

DATA: BEGIN OF i_pr OCCURS 0,

Header Screen

sno(3), " SNo

bsart(004), " PR Type

epstp(001), " Item Category

knttp(001), " Account Assignment

eeind(010), " Delivery Date

lpein(001), " Category of Del Date

werks(004), " Plant

lgort(004), " Storage Location

ekgrp(003), " Purchasing Group

matkl(009), " Material Group

bednr(010), " Tracking No

afnam(012), " Requisitioner

Item Details

matnr(018), " Material No

menge(017), " Quantity

badat(010),
frgdt(010),

preis(014), " Valuation Price

waers(005), " Currency
peinh(005),
wepos(001),
repos(001),

sakto(010), " GL Account

kostl(010), " Cost Center

bnfpo(005),

END OF i_pr.

Internal Table for header Data

DATA: BEGIN OF it_header OCCURS 0,

sno(3), " SNo

bsart(004), " PR Type

epstp(001), " Item Category

knttp(001), " Account Assignment

eeind(010), " Delivery Date

werks(004), " Plant

lgort(004), " Storage Location

ekgrp(003), " Purchasing Group

matkl(009), " Material Group

bednr(010), " Tracking No

afnam(012), " Requisitioner

END OF it_header.

Internal Table for Item Data

DATA: BEGIN OF it_item OCCURS 0,

sno(3), " SNo

matnr(018), " Material No

menge(017), " Quantity

preis(014), " Valuation Price

sakto(010), " GL Account

kostl(010), " Cost Center

END OF it_item.

Data Variables & Constants

CONSTANTS : c_x VALUE 'X'. " Flag

DATA : v_l(2), " Counter

v_rowno(5), " Row No

v_2(2), " Counter

v_rows LIKE sy-srows, " Rows in TC

v_field(45). " String

Parameters

PARAMETERS: p_file LIKE ibipparms-path. " Filename

At selection-screen on Value Request for file Name

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

Get the F4 Values for the File

CALL FUNCTION 'F4_FILENAME'

EXPORTING

program_name = syst-cprog

dynpro_number = syst-dynnr

IMPORTING

file_name = p_file.

Start of Selection

START-OF-SELECTION.

Open the BDC Session

PERFORM open_group.

Upload the File into internal Table

CALL FUNCTION 'UPLOAD'

EXPORTING

filename = p_file

filetype = 'DAT'

TABLES

data_tab = i_pr

EXCEPTIONS

conversion_error = 1

invalid_table_width = 2

invalid_type = 3

no_batch = 4

unknown_error = 5

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

SORT i_pr BY sno.

LOOP AT i_pr.

MOVE-CORRESPONDING i_pr TO it_item.

APPEND it_item.

CLEAR it_item.

AT END OF sno.

READ TABLE i_pr INDEX sy-tabix.

MOVE-CORRESPONDING i_pr TO it_header.

APPEND it_header.

CLEAR it_header.

ENDAT.

ENDLOOP.

SORT it_header BY sno.

SORT it_item BY sno.

v_rows = sy-srows - 6.

Upload the Data from Internal Table

LOOP AT it_header.

Header Data

PERFORM bdc_dynpro USING 'SAPMM06B' '0100'.

PERFORM bdc_field USING 'BDC_CURSOR'

'EBAN-BEDNR'.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

PERFORM bdc_field USING 'EBAN-BSART'

it_header-bsart.

PERFORM bdc_field USING 'RM06B-EPSTP'

it_header-epstp.

PERFORM bdc_field USING 'EBAN-KNTTP'

it_header-knttp.

PERFORM bdc_field USING 'RM06B-EEIND'

it_header-eeind.

PERFORM bdc_field USING 'RM06B-LPEIN'
it_header-lpein.

PERFORM bdc_field USING 'EBAN-WERKS'

it_header-werks.

PERFORM bdc_field USING 'EBAN-LGORT'

it_header-lgort.

PERFORM bdc_field USING 'EBAN-EKGRP'

it_header-ekgrp.

PERFORM bdc_field USING 'EBAN-MATKL'

it_header-matkl.

PERFORM bdc_field USING 'EBAN-BEDNR'

it_header-bednr.

PERFORM bdc_field USING 'EBAN-AFNAM'

it_header-afnam.

Item Details

v_l = 0.

To add no. of rows

v_2 = 0 .

As the screen is showing 13 rows defaulted to 130

v_rowno = 130 .

LOOP AT it_item WHERE sno = it_header-sno.

v_l = v_l + 1.

IF v_l = 14 .

IF v_2 = 12 .

v_2 = 12 .

v_l = 2 .

From second time onwards it is displaying 12 rows only

v_rowno = v_rowno + 120 .

PERFORM bdc_dynpro USING 'SAPMM06B' '0106'.

PERFORM bdc_field USING 'BDC_CURSOR'

'RM06B-BNFPO'.

PERFORM bdc_field USING 'RM06B-BNFPO'

v_rowno.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

ELSE.

V_2 initialized to 12 for second screen purpose

v_2 = 12 .

v_l = 2 .

PERFORM bdc_dynpro USING 'SAPMM06B' '0106'.

PERFORM bdc_field USING 'BDC_CURSOR'

'RM06B-BNFPO'.

PERFORM bdc_field USING 'RM06B-BNFPO'

v_rowno .

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

ENDIF.

PERFORM bdc_dynpro USING 'SAPMM06B' '0106'.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

CLEAR v_field.

CONCATENATE 'EBAN-MATNR(' v_l ')' INTO v_field.

PERFORM bdc_field USING v_field it_item-matnr.

CLEAR v_field.

CONCATENATE 'EBAN-MENGE(' v_l ')' INTO v_field.

PERFORM bdc_field USING v_field it_item-menge.

PERFORM bdc_dynpro USING 'SAPMM06B' '0102'.

PERFORM bdc_field USING 'BDC_CURSOR'

'EBAN-PREIS'.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

PERFORM bdc_field USING 'EBAN-PREIS'

it_item-preis.

PERFORM bdc_dynpro USING 'SAPMM06B' '0505'.

PERFORM bdc_field USING 'BDC_CURSOR'

'EBKN-SAKTO'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=ENTE'.

PERFORM bdc_field USING 'EBKN-SAKTO'

it_item-sakto.

Cost Center

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_item-kostl.

PERFORM bdc_field USING 'BDC_OKCODE'

'=ENTE'.

ENDLOOP.

PERFORM bdc_dynpro USING 'SAPMM06B' '0106'.

PERFORM bdc_field USING 'BDC_CURSOR'

'RM06B-BNFPO'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=BU'.

Call The Transaction

PERFORM bdc_transaction USING 'ME51'.

ENDLOOP.

Close the BDC Session

PERFORM close_group.

<b>Reward points for useful Answers</b>

Regards

Anji

Re: Uploadding data from excel

Former Member — Tue, 24 Jul 2007 07:40:00 GMT

Hi,

Try the below code:

TYPE-POOLS: truxs.

TYPES: BEGIN OF ty_dataupload, "Input file load structure

col01(60) TYPE c,

col02(60) TYPE c,

col03(60) TYPE c,

col04(60) TYPE c,

END OF ty_dataupload.

DATA: it_raw TYPE truxs_t_text_data .

DATA : It_datatab TYPE ty_dataupload OCCURS 0,

PARAMETERS: p_file LIKE ibipparms-path OBLIGATORY.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

CALL FUNCTION 'F4_FILENAME' "For upload of the program

EXPORTING

field_name = 'P_FILE'

IMPORTING

file_name = p_file.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

i_line_header = ' '

i_tab_raw_data = it_raw "work table

i_filename = p_file

TABLES

i_tab_converted_data = It_datatab[] "internal table to store the the data

EXCEPTIONS

conversion_failed = 1

OTHERS = 2.

IF sy-subrc <> 0.

ENDIF.

LOOP AT it_datatab INTO wa_datatab .

IF wa_datatab-col01 = 'H' OR wa_datatab-col01 = 'I' .

IF wa_datatab-col01 EQ 'H'.

IF wa_datatab-col02 IS NOT INITIAL.

MOVE wa_datatab-col02 TO wa_itab-field2.

IF wa_datatab-col03 IS NOT INITIAL.

MOVE wa_datatab-col03 TO wa_itab-field3.

IF wa_datatab-col04 IS NOT INITIAL.

MOVE wa_datatab-col04 TO wa_itab-field4.

IF wa_datatab-col05 IS NOT INITIAL.

MOVE wa_datatab-col05 TO wa_itab-field5.

IF wa_datatab-col06 IS NOT INITIAL.

MOVE wa_datatab-col06 TO wa_itab-field6.

IF wa_datatab-col07 IS NOT INITIAL.

MOVE wa_datatab-col07 TO wa_itab-field7.

...

......

IF wa_datatab-col32 IS NOT INITIAL.

WRITE wa_datatab-col32 TO wa_itab-field32.

ENDIF

AT END OF col01.

IF wa_datatab-col01 EQ 'I'. "Counter reset for next document

CLEAR .

CLEAR flag.

ENDIF.

ENDAT.

Regards