13 Replies Latest reply: Jun 26, 2012 7:15 PM by Rob Burbank RSS

Reading data from excel files

Aviroop Deb
Currently Being Moderated

Hello,

 

My requirement is that i have to read data from an excel file. The difficulty is that i cannot use one of the standard function modules because i have to read from particular cells and particular  sheets of the excel file.

 

Can anyone suggest a way out for this problem.

 

thanks

Aviroop

  • Re: Reading data from excel files
    Amandeep Singh
    Currently Being Moderated

    The code I copied from the Weblog.

     

     

    Reading data from files is a very common requirement in project implementations. Excel spread sheets are simpler and better readable to text files. The standard ABAP function module ALSM_EXCEL_TO_INTERNAL_TABLE reads data from Excel file into internal table of type ALSMEX_TABLINE. This function module ONLY reads data from the active worksheet i.e., if the excel file has multiple worksheets, the data can be read from any one worksheet. The following customized function module is an alternate approach to read data from all the worksheets within a excel file into the internal table. The source code logic uses the XML classes and interfaces and the assumption is that the source Excel file will be saved as XML Spreadsheet file.

     

    One of the advantages of Excel file is that it can be saved as XML file which is the global standard for data exchange between systems. When an Excel file is saved as XML Spreadsheet, the data is represented in the form of XML elements. Using standard XML Classes and Interfaces, any Excel file with multiple worksheets can be read from ABAP program assuming that the source Excel file is saved as XML Spreadsheet.

     

    The main differences between XML files and Excel XML files lies with the nodes, elements and attribute names. Create an Excel file with two worksheets (MATERIALS, PLANTS) and with sample data in both the worksheets and save as XML Spreadsheet file type and this file is Excel XML file. Open the file in notepad and structure will be in the following format. You will see the difference where the Excel XML files have tags Workbook, Worksheet, Table, Row, Column, Cell and Data.

     

    GET_CONTENT_AS_STRING

    RECEIVING

    RVAL = V_TXT_VAL.

     

    Step 7.3.2.2

    *Populate internal table with data

    WA_EXCEL-WORKSHEET = V_WORKSHEET_SS_NAME.

    WA_EXCEL-ROW = V_ROW_COUNT.

    WA_EXCEL-COLUMN = V_CELL_COUNT.

    WA_EXCEL-DTYPE = V_DATA_TYPE.

    WA_EXCEL-VALUE = V_TXT_VAL.

    APPEND WA_EXCEL TO IT_EXCEL.

     

    The structure representing the internal table IT_EXCEL is enhanced structure of ALSMEX_TABLINE with the above fields.

     

    There are few points need to be known. Any empty cells in the Excel XML files are not displayed in the XML structure.In order to keep track of empty cells, we can use the 'Index' property of the Cell. Also, the value of the cell can be extracted depending on the data type of the Cell(with leading zeros,signs,decimals and date).

     

    There may be other ways of reading data from multiple worksheets but this approach has the advantage of working with XML and using standard SAP XML classes in a powerful way.

    Kamal Shaik is a Certified ABAP and XI Consultant working for SAP GDC, India.

    ________________________________________

    This approach can be enhanced further to map the other features of Excel XML files(like merging of cells). Any corrections or suggestions are welcome.

    Comment on this weblog

     

    Showing messages 1 through 5 of 5.

    ________________________________________

    Titles Only

    Main Topics

    Oldest First

     

         Problem to use this Algorithm

    2006-02-01 03:48:14 Duarte Santos Business Card

     

    Hi there Kamal, i am trying to do something like you explain in this weblog, the problem is that cannot find weblog´s with the first steps:

    1.Upload data from Excel XML file.

    2.Create XML Stream factory and Input stream to hold XML

    data.

    3.Create a new XML DOM.

    4.Create the parser using the stream and the DOM object.

     

    Can you help me?

         Problem to use this Algorithm

    2006-02-01 06:11:45 Kamal Shaik   Business Card

     

    Hi,

     

    Following is the code for the first 4 steps and the required declarations.

     

    *EXCEL XML file name

    DATA: FILENAME TYPE STRING.

    *Main iXML factory

    DATA: G_IXML TYPE REF TO IF_IXML.

    *XML data bytes size

    DATA: G_XML_DATA_BYTES TYPE I.

    *XML data

    DATA: G_XML_DATA(500) OCCURS 0.

    *Stream factory.

    DATA: G_STREAMFACTORY TYPE REF TO IF_IXML_STREAM_FACTORY.

    *XML istream

    DATA: G_ISTREAM TYPE REF TO IF_IXML_ISTREAM.

    *XML parser

    DATA: G_PARSER TYPE REF TO IF_IXML_PARSER.

    *XML DOM object

    DATA: G_OBJ_DOM_XML TYPE REF TO IF_IXML_DOCUMENT.

     

    *Upload the excel XML file

    CALL METHOD cl_gui_frontend_services=>gui_upload

    EXPORTING

    filename = FILENAME

    filetype = 'BIN'

    IMPORTING

    filelength = G_XML_DATA_BYTES

    CHANGING

    data_tab = G_XML_DATA.

     

    *Load ixml library

    CLASS CL_IXML DEFINITION LOAD.

     

    *Creating the main factory for the iXML library

    G_IXML = CL_IXML=>CREATE( ).

     

    *Create a main stream factory.

    CALL METHOD G_IXML->CREATE_STREAM_FACTORY

    RECEIVING

    RVAL = G_STREAMFACTORY.

     

    *Create a stream for the itab containg XML raw content.

    CALL METHOD G_STREAMFACTORY->CREATE_ISTREAM_ITABLE

    EXPORTING

    SIZE = G_XML_DATA_BYTES

    TABLE = G_XML_DATA[]

    RECEIVING

    RVAL = G_ISTREAM.

     

    *Create a new XML DOM object.

    G_OBJ_DOM_XML = G_IXML->CREATE_DOCUMENT( ).

     

    *Create XML parser.

    CALL METHOD G_IXML->CREATE_PARSER

    EXPORTING

    STREAM_FACTORY = G_STREAMFACTORY

    ISTREAM = G_ISTREAM

    DOCUMENT = G_OBJ_DOM_XML

    RECEIVING

    RVAL = G_PARSER.

     

    *Parse the XML source.

    CALL METHOD G_PARSER->PARSE

    RECEIVING

    RVAL = V_SUBRC.

     

    IF V_SUBRC <> 0.

    EXIT.

    ENDIF.

     

    *Make sure DOM is created

    IF G_PARSER->IS_DOM_GENERATING( ) NE 'X'.

    EXIT.

    ENDIF.

     

    *Close the opened stream and thereby the opened xml file.

    CALL METHOD G_ISTREAM->CLOSE

         Nice One!

    2006-01-25 06:52:59 Abdul Hakim Business Card

     

    Hi Kamal,

    Very nice weblog.Keep up ur good work..

    Well is it possible to read from an excel work book which is not saved in XML format?

     

    Regards,

    Abdul

         Nice One!

    2006-01-25 07:50:06 Kamal Shaik   Business Card

     

    Hi Abdul,

     

    We can read data from excel file using standard function module, but that would be from ONLY one worksheet. I couldn't find anything standard to read from multiple worksheets and that's how I came up with this approach.

     

    Let me know if you find any alternate approach.

     

    Thanks

    Kamal

         Nice One!

    2006-01-28 09:15:59 Svetlin Rusev Business Card

     

    I guess, multiple worksheets can be read using Desktop office intergration.

     

    Svetlin

     

  • Re: Reading data from excel files
    Ashish Gundawar
    Currently Being Moderated

    Hi,

     

    Try this function module ALSM_EXCEL_TO_INTERNAL_TABLE.

  • Re: Reading data from excel files
    Laxmana Appana
    Currently Being Moderated

    Hi,

     

    Please check this code.use FM : 'ALSM_EXCEL_TO_INTERNAL_TABLE' to upload excel data into internal table. then process the internal table data according to starting row and column of the excel sheet.

     

    Laxman

     

    code :

     

      CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

        EXPORTING

          filename                = p_file

          i_begin_col             = 1

          i_begin_row             = 1

          i_end_col               = 11

          i_end_row               = 9999

        TABLES

          intern                  = i_excel

        EXCEPTIONS

          inconsistent_parameters = 1

          upload_ole              = 2

          OTHERS                  = 3.

      IF sy-subrc <> 0.

        MESSAGE e000(zo_spa) WITH text-063."Invalid File

      ENDIF.

     

     

      LOOP AT i_excel.

        MOVE-CORRESPONDING i_excel TO i_intern1.

        APPEND i_intern1.

        CLEAR i_intern1.

      ENDLOOP.

     

     

      ws_row_id = '0001'.

      LOOP AT i_intern1 INTO wa_intern1.

     

    *Header is removed

        IF wa_intern1-row EQ ws_row_id.

          CONTINUE.

        ENDIF.

     

        CASE wa_intern1-col.

          WHEN '1'.

            CONDENSE wa_intern1-value-line.

            i_data-cust_no = wa_intern1-value-line.

          WHEN '2'.

            CONDENSE wa_intern1-value-line.

            i_data-inst_name = wa_intern1-value-line.

          WHEN '3'.

            CONDENSE wa_intern1-value-line.

            i_data-street = wa_intern1-value-line.

          WHEN '4'.

            CONDENSE wa_intern1-value-line.

            i_data-house_no = wa_intern1-value-line.

          WHEN '5'.

            CONDENSE wa_intern1-value-line.

            i_data-city = wa_intern1-value-line.

          WHEN '6'.

            CONDENSE wa_intern1-value-line.

            i_data-post_code = wa_intern1-value-line.

          WHEN '7'.

            CONDENSE wa_intern1-value-line.

            i_data-mprn1 = wa_intern1-value-line.

          WHEN '8'.

            CONDENSE wa_intern1-value-line.

            i_data-mprn2 = wa_intern1-value-line.

          WHEN '9'.

            CONDENSE wa_intern1-value-line.

            i_data-mprn3 = wa_intern1-value-line.

          WHEN '10'.

            CONDENSE wa_intern1-value-line.

            i_data-mprn4 = wa_intern1-value-line.

          WHEN '11'.

            CONDENSE wa_intern1-value-line.

            i_data-mprn5 = wa_intern1-value-line.

     

        ENDCASE.

     

        AT END OF row.

     

          APPEND i_data.

          CLEAR  i_data.

        ENDAT.

     

      ENDLOOP.

    • Re: Reading data from excel files
      Rich Heilman
      Currently Being Moderated

      <i>....I cannot use one of the standard function modules because i have to read from particular cells and particular sheets of the excel file.</i>

       

       

      I do not believe that ALSM_EXCEL_TO_INTERNAL_TABLE will help you with reading certain sheets of an excel file.

       

       

      You can use this function module to show you how to work with OLE.  You just need to add the part where it is selecting a certain sheet.

       

       

      Regards,

      Rich Heilman

      • Re: Reading data from excel files
        Har Mayank Sahai
        Currently Being Moderated

        Hi Alok,

        Try using the FM ,

           CATSXT_GET_HOLIDAYS for finding out no of days defined as holidays in ur HR Module.It will give you entries as dates falling on Holidays in an internal table. Take a count of the internal table.

         

        Use the FM HR_SGPBS_YRS_MTHS_DAYS to find out no of days between a date range.

         

        Subtract the days between date ranges and the no of days as holidays between them.

         

        I have tried to write a piece of code for you.

         

        If you find it helpful,Please reward point.

         

        REPORT zharmayforum_days .

         

        DATA: wa_days TYPE i.

        PARAMETERS: wa_frdat TYPE sy-datum DEFAULT '20051216',

                    wa_todat TYPE sy-datum DEFAULT '20051221'.

         

        PERFORM get_days USING wa_frdat wa_todat CHANGING wa_days.

         

        WRITE: 'No. of days: ', wa_days.

         

         

         

         

         

        &----


        *&      Form  GET_DAYS

        &----


        •       text

        ----


        •      -->P_WA_FRDAT  text

        •      -->P_WA_TODAT  text

        •      <--P_WA_DAYS  text

        ----


        FORM get_days USING    p_wa_frdat

                               p_wa_todat

                      CHANGING p_wa_days.

         

          DATA : tot_days TYPE i,

                 tot_holi TYPE i.

          DATA : holi_table TYPE catsxt_iscal_day_itab.

         

          DATA: lin TYPE i,

              ini TYPE i,

              knd TYPE c.

         

          CALL FUNCTION 'HR_SGPBS_YRS_MTHS_DAYS'

            EXPORTING

              beg_da              = p_wa_frdat

              end_da              = p_wa_todat

           IMPORTING

        •     NO_DAY              =

        •     NO_MONTH            =

        •     NO_YEAR             =

             no_cal_day          = tot_days

           EXCEPTIONS

             dateint_error       = 1

             OTHERS              = 2

                    .

          IF sy-subrc <> 0.

        • MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

        •         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

          ENDIF.

         

          CALL FUNCTION 'CATSXT_GET_HOLIDAYS'

               EXPORTING

                    im_personnel_number = '00000024'

                    im_begin_date       = p_wa_frdat

                    im_end_date         = p_wa_todat

                    im_get_weekend_days  = 'X'

               IMPORTING

                    ex_holidays         = holi_table.

         

          DESCRIBE TABLE holi_table LINES lin OCCURS ini KIND knd.

         

          p_wa_days = tot_days - lin.

         

         

        ENDFORM.                    " GET_DAYS.

         

        Thanks,

         

        Mayank

  • Re: Reading data from excel files
    Andri Thaslim
    Currently Being Moderated

    to read data from excel for particular worksheet, I've modified FM ALSM_EXCEL_TO_INTERNAL_TABLE a little bit..

    add 1 new parameter Called :

    - SHEETNO TYPE I to your NEW FM

     

    these are the steps:

    - Create a new FM, copy all sourcecode from ALSM_EXCEL_TO_INTERNAL_TABLE, including parameters and other needed files to your new FM (eq : ZALSM_EXCEL).

    - Add 1 new Import (parameter) as follow:

      >> Sheetno type I

     

    - Open ZALSM_EXCEL source code

    - Find Sentence:

    *****

    • set property of application 'Visible' = 1.

    • m_message.

      GET PROPERTY OF  application 'ACTIVESHEET' = worksheet.

      m_message.

    *****

     

    and replace with the following code:

    *****

    • set property of application 'Visible' = 1.

    • m_message.

    • GET PROPERTY OF  application 'ACTIVESHEET' = worksheet.

    • m_message.

     

    CALL METHOD OF APPLICATION 'Worksheets' =

                      worksheet EXPORTING #1 = sheetno.

    m_message.

    CALL METHOD OF worksheet 'Activate'.

    m_message.

    GET PROPERTY OF application 'ACTIVESHEET' = worksheet.

    m_message.

    *****

     

    - then call the FM as follow:

     

      CALL FUNCTION 'ZALSM_EXCEL'

        EXPORTING

          FILENAME                = FILE

          I_BEGIN_COL             = '1'

          I_BEGIN_ROW             = '1'

          I_END_COL               = '200'

          I_END_ROW               = '5000'

    • this is a new parameter

          SHEETNO                 = P_SHEET

    ****

        TABLES

          INTERN                  = XCEL

        EXCEPTIONS

          INCONSISTENT_PARAMETERS = 1

          UPLOAD_OLE              = 2

          OTHERS                  = 3.

     

    - hope its help.

     

    regards,

    andri thaslim

  • Re: Reading data from excel files
    Currently Being Moderated

    HI,

     

    Go thru this link

     

    http://www.erpgenie.com/abap/functions.htm

     

    Thanks

    Sunil

  • Re: Reading data from excel files
    SATYA NARAYANA GUDEPU
    Currently Being Moderated

    Check my own developed FM:

    < Request for points removed >

     

    ************************************************************************
    * PROGRAM TITLE : Z_READ_MULTISHEET_EXCEL.                             *
    *----------------------------------------------------------------------*
    * CREATED BY    : SATYANARAYANA GUDEPU                 *
    * DESCRIPTION   : This FM is part of PB calculator programme, which    *
    *                 is used to upload Performance Bonus detials of employ*
    *                 ees(P) by HR users. Excel sheet with multiple sheets *
    *                 are taken as input.                                  *
    *----------------------------------------------------------------------*
    * Note : All subsheets are must be similar to each other.              *
    *----------------------------------------------------------------------*
    * TABLES        :                                                      *
    * MODULE        : HR-PA                                                *
    * TYPE          : ABAP Report                                          *
    * R/3 Release   : SAP ECC 6.0                                          *
    *----------------------------------------------------------------------*
    *                         VERSION HISTORY                              *
    *----------------------------------------------------------------------*
    * AUTHOR    |   DATE   |Req No| TR NO.   | DESCRIPTION                 *
    ************************************************************************
    *
    *
    ************************************************************************

    FUNCTION z_read_multisheet_excel.
    *"----------------------------------------------------------------------
    *"*"Local Interface:
    *"  IMPORTING
    *"     REFERENCE(XLS_FILE_NAME) TYPE  RLGRAP-FILENAME
    *"     REFERENCE(START_ROW_ALLSHEETS) TYPE  I
    *"     REFERENCE(START_COLUMN_ALLSHEETS) TYPE  I
    *"     REFERENCE(END_ROW_ALLSHEETS) TYPE  I
    *"     REFERENCE(END_COLUMN_ALLSHEETS) TYPE  I
    *"     REFERENCE(MULTI_SHEET) TYPE  CHAR1 DEFAULT 'X'
    *"     REFERENCE(WITH_HEADER) TYPE  CHAR1 DEFAULT 'X'
    *"  TABLES
    *"      IT_DATATAB STRUCTURE  ALSMEX_TABLINE
    *"  EXCEPTIONS
    *"      INCONSISTENT_PARAMETERS
    *"      UPLOAD_OLE
    *"----------------------------------------------------------------------

      TYPES: ty_t_sender(1500) TYPE c.
      DATA: application  TYPE ole2_object,
            workbook     TYPE ole2_object,
            sheet        TYPE ole2_object,
            range        TYPE ole2_object,
            worksheet    TYPE ole2_object,
            h_cell       TYPE ole2_object,
            h_cell1      TYPE ole2_object,
            ld_separator TYPE c,
            ld_rc        TYPE i,
            lv_sheet     TYPE i,
            lv_flag      TYPE i.
      DATA: excel_tab    TYPE TABLE OF ty_s_senderline,
            excel_tab1   TYPE TABLE OF ty_s_senderline,
            it_data      TYPE STANDARD TABLE OF alsmex_tabline INITIAL SIZE 0.

    *Function Module To Upload Data From
    *Excel File Into Two Internal Tables
    *© 2005 SAP AG 6
    *__MESSAGE DEFINATION
      DEFINE m_message.
        case sy-subrc.
          when 0.
          when 1.
            message id sy-msgid type sy-msgty number sy-msgno
            with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
          when others. raise upload_ole.
        endcase.
      END-OF-DEFINITION.
    *PARAMETER CHECK.
      IF start_row_allsheets > end_row_allsheets.
        RAISE inconsistent_parameters.
      ENDIF.

      IF start_column_allsheets > end_column_allsheets.
        RAISE inconsistent_parameters.
      ENDIF.

      CLASS cl_abap_char_utilities DEFINITION LOAD.
      ld_separator = cl_abap_char_utilities=>horizontal_tab.
    *__Create Excel Application.
      IF application-header = space OR application-handle = -1.
        CREATE OBJECT application 'Excel.Application'.
        m_message.
      ENDIF.

      CALL METHOD OF
          application
          'Workbooks' = workbook.
      m_message.

      CALL METHOD OF
          workbook
          'Open'

        EXPORTING
          #1       = xls_file_name.
      m_message.

      CLEAR: lv_sheet, lv_flag.
    *__Check for multisheet.
      IF multi_sheet IS NOT INITIAL.
        lv_sheet = 99.
      ELSE.
        lv_sheet = 1.
      ENDIF.
    *__Get data from all worksheets; can work for 99 sheets.
      DO lv_sheet TIMES.
        lv_flag = lv_flag + 1.
        CALL METHOD OF
            application
            'Worksheets' = sheet
          EXPORTING
            #1           = lv_flag.
        m_message.
    *__Make active the sheet of given sheet no.
        CALL METHOD OF
            sheet
            'Activate'.
        m_message.

        GET PROPERTY OF application 'ACTIVESHEET' = sheet.
        m_message.

        CALL METHOD OF
            sheet
            'Cells' = h_cell
          EXPORTING
            #1      = start_row_allsheets
            #2      = start_column_allsheets.
        m_message.

        CALL METHOD OF
            sheet
            'Cells' = h_cell1
          EXPORTING
            #1      = end_row_allsheets
            #2      = end_column_allsheets.
        m_message.

        CALL METHOD OF
            sheet
            'RANGE' = range
          EXPORTING
            #1      = h_cell
            #2      = h_cell1.
        m_message.
        CALL METHOD OF
            range
            'SELECT'.
        m_message.

    *__Copy marked area (SHEET1) into Clippboard
        CALL METHOD OF
            range
            'COPY'.
        m_message.

    *__Read clipboard into ABAP.
        REFRESH: excel_tab, it_data.
        CALL METHOD cl_gui_frontend_services=>clipboard_import
          IMPORTING
            data                 = excel_tab
          EXCEPTIONS
            cntl_error           = 1
            error_no_gui         = 2
            not_supported_by_gui = 3
            OTHERS               = 4.
        IF sy-subrc <> 0.
          MESSAGE a037(alsmex).
        ENDIF.
    *__Function Module To Upload Data From Excel File Into Two Internal Tables
        PERFORM separated_to_intern_convert TABLES excel_tab it_data
        USING ld_separator.

        APPEND LINES OF it_data TO it_datatab.
    *__Clear the clipboard
        REFRESH excel_tab.
        CALL METHOD cl_gui_frontend_services=>clipboard_export
          IMPORTING
            data                 = excel_tab
          CHANGING
            rc                   = ld_rc
          EXCEPTIONS
            cntl_error           = 1
            error_no_gui         = 2
            not_supported_by_gui = 3
            OTHERS               = 4.
        IF sy-subrc EQ 0.
        ENDIF.
      ENDDO.
    *__Leaving Application
      CALL METHOD OF
          application
          'QUIT'.
      m_message.
      CALL METHOD OF
          application
          'FREE'.
      m_message.
    * >>>>> Begin of change note 575877
    * to kill the Excel process it's necessary to free all used objects
      FREE OBJECT h_cell.       m_message.
      FREE OBJECT h_cell1.      m_message.
      FREE OBJECT range.        m_message.
      FREE OBJECT worksheet.    m_message.
      FREE OBJECT workbook.     m_message.
      FREE OBJECT application.  m_message.
    * <<<<< End of change note 575877
    *__Remove header records.


    ENDFUNCTION.

     

    Moderator Message : Please do not ask for points hereafter.

     

    Message was edited by: Kesavadas Thekkillath

  • Re: Reading data from excel files
    Tima M
    Currently Being Moderated

    Hi Aviroop,

     

    Please, look through the next document

    http://wiki.sdn.sap.com/wiki/display/ABAP/Excel+with+SAP+-+An+overview
    There is collected lots of different ways how to read\write Excel documents. Choose the one which fits you.

     

    Also for your case you can use DOI (Desktop Office Integration).

  • Re: Reading data from excel files
    Kartik P
    Currently Being Moderated

    Hi,

     

    Please have a look at the following document. It will solve your problem.

     

    Copying data from Microsoft Excel to ABAP using OLE

     

    Here for copying the data, active excel sheet is used, in the same way you can pass sheet names as parameters and copy data from the specified sheet. Hope it helps. Thank you.

     

    Regards,

    Kartik

  • Re: Reading data from excel files
    Ashwath Shetty
    Currently Being Moderated

    Hii Aviroop,

     

    OLE concept is best method to upload Excel sheet.

     

     

    Regards,

    Ashwath.

Actions