excel data transfer into SAP internal table with GUI_UPLOAD

[Former Member]

hi all,

i m using SRM4 system and i wanted to develop one report which will upload data from excel and convert it into IT.

i know that many threads are posted on this topic.

but my requirement is slight different. in the system only one function module is available that is "GUI_UPLOAD" and we want that user shd not save file as tab delimited before calling this fm. instead, program shd take care of all these things...

please suggest something asap..

helpful ans will be rewarded..

thanks,

jigs.

[Former Member]

check anversha 2nd reply fullfill ur requirmrnt.

[Former Member]

see below code from one of the posted thread -

hi,

chk this, put the data into an excel file.

fields inside it are name and age.

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

sample excel sheet.

coloumn 1 is name and column 2 is age

name age

A 8

C 13

D 55

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

DATA : int_excel LIKE alsmex_tabline OCCURS 0 WITH HEADER LINE.

data : record like db_name_age occurs 0 with header line.

DATA : v_start_col TYPE i VALUE '1', "starting col

v_start_row TYPE i VALUE '1', " starting row

v_end_col TYPE i VALUE '2', " total columns

v_end_row TYPE i VALUE '10'. "total no of record

FORM f_upload .

CLEAR : int_excel, int_excel[].

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = wf_filename

i_begin_col = v_start_col

i_begin_row = v_start_row

i_end_col = v_end_col

i_end_row = v_end_row

TABLES

intern = int_excel

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

IF sy-subrc <> 0.

*Message is 'Unable to upload data from ' wf_filename.

MESSAGE e169(zm050) WITH wf_filename.

ELSE.

SORT int_excel BY row col.

REFRESH : record.

CLEAR : record.

LOOP AT int_excel.

CASE int_excel-col. "go thru each column.

WHEN 1.

record-name = int_excel-value.

WHEN 2.

record-age = int_excel-value.

ENDCASE.

AT END OF row.

APPEND record.

CLEAR record.

ENDAT.

ENDLOOP.

*inserting into table

modfiy db_name_age from table record.

ENDIF.

[Former Member]

try to use fm

ALSM_EXCEL_TO_INTERNAL_TABLE

regards

shiba dutta

[Former Member]

hi,

function modules 'ALSM_EXCEL_TO_INTERNAL_TABLE' , 'TEXT_CONVERT_XLS_TO_SAP' are not available in SRM4.

jigs

[Former Member]

use following method

data : DATA : MYDESKTOP TYPE STRING.

DATA: FNAME TYPE STRING,

DWNLD_FNAME TYPE STRING.

CALL METHOD CL_GUI_FRONTEND_SERVICES=>GET_DESKTOP_DIRECTORY

CHANGING

DESKTOP_DIRECTORY = MYDESKTOP

EXCEPTIONS

CNTL_ERROR = 1

ERROR_NO_GUI = 2

NOT_SUPPORTED_BY_GUI = 3

OTHERS = 4.

2 -step is

CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_SAVE_DIALOG

EXPORTING

• WINDOW_TITLE =

DEFAULT_EXTENSION = '.XLS'

DEFAULT_FILE_NAME = 'EST_Status_Update.XLS'

• FILE_FILTER =

INITIAL_DIRECTORY = MYDESKTOP

CHANGING

FILENAME = FNAME

PATH = MYDESKTOP

FULLPATH = DWNLD_FNAME

• USER_ACTION =

EXCEPTIONS

CNTL_ERROR = 1

ERROR_NO_GUI = 2

OTHERS = 3.

IF SY-SUBRC = 0 AND NOT DWNLD_FNAME IS INITIAL.

CALL FUNCTION 'GUI_DOWNLOAD'

EXPORTING

FILENAME = DWNLD_FNAME

FILETYPE = 'ASC'

WRITE_FIELD_SEPARATOR = 'X'

TABLES

DATA_TAB = IT_DISPLAY1.

[Former Member]

Dear Jigs,

Please go though the following lines of code:

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

• D A T A D E C L A R A T I O N *

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

TABLES: ANEP,

BKPF.

TYPES: BEGIN OF TY_TABDATA,

MANDT LIKE SY-MANDT, " Client

ZSLNUM LIKE ZSHIFTDEPN-ZSLNUM, " Serial Number

