04-10-2008 9:02 AM
hai ,
please help me to find out how can i convert excel file and store the data into data base
04-10-2008 10:18 AM
Hi,
Check the following code:
Upload data direct from excel.xls file to SAP
REPORT ZEXCELUPLOAD.
PARAMETERS: filename LIKE rlgrap-filename MEMORY ID M01,
begcol TYPE i DEFAULT 1 NO-DISPLAY,
begrow TYPE i DEFAULT 1 NO-DISPLAY,
endcol TYPE i DEFAULT 100 NO-DISPLAY,
endrow TYPE i DEFAULT 32000 NO-DISPLAY.
Tick don't append header
PARAMETERS: kzheader AS CHECKBOX.
DATA: BEGIN OF intern OCCURS 0.
INCLUDE STRUCTURE alsmex_tabline.
DATA: END OF intern.
DATA: BEGIN OF intern1 OCCURS 0.
INCLUDE STRUCTURE alsmex_tabline.
DATA: END OF intern1.
DATA: BEGIN OF t_col OCCURS 0,
col LIKE alsmex_tabline-col,
size TYPE i.
DATA: END OF t_col.
DATA: zwlen TYPE i,
zwlines TYPE i.
DATA: BEGIN OF fieldnames OCCURS 3,
title(60),
table(6),
field(10),
kz(1),
END OF fieldnames.
No of columns
DATA: BEGIN OF data_tab OCCURS 0,
value_0001(50),
value_0002(50),
value_0003(50),
value_0004(50),
value_0005(50),
value_0006(50),
value_0007(50),
value_0008(50),
value_0009(50),
value_0010(50),
value_0011(50),
value_0012(50),
value_0013(50),
value_0014(50),
value_0015(50),
value_0016(50),
value_0017(50),
value_0018(50),
value_0019(50),
value_0020(50),
value_0021(50),
value_0022(50),
value_0023(50),
value_0024(50),
value_0025(50),
value_0026(50),
value_0027(50),
value_0028(50),
value_0029(50),
value_0030(50),
value_0031(50),
value_0032(50),
value_0033(50),
value_0034(50),
value_0035(50),
value_0036(50),
value_0037(50),
value_0038(50),
value_0039(50),
value_0040(50),
value_0041(50),
value_0042(50),
value_0043(50),
value_0044(50),
value_0045(50),
value_0046(50),
value_0047(50),
value_0048(50),
value_0049(50),
value_0050(50),
value_0051(50),
value_0052(50),
value_0053(50),
value_0054(50),
value_0055(50),
value_0056(50),
value_0057(50),
value_0058(50),
value_0059(50),
value_0060(50),
value_0061(50),
value_0062(50),
value_0063(50),
value_0064(50),
value_0065(50),
value_0066(50),
value_0067(50),
value_0068(50),
value_0069(50),
value_0070(50),
value_0071(50),
value_0072(50),
value_0073(50),
value_0074(50),
value_0075(50),
value_0076(50),
value_0077(50),
value_0078(50),
value_0079(50),
value_0080(50),
value_0081(50),
value_0082(50),
value_0083(50),
value_0084(50),
value_0085(50),
value_0086(50),
value_0087(50),
value_0088(50),
value_0089(50),
value_0090(50),
value_0091(50),
value_0092(50),
value_0093(50),
value_0094(50),
value_0095(50),
value_0096(50),
value_0097(50),
value_0098(50),
value_0099(50),
value_0100(50).
DATA: END OF data_tab.
DATA: tind(4) TYPE n.
DATA: zwfeld(19).
FIELD-SYMBOLS: <fs1>.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR filename.
CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
EXPORTING
mask = '*.xls'
static = 'X'
CHANGING
file_name = filename.
START-OF-SELECTION.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = filename
i_begin_col = begcol
i_begin_row = begrow
i_end_col = endcol
i_end_row = endrow
TABLES
intern = intern
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.
IF sy-subrc <> 0.
WRITE:/ 'Upload Error ', SY-SUBRC.
ENDIF.
END-OF-SELECTION.
LOOP AT intern.
intern1 = intern.
CLEAR intern1-row.
APPEND intern1.
ENDLOOP.
SORT intern1 BY col.
LOOP AT intern1.
AT NEW col.
t_col-col = intern1-col.
APPEND t_col.
ENDAT.
zwlen = strlen( intern1-value ).
READ TABLE t_col WITH KEY col = intern1-col.
IF sy-subrc EQ 0.
IF zwlen > t_col-size.
t_col-size = zwlen.
Internal Table, Current Row Index
MODIFY t_col INDEX sy-tabix.
ENDIF.
ENDIF.
ENDLOOP.
DESCRIBE TABLE t_col LINES zwlines.
SORT intern BY row col.
IF kzheader = 'X'.
LOOP AT intern.
fieldnames-title = intern-value.
APPEND fieldnames.
AT END OF row.
EXIT.
ENDAT.
ENDLOOP.
ELSE.
DO zwlines TIMES.
WRITE sy-index TO fieldnames-title.
APPEND fieldnames.
ENDDO.
ENDIF.
SORT intern BY row col.
LOOP AT intern.
IF kzheader = 'X'
AND intern-row = 1.
CONTINUE.
ENDIF.
tind = intern-col.
CONCATENATE 'DATA_TAB-VALUE_' tind INTO zwfeld.
ASSIGN (zwfeld) TO <fs1>.
<fs1> = intern-value.
AT END OF row.
APPEND data_tab.
CLEAR data_tab.
ENDAT.
ENDLOOP.
CALL FUNCTION 'DISPLAY_BASIC_LIST'
EXPORTING
file_name = filename
TABLES
data_tab = data_tab
fieldname_tab = fieldnames.
End of Program
Regards,
Bhaskar
04-10-2008 9:14 AM
Hi,
report ztims_partsmupd.
tables:zparts_mattgt,mara.
data:begin of it_parts occurs 0,
date(10),
regio(3),
kunnr(10),
matnr_i(18),
desc(40),
kwmeng_i(16),
end of it_parts.
call function 'UPLOAD'
exporting
filename = 'C:\'
filetype = 'DAT'
tables
data_tab = it_parts.
loop at it_parts.
if it_parts-date+3(2) = '04' or
it_parts-date+3(2) = '05' or
it_parts-date+3(2) = '06'.
zparts_mattgt-quarter = '1'.
elseif it_parts-date+3(2) = '07' or
it_parts-date+3(2) ='08'.
zparts_mattgt-quarter = '2'.
elseif it_parts-date+3(2) = '09' or
it_parts-date+3(2) = '10' or
it_parts-date+3(2) = '11' or
it_parts-date+3(2) = '12'.
zparts_mattgt-quarter = '3'.
elseif it_parts-date+3(2) = '01' or
it_parts-date+3(2) = '02' or
it_parts-date+3(2) = '03'.
zparts_mattgt-quarter = '4'.
endif.
if it_parts-kunnr ca sy-abcde.
else.
unpack it_parts-kunnr to it_parts-kunnr.
endif.
if it_parts-matnr_i ca sy-abcde.
else.
unpack it_parts-matnr_i to it_parts-matnr_i.
endif.
select single * from mara where matnr = it_parts-matnr_i.
zparts_mattgt-zyear = it_parts-date+6(4).
zparts_mattgt-zmonth = it_parts-date+3(2).
zparts_mattgt-kunnr = it_parts-kunnr.
zparts_mattgt-zpartno = it_parts-matnr_i.
zparts_mattgt-qnty = it_parts-kwmeng_i.
zparts_mattgt-regio = it_parts-regio.
zparts_mattgt-ernam = sy-uname.
zparts_mattgt-erdat = sy-datum.
zparts_mattgt-meins = mara-meins.
insert zparts_mattgt.
clear:it_parts,zparts_mattgt,mara.
endloop.
Reward if usefull
04-10-2008 9:24 AM
Hi...
execute this..
REPORT ZVATPHASE2_UPLOAD .
TYPE-POOLS: TRUXS.
TABLES: J_1IMOVEND.
DATA: I_TEXT_DATA TYPE TRUXS_T_TEXT_DATA,
V_FILENAME_STRING TYPE STRING,
FILE_NAME LIKE IBIPPARMS-PATH,
WF_LINES(6).
*DATA : BEGIN OF ITAB1 OCCURS 0.
* INCLUDE TYPE J_1IMOVEND.
*DATA : END OF ITAB1.
DATA : BEGIN OF ITAB1 OCCURS 0,
LIFNR LIKE J_1IMOVEND-LIFNR,
J_1ILSTNO LIKE J_1IMOVEND-J_1ILSTNO,
END OF ITAB1.
*SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-001.
* PARAMETERS: P_FILE LIKE RLGRAP-FILENAME.
*SELECTION-SCREEN END OF BLOCK B1 .
*
*AT SELECTION-SCREEN.
CALL FUNCTION 'F4_FILENAME'
EXPORTING
PROGRAM_NAME = SYST-CPROG
DYNPRO_NUMBER = SYST-DYNNR
IMPORTING
FILE_NAME = FILE_NAME.
*START-OF-SELECTION.
IF NOT FILE_NAME IS INITIAL.
V_FILENAME_STRING = FILE_NAME.
CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
FILENAME = V_FILENAME_STRING
FILETYPE = 'ASC'
HAS_FIELD_SEPARATOR = 'X'
DAT_MODE = ''
TABLES
DATA_TAB = I_TEXT_DATA.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
I_FIELD_SEPERATOR = 'X'
* I_LINE_HEADER =
I_TAB_RAW_DATA = I_TEXT_DATA
I_FILENAME = FILE_NAME
TABLES
I_TAB_CONVERTED_DATA = ITAB1
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.
ELSE.
PERFORM CALLERR USING 'Enter a Valid File Name, Thank You'.
ENDIF.
*&*&
DATA : BEGIN OF ITAB OCCURS 0.
INCLUDE TYPE J_1IMOVEND.
DATA : END OF ITAB.
SELECT * FROM J_1IMOVEND INTO TABLE ITAB.
*LOOP AT ITAB.
* READ
LOOP AT ITAB1.
UPDATE J_1IMOVEND SET J_1ILSTNO = ITAB1-J_1ILSTNO
WHERE LIFNR EQ ITAB1-LIFNR.
ENDLOOP.
CLEAR : WF_LINES.
DESCRIBE TABLE ITAB1 LINES WF_LINES.
WRITE : WF_LINES, 'Records Updated Successfully'.
*&---------------------------------------------------------------------*
*& Form CALLERR
*&---------------------------------------------------------------------*
* CALLING FUN MOD FOR GIVING STATUS MESSAGE & ERER MSG
*----------------------------------------------------------------------*
FORM CALLERR USING TEXT1.
CALL FUNCTION 'FC_POPUP_ERR_WARN_MESSAGE'
EXPORTING
POPUP_TITLE = 'VAT Report Phase II'
IS_ERROR = 'X'
MESSAGE_TEXT = TEXT1
START_COLUMN = 30
START_ROW = 8.
STOP.
ENDFORM. " CALLERR
With Rgds,
S.Barani
04-10-2008 9:35 AM
Hi ,
First is you need to upload that excell data into an internal table through function module 'ALSM_EXCELL_TO_INTERNAL_TABLE' then you can read this data to work area and modify the database tables .
Regards,
Naveen Kumar M S
04-10-2008 10:18 AM
Hi,
Check the following code:
Upload data direct from excel.xls file to SAP
REPORT ZEXCELUPLOAD.
PARAMETERS: filename LIKE rlgrap-filename MEMORY ID M01,
begcol TYPE i DEFAULT 1 NO-DISPLAY,
begrow TYPE i DEFAULT 1 NO-DISPLAY,
endcol TYPE i DEFAULT 100 NO-DISPLAY,
endrow TYPE i DEFAULT 32000 NO-DISPLAY.
Tick don't append header
PARAMETERS: kzheader AS CHECKBOX.
DATA: BEGIN OF intern OCCURS 0.
INCLUDE STRUCTURE alsmex_tabline.
DATA: END OF intern.
DATA: BEGIN OF intern1 OCCURS 0.
INCLUDE STRUCTURE alsmex_tabline.
DATA: END OF intern1.
DATA: BEGIN OF t_col OCCURS 0,
col LIKE alsmex_tabline-col,
size TYPE i.
DATA: END OF t_col.
DATA: zwlen TYPE i,
zwlines TYPE i.
DATA: BEGIN OF fieldnames OCCURS 3,
title(60),
table(6),
field(10),
kz(1),
END OF fieldnames.
No of columns
DATA: BEGIN OF data_tab OCCURS 0,
value_0001(50),
value_0002(50),
value_0003(50),
value_0004(50),
value_0005(50),
value_0006(50),
value_0007(50),
value_0008(50),
value_0009(50),
value_0010(50),
value_0011(50),
value_0012(50),
value_0013(50),
value_0014(50),
value_0015(50),
value_0016(50),
value_0017(50),
value_0018(50),
value_0019(50),
value_0020(50),
value_0021(50),
value_0022(50),
value_0023(50),
value_0024(50),
value_0025(50),
value_0026(50),
value_0027(50),
value_0028(50),
value_0029(50),
value_0030(50),
value_0031(50),
value_0032(50),
value_0033(50),
value_0034(50),
value_0035(50),
value_0036(50),
value_0037(50),
value_0038(50),
value_0039(50),
value_0040(50),
value_0041(50),
value_0042(50),
value_0043(50),
value_0044(50),
value_0045(50),
value_0046(50),
value_0047(50),
value_0048(50),
value_0049(50),
value_0050(50),
value_0051(50),
value_0052(50),
value_0053(50),
value_0054(50),
value_0055(50),
value_0056(50),
value_0057(50),
value_0058(50),
value_0059(50),
value_0060(50),
value_0061(50),
value_0062(50),
value_0063(50),
value_0064(50),
value_0065(50),
value_0066(50),
value_0067(50),
value_0068(50),
value_0069(50),
value_0070(50),
value_0071(50),
value_0072(50),
value_0073(50),
value_0074(50),
value_0075(50),
value_0076(50),
value_0077(50),
value_0078(50),
value_0079(50),
value_0080(50),
value_0081(50),
value_0082(50),
value_0083(50),
value_0084(50),
value_0085(50),
value_0086(50),
value_0087(50),
value_0088(50),
value_0089(50),
value_0090(50),
value_0091(50),
value_0092(50),
value_0093(50),
value_0094(50),
value_0095(50),
value_0096(50),
value_0097(50),
value_0098(50),
value_0099(50),
value_0100(50).
DATA: END OF data_tab.
DATA: tind(4) TYPE n.
DATA: zwfeld(19).
FIELD-SYMBOLS: <fs1>.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR filename.
CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
EXPORTING
mask = '*.xls'
static = 'X'
CHANGING
file_name = filename.
START-OF-SELECTION.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = filename
i_begin_col = begcol
i_begin_row = begrow
i_end_col = endcol
i_end_row = endrow
TABLES
intern = intern
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.
IF sy-subrc <> 0.
WRITE:/ 'Upload Error ', SY-SUBRC.
ENDIF.
END-OF-SELECTION.
LOOP AT intern.
intern1 = intern.
CLEAR intern1-row.
APPEND intern1.
ENDLOOP.
SORT intern1 BY col.
LOOP AT intern1.
AT NEW col.
t_col-col = intern1-col.
APPEND t_col.
ENDAT.
zwlen = strlen( intern1-value ).
READ TABLE t_col WITH KEY col = intern1-col.
IF sy-subrc EQ 0.
IF zwlen > t_col-size.
t_col-size = zwlen.
Internal Table, Current Row Index
MODIFY t_col INDEX sy-tabix.
ENDIF.
ENDIF.
ENDLOOP.
DESCRIBE TABLE t_col LINES zwlines.
SORT intern BY row col.
IF kzheader = 'X'.
LOOP AT intern.
fieldnames-title = intern-value.
APPEND fieldnames.
AT END OF row.
EXIT.
ENDAT.
ENDLOOP.
ELSE.
DO zwlines TIMES.
WRITE sy-index TO fieldnames-title.
APPEND fieldnames.
ENDDO.
ENDIF.
SORT intern BY row col.
LOOP AT intern.
IF kzheader = 'X'
AND intern-row = 1.
CONTINUE.
ENDIF.
tind = intern-col.
CONCATENATE 'DATA_TAB-VALUE_' tind INTO zwfeld.
ASSIGN (zwfeld) TO <fs1>.
<fs1> = intern-value.
AT END OF row.
APPEND data_tab.
CLEAR data_tab.
ENDAT.
ENDLOOP.
CALL FUNCTION 'DISPLAY_BASIC_LIST'
EXPORTING
file_name = filename
TABLES
data_tab = data_tab
fieldname_tab = fieldnames.
End of Program
Regards,
Bhaskar