topic Re: Function ALSM_EXCEL_TO_INTERNAL_TABLE in Application Development and Automation Discussions

Function ALSM_EXCEL_TO_INTERNAL_TABLE

Former Member — Fri, 10 Oct 2008 09:05:40 GMT

Hy guys , im using this function in order to read a excel file , however , I m Getting exception upload_ole = 2 , why is that ? , here some code :

FORM match_archivo USING p_archivo CHANGING archivo.

DATA:lt_files TYPE filetable,

l_file TYPE file_table,

l_title TYPE string,

l_subrc TYPE i,

l_usr_act TYPE i,

l_def_file TYPE string.

l_def_file = p_archivo.

CLEAR lt_files. REFRESH lt_files.

CALL METHOD cl_gui_frontend_services=>file_open_dialog

EXPORTING

window_title = ''

default_extension = '*.xls'

default_filename = '*.xls'

initial_directory = 'c:\'

multiselection = ' '

CHANGING

file_table = lt_files

rc = l_subrc

EXCEPTIONS

file_open_dialog_failed = 1

cntl_error = 2

error_no_gui = 3

OTHERS = 5

IF sy-subrc EQ 0.

LOOP AT lt_files ASSIGNING <fs>.

fic_inl = <fs>.

EXIT.

ENDLOOP.

ELSE.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

ENDFORM.

----

FORM read_fi_in *
*

----

........ *

----

FORM read_fi_in.

CLEAR: fic_inl1, var.

MOVE fic_inl TO fic_inl1 .

REPLACE '.' WITH '~' INTO fic_inl1.

IF sy-subrc EQ 0.

SET LOCALE LANGUAGE sy-langu.

TRANSLATE fic_inl1 TO UPPER CASE.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = fic_inl1

i_begin_col = '1'

i_begin_row = '1'

i_end_col = '256'

i_end_row = '65536'

TABLES

intern = excel

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

IF sy-subrc = 0.

LOOP AT excel.

ENDLOOP.

ENDIF.

ENDFORM.

thank you very much for your help .

Re: Function ALSM_EXCEL_TO_INTERNAL_TABLE

Former Member — Fri, 10 Oct 2008 09:10:34 GMT

hi this is how i used this function module...

take help from this...

call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'

exporting

filename = l_file_name

i_begin_col = 1

i_begin_row = 1

i_end_col = 255

i_end_row = 65536

tables

intern = lt_excel

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,

kushagra sharma

Re: Function ALSM_EXCEL_TO_INTERNAL_TABLE

Former Member — Fri, 10 Oct 2008 09:16:31 GMT

Hi Ali Sanchez,

First close your excel seheet before excuting your program.

See the following code.

FORM get_filddata .

  file = 'C:\Documents and Settings\maheswari.chegu\Desktop\Materialtype.XLS'.

  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = file
      i_begin_col             = '1'
      i_begin_row             = '1'
      i_end_col               = '5'
      i_end_row               = '6000'
    TABLES
      intern                  = xcel
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.

  LOOP AT xcel.

    CASE xcel-col.
      WHEN '0001'.
        itab-fld1 = xcel-value.
      WHEN '0002'.
        itab-fld2 = xcel-value.
      WHEN '0003'.
        itab-fld3 = xcel-value.
      WHEN '0004'.
        itab-fld4 = xcel-value.
      WHEN '0005'.
        itab-fld5 = xcel-value.
    ENDCASE.

    AT END OF row.
      APPEND itab.
      CLEAR itab.
    ENDAT.

  ENDLOOP.

ENDFORM.                    " GET_FILDDATA

Regards,

Mahi

Re: Function ALSM_EXCEL_TO_INTERNAL_TABLE

Former Member — Fri, 10 Oct 2008 09:17:34 GMT

Hi Ali,

Try using the below code for your requirement.

I hope this will solve your problem....


