Reading Excel File on Application Server

[tino_schumacher]

Dear all,

I have to read data from MS Excel files without OLE technology.

The files located on the application server.

Have anyone sample code for this purpose?

Thanks in advance

Tino

[Former Member]

Use this Function Module ALSM_EXCEL_TO_INTERNAL_TABLE or

OPEN DATASET g_name FOR INPUT IN TEXT MODE ENCODING DEFAULT

DO.

READ DATASET g_name INTO temp.

IF sy-subrc 0.

EXIT.

ELSE.

................

ENDIF. "END OF FILE REACHED

ENDDO.

CLOSE DATASET g_name.

Sample code : http://www.sap-img.com/abap/sample-abap-code-on-bapi-po-change.htm

Edited by: Gayathri G on Apr 30, 2009 12:39 PM

[Former Member]

Hi,

Please consider the post below

Regards,

Manish

Edited by: MANISH GUPTA on Apr 30, 2009 12:46 PM

[Former Member]

Use the folowing code

Specify the format of the file in lv_file_separator.

The below code is to read tab separated file from application server.

REPORT zabaptooldemo.
* Table declaration
TABLES: eban.
* Type(s) definition
TYPES: BEGIN OF ty_testfile,
         field1 TYPE bukrs,
         field2 TYPE anln1,
         field3(10) TYPE c,
         field4(10) TYPE c,
         field5(10) TYPE c,
       END OF ty_testfile.
TYPES: BEGIN OF ty_testfile_file,
         field1(10) TYPE c,
         field2(10) TYPE c,
         field3(10) TYPE c,
         field4(10) TYPE c,
         field5(10) TYPE c,
       END OF ty_testfile_file.
* Work area(s) definition
DATA: wa_file_data TYPE text4096,
      wa_testfile TYPE ty_testfile,
      wa_testfile_file TYPE ty_testfile_file.
* Internal table(s) definition
DATA: tb_file_data TYPE TABLE OF text4096,
      tb_testfile TYPE TABLE OF ty_testfile,
      tb_testfile_file TYPE TABLE OF ty_testfile_file.
* Class(s) definition
CLASS cl_abap_char_utilities DEFINITION LOAD.
* EVENT - INITIALIZATION
INITIALIZATION.

* EVENT - START OF SELECTION
START-OF-SELECTION.
* Perform to upload data from a file
  PERFORM upload_file.
  PERFORM data_assign.

*&---------------------------------------------------------------------*
*      Form  upload_file
*&---------------------------------------------------------------------*
*       To upload file data into SAP
*&---------------------------------------------------------------------*
FORM upload_file.
* To read output in specified format from application server
  PERFORM upload_app_server.
*To convert raw format to specified format
  PERFORM data_convert.
ENDFORM.                    "upload_file

*&---------------------------------------------------------------------*
*      Form upload_app_server
*&---------------------------------------------------------------------*
*       To read file from application server
*&---------------------------------------------------------------------*
FORM upload_app_server.
  DATA: lv_app_server_file TYPE string.
  lv_app_server_file = pa_filep.
* To read file from Application server
  OPEN DATASET lv_app_server_file FOR INPUT IN TEXT MODE ENCODING DEFAULT.
  DO.
    READ DATASET lv_app_server_file INTO wa_file_data.
    IF sy-subrc = 0.
      APPEND wa_file_data TO tb_file_data.
    ELSE.
      EXIT.
    ENDIF.
  ENDDO.
  CLOSE DATASET lv_app_server_file.
ENDFORM.                    "upload_app_server

*&---------------------------------------------------------------------*
*      Form data_convert
*&---------------------------------------------------------------------*
*       To convert file to specified format
*&---------------------------------------------------------------------*
FORM data_convert.
  DATA: lv_file_separator TYPE c.
  lv_file_separator = cl_abap_char_utilities=>horizontal_tab.
* To upload file in other formats(CSV, Tab delimited etc)
  CALL FUNCTION 'TEXT_CONVERT_TEX_TO_SAP'
    EXPORTING
      i_field_seperator    = <lv_file_separator>
      i_tab_raw_data       = tb_file_data
    TABLES
      i_tab_converted_data = tb_testfile_file
    EXCEPTIONS
      conversion_failed    = 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.
ENDFORM.                    "data_convert

*&---------------------------------------------------------------------*
*      Form data_assign
*&---------------------------------------------------------------------*
*       To assign field value into character format
*&---------------------------------------------------------------------*
FORM data_assign.
  LOOP AT tb_testfile_file INTO wa_testfile_file.
    wa_testfile-field1 = wa_testfile_file-field1.
    wa_testfile-field2 = wa_testfile_file-field2.
    wa_testfile-field3 = wa_testfile_file-field3.
    wa_testfile-field4 = wa_testfile_file-field4.
    wa_testfile-field5 = wa_testfile_file-field5.
    APPEND wa_testfile TO tb_testfile.
    CLEAR wa_testfile.
  ENDLOOP.
