topic Read excel sheet from application server in Application Development and Automation Discussions

Read excel sheet from application server

Former Member — Tue, 04 Mar 2008 05:14:40 GMT

Hi,

i want to read an excel sheet(.xls) file from the application server. i tried to upload a test excel sheet into the application server using cg3z but the data contains ######## and some other garbage values.how am i supposed to upload the file correctly?and also can i use open,read and close dataset statements for the same?plz reply.

Thanks in advance,

Anand.

Re: Read excel sheet from application server

Former Member — Tue, 04 Mar 2008 05:24:17 GMT

U can do it as follows

To open the file

OPEN DATASET i_filenames-filename FOR INPUT IN TEXT MODE

ENCODING DEFAULT.

To read the file

DO.

CLEAR: lv_string.

READ DATASET i_filenames-filename INTO lv_string.

IF sy-subrc <> 0.

EXIT.

ENDIF. "sy-subrc

CHECK NOT lv_string IS INITIAL.

SPLIT lv_string AT c_tabchar INTO

wa_filerec-sold_to

wa_filerec-name1

wa_filerec-waers

wa_filerec-zmeng

wa_filerec-zieme

wa_filerec-kpein

wa_filerec-kmein

wa_filerec-werks

wa_filerec-posex

wa_filerec-lifnr

wa_filerec-abhod

wa_filerec-hdr_txt.

APPEND wa_filerec TO i_filedata.

ENDDO.

*To close the file

CLOSE DATASET i_filenames-filename.

Re: Read excel sheet from application server

Former Member — Tue, 04 Mar 2008 05:24:56 GMT

hi,

this is the program to upload a file in the application server.

When u use a tab delimited flat file u will het the # symbol.Check whether the excel sheet is correct ie.the fields occupy the correct column width..

report z_logical.

----

*TABLE DECLARATION

----

tables: zcust_master2.

----

*INTERNAL DECLARATION

----

data : begin of itab occurs 0,

text(255),

end of itab.

----

*CONSTANTS DECLARATION

----

constants: con_tab value cl_abap_char_utilities=>horizontal_tab.

----

*DATA DECLARATION

----

data: flag,

format(3),

fname(10000).

data : name1 type string.

----

*PARAMETER DECLARATION

----

data : flatfile like rlgrap-filename.

parameter : p_fname like filepath-pathintern.

write sy-opsys.

call function 'KD_GET_FILENAME_ON_F4'

exporting

program_name = syst-repid

dynpro_number = syst-dynnr

FIELD_NAME = ' '
STATIC = ' '
MASK = ' '

changing

file_name = flatfile

EXCEPTIONS
MASK_TOO_LONG = 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.

name1 = flatfile.

*&----

*& CALLING FUNCTIONAL MODULE 'gui_upload'

*&----

call function 'GUI_UPLOAD'

exporting

filename = name1

filetype = 'ASC'

tables

data_tab = itab

exceptions

file_open_error = 1

file_read_error = 2

no_batch = 3

gui_refuse_filetransfer = 4

invalid_type = 5

no_authority = 6

unknown_error = 7

bad_data_format = 8

header_not_allowed = 9

separator_not_allowed = 10

header_too_long = 11

unknown_dp_error = 12

access_denied = 13

dp_out_of_memory = 14

disk_full = 15

dp_timeout = 16

others = 17

if sy-subrc <> 0.

message id sy-msgid type sy-msgty number sy-msgno

with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

endif.

*&----

*& CALLING FUNCTIONAL MODULE 'FILE_GET_NAME'

*&----

call function 'FILE_GET_NAME'

exporting

logical_filename = 'ZARU_BANKS1'

operating_system = sy-opsys

with_file_extension = '.txt'

importing

emergency_flag = flag

file_format = format

file_name = fname

exceptions

file_not_found = 1

others = 2.

if sy-subrc = 0.

write: / 'Flag :', flag,

/ 'Format :', format,

/ 'Phys. Name:', fname.

endif.

*&----

*& OPENING A FILE

*&----

open dataset fname for output in text mode encoding default.

loop at itab.

transfer itab-text to fname.

endloop.

if sy-subrc = 0.

write : 'file is opened'.

endif.

if sy-subrc <> 0.

exit.

endif.

*&----

*& CLOSING A FILE

*&----

close dataset fname.

Hope this helps u,

Arunsri

Re: Read excel sheet from application server

Former Member — Tue, 04 Mar 2008 05:25:04 GMT

Hi Anand,

My guess is, you are uploading a tab delimited excel file to the application server.

Usually, tab is converted to ##### while uploading. Instead of that, you can convert the excel

CSV format and you will get comma now.

You can use dataset statements.

If you need any more help, get back to me.

Regards,

Jallu

Re: Read excel sheet from application server

Former Member — Tue, 04 Mar 2008 05:30:21 GMT

try this

PARAMETERS: p_file TYPE rlgrap-filename.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

field_name = 'P_FILE'

IMPORTING

file_name = p_file.

DATA: it_raw TYPE truxs_t_text_data.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

i_line_header = 'X'

i_tab_raw_data = it_raw " WORK TABLE

i_filename = p_file

TABLES

i_tab_converted_data = i_uptb[] "ACTUAL DATA

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.