Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

upload data

Former Member

‎2009 May 20 10:45 AM

0 Kudos
133

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

6 REPLIES 6

gautam_totekar
Active Participant

‎2009 May 20 11:07 AM

0 Kudos
92

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.

Former Member

‎2009 May 20 11:50 AM

0 Kudos
92

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.

Former Member

‎2009 May 20 1:09 PM

0 Kudos
92

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.

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

Former Member

‎2009 May 20 1:13 PM

0 Kudos
92

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

Former Member

‎2009 May 21 9:59 AM

0 Kudos
92

hi

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

Former Member

‎2009 May 21 10:42 AM

0 Kudos
92

hi,

I think its not possible to upload excel file using GUI_UPLOAD

regards