topic Re: Reading Excel File on Application Server in Application Development and Automation Discussions

Reading Excel File on Application Server

tino_schumacher — Thu, 30 Apr 2009 10:30:09 GMT

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

Re: Reading Excel File on Application Server

Former Member — Thu, 30 Apr 2009 10:38:53 GMT

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

Re: Reading Excel File on Application Server

Former Member — Thu, 30 Apr 2009 10:39:12 GMT

Hi,

Please consider the post below

Regards,

Manish

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

Re: Reading Excel File on Application Server

Former Member — Thu, 30 Apr 2009 10:42:40 GMT

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

Re: Reading Excel File on Application Server

tino_schumacher — Thu, 30 Apr 2009 11:52:21 GMT

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

Re: Reading Excel File on Application Server

Former Member — Sat, 21 Jul 2012 16:01:25 GMT

Dear Tino,

Did you find a way to solve this ?

Re: Reading Excel File on Application Server

Clemenss — Sat, 21 Jul 2012 19:50:45 GMT

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

Re: Reading Excel File on Application Server

Ruediger_Plantiko — Sat, 21 Jul 2012 20:17:19 GMT

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/

Re: Reading Excel File on Application Server

Former Member — Sun, 22 Jul 2012 04:53:40 GMT

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

Re: Reading Excel File on Application Server

Ruediger_Plantiko — Sun, 22 Jul 2012 07:30:03 GMT

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:

The computer to access provides the files as an FTP, SFTP, or even HTTP server.
The computer to access works as RFC endpoint
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

Re: Reading Excel File on Application Server

Former Member — Sun, 22 Jul 2012 12:19:02 GMT

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 ?

Re: Reading Excel File on Application Server

Ruediger_Plantiko — Sun, 22 Jul 2012 19:58:29 GMT

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

Re: Reading Excel File on Application Server

Former Member — Fri, 24 Apr 2015 12:55:16 GMT

This message was moderated.