topic Re: Reading data from excel files in Application Development and Automation Discussions

Reading data from excel files

Former Member — Thu, 16 Feb 2006 14:54:35 GMT

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

Former Member — Thu, 16 Feb 2006 14:58:06 GMT

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

Former Member — Thu, 16 Feb 2006 15:00:46 GMT

Hi,

Try this function module ALSM_EXCEL_TO_INTERNAL_TABLE.

Re: Reading data from excel files

Laxmana_Appana_ — Thu, 16 Feb 2006 15:03:54 GMT

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