topic Re: Problem in reading excel file in Application Development and Automation Discussions

Problem in reading excel file

Former Member — Wed, 15 Apr 2009 13:30:28 GMT

Hi All,

I have a requirement to read records from an excel to an internal table.I am using FM

KCD_EXCEL_OLE_TO_INT_CONVERT to read excel.

My problem arises when the program comes across a cell which contains some formulae assigned to it.

The FM does not read the value in the specified cell and throws a runtime-error.

Please help me out .

Thanks and regards

Dinesh behera

Re: Problem in reading excel file

Former Member — Wed, 15 Apr 2009 13:46:22 GMT

use the following logic to get excel data into internal table.



 DATA: l_filename LIKE rlgrap-filename.
  DATA: l_mask(80) TYPE c.
  l_mask =  ',All Files (*.*),*.*.'.
  CALL FUNCTION 'WS_FILENAME_GET'
       EXPORTING
            def_path         = 'C:\'
            mask             = l_mask
            title            = 'Choose Input File'
       IMPORTING
            filename         = l_filename
       EXCEPTIONS
            inv_winsys       = 01
            no_batch         = 02
            selection_cancel = 03
            selection_error  = 04.
  IF sy-subrc = 0.
    p_file = l_filename.
  ENDIF.



DO.
    DESCRIBE TABLE p_table LINES vline.
    vline = vline * count.
    IF vline = vlimit.
      CLEAR: iexcel, iexcel[].
      begrow = vline + 1.
      endrow = begrow + 9998.
      CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
           EXPORTING
                filename                = p_file
                i_begin_col             = begcol
                i_begin_row             = begrow
                i_end_col               = endcol
                i_end_row               = endrow
           TABLES
                intern                  = iexcel
           EXCEPTIONS
                inconsistent_parameters = 1
                upload_ole              = 2
                OTHERS                  = 3.
      IF sy-subrc <> 0.
        RAISE error_uploading.
        EXIT.
      ELSE.
        SORT iexcel BY row col.
        count = count + 1.
        LOOP AT iexcel.
          MOVE : iexcel-col TO vf_index.
          ASSIGN COMPONENT vf_index OF STRUCTURE p_table TO <fs>.
          MOVE : iexcel-value TO <fs>.
          AT END OF row.
            APPEND p_table.
            CLEAR p_table.
          ENDAT.
        ENDLOOP.
      ENDIF.
      vlimit = sy-index * 9999.
    ELSE.
      EXIT.
    ENDIF.
  ENDDO.

Re: Problem in reading excel file

Former Member — Wed, 15 Apr 2009 13:47:37 GMT

Hi Try using the FM 'ALSM_EXCEL_TO_INTERNAL_TABLE'.

Re: Problem in reading excel file

Former Member — Wed, 15 Apr 2009 13:50:43 GMT

Hi,

It is reading the forumal cells also for me, but i have used the forumula is for 'SUM'

Regards

Krishna

Re: Problem in reading excel file

Former Member — Wed, 15 Apr 2009 13:52:29 GMT

Hi Dinesh,


CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
           EXPORTING
                filename                = p_file                       "File name
                i_begin_col             = begcol                   "Reading excel  from column
                i_begin_row             = begrow                 "Reading excel  from Row
                i_end_col               = endcol                    "Reading excel  to end column
                i_end_row               = endrow                  "Reading excel  to end row
           TABLES 
                intern                  = iexcel
           EXCEPTIONS
                inconsistent_parameters = 1
                upload_ole              = 2
                OTHERS                  = 3.

Regards,

Prabhudas

Re: Problem in reading excel file

Former Member — Wed, 15 Apr 2009 13:53:37 GMT

Try FM TEXT_CONVERT_XLS_TO_SAP

-RJ

Re: Problem in reading excel file

Former Member — Wed, 15 Apr 2009 13:56:34 GMT

Hi,

Try this one.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = v_file

i_begin_col = 1

i_begin_row = 2

i_end_col = 11

i_end_row = 10

TABLES

intern = it_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.

*looping the table

LOOP AT it_excel INTO wa_excel.

*coloum wise diffrensiate the internal table data

CASE wa_excel-col.

WHEN '0001'.

wa_input-tcode = wa_excel-value.

WHEN '0002'.

wa_input-vendor = wa_excel-value.

WHEN '0003'.

MOVE: wa_excel-value TO wa_input-inv_date.

WHEN '0004'.

MOVE: wa_excel-value TO wa_input-inv_no.

WHEN '0005'.

MOVE: wa_excel-value TO wa_input-post_date.

WHEN '0006'.

MOVE: wa_excel-value TO wa_input-comp_code.

WHEN '0007'.

MOVE: wa_excel-value TO wa_input-acc.

WHEN '0008'.

MOVE: wa_excel-value TO wa_input-cost_center.

WHEN '0009'.

MOVE: wa_excel-value TO wa_input-amt.

WHEN '0010'.

MOVE: wa_excel-value TO wa_input-int_ord.

ENDCASE.

*At end of every row append the excel data into table it_data

AT END OF row.

APPEND wa_input TO it_input.

CLEAR wa_input.

ENDAT.

ENDLOOP.