Excel data to Internal table

[Former Member]

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 ???

[Former Member]

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

[Former Member]

Hi,

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

Regards,

Tanveer

[Former Member]

Hi Tammana,

Save the Excel file as Tab delimted text file and use GUI_UPLOAD

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

FILENAME = V_FILENAME

FILETYPE = 'ASC'

HAS_FIELD_SEPARATOR = '|'

TABLES

DATA_TAB = ITAB

EXCEPTIONS

FILE_OPEN_ERROR = 1

FILE_READ_ERROR = 2

NO_BATCH = 3

GUI_REFUSE_FILETRANSFER = 4

INVALID_TYPE = 5

NO_AUTHORITY = 6

UNKNOWN_ERROR = 7

BAD_DATA_FORMAT = 8

HEADER_NOT_ALLOWED = 9

SEPARATOR_NOT_ALLOWED = 10

HEADER_TOO_LONG = 11

UNKNOWN_DP_ERROR = 12

ACCESS_DENIED = 13

DP_OUT_OF_MEMORY = 14

DISK_FULL = 15

DP_TIMEOUT = 16

OTHERS = 17.

Regards,

Santosh

[RichHeilman]

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

[Former Member]

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

[Former Member]

Hi,

check these...

http://www.sapdevelopment.co.uk/file/file_upexcelalt1.htm

http://www.sapdevelopment.co.uk/file/file_upexcelalt2.htm

Regards

vijay