topic Re: error in uploading excel sheet data into internal table in Application Development and Automation Discussions

error in uploading excel sheet data into internal table

Former Member — Mon, 01 Jun 2009 06:07:45 GMT

Dear all,

i am facing problem when uploading data from excel. i used KD_GET_FILENAME_ON_F4.i select the file and pass on to ALSM_EXCEL_INTO_INTERNAL_TABLE.and i get the ERROR....

Illegal type when transferring an internal table to a FORM. this is my code .

types : begin of ty_mm01,

matnr like rmmg1-matnr,

mbrsh like rmmg1-mbrsh,

mtart like rmmg1-mtart,

maktx like makt-maktx,

meins like mara-meins,

matkl like mara-matkl,

bismt like mara-bismt,

spart like mara-spart,

mtpos like mara-mtpos_mara,

end of ty_mm01.

data : tt_mm01 type standard table of ty_mm01,

wa_mm01 like TT_MM01.

data : t_bdcdata like standard table of bdcdata,

t_bdcmsgcoll like standard table of bdcmsgcoll.

constants: begcol TYPE i value 1 ,

begrow TYPE i value 1,

endcol TYPE i value 100,

endrow TYPE i value 32000.

selection-screen : begin of block bdc with frame.

parameter : tfile like rlgrap-filename obligatory.

selection-screen : end of block bdc.

at selection-screen on value-request for tfile.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

PROGRAM_NAME = 'ZMM_MAT_MAS_BASIC_DATA'

DYNPRO_NUMBER = '1000'

FIELD_NAME = 'TFILE'

STATIC = 'X'

MASK = ',*.xls,'

CHANGING

FILE_NAME = tfile

start-of-selection.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = tfile

I_BEGIN_COL = begcol

I_BEGIN_ROW = begrow

I_END_COL = endcol

I_END_ROW = endrow

TABLES

INTERN = tt_mm01

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.

Thanks in advance

Re: error in uploading excel sheet data into internal table

Former Member — Mon, 01 Jun 2009 06:41:36 GMT

Hi,

Check these FM : KCD_EXCEL_OLE_TO_INT_CONVERT

Report ZPSP_TEST.

data: bdc_DATA like bdcdata occurs 0 with header line,

mess_tab like bdcmsgcoll occurs 0 with header line.

DATA: BEGIN OF ITAB OCCURS 0 ,

tcnt TYPE i, "Table Counter &H0D

WERKS LIKE T001W-WERKS,

BNFPO LIKE EBAN-BNFPO,

MATNR LIKE MARA-MATNR,

MENGE LIKE EBAN-MENGE,

END OF ITAB.

start-of-selection.

PERFORM upload_data.

loop at itab.

perform bdc_dynpro using 'SAPMM06B' '0100'.

perform bdc_field using 'BDC_CURSOR'

'EBAN-BSART'.

perform bdc_field using 'BDC_OKCODE'

'/00'.

perform bdc_field using 'EBAN-BSART'

'NB'.

perform bdc_field using 'RM06B-LPEIN'

'T'.

perform bdc_field using 'EBAN-WERKS'

ITAB-WERKS.

perform bdc_dynpro using 'SAPMM06B' '0106'.

perform bdc_field using 'BDC_CURSOR'

'RM06B-EKGRP'.

perform bdc_field using 'BDC_OKCODE'

'/00'.

perform bdc_field using 'RM06B-BNFPO'

ITAB-BNFPO.

perform bdc_dynpro using 'SAPMM06B' '0106'.

perform bdc_field using 'BDC_CURSOR'

'EBAN-MENGE(01)'.

perform bdc_field using 'BDC_OKCODE'

'/00'.

perform bdc_field using 'EBAN-MATNR(01)'

ITAB-MATNR.

perform bdc_field using 'EBAN-MENGE(01)'

ITAB-MENGE.

perform bdc_dynpro using 'SAPMM06B' '0102'.

perform bdc_field using 'BDC_CURSOR'

'EBAN-MENGE'.

perform bdc_field using 'BDC_OKCODE'

'/00'.

*perform bdc_field using 'RM06B-EEIND'

record-EEIND_010.

*perform bdc_field using 'RM06B-LPEIN'

record-LPEIN_011.

*perform bdc_field using 'EBAN-EKGRP'

record-EKGRP_012.

*perform bdc_field using 'EBAN-BADAT'

record-BADAT_013.

