Ads

SAP ABAP Code for FM(Function Module) to retrieve data from excel into an internal table of any type

-

SAP ABAP Code for FM(Function Module) to retrieve data from excel into an internal table of any type:

Here ,you can understand the custom code.
FUNCTION y_excel_to_itab.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  IMPORTING
*"     REFERENCE(IV_FILENAME) TYPE  LOCALFILE
*"     REFERENCE(IV_FIELDNAME_HEADERS) TYPE  CHAR01
*"     REFERENCE(IV_STRUCTURE) TYPE  TABNAME
*"  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..

  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_fieldname_headers = 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 there is no header, data is available from first line
    lv_start_row = 1.
  ENDIF.

  "Genearte 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_fieldname_headers = 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
      ENDIF.
    ELSE.
      ASSIGN COMPONENT ls_excel_cell-col OF STRUCTURE <lfs_dyn_line> TO <lfs_dyn_cell>."Assign the destination field of the work area
    ENDIF.

    READ TABLE lt_dd_field_list INTO ls_dd_field
                                WITH KEY
                                  fieldname = ls_excel_cell_hdr-value.
    IF sy-subrc <> 0.
    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.

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_fieldname_headers = abap_true
        iv_structure         = p_str
      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.

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

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.

ABAP Function Module to retrieve data from excel into an internal table of any type

Comments

Post a Comment

Popular posts from this blog

BADI Interview Questions in SAP ABAP

-
BADI Interview Questions in SAP ABAP A BAdI is an object-oriented enhancement option, which makes it the most sophisticated enhancement type. The main characteristic of a BAdI is that it provides a mechanism to change the functionality of a well-defined business function without making changes to the delivered source code. Future upgrades of the original business function can be applied without losing the customer-specific enhancements or the need to merge the changes.  If you want to become a professional and technical blogger practically, read these real-world practical SAP ABAP books . These books will help you to become a more flexible coding developer in future SAP technology. What is a BADI in SAP? BADI (Business Add-In) is a new SAP Object Oriented enhancement technique that is used to add our own business functionality to the existing SAP standard functionality.  BADI follows the Object-Oriented approach to make them reusable. A BADI can be used any number of times
Read more

Sample SAP ABAP Programming Examples for Practice

-
Sample SAP ABAP Programming Examples for Practice Are you a beginner at ABAP Language, you're looking for example programs for practice. Here I collected some sample programs from various objects in ABAP that are useful for beginners to improve their Technical skills. To familiar with coding and ABAP applications, you must be practice all applications. As per my experience, practice makes you perfect, as a technical consultant, should be ready to develop any object based on the need of the client, then only, we will get appreciation from the client and from our team side and it helps us to develop complex objects in SAP. ABAP Syntax ABAP Statements ABAP Program Types SAP ABAP Workbench Tools SAP Modules SAP Transaction Codes SAP Tables SAP ABAP Hello world Program Example ABAP- Simple ALV report Example ABAP- Interactive ALV Report Example ABAP- BDC Call Transaction Method Programming ABAP- BDC Session Method Programming Example ABAP- Smartforms
Read more

Module Pool Programming Interview Questions and Answers in SAP ABAP

-
Module Pool Programming(Dialog Programming)  Interview Questions and Answers in SAP ABAP As an ABAP Consultant, you should be strong in Module pool programming techniques as well as when you face the real-time or fresher interview, Questions, and answers to test your talent to select for the interview. For that purpose, I am sharing my interview experiences in which I faced the most of the times on these topics that are at exit command, events difference between set screen and call screen difference between call screen and set screen in module pool,chain-end chain in module pool. What is the transaction code for the screen painter? SE51. What is the transaction code for Menu painter? SE41. What is the transaction code for the screen painter? SE93. What are the main components of dialog programs? Screens Module pools Subroutines Menus Transactions What is Screen flow Logic? The screen flow logic is like an ABAP program in that it serves as a
Read more

Step by Step tutorial on BDC Session Method Program in SAP ABAP

-
Image
Step by Step tutorial on BDC Session Method Program in SAP ABAP BDC Session Method is used to upload data from Non-SAP to SAP System. Using Session method, we can transfer data through more than one Transaction, Unlike BDC Call Transaction Method Program . We are Processing Batch input Session From SM35 Transaction Code. Session method. 1) synchronous processing. 2) can transfer a large amount of data. 3) processing is slower. 4) error log is created 5) data is not updated until the session is processed. Call transaction Method. 1) asynchronous processing 2) can transfer a small amount of data 3) processing is faster. 4) errors need to be handled explicitly 5) data is updated automatically Go to SHDB and Press Enter Click on New Recording Button and Give the zrecord1 name and enter transaction code MM01 and click on the Start recording button. The system goes to the Create material screen, there give the industry sector and material type and selects ba
Read more

SAP ABAP Interview Questions and Answers for 10 Years Experienced

-
SAP ABAP Interview Questions and Answers for 10 Years Experienced This post will helpful for SAP ABAP experienced candidates who may have 3,4 5, 6,7, and 10 years of experience, all of them can be follow this interview pattern for their successful interview.  SAP Data Dictionary Realtime Interview Questions SAP BDC Realtime Interview Questions SAP ALV Reports  Real-time Interview Questions Module Pool Interview Questions SAP Smartforms Realtime Interview Questions SAP BADI Realtime Interview Questions SAP BAPI Realtime Interview Questions SAP  IDOC Realtime Interview Questions ABAP Check and Value Table Questions SAP SD MM FICO Flow Interview Questions SAP OOPs Realtime Interview Questions SAP OOPs Part One  Real-time Interview Questions SAP OOPs Part two Real time Interview Questions SAP OOPs Part three Realtime Interview Questions SAP OOPs Part four Realtime Interview Questions ABAP  Realtime Interview Questio
Read more