FORM p_upload_data .
  DATA : gd_scol   TYPE i VALUE '1',
          gd_srow   TYPE i VALUE '2',
          gd_ecol   TYPE i VALUE '5',
          gd_erow   TYPE i VALUE '60000'.

  DATA: it_tab TYPE filetable,
        gd_subrc TYPE i.




  DATA : lt_intern TYPE  kcde_cells OCCURS 0 WITH HEADER LINE.

  DATA : ld_index TYPE i.
  FIELD-SYMBOLS : <fs> .

  CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'
    EXPORTING
      filename                = p_path
      i_begin_col             = gd_scol
      i_begin_row             = gd_srow
      i_end_col               = gd_ecol
      i_end_row               = gd_erow
    TABLES
      intern                  = lt_intern
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.
  IF sy-subrc <> 0.
    FORMAT COLOR COL_BACKGROUND INTENSIFIED.
    WRITE:/ 'Error Uploading file'.
    EXIT.
  ENDIF.

  IF lt_intern[] IS INITIAL.
    FORMAT COLOR COL_BACKGROUND INTENSIFIED.
    WRITE:/ 'No Data Uploaded'.
    EXIT.
  ELSE.
    SORT lt_intern BY row col.
    LOOP AT lt_intern.
      MOVE lt_intern-col TO ld_index.
      ASSIGN COMPONENT ld_index OF STRUCTURE it_final TO <fs>.
      MOVE lt_intern-value TO <fs>.
      AT END OF row.
        APPEND it_final .
        CLEAR  it_final.
      ENDAT.
    ENDLOOP.

  ENDIF.
ENDFORM.                    " P_UPLOAD_DATA

Thanks,

Chidanand

Re: Function ALSM_EXCEL_TO_INTERNAL_TABLE

Former Member — Fri, 10 Oct 2008 09:18:00 GMT

Hi Ali Sanchez,

First Close your excel sheet before executing your code.

Example code to upload excel file.

FORM get_filddata .

  file = 'C:\Documents and Settings\maheswari.chegu\Desktop\Materialtype.XLS'.

  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = file
      i_begin_col             = '1'
      i_begin_row             = '1'
      i_end_col               = '5'
      i_end_row               = '6000'
    TABLES
      intern                  = xcel
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.

  LOOP AT xcel.

    CASE xcel-col.
      WHEN '0001'.
        itab-fld1 = xcel-value.
      WHEN '0002'.
        itab-fld2 = xcel-value.
      WHEN '0003'.
        itab-fld3 = xcel-value.
      WHEN '0004'.
        itab-fld4 = xcel-value.
      WHEN '0005'.
        itab-fld5 = xcel-value.
    ENDCASE.

    AT END OF row.
      APPEND itab.
      CLEAR itab.
    ENDAT.

  ENDLOOP.

ENDFORM.                    " GET_FILDDATA

Regards,

Mahi

Re: Function ALSM_EXCEL_TO_INTERNAL_TABLE

Former Member — Fri, 10 Oct 2008 09:19:03 GMT

Hi,

The problem is when uploading data. Properly upload data from excel.

Regards,

Dhanunjaya Reddy

Re: Function ALSM_EXCEL_TO_INTERNAL_TABLE

Former Member — Fri, 10 Oct 2008 09:20:16 GMT

Hi,

Try this

Example ur filename is a.xls

Here's the code , very simple

PARAMETER:pr_file LIKE rlgrap-filename OBLIGATORY.

DATA: rawdata(4096) TYPE c OCCURS 0.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR pr_file.

CALL FUNCTION 'WS_FILENAME_GET'

EXPORTING

def_filename = pr_file

mask = ',*.xls.'

mode = 'O'

title = 'Upload File'(i03)

IMPORTING

filename = pr_file

EXCEPTIONS

inv_winsys = 1

no_batch = 2

selection_cancel = 3

selection_error = 4

OTHERS = 5.

START-OF-SELECTION.

WRITE pr_file TO w_filenm.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_FIELD_SEPERATOR =

I_LINE_HEADER =

i_tab_raw_data = rawdata

i_filename = w_filenm

TABLES

i_tab_converted_data = itab1

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.

Re: Function ALSM_EXCEL_TO_INTERNAL_TABLE

Former Member — Fri, 10 Oct 2008 11:10:44 GMT

I think, the problem is related with microsoft office 2007 excel file, does anyone try this with that office version. ??? . I created the file in that version and save it as Microsoft Office Excel 97-2003 Worksheet