*perform bdc_field using 'EBAN-FRGDT'

record-FRGDT_014.

*perform bdc_field using 'EBAN-PREIS'

record-PREIS_015.

*perform bdc_field using 'EBAN-WAERS'

record-WAERS_016.

*perform bdc_field using 'EBAN-PEINH'

record-PEINH_017.

*perform bdc_field using 'EBAN-REPOS'

record-REPOS_018.

perform bdc_dynpro using 'SAPMM06B' '0106'.

perform bdc_field using 'BDC_CURSOR'

'EBAN-MENGE(02)'.

perform bdc_field using 'BDC_OKCODE'

'/00'.

*perform bdc_field using 'RM06B-BNFPO'

record-BNFPO_019.

*perform bdc_field using 'EBAN-MATNR(02)'

record-MATNR_02_020.

*perform bdc_field using 'EBAN-MENGE(02)'

record-MENGE_02_021.

perform bdc_dynpro using 'SAPMM06B' '0102'.

perform bdc_field using 'BDC_CURSOR'

'EBAN-MENGE'.

perform bdc_field using 'BDC_OKCODE'

'/00'.

*perform bdc_field using 'EBAN-MENGE'

ITAB-MENGE_023.

*perform bdc_field using 'RM06B-EEIND'

record-EEIND_024.

*perform bdc_field using 'RM06B-LPEIN'

record-LPEIN_025.

*perform bdc_field using 'EBAN-EKGRP'

record-EKGRP_026.

*perform bdc_field using 'EBAN-BADAT'

record-BADAT_027.

*perform bdc_field using 'EBAN-FRGDT'

record-FRGDT_028.

*perform bdc_field using 'EBAN-PREIS'

record-PREIS_029.

*perform bdc_field using 'EBAN-WAERS'

record-WAERS_030.

*perform bdc_field using 'EBAN-PEINH'

record-PEINH_031.

*perform bdc_field using 'EBAN-REPOS'

record-REPOS_032.

perform bdc_field using 'EBAN-TXZ01'

'BEARING 2"X2"'.

perform bdc_field using 'EBAN-MENGE'

'65'.

perform bdc_field using 'RM06B-EEIND'

'2005/01/03'.

perform bdc_field using 'RM06B-LPEIN'

'D'.

perform bdc_field using 'EBAN-EKGRP'

'M11'.

perform bdc_field using 'EBAN-BADAT'

'2005/01/03'.

perform bdc_field using 'EBAN-FRGDT'

'2005/01/03'.

perform bdc_field using 'EBAN-PREIS'

' 1,120.00'.

perform bdc_field using 'EBAN-WAERS'

'EUR'.

perform bdc_field using 'EBAN-PEINH'

'1'.

perform bdc_field using 'EBAN-REPOS'

'X'.

perform bdc_dynpro using 'SAPMM06B' '0102'.

perform bdc_field using 'BDC_CURSOR'

'EBAN-MENGE'.

perform bdc_field using 'BDC_OKCODE'

'/00'.

perform bdc_field using 'EBAN-TXZ01'

'DRILLING PIPE 10"'.

perform bdc_field using 'EBAN-MENGE'

'75'.

perform bdc_field using 'RM06B-EEIND'

'2005/01/03'.

perform bdc_field using 'RM06B-LPEIN'

'D'.

perform bdc_field using 'EBAN-EKGRP'

'M11'.

perform bdc_field using 'EBAN-BADAT'

'2005/01/03'.

perform bdc_field using 'EBAN-FRGDT'

'2005/01/03'.

perform bdc_field using 'EBAN-PREIS'

' 0.53'.

perform bdc_field using 'EBAN-WAERS'

'EUR'.

perform bdc_field using 'EBAN-PEINH'

'1'.

perform bdc_field using 'EBAN-REPOS'

'X'.

perform bdc_dynpro using 'SAPMM06B' '0106'.

perform bdc_field using 'BDC_CURSOR'

'RM06B-BNFPO'

perform bdc_field using 'BDC_OKCODE'

'&H3DBU'.

*perform bdc_field using 'RM06B-BNFPO'

CALL TRANSACTION 'ME51' USING BDC_DATA MODE 'A'.

endLOOP.

FORM upload_data.

*local variable declaration

DATA : lv_index TYPE i,

l_count TYPE i.

*local constants declaration

CONSTANTS:

lc_start_col TYPE i VALUE '1' ,

