FM to retrieve data from excel into an internal table of any type

I have developed an FM ‘Y_EXCEL_TO_ITAB ‘, which takes an excel file name and a structure/table name as inputs. And parses the excel content into an internal table with the same structure given as input.

Background:

SAP has provided us the means to create data at run-time. We can use various RTTS (Run Time Type Services), Data references, Field-Symbols to achieve this.

So, we utilize this to develop a general purpose FM, which can read any excel file and put it in an internal table of the specified structure.

Applications:

• Having a general/common FM to retrieve data from an excel file into any internal table following the specified structure.
• With just the path of the file and the target structure, this FM can be re-used in any place where the data from excel needs to be dumped into an internal table.

Potential Enhancements:

• The functionality can be extended to actually update the excel content to DB tables (with just a few lines of code. Example provided.)
• Clearer error messages with message variables
• More validations and messages

Pre-requisites:

• The format of data in the excel should match the format settings of the SAP user executing the FM. e.g., if the date format is dd.mm.yyyy in SU01 for the user, the excel should have date values in the same format.
• If just part of the fields of a given structure are available in the excel, make sure to label the columns with the correct field names
• If no header is specified with the field names, make sure the ordering of fields in the excel are the same as the ordering of the fields in the table/structure definition

Others’ code used:

• The code used in conversion exit FM ‘Y_CONVERSION_EXIT_DECS_INPUT’, is the exact same as the one in the below URL, created by Matthew Billingham (Thanks)
• https://wiki.scn.sap.com/wiki/display/ABAP/Conversion+from+external+to+internal+number+format

The FM ‘Y_EXCEL_TO_ITAB’

Import/Export parameters:

• iv_filename:

Path of the excel file.

• iv_structure:

The structure of the target internal table

• iv_hdr_avl:

Does the excel sheet have field names as headers.

‘X’ – Yes

” – No

This could be used when the actual structure has N fields, but the excel has <N fields/columns.

• iv_mandt_avl:

Does the excel sheet have MANDT passed, in case headers are not provided.

‘X’ – Yes, MANDT is part of the excel

” – No, MANDT is not part of the excel

• ct_return_table:

Generic internal table. Will contain the output, in an internal table, with same structure as iv_structure

Exceptions:

• structure_not_found – The structure ‘<iv_structure>’ could not be found
• field_not_found – There is no field in the structure for a field name in the header. Eg., If i mention field name ‘CARRY_FORWARD’ in header, and pass ‘EABL’ as structure. But there is no field called CARRY_FORWARD in structure EABL.

Questions:

Hope you find this post useful. If you have any questions do let me know. You may utilize the below URL for this.

https://answers.sap.com/index.html

Code:

FUNCTION y_excel_to_itab.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  IMPORTING
*"     REFERENCE(IV_FILENAME) TYPE  LOCALFILE
*"     REFERENCE(IV_STRUCTURE) TYPE  TABNAME
*"     REFERENCE(IV_MANDT_AVL) TYPE  CHAR01 DEFAULT ''
*"     REFERENCE(IV_HDR_AVL) TYPE  CHAR01 DEFAULT 'X'
*"  CHANGING
*"     REFERENCE(CT_RETURN_TABLE) TYPE  TABLE
*"  EXCEPTIONS
*"      STRUCTURE_NOT_FOUND
*"      FIELD_NOT_FOUND
*"----------------------------------------------------------------------

  DATA: lt_dd_field_list TYPE STANDARD TABLE OF dfies,
        lt_dyn_tab       TYPE REF TO data,
        lt_excel_itab_pre_conv TYPE STANDARD TABLE OF alsmex_tabline,

        ls_excel_cell     TYPE alsmex_tabline,
        ls_excel_cell_hdr TYPE alsmex_tabline,
        ls_dd_field       TYPE dfies,
        ls_dyn_line       TYPE REF TO data,

        lv_start_row  TYPE i,
        lv_conv_exit_fm_name TYPE string,

        lv_min_col TYPE i,
        lv_min_row TYPE i,
        lv_max_col TYPE i,
        lv_max_row TYPE i,
        lv_file_name TYPE dbmsgora-filename,
        lv_file_type TYPE sdbad-funct,
        lv_col_num_inc TYPE tabfdpos.

  FIELD-SYMBOLS: <lfs_dyn_tab>  TYPE table,
                 <lfs_dyn_line> TYPE any,
                 <lfs_dyn_cell> TYPE any.

  CONSTANTS: lc_min_col_xlsx TYPE i VALUE 1,
             lc_min_row_xlsx TYPE i VALUE 1,
             lc_max_col_xlsx TYPE i VALUE 16834,
             lc_max_row_xlsx TYPE i VALUE 1048576,
             lc_min_col_xls TYPE i VALUE 1,
             lc_min_row_xls TYPE i VALUE 1,
             lc_max_col_xls TYPE i VALUE 256,
             lc_max_row_xls TYPE i VALUE 65536,
             lc_fil_typ_xls TYPE string VALUE 'XLS',
             lc_fil_typ_xlsx TYPE string VALUE 'XLSX'.

  IF iv_filename IS NOT INITIAL.

    lv_file_name = iv_filename.

    "Find the file extenstion
    CALL FUNCTION 'SPLIT_FILENAME'
      EXPORTING
        long_filename  = lv_file_name
      IMPORTING
