Excel file to internal table

[Former Member]

Hi,

I am having an excel file on desktop which is having 9 coloumns. I want move that file into an internal table which is having 9 fields. Can any body send me the coding which i can execute directly and get the desired result. Very urgent...

Thanks......

[Former Member]

Hi,

I am having an excel file on desktop which is having 9 coloumns. I want move that file into an internal table which is having 9 fields. Can any body send me the coding which i can execute directly and get the desired result. Very urgent...

Thanks......

[Former Member]

hi Anil,

Check this out

http://www.sapdevelopment.co.uk/file/file_upexcel.htm

http://www.sapdevelopment.co.uk/ms/mshome.htm

Regards,

Santosh

[Former Member]

Hi anil,

U can use

1.ALSM_EXCEL_TO_INTERNAL_TABLE

2. 'KCD_EXCEL_OLE_TO_INT_CONVERT' FM

to upload data from excel into internal table.

1. CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = filename

i_begin_col = 1

i_begin_row = 1

i_end_col = 3

i_end_row = 65535

TABLES

intern = it_vbak.

2. DATA : l_intern TYPE kcde_cells OCCURS 0 WITH HEADER LINE.

DATA : l_index TYPE i.

DATA : l_start_col TYPE i VALUE '1',

l_start_row TYPE i VALUE '1',

l_end_col TYPE i VALUE '256',

l_end_row TYPE i VALUE '65536'.

CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'

EXPORTING

filename = p_file

i_begin_col = l_start_col

i_begin_row = l_start_row

i_end_col = l_end_col

i_end_row = l_end_row

TABLES

intern = l_intern

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

http://www.sap-img.com/abap/upload-direct-excel.htm

http://www.sap-img.com/abap/excel_upload_alternative-kcd-excel-ole-to-int-convert.htm

http://www.sapdevelopment.co.uk/file/file_upexcel.htm

[Former Member]

hi,

i am specifying my program .follow it .

report z_bdcp_xk

no standard page heading line-size 255.

tables : lfa1,rf02k.

data : begin of itab occurs 0,

lifnr like rf02k-lifnr,

ktokk like rf02k-ktokk,

name1 like lfa1-name1,

sortl like lfa1-sortl,

land1 like lfa1-land1,

spras like lfa1-spras,

end of itab.

data : itab1 like alsmex_tabline occurs 0 with header line.

data : b1 type i value 1,

c1 type i value 1,

b2 type i value 10,

c2 type i value 99.

include bdcrecx1.

start-of-selection.

call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'

exporting

filename = 'C:\EXCEL.XLS'

i_begin_col = b1

i_begin_row = c1

i_end_col = b2

i_end_row = c2

tables

intern = itab1

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.

perform organize_uploaded_data.

----

---

perform open_group.

loop at itab.

perform bdc_dynpro using 'SAPMF02K' '0100'.

perform bdc_field using 'BDC_CURSOR'

'RF02K-KTOKK'.

perform bdc_field using 'BDC_OKCODE'

'/00'.

perform bdc_field using 'RF02K-LIFNR'

itab-lifnr.

perform bdc_field using 'RF02K-KTOKK'

itab-ktokk.

perform bdc_dynpro using 'SAPMF02K' '0110'.

perform bdc_field using 'BDC_CURSOR'

'LFA1-SPRAS'.

perform bdc_field using 'BDC_OKCODE'

'/00'.

perform bdc_field using 'LFA1-NAME1'

itab-name1.

perform bdc_field using 'LFA1-SORTL'

itab-sortl.

perform bdc_field using 'LFA1-LAND1'

itab-land1.

perform bdc_field using 'LFA1-SPRAS'

itab-spras.

perform bdc_dynpro using 'SAPMF02K' '0120'.

perform bdc_field using 'BDC_CURSOR'

'LFA1-KUNNR'.

perform bdc_field using 'BDC_OKCODE'

'/00'.

perform bdc_dynpro using 'SAPMF02K' '0130'.

perform bdc_field using 'BDC_CURSOR'

'LFBK-BANKS(01)'.

perform bdc_field using 'BDC_OKCODE'

'=ENTR'.

perform bdc_dynpro using 'SAPLSPO1' '0300'.

perform bdc_field using 'BDC_OKCODE'

'=YES'.

perform bdc_transaction using 'XK01'.

perform close_group.

endloop.

form organize_uploaded_data .

sort itab1 by row

col.

loop at itab1.

case itab1-col.

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

when 1.

itab-lifnr = itab1-value.

when 2.

itab-ktokk = itab1-value.

when 3.

itab-name1 = itab1-value.

when 4.

itab-sortl = itab1-value.

when 5.

itab-land1 = itab1-value.

when 6.

itab-spras = itab1-value.

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

endcase.

at end of row.

• wa_tabdata-mandt = sy-mandt.

• APPEND wa_tabdata TO it_tabdata.

• CLEAR: wa_tabdata.

append itab.

clear itab.

endat.

endloop.

check these sites:

http://www.sap-img.com/abap/upload-direct-excel.htm

http://www.sap-img.com/abap/excel_upload_alternative-kcd-excel-ole-to-int-convert.htm

http://www.sapdevelopment.co.uk/file/file_upexcel.htm

[Former Member]

TYPES:
  BEGIN OF ty_upload,
    field1 TYPE c length 12,
    field2 TYPE c length 12,
    field3 TYPE c length 12,
  END OF ty_upload.
  DATA it_upload TYPE STANDARD TABLE OF ty_upload WITH DEFAULT KEY.
  DATA wa_upload TYPE ty_upload.
  DATA itab TYPE STANDARD TABLE OF alsmex_tabline WITH DEFAULT KEY.
  FIELD-SYMBOLS: <wa> type alsmex_tabline.

  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename    = filename
      i_begin_col = 1
      i_begin_row = 1
      i_end_col   = 3
      i_end_row   = 65535
    TABLES
      intern      = itab.

  LOOP AT itab ASSIGNING <wa>.
    CASE <wa>-col.
      WHEN '0001'.
        wa_upload-field1 = <wa>-value.
      WHEN '0002'.
        wa_upload-field2 = <wa>-value.
      WHEN '0003'.
        wa_upload-field3 = <wa>-value.
    ENDCASE.
    APPEND wa_upload TO it_upload.
    CLEAR wa_upload.
  ENDLOOP.

[Former Member]

Dear Anil,

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 *