topic Re: excel upload in Application Development and Automation Discussions

excel upload

Former Member — Fri, 26 Oct 2007 10:50:11 GMT

Hi ,

How to upload excel file from local pc into internal table in sap

Re: excel upload

Former Member — Fri, 26 Oct 2007 10:53:11 GMT

Use Function Module

FAA_FILE_UPLOAD_EXCEL

CALL FUNCTION 'FAA_FILE_UPLOAD_EXCEL'

EXPORTING

I_FILENAME = Yor Excel File

I_TEST =
I_UCOMM =
I_MAXCOLS =
I_DELIMITER = ','

TABLES

ET_FILECONTENT = internal Table

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

Nikhil

Re: excel upload

Former Member — Fri, 26 Oct 2007 10:54:34 GMT

Hi,

File manuplations...............................

1. At the presentation level:

->GUI_UPLOAD

->GUI_DOWNLOAD

->CL_GUI_FRONTEND

2. At the application server level:

->OPEN DATASET : open a file in the application server for reading or writing.

->READ DATASET : used to read from a file on the application server that has been opened for reading

-> TRANSFER DATASET : writing data to a file.

-> CLOSE DATASET : closes the file

-> DELETE DATASET : delete file

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

If file is on the local PC,use the function module GUI_UPLOAD to upload it into an internal table by passing the given parameters......

call function 'GUI_UPLOAD'

exporting

filename = p_file

filetype = 'ASC'

has_field_separator = '#'

tables

data_tab = t_data

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

p_file : excel file path.

t_data : internal table

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

reward points if useful.

regards,

Vinod Samuel.

Re: excel upload

Former Member — Fri, 26 Oct 2007 10:56:36 GMT

Hi Soumya,

use ALSM_EXCEL_TO_INTERNAL_TABLE.

Reward points for helpful answers

Satish

Re: excel upload

varma_narayana — Fri, 26 Oct 2007 11:04:36 GMT

Call the FM :

GUI_UPLOAD

KCD_EXCEL_OLE_TO_INT_CONVERT

REWARD IF HELPFUL.

Re: excel upload

Former Member — Fri, 26 Oct 2007 11:26:14 GMT

First save the excel sheet to text(Tab delimited) file..for eg:data4.txt

delete headings which was there in excell from this notepad..save only data

eg)

mandt pernr

300 13222

400 1444

after deleting the headings it will be

300 1322

400 1444

...and call the function as shown below

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

FILENAME = 'c:\data4.txt'

FILETYPE = 'ASC'

HAS_FIELD_SEPARATOR = '#'

HEADER_LENGTH = 0
READ_BY_LINE = 'X'
DAT_MODE = ' '
CODEPAGE = ' '
IGNORE_CERR = ABAP_TRUE
REPLACEMENT = '#'
CHECK_BOM = ' '
VIRUS_SCAN_PROFILE =
NO_AUTH_CHECK = ' '
IMPORTING
FILELENGTH =
HEADER =

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.

pls note..the order of the data columns in notepad should match with itab field order...

Merlin

Re: excel upload

Former Member — Fri, 26 Oct 2007 12:27:20 GMT

This is a great program it will download and upload to and from tables.

Please reward points if helpful.

REPORT ZTMM_UPLOAD_TABS NO STANDARD PAGE HEADING.

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

ABAP name : ZTMM_UPLOAD_TABS *
*
Created by : XXXXXXXX *
*
Date : XXXXXXXXXXX *
*
Description : Generic Table Upload/Download Utility *
*

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

MODIFICATIONS *

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

Date * Author * Marking code * Description *

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

* * * *

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

*----

TABLES -

TABLES : dd02t.

*----

DATA -

DATA : wt_fieldcat TYPE lvc_t_fcat ,

ws_fieldcat TYPE lvc_s_fcat ,

BEGIN OF wt_tablist OCCURS 0 ,

tabname TYPE tabname ,

ddtext TYPE as4text ,

END OF wt_tablist .

FIELD-SYMBOLS : TYPE ANY . " Structure de la table

*----

SELECTION SCREEN -

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-t01.

PARAMETERS p_table TYPE tabname.

SELECTION-SCREEN END OF BLOCK b1.

