Skip to Content
author's profile photo Former Member
Former Member

Excel data to Internal table

I get an excel file. It might have variable number of rows everytime.

I need to upload all the matnr( it has only one column) into an internal table . If i use the FM ALSM_excel_to_internal_table , i need to specify the number of rows .

Then , how can i get the data from an excel file to internal table whose data will vary everytime ???

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Posted on Jun 08, 2006 at 04:03 PM

    Hi Tamanna,

    YOu don't need to specify the rows in the FM.

    YOu can Just pass the File path and get the data in the internal table.

    Regards,

    Ravi

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 08, 2006 at 04:05 PM

    Hi,

    U can use GUI_UPLOAD to get the rows from the Excel file..

    Regards,

    Tanveer

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi, when specifing the rows in the FM interface, just make it like 65500. This is pretty close to the limit of excel itself, so no worries right? Here is a sample program which will upload an excel sheet into a dynamic internal table. The columns are defined at runtime.

      
      report zrich_0002.
      
      type-pools: slis.
      
      field-symbols: <dyn_table> type standard table,
                     <dyn_wa>,
                     <dyn_field>.
      
      data: it_fldcat type lvc_t_fcat,
            wa_it_fldcat type lvc_s_fcat.
      
      type-pools : abap.
      
      data: new_table type ref to data,
            new_line  type ref to data.
      
      data: xcel type table of alsmex_tabline with header line.
      
      selection-screen begin of block b1 with frame title text .
      parameters: p_file type  rlgrap-filename default 'c:Test.csv'.
      parameters: p_flds type i.
      selection-screen end of block b1.
      
      start-of-selection.
      
      * Add X number of fields to the dynamic itab cataelog
        do p_flds times.
          clear wa_it_fldcat.
          wa_it_fldcat-fieldname = sy-index.
          wa_it_fldcat-datatype = 'C'.
          wa_it_fldcat-inttype = 'C'.
          wa_it_fldcat-intlen = 10.
          append wa_it_fldcat to it_fldcat .
        enddo.
        .
      * Create dynamic internal table and assign to FS
        call method cl_alv_table_create=>create_dynamic_table
                     exporting
                        it_fieldcatalog = it_fldcat
                     importing
                        ep_table        = new_table.
      
        assign new_table->* to <dyn_table>.
      
      * Create dynamic work area and assign to FS
        create data new_line like line of <dyn_table>.
        assign new_line->* to <dyn_wa>.
      
      * Upload the excel
        call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
             exporting
                  filename                = p_file
                  i_begin_col             = '1'
                  i_begin_row             = '1'
                  i_end_col               = '200'
                  i_end_row               = '65000'
             tables
                  intern                  = xcel
             exceptions
                  inconsistent_parameters = 1
                  upload_ole              = 2
                  others                  = 3.
      
      * Reformt to dynamic internal table
        loop at xcel.
          assign component xcel-col of structure <dyn_wa> to <dyn_field>.
          if sy-subrc = 0.
           <dyn_field> = xcel-value.
          endif.
      
          at end of row.
            append <dyn_wa> to <dyn_table>.
            clear <dyn_wa>.
          endat.
        endloop.
      
      * Write out data from table.
        loop at <dyn_table> into <dyn_wa>.
          do.
            assign component  sy-index  of structure <dyn_wa> to <dyn_field>.
            if sy-subrc <> 0.
              exit.
            endif.
            if sy-index = 1.
              write:/ <dyn_field>.
            else.
              write: <dyn_field>.
            endif.
          enddo.
        endloop.
      
      

      Regards,

      Rich Heilman

  • Posted on Jun 08, 2006 at 04:06 PM

    Hi Tamanna,

    Refer the link and code below and reward points if it helps.

    Try this function module.

    FILE_READ_AND_CONVERT_SAP_DATA

    pass 'XLS' to I_FILEFORMAT.
    pass 'PRS' to i_servertyp.

    REPORT zexceltabc.
    PARAMETERS: filename LIKE rlgrap-filename,
                begcol TYPE i DEFAULT 1 NO-DISPLAY,
                begrow TYPE i DEFAULT 1 NO-DISPLAY,
                endcol TYPE i DEFAULT 100 NO-DISPLAY,
                endrow TYPE i DEFAULT 32000 NO-DISPLAY.
    PARAMETERS: kzheader AS CHECKBOX.
    DATA: BEGIN OF intern OCCURS 0.
            INCLUDE STRUCTURE  alsmex_tabline.
    DATA: END OF intern.
    DATA: BEGIN OF intern1 OCCURS 0.
            INCLUDE STRUCTURE  alsmex_tabline.
    DATA: END OF intern1.
    DATA: BEGIN OF t_col OCCURS 0,
           col LIKE alsmex_tabline-col,
           size TYPE i.
    DATA: END OF t_col.
    DATA: zwlen TYPE i,
          zwlines TYPE i.
    DATA: BEGIN OF fieldnames OCCURS 3,
            title(60),
            table(6),
            field(10),
            kz(1),
          END OF fieldnames.
    DATA: BEGIN OF data_tab OCCURS 0,
           value_0001(50),
           value_0002(50),
           value_0003(50),
           value_0004(50),
           value_0005(50),
           value_0006(50),
           value_0007(50),
           value_0008(50),
           value_0009(50),
           value_0010(50),
           value_0011(50),
           value_0012(50),
           value_0013(50),
           value_0014(50),
           value_0015(50),
           value_0016(50),
           value_0017(50),
           value_0018(50),
           value_0019(50),
           value_0020(50),
           value_0021(50),
           value_0022(50),
           value_0023(50),
           value_0024(50),
           value_0025(50),
           value_0026(50),
           value_0027(50),
           value_0028(50),
           value_0029(50),
           value_0030(50),
           value_0031(50),
           value_0032(50),
           value_0033(50),
           value_0034(50),
           value_0035(50),
           value_0036(50),
           value_0037(50),
           value_0038(50),
           value_0039(50),
           value_0040(50),
           value_0041(50),
           value_0042(50),
           value_0043(50),
           value_0044(50),
           value_0045(50),
           value_0046(50),
           value_0047(50),
           value_0048(50),
           value_0049(50),
           value_0050(50),
           value_0051(50),
           value_0052(50),
           value_0053(50),
           value_0054(50),
           value_0055(50),
           value_0056(50),
           value_0057(50),
           value_0058(50),
           value_0059(50),
           value_0060(50),
           value_0061(50),
           value_0062(50),
           value_0063(50),
           value_0064(50),
           value_0065(50),
           value_0066(50),
           value_0067(50),
           value_0068(50),
           value_0069(50),
           value_0070(50),
           value_0071(50),
           value_0072(50),
           value_0073(50),
           value_0074(50),
           value_0075(50),
           value_0076(50),
           value_0077(50),
           value_0078(50),
           value_0079(50),
           value_0080(50),
           value_0081(50),
           value_0082(50),
           value_0083(50),
           value_0084(50),
           value_0085(50),
           value_0086(50),
           value_0087(50),
           value_0088(50),
           value_0089(50),
           value_0090(50),
           value_0091(50),
           value_0092(50),
           value_0093(50),
           value_0094(50),
           value_0095(50),
           value_0096(50),
           value_0097(50),
           value_0098(50),
           value_0099(50),
           value_0100(50).
    DATA: END OF data_tab.
    DATA: tind(4) TYPE n.
    DATA: zwfeld(19).
    FIELD-SYMBOLS: <fs1>.
     
    AT SELECTION-SCREEN ON VALUE-REQUEST FOR filename.
      CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
           EXPORTING
                mask      = '*.xls'
                static    = 'X'
           CHANGING
                file_name = filename.
     
     
    START-OF-SELECTION.
     
     
      CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
           EXPORTING
                filename                = filename
                i_begin_col             = begcol
                i_begin_row             = begrow
                i_end_col               = endcol
                i_end_row               = endrow
           TABLES
                intern                  = intern
           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.
     
    END-OF-SELECTION.
    * max. Feldlänge je Spalte ermitteln...
      LOOP AT intern.
        intern1 = intern.
        CLEAR intern1-row.
        APPEND intern1.
      ENDLOOP.
      SORT intern1 BY col.
      LOOP AT intern1.
        AT NEW col.
          t_col-col = intern1-col.
          APPEND t_col.
        ENDAT.
        zwlen = strlen( intern1-value ).
        READ TABLE t_col WITH KEY col = intern1-col.
        IF sy-subrc EQ 0.
          IF zwlen > t_col-size.
            t_col-size = zwlen.
            MODIFY t_col INDEX sy-tabix.
          ENDIF.
        ENDIF.
      ENDLOOP.
    * max. Spaltenanzahl ermitteln.
      DESCRIBE TABLE t_col LINES zwlines.
    * wenn die 1. Zeile die Spaltenüberschriften enthält...
      SORT intern BY row col.
      IF kzheader = 'X'.
        LOOP AT intern.
          fieldnames-title = intern-value.
          APPEND fieldnames.
          AT END OF row.                  " Ende der 1. Zeile
            EXIT.
          ENDAT.
        ENDLOOP.
      ELSE.
        DO zwlines TIMES.
          WRITE sy-index TO fieldnames-title.
          APPEND fieldnames.
        ENDDO.
      ENDIF.
    * Data_tab füllen ...
      SORT intern BY row col.
      LOOP AT intern.
        IF kzheader = 'X'
        AND intern-row = 1.
          CONTINUE.
        ENDIF.
        tind = intern-col.
        CONCATENATE 'DATA_TAB-VALUE_' tind INTO zwfeld.
        ASSIGN (zwfeld) TO <fs1>.
        <fs1> = intern-value.
        AT END OF row.
          APPEND data_tab.
          CLEAR data_tab.
        ENDAT.
      ENDLOOP.
     
      CALL FUNCTION 'DISPLAY_BASIC_LIST'
           EXPORTING
                file_name     = filename
           TABLES
                data_tab      = data_tab
                fieldname_tab = fieldnames.

    Message was edited by: Amit Mishra

    Message was edited by: Amit Mishra

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 08, 2006 at 04:21 PM
    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.