BDC -Excel

[Former Member]

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

[Former Member]

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

[Former Member]

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.

[Former Member]

Thanku very much for ur replies

[Former Member]

Hi Indrajale,

with ur link i can upload only 2 coloumns ...plz tell me how to upload multiple coloumns

Thanku in advance

[Former Member]

Hi Indrajale,

with ur link i can upload only 2 coloumns ...plz tell me how to upload multiple coloumns

Thanku in advance