Skip to Content

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

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

To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

  1. Abdul Hakim
    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

    (0) 
    1. Anonymous
      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

      (0) 
      1. Siew Tim, Colin Cheong
        Hi Kamal,

        I am facing the same problem as raja rajavel whose mail dated 2007-04-24 00:58:13 which had not been answered.
        Can you help to show us how we can get retreive the indec of the empty cell and allow it to be uploaded?

        (0) 
  2. Duarte Santos
    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?

    (0) 
    1. Anonymous
      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

      (0) 
      1. raja rajavel
        Hi kamal ,
        THere is a problem if there is a blank cell .
        THe program simply ignores it and gets the next value found in the table/workarea where a empty field must be present .

        I tried using get->attribute method to get the index attribute which is set to the next cell with value  if any cell elements (one or multiple continuously ) are empty .
        But its not reading the Index attribute . can u please help me out .

        i have provided the code snippet below

        CALL METHOD v_obj_cell_itr->get_next
                RECEIVING
                  rval = v_obj_cell_node.

              IF v_obj_cell_node IS  INITIAL .
                 append wa_OUtPUT to l_output .
                  exit.
              endif .
        *Convert node to element
              v_obj_cell_element ?=
              v_obj_cell_node->query_interface(
              ixml_iid_element ).

        call method v_obj_cell_element->get_attribute
           exporting
               name = ‘Index’
           RECEIVING
              RVAL = INDEX .
        “assign COMPONENT
              “Step 7.3.2.1
        *Get the ‘Data’ element under the ‘Cell’ element.
              CALL METHOD v_obj_cell_element->find_from_name
                EXPORTING
                  name  = ‘Data’
                  depth = 1
                RECEIVING
                  rval  = v_obj_data_element.

        (0) 
      2. raja rajavel
        Hi kamal ,
        THere is a problem if there is a blank cell .
        THe program simply ignores it and gets the next value found in the table/workarea where a empty field must be present .

        I tried using get->attribute method to get the index attribute which is set to the next cell with value  if any cell elements (one or multiple continuously ) are empty .
        But its not reading the Index attribute . can u please help me out .

        i have provided the code snippet below

        CALL METHOD v_obj_cell_itr->get_next
                RECEIVING
                  rval = v_obj_cell_node.

              IF v_obj_cell_node IS  INITIAL .
                 append wa_OUtPUT to l_output .
                  exit.
              endif .
        *Convert node to element
              v_obj_cell_element ?=
              v_obj_cell_node->query_interface(
              ixml_iid_element ).

        call method v_obj_cell_element->get_attribute
           exporting
               name = ‘Index’
           RECEIVING
              RVAL = INDEX .
        “assign COMPONENT
              “Step 7.3.2.1
        *Get the ‘Data’ element under the ‘Cell’ element.
              CALL METHOD v_obj_cell_element->find_from_name
                EXPORTING
                  name  = ‘Data’
                  depth = 1
                RECEIVING
                  rval  = v_obj_data_element.

        (0) 
  3. Francesco Convertini
    Hello,

    The coding inserted by you works really well and I would like to extent it inserting, for each cell, also the right reading index of the line, of the column and
    the field’s characteristic. The fields you indicated are:

    ……
    WA_EXCEL-ROW = V_ROW_COUNT.
    WA_EXCEL-COLUMN = V_CELL_COUNT.
    WA_EXCEL-DTYPE = V_DATA_TYPE.
    ……

    Could you, please, suggest me a method to retrieve the line and the column?

    My problem occurs when the cells in Excel are empty, when the convertion is performed in XML, exactly as you have indicated, the empty cells are not
    converted and I need the exact coordinates for inserting correctly the data in the table.

    Thanks and regards.

    Francesco Convertini

    (0) 
    1. Siew Tim, Colin Cheong
      Hi,

      The V_DATA_TYPE return a value “4”.
      what type is it?
      where can I get some info on it?
      Overall, the XML upload looks workable except for the blank cell or unwanted worksheet that need to be bypassed.

      (0) 

Leave a Reply