lc_start_row TYPE i VALUE '2' ,

lc_end_col TYPE i VALUE '256' ,

lc_end_row TYPE i VALUE '65536'.

*local field symbol declaration

FIELD-SYMBOLS : <lf_s>.

*loacal internal table declaration

DATA : li_intern TYPE kcde_cells OCCURS 0 WITH HEADER LINE.

*refresh internal table for each loop

CLEAR: li_intern,

l_count .

REFRESH li_intern.

to upload the data in excel on the presentation server this function
module converts the data from excel file into an internal table
containing row no col no and value

CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'

EXPORTING

filename &H3D 'Give file location here'

i_begin_col &H3D lc_start_col

i_begin_row &H3D lc_start_row

i_end_col &H3D lc_end_col

i_end_row &H3D lc_end_row

TABLES

intern &H3D li_intern

EXCEPTIONS

inconsistent_parameters &H3D 1

upload_ole &H3D 2.

checking for data in internal table

CHECK NOT li_intern[] IS INITIAL.

sorting internal table

SORT li_intern BY row col.

collecting data into an internal table

LOOP AT li_intern.

MOVE: li_intern-col TO lv_index.

lv_index &H3D lv_index + 1.

ASSIGN COMPONENT lv_index OF STRUCTURE itab TO <lf_s>.

MOVE : li_intern-value TO <lf_s>.

AT END OF row.

l_count &H3D l_count + 1.

itab &H3D l_count.

APPEND itab.

ENDAT. " at end of row

ENDLOOP. " loop at li_intern

Reg,

Siva

Edited by: Siva Prasad on Jun 1, 2009 8:41 AM

Edited by: Siva Prasad on Jun 1, 2009 4:25 PM

Re: error in uploading excel sheet data into internal table

Former Member — Mon, 01 Jun 2009 06:48:39 GMT

try to use this fm 'TEXT_CONVERT_XLS_TO_SAP' for uploading excel data to SAP internal table

An Example for ur reference

REPORT zgui_upload.

TYPE-POOLS truxs.

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

field_name = 'P_FILE'

IMPORTING

file_name = p_file.

START-OF-SELECTION.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

i_tab_raw_data = it_type

i_filename = p_file

TABLES

i_tab_converted_data = ITAB[]

EXCEPTIONS

conversion_failed = 1

OTHERS = 2.

Regards

Re: error in uploading excel sheet data into internal table

Former Member — Mon, 01 Jun 2009 10:36:35 GMT

thanks for your reply,

now i dont get any short dump.............. but my internal table is not filled with data.

and sy-subrc = 1 after calling FM TEXT_CONVERT_XLS_TO_SAP.

help me in this regard

Thanks in advance

Re: error in uploading excel sheet data into internal table

Former Member — Mon, 01 Jun 2009 11:28:58 GMT

Hi,

If you have header information in the excel file, the FM should be written this way

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

i_field_seperator = c_x

i_line_header = c_x

i_tab_raw_data = fe_raw

i_filename = fe_file

TABLES

i_tab_converted_data = i_valuemapld

EXCEPTIONS

conversion_failed = 1

OTHERS = 2.

Re: error in uploading excel sheet data into internal table

Former Member — Mon, 01 Jun 2009 11:35:07 GMT

Hi Naren,

Please check if the format of your excel sheet is in 2003 i.e .xls(you might have used .xlsx which is not supported by the function module) or not.And secondly make a provision in your program for exception handling by uncommenting the exceptions part in the fm used.

Regards,

Ram.

Re: error in uploading excel sheet data into internal table

Former Member — Tue, 02 Jun 2009 07:41:21 GMT

use this FM

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.

Re: error in uploading excel sheet data into internal table

Former Member — Wed, 03 Jun 2009 03:12:35 GMT

Hi,

Use the following Code



DATA: tb_file_data TYPE TABLE OF text4096.

  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
      i_line_header        = ''
      i_tab_raw_data       = tb_file_data
      i_filename           = <file path>
    TABLES
      i_tab_converted_data = <final internal table>
    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.

Regards,

Manish

Re: error in uploading excel sheet data into internal table

Former Member — Wed, 03 Jun 2009 08:31:35 GMT

Hi,

Check out this wiki written by me:

https://www.sdn.sap.com/irj/scn/wiki?path=/display/abap/prog%252bto%252bupload%252bfrom%252bxl%252bto%252bddic%252btable

Thanks,

Krishna..