topic excel upload in Application Development and Automation Discussions

excel upload

Former Member — Wed, 28 Nov 2007 03:07:14 GMT

hi can anybody suggest me how to upload/download excel file

Re: excel upload

Former Member — Wed, 28 Nov 2007 05:49:50 GMT

UPLOAD ABAP code for uploading an Excel document into an internal table. See code below for structures. The

code is based on uploading a simple Excel spreadsheet or for an actual Excel file click here.

There are also a couple of alternatives which use fucntion modules 'KCD_EXCEL_OLE_TO_INT_CONVERT'

and 'ALSM_EXCEL_TO_INTERNAL_TABLE' but the method below is by far the simplest method to used.

A big thanks to Jayanta for bringing this method to my attention.

*..............................................................

*: Description :

*: -

*: This is a simple example program to get data from an excel :

*: file and store it in an internal table. :

*: :

*: Author : www.sapdev.co.uk, based on code from Jayanta :

*: :

*: SAP Version : 4.7 :

*:............................................................:

REPORT zupload_excel_to_itab.

TYPE-POOLS: truxs.

PARAMETERS: p_file TYPE rlgrap-filename.

TYPES: BEGIN OF t_datatab,

col1(30) TYPE c,

col2(30) TYPE c,

col3(30) TYPE c,

END OF t_datatab.

DATA: it_datatab type standard table of t_datatab,

wa_datatab type t_datatab.

DATA: it_raw TYPE truxs_t_text_data.

At selection screen

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

field_name = 'P_FILE'

IMPORTING

file_name = p_file.

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

*START-OF-SELECTION.

START-OF-SELECTION.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_FIELD_SEPERATOR =

i_line_header = 'X'

i_tab_raw_data = it_raw " WORK TABLE

i_filename = p_file

TABLES

i_tab_converted_data = it_datatab[] "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.

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

END-OF-SELECTION.

END-OF-SELECTION.

LOOP AT it_datatab INTO wa_datatab.

WRITE:/ wa_datatab-col1,

wa_datatab-col2,

wa_datatab-col3.

ENDLOOP.

DOWNLOAD

TABLES : mara.

DATA: ld_filename TYPE string VALUE 'C:\TEMP\DATA.XLS'.

DATA: itab LIKE mara OCCURS 0 WITH HEADER LINE.

SELECT * FROM mara INTO TABLE itab.

CALL FUNCTION 'GUI_DOWNLOAD'

EXPORTING

filename = ld_filename

filetype = 'ASC'

WRITE_FIELD_SEPARATOR = ';' "Add this line

TABLES

data_tab = itab[]

EXCEPTIONS

file_open_error = 1

file_write_error = 2

OTHERS = 3.

Reward if usefull

Re: excel upload

Former Member — Wed, 28 Nov 2007 05:57:56 GMT

Hi,

You can download to an excel sheet using the function module GUI_DOWNLOAD. Set the parameter WRITE_COLUMN_SEPARATOR as "X", and give a file name with the .xls extension. Now if you want to upload that file back into your program, use GUI_UPLOAD and set the HAS_COLUMN_SEPARATOR to "X". I believe the GUI_UPLOAD will only work correctly if the file that you are uploading was actually created by the GUI_DOWNLOAD function module. Otherwise, it will not recognize the separator.

Check demo programs

GREXCEL0

GREXCEL1

Regards,

Omkar.

Re: excel upload

varma_narayana — Wed, 28 Nov 2007 06:16:38 GMT

Hi..

We have the Class CL_GUI_Frontend_Services which has various methods for communicating with Presentation server files (Excel, Notepad etc)

Check the methods of this class in SE24.

CL_GUI_Frontend_Services=>GUI_Upload

CL_GUI_Frontend_Services=>GUI_Download

Re: excel upload

Former Member — Wed, 28 Nov 2007 10:00:58 GMT

Hi,

You can use function modules GUI_UPLOAD & GUI_DOWNLOAD to upload & download a file respectively.

Sample code for uploading a file:

REPORT y689_upload.

tables: y689emp1.

PARAMETERS: p_infile LIKE rlgrap-filename OBLIGATORY.

DATA: gd_file type string.

*Internal tabe to store upload data

TYPES: BEGIN OF t_record,

EMPID LIKE y689emp1-EMPID,

NAME like y689emp1-NAME,

age like y689emp1-age,

role like y689emp1-role,

experience like y689emp1-experience,

END OF t_record.

DATA: it_record TYPE STANDARD TABLE OF t_record INITIAL SIZE 0,

wa_record TYPE t_record.

