topic Re: upload data in Application Development and Automation Discussions

upload data

Former Member — Wed, 20 May 2009 09:45:01 GMT

show me code for inserting data from excel file to ztable using gui_upload function

Re: upload data

gautam_totekar — Wed, 20 May 2009 10:07:16 GMT

Use function module to get the file name by browsing to it.

CALL FUNCTION 'WS_FILENAME_GET'

EXPORTING

def_filename = '.'

def_path = 'D:\'

mask = ',.txt,.txt.,*.xls.'

mode = 'O'

IMPORTING

filename = upfile

EXCEPTIONS

inv_winsys = 1

no_batch = 2

selection_cancel = 3

selection_error = 4

OTHERS = 5.

use this function module to uplaod data from the specified file name to internal table int_excel.

int_ecel should be of type

int excel TYPE alsmextabline OCCURS 0 WITH HEADER LINE.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = upfile

i_begin_col = 1

i_begin_row = 4

i_end_col = 12 "12

i_end_row = 9999

TABLES

intern = int_excel.

IF sy-subrc <> 0.

ENDIF.

Then map the data into work area and then insert into Databse Z table.

DATA : trow TYPE i,

row TYPE i.

trow = 1.

loop at internal table it_excel

LOOP AT int_excel WHERE value NE ' '.

IF trow NE int_excel-row.

trow = trow + 1.

ENDIF.

ENDLOOP.

Data coming from External File into transection md61

row = 1.

WHILE row <= trow.

LOOP AT int_excel WHERE row = row.

CASE int_excel-col.

WHEN '0001'.

wa_mytab-matnr = int_excel-value.

WHEN '0002'.

wa_mytab1-pbdnr = int_excel-value .

WHEN '0003'.

wa_mytab1-werks = int_excel-value .

WHEN '0004'.

wa_mytab1-versb = int_excel-value .

WHEN '0005'.

wa_mytab1-datve = int_excel-value .

WHEN '0006'.

wa_mytab1-datbe = int_excel-value .

WHEN '0007'.

wa_mytab-pln07 = int_excel-value.

ENDCASE.

ENDLOOP.

*append internal table mytab and mytab1

APPEND wa_mytab to ZTABLE.

row = row + 1.

CLEAR int_mytab.

ENDWHILE.

your Z tabel has the above mentioned fields of the work area. wa_mytab. You can change fields as required.

Re: upload data

Former Member — Wed, 20 May 2009 10:50:51 GMT

Hi Sami,

I hope following line of code might resolve your problem

PARAMETER : p_file TYPE rlgrap-filename.

DATA : date TYPE sy-datum.

DATA: it_bdcdata TYPE bdcdata OCCURS 0 WITH HEADER LINE.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

program_name = syst-cprog

dynpro_number = syst-dynnr

FIELD_NAME = 'MATNR'

IMPORTING

file_name = p_file

DATA : p_file1 TYPE string.

p_file1 = p_file.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = p_file1

FILETYPE = 'ASC' <----

Change the file type to XLS

HAS_FIELD_SEPARATOR = ' '

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 =

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.

By doing this u can upload the data from excell to your ztable.

Thanks & Regards,

Lokesh.

Re: upload data

Former Member — Wed, 20 May 2009 12:09:32 GMT

TABLES: pbim,

pbpt.

DATA: bdcdata LIKE bdcdata OCCURS 0 WITH HEADER LINE,

int_excel TYPE alsmex_tabline OCCURS 0 WITH HEADER LINE.

define record structure for uploading xls sheet values in the transection md61

DATA: BEGIN OF record,

mataw_001(001),

pbdaw_002(001),

pbdnr_003(010),

werks_004(004),

versb_005(002),

datve_006(010),

datbe_007(010),

entlu_008(001),

matnr_08_009(018),

pln07_08_010(017),

END OF record.

*define internal table int__mytab for uploading xls file value

DATA :BEGIN OF int_mytab OCCURS 0,

matnr(14) TYPE c,

pln07(13) TYPE c,

END OF int_mytab.

*define internal table int_mytab1

DATA : BEGIN OF int_mytab1 OCCURS 0 ,

pbdnr(10) TYPE c,

werks(8) TYPE c,

versb(2) TYPE c,

datve(10) TYPE c,

datbe(10) TYPE c,

END OF int_mytab1.

DATA v_fnam(30) TYPE c.

DATA l_index TYPE n LENGTH 2.

DATA: ft_flg TYPE i VALUE 0.

DATA g_loop(10).

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

*selection screen choosing for XL file

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

SELECTION-SCREEN BEGIN OF BLOCK params WITH FRAME TITLE text-002.

PARAMETER: upfile LIKE rlgrap-filename OBLIGATORY.

SELECTION-SCREEN END OF BLOCK params.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR upfile.

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

*call function WS_FILENAME_GET. for selecting external file

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

CALL FUNCTION 'WS_FILENAME_GET'

EXPORTING

def_filename = '.'

def_path = 'D:\'

mask = ',.txt,.txt.,*.xls.'

mode = 'O'

IMPORTING

filename = upfile

EXCEPTIONS

