Read the Excel file from Presentation server Dynamically

[Former Member]

I have been given 25 Fields Column Headings (Fields) .

An excel file which contains any number of fields ( 2 to 25 not fixed, not in same sequence always) from Specified 25 Field names.

Could you let know How can read this excel file, so that I can fill the inetrnal table with specified format.

Thanks

[colin_cheong]

Any reason why you don't fixed the column info?

It may helps in the easier processing.

[Former Member]

Hi Colin,

IF fix the column info , It is very easy for me.

My requirement is Not fixed clumns, because Business people require this functionality

[rahulkavuri]

Hi,

You need to use the concept of dynamic internal tables in this scenario. Its complicated since some fields are left out in the excel file that you are trying to upload. I am dividing the code into calls and actual form routines and this works for me.

PERFORM read_input_data USING p_fname
                                    p_datarow
                                    c_endrow
                           CHANGING tbl_excel.

      IF tbl_excel[] IS INITIAL.
        MESSAGE e901 WITH text-008.
      ENDIF.

**Build Field Catalog
      PERFORM build_field_catalog CHANGING tbl_fieldcat tbl_original.

**Create Dynamic Internal Table <fs_wa1> in the table structure format
      PERFORM create_dynamic_itab USING tbl_fieldcat
                               CHANGING tbl_dynamic.

*Assign the Dynamic internal table reference to a Field Symbol and
*create a Work Area of its Line type.
      IF sy-subrc EQ 0.
        ASSIGN tbl_dynamic->* TO <fs_table1>.
        CREATE DATA wa_dynamic LIKE LINE OF <fs_table1>.
        ASSIGN wa_dynamic->*  TO <fs_wa1>.
      ELSE.
        MESSAGE e902 WITH text-002 p_tabnam.
      ENDIF.

**Create Dynamic Internal Table <fs_wa2> in the excel input file format
      PERFORM create_dynamic_itab USING tbl_original
                               CHANGING tbl_dynamic.

*Assign the Dynamic internal table reference to a Field Symbol and
*create a Work Area of its Line type.
      IF sy-subrc EQ 0.
        ASSIGN tbl_dynamic->* TO <fs_table2>.
        CREATE DATA wa_dynamic LIKE LINE OF <fs_table2>.
        ASSIGN wa_dynamic->*  TO <fs_wa2>.
      ELSE.
        MESSAGE e902 WITH text-002 p_tabnam.
      ENDIF.

**Fill the Dynamic Internal Table
      PERFORM fill_dynamic_itab   USING tbl_fieldcat.

DATA change_dyntab  TYPE REF TO zcl_ex_ko_dyn_file_upload.

  CALL FUNCTION 'ENQUEUE_E_TABLE'
    EXPORTING
      tabname        = p_tabnam
    EXCEPTIONS
      foreign_lock   = 1
      system_failure = 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.
  ELSE.
    g_tablock = 'X'.
  ENDIF.

  CREATE OBJECT change_dyntab.

  MODIFY (p_table) FROM TABLE <fs_table2>.
  IF sy-subrc = 0.
    MESSAGE s902 WITH p_table text-011.
  ENDIF.

**Dequeue Function Module
  IF g_tablock  = 'X'.
    CALL FUNCTION 'DEQUEUE_E_TABLE'
     EXPORTING
       mode_rstable       = 'E'
       tabname            = p_tabnam
*       VARKEY             =
       x_tabname          = ' '
       x_varkey           = ' '
       _scope             = '3'
       _synchron          = ' '
       _collect           = ' '
              .
  ENDIF.

*&---------------------------------------------------------------------*
*&      Form  READ_INPUT_DATA
*&---------------------------------------------------------------------*
  FORM read_input_data USING  value(l_fname) TYPE any
                              l_start TYPE any
                              l_end   TYPE any
                    CHANGING  lt_excel TYPE STANDARD TABLE.

    CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
      EXPORTING
        percentage = 0
        text       = text-009.

    CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
      EXPORTING
        filename                = l_fname
        i_begin_col             = c_begin_col
        i_begin_row             = l_start
        i_end_col               = c_end_col
        i_end_row               = l_end
      TABLES
        intern                  = lt_excel[]
      EXCEPTIONS
        inconsistent_parameters = 1
        upload_ole              = 2.
    IF sy-subrc NE 0.
      MESSAGE e901 WITH text-020.
    ENDIF.

  ENDFORM.  " READ_INPUT_DATA
