read dataset problem ##### symbol appears

[bandal_amol]

Hello Developers,

Currently i am working on report in that i want read excel (.xls) file from application server.

but problem is that when i tried to read .xls file from application server it's showing such symbol ÐÏ#ࡱ#á################.

Please find my attacched code here.

Open dataset p_file1 in text mode for input encoding non-unicode ignoring conversion errors.

   if sy-subrc <> 0.

     message 'File is not Valid' type 'E'.

   endif.

   do.

     read dataset p_file into v_record.     " at this point i's show ####### symbol

     if sy-subrc ne 0.

       exit.

     endif.

     split v_record at '#' into  t_field1-kunnr  t_field1-vkorg t_field1-vtweg t_field1-spart  t_field1-klabc.

     append t_field1  to t_field.

   enddo.

   close dataset p_file.

looking for your reply.

regards,

Amol

[bandal_amol]

hello all ,

finally i got solution to read .xls file from from appllication server.

my file format will be like this : 170172;2020;01;20;C+.

*****************************************************************************************

data: p_file1 type filename-fileintern.

selection-screen begin of block b1 with frame title text-001.

parameters : p_file   type rlgrap-filename,                  " File Path

              p_mode   type c default 'N'.         " Mode

selection-screen end of block b1.

at selection-screen.

   if p_mode = 'A' or p_mode = 'N'.

   else.

     message 'Please Enter The Mode A or N' type 'E'.

   endif.

at selection-screen on value-request for p_file.

   call function 'F4_FILENAME'

     exporting

       program_name  = syst-cprog

       dynpro_number = syst-dynnr

       field_name    = ' '

     importing

       file_name     = p_file.

start-of-selection.

p_file1 = p_file.

open dataset p_file in text mode for input encoding default.

   if sy-subrc <> 0.

     message 'File is not Valid' type 'E'.

   endif.

   do.

     read dataset p_file into v_record.

     if sy-subrc ne 0.

       exit.

     endif.

     split v_record at ';' into t_field1-kunnr t_field1-vkorg t_field1-vkorg t_field1-vtweg t_field1-spart  t_field1-klabc.

     append t_field1  to t_field.

   enddo.

   close dataset p_file.

************************************************************************************************

This code worked for my requiremnt. for me solution is ok.

Thanking you all.

Regards,

@mol

[Former Member]

Hi ,

Please post the excel file data,at least one record.

from application server perspective + is nothing but vertical tab or horizontal tab.

regards,

Lokesh

[bandal_amol]

Hello lokeswar,

.Xls file contains this data.

170172;2020;01:A1:C+

[yogendra_bhaskar]

Hi Amol ,

Convert the xls file to csv and then try

Regards

Yogendra Bhaskar

[Former Member]

Hi Amol

Seems 'open dataset' only works for .csv. uh, question is how you can change the excel from .xls to .csv in application server.

regards,

Archer

[bandal_amol]

hello Dengyong ,

yes. you are are right even i tried to execute with .csv file it's working fine. my file is on application server i dont want to change it.

i wanted to upload it with my logic, but its not working.

Thanking you,

regards,

@amol

[matt]

Why on earth do you think that you can use a simple OPEN DATASET and READ to read a complicated non-flat file like an xls or xlsx? xls is a proprietry binary format - it needs a program like MS Excel to read it.

[matt]

Dengyong Zhang wrote:

Hi Amol
Seems 'open dataset' only works for .csv. uh,

regards,
Archer

Of course it does. Why would you expect anything else. XLS is a proprietry BINARY format.

Dengyong Zhang wrote:

question is how you can change the excel from .xls to .csv in application server.

regards,
Archer

Unless you have Excel installed on the application server, or some program that can do the conversion - you can't.

[Former Member]

I just see something from other thread, 'Open dataset’ code which reads from app server will only cater to these excel files whose value are separated by tab separator.  Just try this?

[matt]

wrong

OPEN DATASET in this context will only work with ASCII flat-files: such as tab delimited, comma separated files etc.

[Former Member]

The issue is caused by a difference in the way you are reading the file.

Ensure your reading the file using the correct code page.

[bandal_amol]

hello all ,

finally i got solution to read .xls file from from appllication server.

my file format will be like this : 170172;2020;01;20;C+.

*****************************************************************************************

data: p_file1 type filename-fileintern.

selection-screen begin of block b1 with frame title text-001.

parameters : p_file   type rlgrap-filename,                  " File Path

              p_mode   type c default 'N'.         " Mode

selection-screen end of block b1.

at selection-screen.

   if p_mode = 'A' or p_mode = 'N'.

   else.

     message 'Please Enter The Mode A or N' type 'E'.

   endif.

at selection-screen on value-request for p_file.

   call function 'F4_FILENAME'

     exporting

       program_name  = syst-cprog

       dynpro_number = syst-dynnr

       field_name    = ' '

     importing

       file_name     = p_file.

start-of-selection.

p_file1 = p_file.

open dataset p_file in text mode for input encoding default.

   if sy-subrc <> 0.

     message 'File is not Valid' type 'E'.

   endif.

   do.

     read dataset p_file into v_record.

     if sy-subrc ne 0.

       exit.

     endif.

     split v_record at ';' into t_field1-kunnr t_field1-vkorg t_field1-vkorg t_field1-vtweg t_field1-spart  t_field1-klabc.

     append t_field1  to t_field.

   enddo.

   close dataset p_file.

************************************************************************************************

This code worked for my requiremnt. for me solution is ok.

Thanking you all.

Regards,

@mol