*       PURE_FILENAME  =
        pure_extension = lv_file_type.

    TRANSLATE lv_file_type TO UPPER CASE.

    "Row, Column limits depends on file type
    IF lv_file_type = lc_fil_typ_xls.
      lv_min_col = lc_min_col_xls.
      lv_min_row = lc_min_row_xls.
      lv_max_col = lc_max_col_xls.
      lv_max_row = lc_max_row_xls.
    ELSEIF lv_file_type = lc_fil_typ_xlsx.
      lv_min_col = lc_min_col_xlsx.
      lv_min_row = lc_min_row_xlsx.
      lv_max_col = lc_max_col_xlsx.
      lv_max_row = lc_max_row_xlsx.
    ELSE.
    ENDIF.

    "Retrieve the excel file content
    CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
      EXPORTING
        filename                = iv_filename
        i_begin_col             = lv_min_col
        i_begin_row             = lv_min_row
        i_end_col               = lv_max_col
        i_end_row               = lv_max_row
      TABLES
        intern                  = lt_excel_itab_pre_conv
      EXCEPTIONS
        inconsistent_parameters = 1
        upload_ole              = 2
        OTHERS                  = 3.
    IF sy-subrc <> 0.
* Implement suitable error handling here
    ELSE.

    ENDIF.
  ENDIF.

  "Get the list of fields in the structure
  CALL FUNCTION 'DDIF_FIELDINFO_GET'
    EXPORTING
      tabname        = iv_structure
    TABLES
      dfies_tab      = lt_dd_field_list
    EXCEPTIONS
      not_found      = 1
      internal_error = 2
      OTHERS         = 3.
  IF sy-subrc <> 0.