ENDFORM.                    "data_assign

  

Regards,

Manish

Edited by: MANISH GUPTA on Apr 30, 2009 12:46 PM

[tino_schumacher]

Thanks for answering but this is not what i want.

How can i read a single cells e. g. "A1" in an *.XLS file format (NOT CSV or tab delimited).

Regards

Tino

[Former Member]

Dear Tino,

Did you find a way to solve this ?

[Clemenss]

Hi Tino,

pure excel format is microsofts own format that can be handled only by microsoft. OLE just means to use an interface to access microsofts application software.

You can read the excel from application server into an internal table of type SOLIX_TAB. Then you can (working online, not in background) pass the data to an I_OI_DOCUMENT_PROXY

  mo_document_proxy->open_document_from_table(

    EXPORTING

      document_size    = mv_file_length

      document_table   = mt_datax_tab

...

CALL METHOD mo_document_proxy->get_spreadsheet_interface

     EXPORTING

       no_flush        = ' '

  IMPORTING

    sheet_interface = mo_spreadsheet

Refer to Desktop Office Integration (BC-CI)

Regards

Clemens

[Ruediger_Plantiko]

If it really has to be xls (not xlsx), i.e. the pre-2007 Excel format (BIFF), check out my function module Z_EXCEL_TO_INTERNAL_TABLE:

http://bsp.mits.ch/code/fugr/zutil_excel

If instead xlsx is the source format, you may use the custom software component abap2xlsx (wich, despite its name, has methods for transformations in both directions):

http://wiki.sdn.sap.com/wiki/display/ABAP/abap2xlsx

Another option for xlsx->abap would be

• to unzip the xlsx file
• to write an XSLT transformation which extracts the desired data directly from the XML data source of the Excel sheet.

The xlsx format is really not difficult to understand. You can use Sal Mangano's XSLT cookbook for an example how to extract data from an Excel Spreadsheet with XSLT:

http://flylib.com/books/en/2.765.1.142/1/

[Former Member]

Dear Rudiger,

Thanks for your reply. But would cl_gui_frontend_services=>gui_upload work if the excel needs to be read in background mode ?

Thanks and Regards,

Vasu

[Ruediger_Plantiko]

Dear Vasu,

Of course, cl_gui_frontend_services=>gui_upload will not work if the excel needs to be read in background mode. If you think about it, it can't be possible: You want to read data from a machine (the presentation server) which is not available!

If your process requires to read data from another machine into the application server, then this machine should be open for access from the ABAP application server. A Windows file system network will not suffice, since it requires a Windows computer for access (it could only work if your application server is a Windows box, too - which is a rare choice).

Possible ways:

1. The computer to access provides the files as an FTP, SFTP, or even HTTP server.
2. The computer to access works as RFC endpoint
3. Another computer of the network  performs 1) or 2), accessing to the required destination through the network.

All the options require a server process constantly running on the target machine. In cases 1) and 2), this is the machine where the file resides. In 3), it is another machine of the same network.

Did you think about the case that the employee has switched off his computer after leaving, before your nightly background job is trying to access the data?

Usually, in such processes, the user directly uploads his file to the server when he has completed its data. The data are then

• either immediately parsed into ABAP data by the custom transaction which does the upload.
• Or they are stored on the app server and analyzed later.

The first option is much better, since the user can be informed directly about input errors.

Regards,

Rüdiger

[Former Member]

Dear Rudiger,

Thanks for your detailed explanation. Highly appreciated. In my case , i would have to read the excel file consisting of legacy records from Application Server using a batch program. Then convert this excel sheet into an internal table and proceed with creation of Business Partners , relationships etc.

I initially explored GUI_UPLOAD and concluded that it wouldnt work on batch programs. The other option is to use OPEN DATASET , i am not sure if it would be the wise way to read excel files where fields are in seperate coloumns,

Any advice ?

[Ruediger_Plantiko]

Dear Vasudev,

I understand that the upload of the Excel file is a one-time activity of yourself. In this case, I would recommend to store the data in the CSV file format, then upload it with a transaction like CG3Z and store it as a text file on the application server.

Then, in your batch job:

• you read this file with OPEN DATASET etc., line by line will be OK, 
• parse the CSV format into ABAP data which is relatively easy
• call the business logic modules, e.g. for creating business partners, with these data.

Regards,

Rüdiger

[Former Member]

This message was moderated.