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

Fetching data from an excel into an internal table dynamically.

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    Posted on Nov 08, 2012 at 08:51 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 08, 2012 at 08:36 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

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.