Skip to Content
Technical Articles
Author's profile photo Joshua Gnanaraj

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

Assigned Tags

      10 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Sandra Rossi
      Sandra Rossi

      Nowadays, ABAP developers use abap2xlsx (it works in both directions)

      EDIT (to make my comment more constructive): I understand that your tool is made of the following two parts, and my comment is only about loading the Excel file into variable:

      • Load Excel file into variable
      • Based on the first row of the Excel worksheet or a parameter containing the structure of a table, create dynamically an internal table via RTTS

      I recommend that you propose code based on abap2xlsx rather than using ALSM_EXCEL_TO_INTERNAL_TABLE which works only in dialog mode, in SAP ERP only (not in CRM, SRM and so on), is slow (OLE), is not released for customers, is limited to 9999 rows and columns, maximum 50 characters per cell.

      For RTTS, there has been a number of blog posts since it was proposed, 15 years ago (and before that, there was still another solution to generate dynamic tables).

      Author's profile photo Sercan Küçükdemirci
      Sercan Küçükdemirci

      Not everyone allowed to install third party libraries into their system.

      Author's profile photo Sandra Rossi
      Sandra Rossi

      I don’t understand how clients prefer to reinvent the wheel / spend money instead of adopting existing open source software widely adopted by the community. Especially in 21st century ?

      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva
      • ABAP2XLSX does not involve "installing third party libraries". You can literally just open the code on GitHub and copy paste it, it consists of Z... classes.
      • It comes with an open source license. If you're an SAP customer and are just running an SAP system for your own needs, then the code is available for you to use at no cost.
      • I have personally reviewed the code for any potential funny business, such as money being moved to the author's Swiss bank account and such, and found no concerns. We are using ABAP2XLSX for an internal SAP system at IBM with great success. (In fact, IBM embraces open source concept, as everyone should these days.) Obviously, it won't be used in the commercial code that is created for the customers, that's a different story.
      • It is very simple to use, the developers can use it to learn more about OOP and how to write better code.

      As long as the use is within the open source license boundaries, one must be out of their mind not to take advantage of ABAP2XLSX. Is this not enough to "sell" it to the organization? Simple statement "your competitors are using it and taking advantage of it, do you really want to be left behind?" should seal the deal.

      Author's profile photo Sercan Küçükdemirci
      Sercan Küçükdemirci

      I am not denying that ABAP2XLSX is a great library, i am using it whenever i need to work with excel files but it is overkill for simple tasks such as downloading internal table into excel file. Also you may not believe me but there are audits that check every single line of abap code you put into their system, they won't be happy to see various unexplained Z files even though it is a well known open source project.

      Author's profile photo Sandra Rossi
      Sandra Rossi

      That's a good point. Same remark as for clients, auditors should understand that it's code from community widely adopted. If there is a good reason to change some code, then it's maybe the moment to contribute to the project.

      I would say that "overkilling" is NOT using existing libraries that simplify the process.

      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva

      Can't speak for all the countries and audits, but AFAIK there is nothing in SOX (in the US) or ISO rules that would prohibit ABAP2XLSX. Internal audit only checks for compliance with the IT's own documented procedures, so it's up to IT management to change that. It's not like audit just comes up with their own rules.

      Author's profile photo Paul Hardy
      Paul Hardy

      I am willing to bet that that 99.9% of organisations that don't allow the use of open source ABAP, use open source software somewhere in their organisation, usually quite heavily,

      If there is a "no open source" rule than to be consistent you should ban the use of smartphones as well. The apps there crawl with open source code. Oh yes, and don't use the internet either.

      I have always asked the question - "If you have a no open source policy, can you please tell me the name of your company, so i can buy shares in your competitors".

      Author's profile photo Paul Hardy
      Paul Hardy

      It would not be a normal month here in the SAP Community if someone did not re-invent ABAP2XLSX....

      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva

      Especially as a function module!