*&---------------------------------------------------------------------*
*&      Form  BUILD_FIELD_CATALOG
*&---------------------------------------------------------------------*
  FORM build_field_catalog CHANGING lt_fieldcat TYPE lvc_t_fcat
                                    lt_original TYPE lvc_t_fcat.

    DATA: la_header LIKE LINE OF tbl_header,
          la_fcat   TYPE lvc_s_fcat,
          l_tabix   TYPE sy-tabix.

    CALL FUNCTION 'LVC_FIELDCATALOG_MERGE'
      EXPORTING
        i_structure_name       = p_tabnam
      CHANGING
        ct_fieldcat            = lt_original[]
      EXCEPTIONS
        inconsistent_interface = 1
        program_error          = 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.
    ELSE.

**Organize lt_original based on the excel header row TBL_HEADER
**so that both data records and header contain fields in same position

      CALL FUNCTION 'LVC_FIELDCAT_COMPLETE'
        CHANGING
          ct_fieldcat = lt_original[].

      LOOP AT tbl_header INTO la_header.
        l_tabix = sy-tabix.
        READ TABLE lt_original INTO la_fcat
                               WITH KEY fieldname = la_header-value.
        CHECK sy-subrc = 0.
        la_fcat-col_pos = l_tabix.
        la_fcat-tabname = p_tabnam.
        APPEND la_fcat TO lt_fieldcat.
      ENDLOOP.

      IF lt_fieldcat[] IS INITIAL.
        MESSAGE e902 WITH text-022.
      ENDIF.

    ENDIF.

  ENDFORM.                    " BUILD_FIELD_CATALOG
*&---------------------------------------------------------------------*
*&      Form  CREATE_DYNAMIC_ITAB
*&---------------------------------------------------------------------*
  FORM create_dynamic_itab USING  value(lt_fieldcat)  TYPE lvc_t_fcat
                        CHANGING lt_dynamic   TYPE REF TO data.

    CLEAR lt_dynamic.

    CALL METHOD cl_alv_table_create=>create_dynamic_table
      EXPORTING
        it_fieldcatalog = lt_fieldcat
      IMPORTING
        ep_table        = lt_dynamic.

  ENDFORM.                    " CREATE_DYNAMIC_ITAB
*&---------------------------------------------------------------------*
*&      Form  FILL_DYNAMIC_ITAB
*&---------------------------------------------------------------------*
  FORM fill_dynamic_itab USING value(lt_fieldcat)  TYPE lvc_t_fcat.

    DATA: la_fieldcat TYPE lvc_s_fcat,
          l_keyfld    TYPE fieldname,
          v_value     TYPE string,
          l_tabix TYPE char1,
          l_temp  TYPE fieldname.

    DATA: l_key1  TYPE string,
          l_key2  TYPE string,
          l_key3  TYPE string,
          l_key4  TYPE string,
          l_key5  TYPE string,
          l_key6  TYPE string,
          l_key7  TYPE string,
          l_key8  TYPE string,
          l_key9  TYPE string.

    DATA  la_dynamic  TYPE REF TO data.

    FIELD-SYMBOLS: <l_fs1>  TYPE ANY,
                   <l_fst>  TYPE ANY TABLE.

    LOOP AT tbl_excel ASSIGNING <fs_xls_data>.

      READ TABLE lt_fieldcat INTO la_fieldcat
                             WITH KEY col_pos = <fs_xls_data>-col.
      CHECK sy-subrc EQ 0.

      CONCATENATE c_fs_wa la_fieldcat-fieldname INTO v_value.

      ASSIGN (v_value) TO <fs_current_field>.

      IF <fs_current_field> IS ASSIGNED.
        TRY .
            CASE la_fieldcat-datatype.
              WHEN 'DATS'.
                PERFORM convert_date_ext_to_int.
              WHEN 'TIMS'.
                PERFORM convert_time_ext_to_int.
              WHEN  'DEC' OR 'CURR' OR 'INT1' OR 'INT2' OR 'INT4' OR 'QUAN'.
                REPLACE ALL OCCURRENCES OF ','
                IN <fs_xls_data>-value WITH space.
                IF sy-subrc = 0.
                  CONDENSE  <fs_xls_data>-value.
                  <fs_current_field>  = <fs_xls_data>-value.
                ENDIF.
              WHEN OTHERS.
                <fs_current_field>  = <fs_xls_data>-value.
            ENDCASE.
          CATCH cx_sy_conversion_no_number.
            MESSAGE e902 WITH text-012 la_fieldcat-fieldname.
        ENDTRY.
      ENDIF.

      AT END OF row.