* Implement suitable error handling here
    RAISE structure_not_found.
  ENDIF.

  SORT lt_excel_itab_pre_conv
                      BY row ASCENDING
                         col ASCENDING.

  "If there are field names in headers, they should match the field names in the structure
  IF iv_hdr_avl = abap_true.
    "For each Field name in header
    LOOP AT lt_excel_itab_pre_conv INTO ls_excel_cell
                                   WHERE
                                    row = 1.
      CONDENSE ls_excel_cell-value.
      TRANSLATE ls_excel_cell-value TO UPPER CASE.
      "Check if it is valid
      READ TABLE lt_dd_field_list TRANSPORTING NO FIELDS
                                  WITH KEY
                                    fieldname = ls_excel_cell-value.
      IF sy-subrc <> 0.
        RAISE field_not_found.
      ENDIF.
    ENDLOOP.
    "If there is a header, data is available from second line
    lv_start_row = 2.
  ELSE.
    IF iv_mandt_avl = abap_false.
      "If MANDT is part of the structure, but not passed in the excel
      "Do not attempt to read it
      DELETE lt_dd_field_list WHERE fieldname = 'MANDT'.
      IF sy-subrc = 0.
        "If MANDT field was available in the structure, and deleted
        "Make sure, we increment the column counters
        lv_col_num_inc = 1.
      ENDIF.
    ENDIF.

    "If there is no header, data is available from first line
    lv_start_row = 1.
  ENDIF.

  "Generate work area
  CREATE DATA ls_dyn_line TYPE (iv_structure).
  "Generate table
  CREATE DATA lt_dyn_tab TYPE TABLE OF (iv_structure).
  ASSIGN lt_dyn_tab->* TO <lfs_dyn_tab>.

  "Loop through the list of cells
  LOOP AT lt_excel_itab_pre_conv INTO ls_excel_cell
                                 WHERE
                                  row >= lv_start_row.
    "Assign the Work area to Field symbol
    ASSIGN ls_dyn_line->* TO <lfs_dyn_line>.
    CONDENSE ls_excel_cell-value. "Strip leading and trailing spaces from value
    "If field names are available in headers
    IF iv_hdr_avl = abap_true.
      "Find the name of the field
      READ TABLE lt_excel_itab_pre_conv INTO ls_excel_cell_hdr
                                        WITH KEY
                                          row = 1
                                          col = ls_excel_cell-col.
      IF sy-subrc = 0.
        CONDENSE ls_excel_cell_hdr-value."Strip leading and trailing spaces from field name
        TRANSLATE ls_excel_cell_hdr-value TO UPPER CASE.
        ASSIGN COMPONENT ls_excel_cell_hdr-value OF STRUCTURE <lfs_dyn_line> TO <lfs_dyn_cell>."Assign the destination field of the work area
        "Get the field details, based on field name
        READ TABLE lt_dd_field_list INTO ls_dd_field
                            WITH KEY
                              fieldname = ls_excel_cell_hdr-value.

      ENDIF.
    ELSE.
      "In case there is MANDT in structure, but not in excel, offset the column number by 1
      ADD lv_col_num_inc TO ls_excel_cell-col.
      "Get the field details, based on position
      READ TABLE lt_dd_field_list INTO ls_dd_field
                          WITH KEY
                            position = ls_excel_cell-col.
      ASSIGN COMPONENT ls_excel_cell-col OF STRUCTURE <lfs_dyn_line> TO <lfs_dyn_cell>."Assign the destination field of the work area
    ENDIF.

    IF ls_dd_field IS INITIAL.
    ELSE.
      "If a conversion exit is specified, use it
      IF ls_dd_field-convexit IS NOT INITIAL.
        CONCATENATE 'CONVERSION_EXIT_' ls_dd_field-convexit '_INPUT' INTO lv_conv_exit_fm_name.
        CALL FUNCTION lv_conv_exit_fm_name
          EXPORTING
            input  = ls_excel_cell-value
          IMPORTING
            output = <lfs_dyn_cell>.
      ELSE.
        "Writing separate logic for DATE and Decimal fields with no conversion exits
        "If needed add other fields with their respective conversion logic in this branch
        IF ls_dd_field-datatype = 'DATS'.
          CALL FUNCTION 'CONVERT_DATE_TO_INTERNAL'
            EXPORTING
              date_external            = ls_excel_cell-value
            IMPORTING
              date_internal            = <lfs_dyn_cell>
            EXCEPTIONS
              date_external_is_invalid = 1
              OTHERS                   = 2.
        ELSEIF ls_dd_field-datatype = 'DEC'.
          "Below FM Code Courtesy:Created by Matthew Billingham, last modified on Sep 17, 2010
          "URL:https://wiki.scn.sap.com/wiki/display/ABAP/Conversion+from+external+to+internal+number+format
          CALL FUNCTION 'Y_CONVERSION_EXIT_DECS_INPUT'
            EXPORTING
              input                    = ls_excel_cell-value
            IMPORTING
              output                   = <lfs_dyn_cell>
            EXCEPTIONS
              date_external_is_invalid = 1
              OTHERS                   = 2.
        ELSE.
          <lfs_dyn_cell> = ls_excel_cell-value.
        ENDIF.
      ENDIF.

    ENDIF.
    AT END OF row.
      APPEND <lfs_dyn_line> TO <lfs_dyn_tab>.
    ENDAT.
  ENDLOOP.

  ct_return_table[] = <lfs_dyn_tab>.

ENDFUNCTION.

Below report shows how the FM could be triggered and used:

*&---------------------------------------------------------------------*
*& Report  Y_EXCEL_TO_ITAB
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT y_excel_to_itab.

PARAMETERS: p_file LIKE rlgrap-filename,
            p_str  TYPE tabname,
            p_hdr  as CHECKBOX,
            p_cli  as CHECKBOX.

START-OF-SELECTION.

  DATA: lt_dyn_tab             TYPE REF TO data.

  FIELD-SYMBOLS : <lfs_dyn_tab> TYPE ANY TABLE.

  IF p_file IS NOT INITIAL.

    "Generate table
    CREATE DATA lt_dyn_tab TYPE TABLE OF (p_str).
    ASSIGN lt_dyn_tab->* TO <lfs_dyn_tab>.

    CALL FUNCTION 'Y_EXCEL_TO_ITAB'
      EXPORTING
        iv_filename          = p_file
        iv_structure         = p_str
        IV_MANDT_AVL         = p_cli
        IV_HDR_AVL           = p_hdr
      CHANGING
        ct_return_table      = <lfs_dyn_tab>
      EXCEPTIONS
        structure_not_found  = 1
        field_not_found      = 2
        OTHERS               = 3.
    IF sy-subrc <> 0.
* Implement suitable error handling here
    ELSE.
      "Do what you must with the converted data
*****        ">>>If needed the below line could be used to update the data to DB
*****        MODIFY (p_str) FROM TABLE <lfs_dyn_tab>.
*****        "<<<
    ENDIF.
  ENDIF.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

  CALL FUNCTION 'F4_FILENAME'
    EXPORTING
      program_name  = syst-cprog
      dynpro_number = syst-dynnr
      field_name    = ' '
    IMPORTING
      file_name     = p_file.

Sample Excel File:

Sample Report Input:

Sample FM Output:

1. Update 1-4th February 2020

Updated FM and the caller program to consider the possibility MANDT may not be part of the excel.