SELECTION-SCREEN SKIP 1.

SELECTION-SCREEN BEGIN OF BLOCK b2 WITH FRAME TITLE text-t02.

PARAMETERS : p_imp RADIOBUTTON GROUP ra1 ,

p_exp RADIOBUTTON GROUP ra1

DEFAULT 'X' ,

p_clear AS CHECKBOX .

SELECTION-SCREEN END OF BLOCK b2.

SELECTION-SCREEN SKIP 1.

SELECTION-SCREEN BEGIN OF BLOCK b3 WITH FRAME TITLE text-t03.

PARAMETERS : p_file TYPE localfile

OBLIGATORY .

SELECTION-SCREEN SKIP 1.

PARAMETERS : p_excl RADIOBUTTON GROUP ra2 ,

p_flat RADIOBUTTON GROUP ra2 .

SELECTION-SCREEN END OF BLOCK b3.

*----

MAIN -

START-OF-SELECTION.

Checking of the options of selection.

PERFORM p_check.

Unloading of the table.

IF p_exp EQ 'X'.

PERFORM p_downloading.

Loading of the table.

ELSE.

PERFORM p_upload.

ENDIF.

END-OF-SELECTION.

*----

Form P_CHECK. *

*----

FORM p_check.

DATA : wlv_tabname TYPE tabname.

Check that the table exists

SELECT tabname

UP TO 1 ROWS

INTO wlv_tabname

FROM dd02t

WHERE tabname EQ p_table

AND as4local EQ 'A'.

ENDSELECT.

IF sy-subrc NE space.

WRITE : /3 text-001.

STOP.

ENDIF.

One treats only the loading of the Z* tables.

IF p_imp EQ 'X' AND p_table+0(1) NE 'Z'.

WRITE : /1 text-007.

STOP.

ENDIF.

ENDFORM. " P_CHECK.

*----

Form P_DOWNLOADING. *

*----

Uploading of the table. *

*----

FORM p_downloading.

Creation of the table interns dynamic type of the table.

PERFORM p_create_dyntable

USING p_table.

Reading of the table (and yes pretty a select *! )

SELECT *

INTO TABLE

FROM (p_table).

If download in format Excel.

IF p_excl EQ 'X'.

CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'

EXPORTING

i_filename = p_file

TABLES

i_tab_sap_data =

EXCEPTIONS

conversion_failed = 1

OTHERS = 2.

IF sy-subrc NE space.

WRITE : /3 text-003.

ENDIF.

If flat file.

ELSEIF p_flat EQ 'X'.

CALL FUNCTION 'WS_DOWNLOAD'

EXPORTING

filename = p_file

filetype = 'DAT'

TABLES

data_tab =

EXCEPTIONS

OTHERS = 10.

IF sy-subrc NE space.

WRITE : /3 text-003.

ENDIF.

ENDFORM. " P_DECHARGEMENT.

*----

Form P_UPLOAD. *

*----

Chargement. *

*----

FORM p_upload.

DATA : wlv_count_col TYPE i ,

wlv_flag_row TYPE kcd_ex_row_n ,

wlt_file TYPE TABLE OF alsmex_tabline

WITH HEADER LINE ,

wlv_char1 TYPE char1.

FIELD-SYMBOLS : TYPE ANY.

Creation of the table interns dynamic type of the table

PERFORM p_create_dyntable

USING p_table.

Lecture du fichier.
Si déchargen en format Excel.

IF p_excl EQ 'X'.

Seek number of column.

DESCRIBE TABLE wt_fieldcat LINES wlv_count_col.

Function to read the Microsoft Excel file.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = p_file

i_begin_col = '1'

i_begin_row = '1'

i_end_col = wlv_count_col

i_end_row = '10000'

TABLES

intern = wlt_file

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

IF sy-subrc NE space.

WRITE : /3 text-003.

STOP.

ENDIF.

Set data.

LOOP AT wlt_file.

IF wlt_file-row NE wlv_flag_row.

MOVE wlt_file-row TO wlv_flag_row.

IF wlt_file-row NE 1.

APPEND .

ENDCATCH.

IF sy-subrc EQ 1.

" A FAIRE

ENDIF.

ENDLOOP.