inv_winsys = 1

no_batch = 2

selection_cancel = 3

selection_error = 4

OTHERS = 5.

END-OF-SELECTION.

End generated data section ***

****TYPES*********************************************************************

*start-of-selection

START-OF-SELECTION.

PERFORM open_my_datafile.

DATA : trow TYPE i,

row TYPE i.

trow = 1.

loop at internal table it_excel

LOOP AT int_excel WHERE value NE ' '.

IF trow NE int_excel-row.

trow = trow + 1.

ENDIF.

ENDLOOP.

Data coming from External File into transection md61

row = 1.

WHILE row <= trow.

LOOP AT int_excel WHERE row = row.

CASE int_excel-col.

WHEN '0001'.

int_mytab-matnr = int_excel-value.

WHEN '0002'.

int_mytab1-pbdnr = int_excel-value .

WHEN '0003'.

int_mytab1-werks = int_excel-value .

WHEN '0004'.

int_mytab1-versb = int_excel-value .

WHEN '0005'.

int_mytab1-datve = int_excel-value .

WHEN '0006'.

int_mytab1-datbe = int_excel-value .

WHEN '0007'.

int_mytab-pln07 = int_excel-value.

ENDCASE.

ENDLOOP.

*append internal table mytab and mytab1

APPEND int_mytab.

APPEND int_mytab1.

row = row + 1.

CLEAR int_mytab.

ENDWHILE.

DELETE int_mytab WHERE matnr = ' '.

make a loop in internal table mytab1 .

LOOP AT int_mytab1 TO 1.

DATA : l_pbdnr TYPE pbim-pbdnr,

l_matnr TYPE pbim-matnr.

l_pbdnr = int_mytab1-pbdnr.

set extended check off.

TRANSLATE l_pbdnr TO UPPER CASE.

SELECT SINGLE matnr

FROM pbim INTO l_matnr

WHERE werks = int_mytab1-werks

AND versb = int_mytab1-versb

AND pbdnr = l_pbdnr ."MYTAB1-PBDNR.

ENDSELECT.

IF sy-subrc = '0'.

MESSAGE e000.

ENDIF.

PERFORM bdc_dynpro USING 'SAPMM60X' '0100'.

PERFORM bdc_field USING 'BDC_CURSOR'

'AM60X-PBDAW'.

PERFORM bdc_field USING 'AM60X-PBDAW'

'X'.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

PERFORM bdc_field USING 'AM60X-PBDNR'

int_mytab1-pbdnr.

PERFORM bdc_field USING 'AM60X-WERKS'

int_mytab1-werks.

PERFORM bdc_field USING 'RM60X-VERSB'

int_mytab1-versb.

PERFORM bdc_field USING 'RM60X-DATVE'

int_mytab1-datve.

PERFORM bdc_field USING 'RM60X-DATBE'

int_mytab1-datbe.

PERFORM bdc_dynpro USING 'SAPLM60E' '0200'.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

PERFORM bdc_field USING 'BDC_CURSOR'

'RM60X-PLN01(01)'.

MOVE 1 TO l_index.

Loop at internal table mytab ***************

LOOP AT int_mytab.

CONCATENATE 'PBPT-MATNR(' l_index ')' INTO v_fnam.

PERFORM bdc_field USING v_fnam int_mytab-matnr.

CLEAR v_fnam.

CONCATENATE 'RM60X-PLN01(' l_index ')' INTO v_fnam.

PERFORM bdc_field USING v_fnam int_mytab-pln07.

CLEAR v_fnam.

L_index start hear

IF l_index = 19.

IF ft_flg = 0.

PERFORM bdc_dynpro USING 'SAPLM60E' '0200'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=P+'.

PERFORM bdc_field USING 'BDC_SUBSCR'

'SAPLM60E 0206KOPF1'.

PERFORM bdc_field USING 'BDC_SUBSCR'

'SAPLM60E 0202SUB2'.

PERFORM bdc_field USING 'BDC_CURSOR'

'PBPT-MATNR(01)'.

PERFORM bdc_dynpro USING 'SAPLM60E' '0200'.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

PERFORM bdc_field USING 'BDC_SUBSCR'

'SAPLM60E 0206KOPF1'.

PERFORM bdc_field USING 'BDC_SUBSCR'

'SAPLM60E 0202SUB2'.

PERFORM bdc_field USING 'BDC_CURSOR'

'RM60X-PLN01(01)'.

ENDIF.

flag start hear

IF ft_flg = 1.

PERFORM bdc_dynpro USING 'SAPLM60E' '0200'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=P+'.

PERFORM bdc_field USING 'BDC_SUBSCR'

'SAPLM60E 0206KOPF1'.

PERFORM bdc_field USING 'BDC_SUBSCR'

'SAPLM60E 0202SUB2'.

PERFORM bdc_field USING 'BDC_CURSOR'

'PBPT-MATNR(01)'.

PERFORM bdc_dynpro USING 'SAPLM60E' '0200'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=P+'.

PERFORM bdc_field USING 'BDC_SUBSCR'

'SAPLM60E 0206KOPF1'.

