error in uploading excel sheet data into internal table

[Former Member]

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

[Former Member]

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

[Former Member]

hi

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

[Former Member]

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

[Former Member]

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.

[Former Member]

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.

[Former Member]

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.

[Former Member]

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

[Former Member]

Hi,

Check out this wiki written by me:

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

Thanks,

Krishna..