Append last time.

APPEND .

Si fichier plat.

ELSEIF p_flat EQ 'X'.

CALL FUNCTION 'WS_UPLOAD'

EXPORTING

filename = p_file

filetype = 'DAT'

TABLES

data_tab =

EXCEPTIONS

OTHERS = 10.

IF sy-subrc NE space.

WRITE : /3 text-003.

STOP.

ENDIF.

Si vide la table avant.

IF p_clear EQ 'X'.

Verification avant la suppression de la table.

CALL FUNCTION 'POPUP_TO_CONFIRM'

EXPORTING

titlebar = text-004

text_question = text-005

IMPORTING

answer = wlv_char1

EXCEPTIONS

text_not_found = 1

OTHERS = 2.

IF wlv_char1 EQ '2'.

STOP.

ENDIF.

On supprime tout.

DELETE FROM (p_table) CLIENT SPECIFIED

WHERE mandt EQ sy-mandt.

IF sy-subrc NE space.

WRITE : /3 text-003.

STOP.

ENDIF.

Chargement de la base.

MODIFY (p_table) FROM TABLE .

IF sy-subrc NE space.

WRITE : /3 text-003.

STOP.

ENDIF.

ENDFORM. " P_CHARGEMENT.

*----

Form P_CREATE_DYNTABLE. *

*----

FORM p_create_dyntable

USING wpv_tabname TYPE tabname.

DATA : wlt_table TYPE REF TO data ,

wls_table TYPE REF TO data .

On assigne le nom de la table.

IF .

Recherche de la description du dictionnaire.

REFRESH wt_fieldcat.

CALL FUNCTION 'LVC_FIELDCATALOG_MERGE'

EXPORTING

i_structure_name =

i_bypassing_buffer = 'X'

CHANGING

ct_fieldcat = wt_fieldcat

EXCEPTIONS

inconsistent_interface = 1

program_error = 2

OTHERS = 3.

IF sy-subrc NE space.

WRITE : /1 text-003.

STOP.

ENDIF.

Création de la table interne dynamique.

IF .

Création de la structure du type de la table.

IF .

ENDFORM. " P_CREATE_DYNTABLE.

*----

EVENTS -

Au démarage de l'application.

INITIALIZATION.

On desactive la zone vider la table, elle n'est active que pour
le chargement.

LOOP AT SCREEN.

IF screen-name EQ 'P_CLEAR'.

MOVE '0' TO screen-active.

MODIFY SCREEN.

ENDIF.

ENDLOOP.

A l'événement rafraichissement de l'écran.

AT SELECTION-SCREEN OUTPUT.

LOOP AT SCREEN.

Si on charge alors on active la zone P_CLEAR.

IF p_imp EQ 'X'.

IF screen-name EQ 'P_CLEAR'.

MOVE '1' TO screen-active.

MODIFY SCREEN.

ENDIF.

ELSE.

IF screen-name EQ 'P_CLEAR'.

MOVE '0' TO screen-active.

MODIFY SCREEN.

ENDIF.

ENDLOOP.

Evenement F4 sur P_FILE.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

CALL FUNCTION 'F4_FILENAME'

IMPORTING

file_name = p_file.

Re: excel upload

Former Member — Fri, 26 Oct 2007 12:27:30 GMT

Hi,

It is better to use class functions to do that,

refer the below code

CALL METHOD cl_gui_frontend_services=>file_open_dialog

EXPORTING

window_title = 'Select file'

default_extension = '.xls'

default_file_name = lv_path

initial_directory = lc_c

CHANGING

filename = lv_path

path = lc_c

fullpath = lv_fullpath

EXCEPTIONS

cntl_error = 1

error_no_gui = 2

OTHERS = 3.

IF sy-subrc <> 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ELSE.

v_fnam = lv_fullpath.

ENDIF.

IF v_fnam IS INITIAL.

RETURN.

ENDIF.

CALL METHOD cl_gui_frontend_services=>gui_upload

EXPORTING

filename = v_fnam

filetype = 'DAT'

HEADER = header

append = 'X'

write_field_separator = 'X'

CHANGING

data_tab = i_finaltable

EXCEPTIONS

OTHERS = 8.

regards,

sheron