topic Re: Dataset Problem while reading an excel file in Application Development and Automation Discussions

Dataset Problem while reading an excel file

Former Member — Mon, 22 Mar 2010 10:11:36 GMT

Hi Friends,

I have a excel sheet in one of the directory in application server...

Now iam opening the data in text mode to read line by line of the excel..

Open data Zsample in text mode encoding default

Now iam able to open a file here but when i try to read data using read dataset iam getting a short dump here...

The dump error is cannot convert to code page 4102.

Now how to over come this senario... i tried using UTF-8 also but iam getting the same dump.. UTF-16 is giving me a syntax error.

when i tried to open the same excel sheet using AL11 manually iam getting the same dump as above..

Now my question is can we read an excel file from application server... if so how to proceed further .. ?

Suggestions will be appreciated..

Regards

Kumar

Re: Dataset Problem while reading an excel file

Former Member — Mon, 22 Mar 2010 10:13:21 GMT

Hello,

Try using the IGNORING CONVERSION ERROR addition in the open dataset statement and check


Open dataset zsample in text mode encoding default ignoring conversion errors.

Vikranth

Re: Dataset Problem while reading an excel file

rainer_hbenthal — Mon, 22 Mar 2010 10:27:32 GMT

Are you really reading an excel file or a CSV file?

Re: Dataset Problem while reading an excel file

Former Member — Mon, 22 Mar 2010 14:10:26 GMT

Hi Vikranth,

I used the syntax Ignorning Conversion Errors

By that syntax iam able to read the data from the excel file and appending into internal table itab.

But here iam getting the data as ####%##74##############+#######$@####4@####4@#####@###

How can i correct it..

Regards

Kumar

Re: Dataset Problem while reading an excel file

rainer_hbenthal — Mon, 22 Mar 2010 14:36:18 GMT

is the character indicating a character which could not be translated. ignoring conversion error is not a miracle which eliminates errors in a secret manner.

Re: Dataset Problem while reading an excel file

Former Member — Mon, 22 Mar 2010 14:45:47 GMT

Hi Rainer,

How to over come this error...

can any one suggest me..

Regards

kumar

Re: Dataset Problem while reading an excel file

rainer_hbenthal — Mon, 22 Mar 2010 15:00:43 GMT

Why didnt you answer my question about excel and csv?

And if the system prompts you to install a codepage, you need to do that. Ignoring this will give hash sign for all characters which can not be converted.

Re: Dataset Problem while reading an excel file

Former Member — Mon, 22 Mar 2010 15:44:30 GMT

Hi Rainer,

My Question is Dataset Problem while reading an excel file

Yes it is a excel file..

How to correct this..

Regards

Kumar

Re: Dataset Problem while reading an excel file

Former Member — Mon, 22 Mar 2010 16:50:47 GMT

Hi Kumar

I think that read the excel(native) file by Open Dataset is impossible (not in text mode) because it isn't be a text file and without OLE2 you don't get any information from there.

Best Regards

Szymon Glapiak

Re: Dataset Problem while reading an excel file

Former Member — Mon, 22 Mar 2010 17:06:43 GMT

Any further info on this...

or else how to covert ####%##74##############+#######$@####4@####4@#####@###

into charecter format..

Regards

Kumar

Re: Dataset Problem while reading an excel file

Former Member — Mon, 22 Mar 2010 17:31:22 GMT

kumar,

I am pretty sure that this cannot, be done. Excel (.XLS) is a Microsoft proprietary format that need to be properly translated. The open dataset, read, and transfer statement simply reads and right raw data. I believe it has no chance of being able to translate it properly. Your best option would be to save the Excel file into a CSV file prior to putting it on the application server. Then you can read it without problems using open dataset and read. Otherwise you will have a very very long row to hoe.

I also am assuming that this program is running as a background process or that the Excel file cannot be saved locally or on the network.. If it can be run interactive, then you have a shot using a FM such as ALSM_EXCEL_TO_INTERNAL_TABLE and others.

Re: Dataset Problem while reading an excel file

Former Member — Mon, 22 Mar 2010 21:22:57 GMT

any further approach on this...

Moderator message - Please do not offer po1nts

Regards

Kumar

Edited by: Rob Burbank on Mar 22, 2010 5:35 PM

Re: Dataset Problem while reading an excel file

Former Member — Tue, 23 Mar 2010 00:15:56 GMT

Why don't you try using open dataset in BINARY MODE.

OPEN DATASET p_file FOR INPUT IN BINARY MODE.

In this way you get all the data as an XSTRING and then to convert xstring to string data type use this [link|http://www.divulgesap.com/blog.php?p=NjA=].

Cheers,

Ravi

Re: Dataset Problem while reading an excel file

Former Member — Tue, 23 Mar 2010 12:29:15 GMT

u can use like that

data w_lfimg(17).

*Concatenating data and time into file.

concatenate filepath

sy-datum

sy-uzeit

'.csv'

into filepath.

htab = cl_abap_char_utilities=>horizontal_tab.

OPEN DATASET filepath FOR OUTPUT IN TEXT MODE ENCODING DEFAULT.

Re: Dataset Problem while reading an excel file

rainer_hbenthal — Tue, 23 Mar 2010 12:36:00 GMT

What part of

You cant upload/interpret an excel file via open dataset

you dont understand?

Transform the file into CSV which is readable.