Fetching data from an excel into an internal table dynamically.

[Former Member]

Hi All,

We have a requirement wherein we need to fetch data from an excel into an internal table, but the problem here is the structure of excel is not fixed. So we want to code in a way which does not depend on excel structure. Please advice.

[Venkat_Sesha]

If you can have the Field Names in Excel that is simple to do that... I know that you are aware of how to do this.

Lets assume client/user is not maintaining that also in the excel sheet.

Then we can do like this. Assuming the Columns for each structure is Unique that means

for Excel 1 which is having 10 columns and for excel 2 it is having 20 columns and so on....

Any of the excel structures have same number of columns.

1. fill the excel data into an ITAB with String and seperated by SPACE or #

2. Loop ITAB and SPLIT the WA into Table ITAB_# and Identify the no. of columns.

2. After that use the below mentioned Methods to fill the String data into WA

    CL_ABAP_CONTAINER_UTILITIES=>FILL_CONTAINER_C and READ_CONTAINER_C

    Read is to read the Data from String and fills the data into the WA.---> use This.

Lets assume Excels also have same number of columns.. Still you want to fill the data dynamically.

then Use the CASE and ENDCASE to compare each and every individual field of all the possible structures to do this. I would say this would be very lengthy for you. Instead it is easy for you to maintain one single row in the Excel naming the FIELD name.

Hope this helps

[Former Member]

Dear Venkat,

Our requirement is like we have only one excel with unknown number of columns. Could you please  tell me then how  to fill excel data into internal table as string as you have mentioned in your reply. We came across some FMs , OLE method etc..to fill data  but we didn't get exact solution, if you know any such way please share. Also note that it should not have performance issues.

Thanks.

[Venkat_Sesha]

From the below solution what Jacob is telling is when we have Column name as MATNR etc...we can do like that. Since we dont have the column name here we go with structure lenght..

We dont know what excel is given for input.. But you know how many structures are there right

1. So first step is use ALSM / GUI_UPLOAD FM to fill the data of an excel into a ITAB.

2. Next step is to identify the Structures we have. lets say in the excel there are 10 columns with the data and we have a structure with 10 fields. so we link up like this

3. Identify how many columns of data you got into ITAB by using a SPLIT into Table ITAB

check the describe table and lines = 10 use structure one to fill data into actual WA as follows

if lines = 15 then use another Structure to fill data...

Read the excel data into a string.

now call the method as mentioned below

        CALL METHOD cl_abap_container_utilities=>read_container_c

          EXPORTING

            im_container           = gv_string

          IMPORTING

            ex_value               = wa_mara

          EXCEPTIONS

            illegal_parameter_type = 1

            OTHERS                 = 2.

Hope this helps

[JJosh]

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               = '5000'

       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.