**If field MANDT exists in the field catalog, assign the value
        v_value = '<FS_WA2>-MANDT'.
        ASSIGN (v_value) TO <fs_current_field>.
        IF <fs_current_field> IS ASSIGNED.
          <fs_current_field>  = sy-mandt.
        ENDIF.
        MOVE-CORRESPONDING <fs_wa1> TO <fs_wa2>.
        APPEND: <fs_wa1> TO <fs_table1>,
                <fs_wa2> TO <fs_table2>.
        CLEAR: <fs_wa1>,<fs_wa2>.
      ENDAT.

    ENDLOOP.

    IF sy-subrc = 4.
      MESSAGE e901 WITH text-004.
    ENDIF.

    SELECT fieldname FROM dd03l UP TO 9 ROWS INTO l_keyfld
                    WHERE tabname = p_tabnam
                      AND keyflag = 'X'.
      l_tabix = l_tabix + 1.
      UNASSIGN <l_fs1>.
      CONCATENATE 'l_key' l_tabix INTO l_temp.
      CONDENSE l_temp NO-GAPS.
      ASSIGN (l_temp) TO <l_fs1>.
      <l_fs1> = l_keyfld.
    ENDSELECT.

    CREATE DATA la_dynamic LIKE <fs_table2>.
    ASSIGN la_dynamic->*  TO <l_fst>.
    <l_fst> = <fs_table2>.

    DELETE ADJACENT DUPLICATES FROM <l_fst>
    COMPARING (l_key1) (l_key2) (l_key3) (l_key4) (l_key5) (l_key6)
              (l_key7) (l_key8) (l_key9).

    IF sy-subrc = 0.
      MESSAGE i902 WITH text-023 text-024.
    ELSE.
      g_uploaded = 'X'.
    ENDIF.

  ENDFORM.                    " FILL_DYNAMIC_ITAB

[Former Member]

Hi,

just as an idea (in pseudocode - haven't tested it, but should work this way). You can make it fully dynamic, if the column names in excel are the same as the names in the internal table, with assign (fieldname) to <fs>...

Kind Regards,

Dirk

*    call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
*    sort GT_EXCEL by ROW COL.
*    loop at GT_EXCEL into GS_EXCEL.
*      if gs_excel-row = 1 "Header!!!
*        gs_head-col  = gs_excel-col.
*        gs_head-name = gs_excel-value.
*        append gs_head to gt_head.
*      else.               "Data lines
*        read table gt_head into gs_head with key col = gs_excel-col.
*        case gs_excel-calue.
*        when 'FIELD1'.
*           GS_XLS_UPLOAD-field1 = gs_excel-value.
*        when 'FIELD2'.
*           GS_XLS_UPLOAD-field2 = gs_excel-value.
*           ...
*      endif
*      at end of ROW.
*        append GS_XLS_UPLOAD to GT_XLS_UPLOAD.   
*        clear GS_XLS_UPLOAD.
*      endat.
*    endloop.

[Former Member]

Hi,

This can be done through Dynamic Internal Table concept.

Just upload the excel using FM, in that first row must be field names.

read the first row and append into it_fc, and also get the count of fields.

create dynamic internal table using the method

CALL METHOD cl_alv_table_create=>create_dynamic_table

EXPORTING

it_fieldcatalog = it_fc

i_length_in_byte = 'X'

IMPORTING

ep_table = dy_table.

ASSIGN dy_table->* TO <dyn_table>.

using the filed count, append the data to dynamic internal table.

Then assign to field symbol and start processing using assign component.

Regards,

Arun.