topic Re: Read Excel File in Application Development and Automation Discussions

Read Excel File

Former Member — Wed, 15 Jun 2005 13:03:24 GMT

hi,

I can read an excel file by using this code in FORGROUND:

CREATE OBJECT gv_excel 'EXCEL.APPLICATION'.

SET PROPERTY OF gv_excel 'VISIBLE' = 0.

CALL METHOD OF gv_excel 'WORKBOOKS' = gv_books.

CALL METHOD OF gv_books 'OPEN'

EXPORTING

#1 = p_file

#2 = 1.

Do.

...

ENDDO.

But when i lunch my program in Background, I can't read the file.

idea?

Thank you

Joseph

Re: Read Excel File

RichHeilman — Wed, 15 Jun 2005 13:06:53 GMT

From where are you reading this file from? The local PC? Like other methods of reading files from the PC, you can not do it in background. The background process is not "attached" to a PC, and hence does not know where "C:\test.xls" is.

You can read files from the application server in background using the OPEN DATASET, READ DATASET commmands.

Regards,

Rich Heilman

Re: Read Excel File

Former Member — Wed, 15 Jun 2005 13:09:12 GMT

thank you,

but if use OPEN DATASET i must transform my Excel file to a text file?

Re: Read Excel File

RichHeilman — Wed, 15 Jun 2005 13:16:02 GMT

What is the code inside your DO loop.

Regards,

Rich Heilman

Re: Read Excel File

Former Member — Wed, 15 Jun 2005 13:30:35 GMT

The code is like this:

DO.

CALL METHOD OF gv_excel 'CELLS' = gv_cell

EXPORTING

#1 = p_row

#2 = p_col.

GET PROPERTY OF gv_cell 'VALUE' = p_val.

move p_val to struct-name.

append struc to tab.

ENDDO.

Message was edited by: joseph fryda

Re: Read Excel File

RichHeilman — Wed, 15 Jun 2005 14:35:54 GMT

I'v tested it, and I am getting junk when reading it from application server using READ DATASET. Maybe someone has a tip/trick?

Regards,

Rich Heilman

Re: Read Excel File

Former Member — Wed, 15 Jun 2005 14:45:34 GMT

Have you aleady tried to upload the xls file to your app server via FTP?

Re: Read Excel File

Former Member — Wed, 15 Jun 2005 14:53:02 GMT

sorry to ask but how do you do that?

Re: Read Excel File

Former Member — Wed, 15 Jun 2005 14:56:47 GMT

How do you do what?! Uploading the file via FTP?!

Re: Read Excel File

Former Member — Wed, 15 Jun 2005 15:04:58 GMT

yes. But I will ask to receive a text file instead an excel it's more easy to read it.

...

even if can upload the excel file via ftp, what it's give me? Can I read the excel file in background?

Re: Read Excel File

Former Member — Wed, 15 Jun 2005 15:34:15 GMT

Yes, in thix case you will be able to read the file in btc processing!

Re: Read Excel File

Former Member — Wed, 15 Jun 2005 21:35:35 GMT

Hi Joseph,

As far as I understood,

Please use open dataset,read dataset when reading from the application server and please make sure you get the file saved in .dat format. You can also get the file saved in txt format but when uploading you have to use split statement with '' in between each field.

If you still want to read it from presentation server please use WS_UPLOAD in which you can read it in excel or txt or ansi format.

AT the end of the day, it is you who should decide whether you want to read it from appliocation or presentation server or in txt format or xls format or dat format.

Please let us know if you have any further questions or concerns.

Sincerely,

Bhavana

Re: Read Excel File

Former Member — Thu, 16 Jun 2005 07:58:24 GMT

hi bhavana,

I proceed as follow (because we will used this prog just one time):

1/ save my excel in txt file TAB DELIMITED

2/ create a abap program that read this file and put the data in internal table that contains the excact structure that I want (length, zero leading....)

3/ then from this internal table I create a NEW file that i save on the server.

4/ now I can read read the file in a correct format with "open dataset".

As i said, this program will be used one time, so what the hell, it took me 10 minutes to do that and everyone is happy

by the way WS_UPLOAD is obsolete. I use

CALL METHOD cl_gui_frontend_services=>gui_upload.

thank you, i appriciated your help

joseph

Re: Read Excel File

Former Member — Thu, 16 Jun 2005 08:54:48 GMT

Hi Joseph,

if you call Excel this way, you use the OLE2-Automation/SAP Desktop Office Integration to communicate with Excel. Using this technology requires a SAPGui-Connection as in this case the Gui "scripts" Excel (Word etc). When your program runs in background this link is not available therefor the OLE2-Automation fails.

Reading the file with open dataset from the application server does not help as you cannot open Excel for editing (the same holds true if you retrieve the file via ftp or http). Uploading the file from the Desktop (WS_UPLOAD/GUI_UPLOAD/CL_GUI_FRONTEND_SERVICES=>GUI_UPLOAD dependent on the R/3-Basis Release you use) on the other hand only works if a Gui is connected (so not if the report runs in background processing).

My suggestion would be dependent on the Office Version for downloading data to use either:

Create a tab-separated text in an internal table and write the content as a textfile with extension ".xls" to the location where you want to have the Excel. If you then open the file via double-click Excel usualy imports it by using the tab as field-separator (as of ABAP-Basis 620 cl_abap_char_utilities=>horizontal_tab).

As an alternative you could check out the XML-schema for MS Excel and build an XML by using the iXML-library (or by lots of concatenations and manual masking). This requires (as far as I know) for the iXMl library ABAP Basis-Release 4.6 or higher and for Excel XP or higher.

For uploading data just go the other way round, save the Excel as Tab-Separated Text or as XML-file in your external Application and use the split/iXML-Library to extract the data.

In case your report runs in batch you only can use:

open dataset or http/ftp requests to exchange files. Please keep in mind that for using these possibilities you have to be able to access the resources from the application server the program is running on (firewalls/authorization/missing proxies etc. may cause problems).

Hope that helps.

Best Regards

Michael

Re: Read Excel File

graghavendra_sharma — Tue, 30 May 2006 12:56:38 GMT

Can you pls send me all the methods related to excel file reading, using OLE interface.

Thanks in advance

Raghav