PERFORM bdc_field USING 'BDC_SUBSCR'

'SAPLM60E 0202SUB2'.

PERFORM bdc_field USING 'BDC_CURSOR'

'PBPT-MATNR(01)'.

PERFORM bdc_dynpro USING 'SAPLM60E' '0200'.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

PERFORM bdc_field USING 'BDC_SUBSCR'

'SAPLM60E 0206KOPF1'.

PERFORM bdc_field USING 'BDC_SUBSCR'

'SAPLM60E 0202SUB2'.

PERFORM bdc_field USING 'BDC_CURSOR'

'RM60X-PLN01(01)'.

ENDIF.

l_index = 0.

ft_flg = 1.

ENDIF.

l_index = l_index + 1.

ENDLOOP.

PERFORM bdc_dynpro USING 'SAPLM60E' '0200'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=SICH'.

PERFORM bdc_field USING 'BDC_CURSOR'

'PBPT-MATNR(01)'.

CALL TRANSACTION 'MD61' USING bdcdata MODE 'A'. "#EC CI_CALLTA

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

BEGIN FORMS HEAR *

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

FORM OPEN_MY_DATAFILE. *

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

FORM open_my_datafile.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = upfile

i_begin_col = 1

i_begin_row = 4

i_end_col = 12 "12

i_end_row = 9999

TABLES

intern = int_excel.

IF sy-subrc <> 0.

ENDIF.

ENDFORM. "open_my_datafile

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

FORM BDC_DYNPRO USING PROGRAM DYNPRO.

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

FORM bdc_dynpro USING program dynpro.

CLEAR bdcdata.

bdcdata-program = program.

bdcdata-dynpro = dynpro.

bdcdata-dynbegin = 'X'.

APPEND bdcdata.

ENDFORM. "BDC_DYNPRO

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

Insert field *

----

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

FORM BDC_FIELD USING FNAM FVAL. *

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

FORM bdc_field USING fnam fval.

CLEAR bdcdata.

bdcdata-fnam = fnam.

bdcdata-fval = fval.

APPEND bdcdata.

ENDFORM. "BDC_FIELD

Re: upload data

Former Member — Wed, 20 May 2009 12:13:57 GMT

CALL FUNCTION 'WS_FILENAME_GET'

EXPORTING

def_filename = '.'

def_path = 'D:\'

mask = ',.txt,.txt.,*.xls.'

mode = 'O'

IMPORTING

filename = upfile

EXCEPTIONS

inv_winsys = 1

no_batch = 2

selection_cancel = 3

selection_error = 4

OTHERS = 5.

START-OF-SELECTION.

PERFORM open_my_datafile.

DATA : trow TYPE i,

row TYPE i.

trow = 1.

loop at internal table it_excel

LOOP AT int_excel WHERE value NE ' '.

IF trow NE int_excel-row.

trow = trow + 1.

ENDIF.

ENDLOOP.

Data coming from External File into transection md61

row = 1.

WHILE row <= trow.

LOOP AT int_excel WHERE row = row.

CASE int_excel-col.

WHEN '0001'.

int_mytab-matnr = int_excel-value.

WHEN '0002'.

int_mytab1-pbdnr = int_excel-value .

WHEN '0003'.

int_mytab1-werks = int_excel-value .

WHEN '0004'.

int_mytab1-versb = int_excel-value .

WHEN '0005'.

int_mytab1-datve = int_excel-value .

WHEN '0006'.

int_mytab1-datbe = int_excel-value .

WHEN '0007'.

int_mytab-pln07 = int_excel-value.

ENDCASE.

ENDLOOP.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = upfile

i_begin_col = 1

i_begin_row = 4

i_end_col = 12 "12

i_end_row = 9999

TABLES

intern = int_excel.

IF sy-subrc <> 0.

ENDIF.

ENDFORM. "open_my_datafile

*append internal table mytab and mytab1

APPEND int_mytab.

APPEND int_mytab1.

row = row + 1.

CLEAR int_mytab.

ENDWHILE.

DELETE int_mytab WHERE matnr = ' '.

Re: upload data

Former Member — Thu, 21 May 2009 08:59:55 GMT

for uploading data from excel sheet to sap db table instead of FM 'GUI_UPLOAD'

try FM 'TEXT_CONVERT_XLS_TO_SAP'

example

report zgui_upload.

type-pools truxs.

tables : zempdata.

data : begin of itab occurs 0,

sname(15) type c,

scity(20) type c,

sage(3) type c,

end of itab.

data : it_type type truxs_t_text_data.

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

start-of-selection.

Uploading the data in the file into internal table

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_FIELD_SEPERATOR =
I_LINE_HEADER = 'X'

i_tab_raw_data = it_type

i_filename = p_file

TABLES

i_tab_converted_data = ITAB[].

EXCEPTIONS
conversion_failed = 1
OTHERS = 2.

if sy-subrc = 0.

insert zempdata from table itab.

endloop.

Regards

Re: upload data

Former Member — Thu, 21 May 2009 09:42:58 GMT

hi,

I think its not possible to upload excel file using GUI_UPLOAD

regards