*Internal table to upload data into

DATA: BEGIN OF it_datatab OCCURS 0,

row(500) TYPE c,

END OF it_datatab.

*Text version of data table

TYPES: BEGIN OF t_uploadtxt,

empid TYPE i,

name(50) TYPE c,

age TYPE i,

role(30) type c,

experience type i,

END OF t_uploadtxt.

DATA: wa_uploadtxt TYPE t_uploadtxt.

*String value to data in initially.

DATA: wa_string(255) TYPE c.

CONSTANTS: con_tab TYPE x VALUE '09'.

*If you have Unicode check active in program attributes then you will

*need to declare constants as follows:

*class cl_abap_char_utilities definition load.

*constants:

con_tab type c value cl_abap_char_utilities=>HORIZONTAL_TAB.

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

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_infile.

CALL FUNCTION 'WS_FILENAME_GET'

EXPORTING

def_filename = p_infile

mask = ',*.txt.'

mode = 'O'

title = 'Upload File'(078)

IMPORTING

filename = p_infile

EXCEPTIONS

inv_winsys = 1

no_batch = 2

selection_cancel = 3

selection_error = 4

OTHERS = 5.

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

START-OF-SELECTION.

gd_file = p_infile.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = gd_file

has_field_separator = 'X' "file is TAB delimited

TABLES

data_tab = it_record

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 NE 0.

write: 'Error ', sy-subrc, 'returned from GUI_UPLOAD FM'.

else.

write: 'Success'.

endif.

END-OF-SELECTION.

*!! Text data is now contained within the internal table IT_RECORD

Display report data for illustration purposes

LOOP AT it_record INTO wa_record.

WRITE:/ sy-vline,

(10) wa_record-empid, sy-vline,

(50) wa_record-name, sy-vline,

(3) wa_record-age, sy-vline,

(30) wa_record-role, sy-vline,

(2) wa_record-experience, sy-vline.

ENDLOOP.

To update the data base table

loop at it_record into wa_record.

move wa_record-empid to y689emp1-empid.

move wa_record-name to y689emp1-name.

move wa_record-age to y689emp1-age.

move wa_record-role to y689emp1-role.

move wa_record-experience to y689emp1-experience.

modify y689emp1.

endloop.

Sample code for downloading a file:

REPORT Y689TEST1.

tables: mara.

data: begin of itab occurs 0,

matnr like mara-matnr,

ERSDA like mara-ERSDA,

ERNAM like mara-ERNAM,

MTART like mara-MTART,

MATKL like mara-MATKL,

MEINS like mara-MEINS,

NTGEW like mara-NTGEW,

end of itab.

select-options: s_matnr for mara-matnr.

parameters: display as checkbox,

file as checkbox, " Do you need download

filename like rlgrap-filename. " download filename

start-of-selection.

select matnr ersda ernam mtart matkl meins ntgew

from mara

into table itab

where matnr in s_matnr.

if not display is initial.

perform display.

endif.

if not file is initial.

perform file_download.

endif.

&----

*& Form file_download

&----

text

----

--> p1 text
<-- p2 text

----

form file_download .

data: filename1 type string.

filename1 = filename.

CALL FUNCTION 'GUI_DOWNLOAD'

EXPORTING

filename = filename1

FILETYPE = 'DAT'

tables

data_tab = itab

EXCEPTIONS

FILE_WRITE_ERROR = 1

NO_BATCH = 2

GUI_REFUSE_FILETRANSFER = 3

INVALID_TYPE = 4

NO_AUTHORITY = 5

UNKNOWN_ERROR = 6

HEADER_NOT_ALLOWED = 7

SEPARATOR_NOT_ALLOWED = 8

FILESIZE_NOT_ALLOWED = 9

HEADER_TOO_LONG = 10

DP_ERROR_CREATE = 11

DP_ERROR_SEND = 12

DP_ERROR_WRITE = 13

UNKNOWN_DP_ERROR = 14

ACCESS_DENIED = 15

DP_OUT_OF_MEMORY = 16

DISK_FULL = 17

DP_TIMEOUT = 18

FILE_NOT_FOUND = 19

DATAPROVIDER_EXCEPTION = 20

CONTROL_FLUSH_ERROR = 21

OTHERS = 22.

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. " file_download

&----

*& Form display

&----

text

----

--> p1 text
<-- p2 text

----

form display .

loop at itab.

write:/ itab-matnr,

itab-ersda,

itab-ernam,

itab-mtart,

itab-matkl,

itab-meins,

itab-ntgew.

endloop.

endform. " display

Reward if useful.

Regards

Sayee