ZASSET LIKE ZSHIFTDEPN-ZASSET, " Original asset that was transferred

ZYEAR LIKE ZSHIFTDEPN-ZYEAR, " Fiscal Year

ZPERIOD LIKE ZSHIFTDEPN-ZPERIOD, " Fiscal Period

ZSHIFT1 LIKE ZSHIFTDEPN-ZSHIFT1, " Shift No. 1

ZSHIFT2 LIKE ZSHIFTDEPN-ZSHIFT1, " Shift No. 2

ZSHIFT3 LIKE ZSHIFTDEPN-ZSHIFT1, " Shift No. 3

END OF TY_TABDATA.

*----

---

• Declaration of the Internal Table with Header Line comprising of the uploaded data.

*----

---

DATA: BEGIN OF IT_FILE_UPLOAD OCCURS 0.

INCLUDE STRUCTURE ALSMEX_TABLINE. " Rows for Table with Excel Data

DATA: END OF IT_FILE_UPLOAD.

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

• S E L E C T I O N - S C R E E N *

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

SELECTION-SCREEN: BEGIN OF BLOCK B1 WITH FRAME,

BEGIN OF BLOCK B2 WITH FRAME.

PARAMETERS: P_FNAME LIKE RLGRAP-FILENAME OBLIGATORY.

SELECTION-SCREEN: END OF BLOCK B2,

END OF BLOCK B1.

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

• E V E N T : AT S E L E C T I O N - S C R E E N *

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

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FNAME.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

• PROGRAM_NAME = SYST-REPID

• DYNPRO_NUMBER = SYST-DYNNR

• FIELD_NAME = ' '

STATIC = 'X'

• MASK = '.'

CHANGING

FILE_NAME = P_FNAME

• 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.

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

• E V E N T : S T A R T - O F - S E L E C T I O N *

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

START-OF-SELECTION.

• --------------------------------------

• Upload Excel file into Internal Table.

• --------------------------------------

PERFORM UPLOAD_EXCEL_FILE.

• -------------------------------------------------------

• Organize the uploaded data into another Internal Table.

• -------------------------------------------------------

PERFORM ORGANIZE_UPLOADED_DATA.

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

• E V E N T : E N D - O F - S E L E C T I O N *

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

END-OF-SELECTION.

&----

---

*& Form UPLOAD_EXCEL_FILE

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

FORM UPLOAD_EXCEL_FILE .

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = P_FNAME

I_BEGIN_COL = 1

I_BEGIN_ROW = 3

I_END_COL = 7

I_END_ROW = 32000

TABLES

INTERN = IT_FILE_UPLOAD

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 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.

ENDIF.

ENDFORM. " UPLOAD_EXCEL_FILE

&----

---

*& Form ORGANIZE_UPLOADED_DATA

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

FORM ORGANIZE_UPLOADED_DATA .

SORT IT_FILE_UPLOAD BY ROW

COL.

LOOP AT IT_FILE_UPLOAD.

CASE IT_FILE_UPLOAD-COL.

• ....................................................

WHEN 1.

WA_TABDATA-ZSLNUM = IT_FILE_UPLOAD-VALUE.

WHEN 2.

WA_TABDATA-ZASSET = IT_FILE_UPLOAD-VALUE.

WHEN 3.

WA_TABDATA-ZYEAR = IT_FILE_UPLOAD-VALUE.

WHEN 4.

WA_TABDATA-ZPERIOD = IT_FILE_UPLOAD-VALUE.

WHEN 5.

WA_TABDATA-ZSHIFT1 = IT_FILE_UPLOAD-VALUE.

WHEN 6.

WA_TABDATA-ZSHIFT2 = IT_FILE_UPLOAD-VALUE.

WHEN 7.

WA_TABDATA-ZSHIFT3 = IT_FILE_UPLOAD-VALUE.

• ....................................................

ENDCASE.

AT END OF ROW.

WA_TABDATA-MANDT = SY-MANDT.

APPEND WA_TABDATA TO IT_TABDATA.

CLEAR: WA_TABDATA.

ENDAT.

ENDLOOP.

ENDFORM. " ORGANIZE_UPLOADED_DATA

In the subroutine --> ORGANIZE_UPLOADED_DATA, data are organized as per the structure declared above.

Regards,

Abir

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

• Don't forget to award points *