03-16-2011 3:10 AM
Hi
i need to upload data from excel to internal table...actually i know this how to do ..but problem is that in this excel file data is in the form of 3-sheets ,so i need to upload 3-sheets data to the internal table at a time not one by one sheet ..Thanks in advance
03-16-2011 3:58 AM
hi, maybe u can check this 2 FM
1st, use this FM to get filename KD_GET_FILENAME_ON_F4
2nd u can use this to get data from that file ALSM_EXCEL_TO_INTERNAL_TABLE
or you can read this
[http://wiki.sdn.sap.com/wiki/display/Snippets/ReadmultiplesheetsofanExcelfileintoSAPthroughABAP]
hope this FM can solve your problem
Edited by: indrajale on Mar 16, 2011 5:04 AM
03-16-2011 3:58 AM
hi, maybe u can check this 2 FM
1st, use this FM to get filename KD_GET_FILENAME_ON_F4
2nd u can use this to get data from that file ALSM_EXCEL_TO_INTERNAL_TABLE
or you can read this
[http://wiki.sdn.sap.com/wiki/display/Snippets/ReadmultiplesheetsofanExcelfileintoSAPthroughABAP]
hope this FM can solve your problem
Edited by: indrajale on Mar 16, 2011 5:04 AM
03-16-2011 4:19 AM
Hi Sudeesh,
You can resolve using OLE.
Below Code is just for your reference.
Its only for 1 Sheet but i think u can use other sheets too.
Hope it will help you.
REPORT ZEXCINT LINE-SIZE 330.
DATA: BEGIN OF RECORD OCCURS 0,
define the internal table with the structure u want.
END OF RECORD.
SELECTION-SCREEN: BEGIN OF BLOCK bl1 WITH FRAME TITLE text-000.
PARAMETER: dataset LIKE rlgrap-filename OBLIGATORY.
SELECTION-SCREEN: END OF BLOCK bl1.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR dataset.
CALL FUNCTION 'WS_FILENAME_GET'
EXPORTING
def_filename = ' '
def_path = ' '
mask= ' '
mode= '0'
title= 'Archivo a importar'
IMPORTING
filename= dataset
EXCEPTIONS
inv_winsys= 01
no_batch= 02
selection_cancel= 03
selection_error= 04.
START-OF-SELECTION.
REFRESH record.
CLEAR record.
PERFORM call_excel TABLES record USING dataset 2 1 9999 100.
LOOP AT RECORD.
WRITE:/(20) RECORD-STATUS,
(10) RECORD-PODAT ,
(10) RECORD-VDAT ,
(30) RECORD-NARR,
(30) RECORD-TCODE,
(30) RECORD-INPUT,
(30) RECORD-DEBIT,
(30) RECORD-CREDIT,
(40) RECORD-BALANCE.
ENDLOOP.
******************
CONSTANTS: tabu TYPE x VALUE 9.
FIELD-SYMBOLS: <pointer>.
DATA: sep(1).
&----
*& * * *Form CALL_EXCEL
&----
* * *-->ITAB * * * text
* * *-->VALUE(ARCH)text
* * *-->VALUE(FILA_text
* * *-->VALUE(COL_1text
* * *-->VALUE(FILA_text
*-->VALUE(COL_2text
----
FORM call_excel TABLES itab
USING value(arch)
value(fila_1)
value(col_1)
value(fila_2)
value(col_2).
DATA: excel_line(4096) OCCURS 10 WITH HEADER LINE.
DATA: app TYPE obj_record,
workbook TYPE obj_record,
worksheet TYPE obj_record,
cell_1 TYPE obj_record,
cell_2 TYPE obj_record,
range TYPE obj_record.
CREATE OBJECT app 'EXCEL.APPLICATION'.
IF sy-subrc NE 0.
" error.
EXIT.
ENDIF.
CALL METHOD OF app 'WORKBOOKS' = workbook.
CALL METHOD OF workbook 'OPEN' * *EXPORTING * * *#1 = arch.
IF sy-subrc NE 0.
WRITE:/ 'Error en excel'.
ENDIF.
GET PROPERTY OF app 'ACTIVESHEET' = worksheet.
CALL METHOD OF worksheet 'Cells' = cell_1
EXPORTING
#1 = fila_1
#2 = col_1.
CALL METHOD OF worksheet 'Cells' = cell_2
EXPORTING
#1 = fila_2
#2 = col_2.
CALL METHOD OF worksheet 'RANGE' = range
EXPORTING
#1 = cell_1
#2 = cell_2.
CALL METHOD OF range 'SELECT'.
CALL METHOD OF range 'COPY'.
CALL FUNCTION 'CLPB_IMPORT'
TABLES
data_tab * = excel_line
EXCEPTIONS
clpb_error = 1
OTHERS * * = 2.
ASSIGN sep TO <pointer> TYPE 'X'.
<pointer> = tabu.
PERFORM delimit_columns TABLES excel_line itab USING sep.
SET PROPERTY OF app 'CutCopyMode' = 0.
CALL METHOD OF app 'QUIT'.
FREE OBJECT app.
ENDFORM. "CALL_EXCEL
&----
*& * * *Form *DELIMIT_COLUMNS
&----
FORM delimit_columns TABLES excel_line itab USING sep.
DATA: BEGIN OF xls OCCURS 5,
value(1024),
END OF xls.
DATA: len TYPE i.
LOOP AT excel_line.
SHIFT excel_line LEFT DELETING LEADING sep.
SHIFT excel_line LEFT DELETING LEADING ' '.
CONDENSE excel_line.
len = STRLEN( excel_line ).
IF len EQ 0.
CONTINUE.
ENDIF.
SPLIT excel_line AT sep INTO TABLE xls.
LOOP AT xls.
ASSIGN COMPONENT sy-tabix OF STRUCTURE itab TO <pointer>.
<pointer> = xls-value.
ENDLOOP.
APPEND itab.
CLEAR itab.
REFRESH xls.
Endif.
ENDLOOP.
ENDFORM. "DELIMIT_COLUMNS
Regards,
Ankit.
03-16-2011 8:06 AM
03-17-2011 4:25 AM
Hi Indrajale,
with ur link i can upload only 2 coloumns ...plz tell me how to upload multiple coloumns
Thanku in advance
03-17-2011 4:27 AM
Hi Indrajale,
with ur link i can upload only 2 coloumns ...plz tell me how to upload multiple coloumns
Thanku in advance