function mudule for MS excel file to sap crm

[Former Member]

Hi,

please tell me which function module should use to upload the data from excel sheet to sap crm.

Thanks

swathi

[Former Member]

I copied this from a standard function (can't remember which one) and enhanced it. Maybe you can use it.

FUNCTION Z_UPLOAD_EXCEL.

*"----

---

""Local interface:

*" IMPORTING

*" REFERENCE(FILENAME) LIKE RLGRAP-FILENAME

*" REFERENCE(I_BEGIN_COL) TYPE I DEFAULT 1

*" REFERENCE(I_BEGIN_ROW) TYPE I DEFAULT 1

*" REFERENCE(I_END_COL) TYPE I

*" REFERENCE(I_END_ROW) TYPE I

*" REFERENCE(I_STRUCT) LIKE DD03L-TABNAME OPTIONAL

*" REFERENCE(I_MANDT) TYPE CHAR1 OPTIONAL

*" REFERENCE(I_HEADER_LINES) TYPE I DEFAULT 0

*" TABLES

*" INTERN STRUCTURE ZZEXCELL_STRUCTURE OPTIONAL

*" TABLE OPTIONAL

*" EXCEPTIONS

*" INCONSISTENT_PARAMETERS

*" UPLOAD_OLE

*" DEFINE_STUCT

*" STRUCT_NOT_FOUND

*"----

---

• This function was copied from a standaard SAP-function (R/3).

• It reads an excel-file in an internal table.

• Some functions were added:

• - based on an existing structure, a structurized table is exported

• - exclude header lines in the excel-sheet

• - exclude mandt: first column is excluded

*

• Created by MVG on 25/07/2002

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

• Modifications:

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

*

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

DATA: EXCEL_TAB TYPE KCDE_SENDER.

DATA: SEPARATOR TYPE C.

FIELD-SYMBOLS: <FIELD>.

DATA: APPLICATION TYPE OLE2_OBJECT,

WORKBOOK TYPE OLE2_OBJECT,

RANGE TYPE OLE2_OBJECT,

WORKSHEET TYPE OLE2_OBJECT.

DATA: H_CELL TYPE OLE2_OBJECT.

DATA: H_CELL1 TYPE OLE2_OBJECT.

data: i_dd03l like dd03l occurs 0 with header line,

h_offset type i,

h_length type i,

h_field(1000),

h_cnt type i,

h_col type i.

data: begin of i_def occurs 0,

field type i,

offset type i,

length type i,

end of i_def.

DEFINE M_MESSAGE.

CASE SY-SUBRC.

WHEN 0.

WHEN 1.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

WHEN OTHERS. RAISE UPLOAD_OLE.

ENDCASE.

END-OF-DEFINITION.

IF I_BEGIN_ROW > I_END_ROW. RAISE INCONSISTENT_PARAMETERS. ENDIF.

IF I_BEGIN_COL > I_END_COL. RAISE INCONSISTENT_PARAMETERS. ENDIF.

IF APPLICATION-HEADER = SPACE OR APPLICATION-HANDLE = -1.

CREATE OBJECT APPLICATION 'Excel.Application'.

M_MESSAGE.

ENDIF.

CALL METHOD OF APPLICATION 'Workbooks' = WORKBOOK.

M_MESSAGE.

CALL METHOD OF WORKBOOK 'Open' EXPORTING #1 = FILENAME.

M_MESSAGE.

• set property of application 'Visible' = 1.

• m_message.

GET PROPERTY OF APPLICATION 'ACTIVESHEET' = WORKSHEET.

M_MESSAGE.

CALL METHOD OF WORKSHEET 'Cells' = H_CELL

EXPORTING #1 = I_BEGIN_ROW #2 = I_BEGIN_COL.

M_MESSAGE.

CALL METHOD OF WORKSHEET 'Cells' = H_CELL1

EXPORTING #1 = I_END_ROW #2 = I_END_COL.

M_MESSAGE.

CALL METHOD OF WORKSHEET 'RANGE' = RANGE

EXPORTING #1 = H_CELL #2 = H_CELL1.

M_MESSAGE.

CALL METHOD OF RANGE 'SELECT'.

M_MESSAGE.

• copy to Clippboard

CALL METHOD OF RANGE 'COPY'.

M_MESSAGE.

• free object application.

M_MESSAGE.

CALL FUNCTION 'CLPB_IMPORT'

TABLES

DATA_TAB = EXCEL_TAB

EXCEPTIONS

CLPB_ERROR = 1

OTHERS = 2.

IF SY-SUBRC <> 0. MESSAGE X001(KX). ENDIF.

ASSIGN SEPARATOR TO <FIELD> TYPE 'X'.

<FIELD> = GC_HEX_TAB.

PERFORM SEPARATED_TO_INTERN_CONVERT TABLES EXCEL_TAB INTERN

USING SEPARATOR.

SET PROPERTY OF APPLICATION 'CutCopyMode' = 0.

M_MESSAGE.

CALL METHOD OF APPLICATION 'QUIT'.

M_MESSAGE.

FREE OBJECT APPLICATION.

M_MESSAGE.

• Additions MVG

if not table is requested.

exit.

endif.

if not i_struct is initial.

select * into table i_dd03l

from dd03l

where tabname eq i_struct.

if sy-subrc ne 0.

raise struct_not_found.

endif.

sort i_dd03l by position.

• Fill up the field-table, containing offset and length.

clear: h_cnt, h_offset, h_length.

loop at i_dd03l.

add 1 to h_cnt.

i_def-field = h_cnt.

i_def-offset = h_offset.

i_def-length = i_dd03l-leng.

add i_dd03l-leng to h_offset.

append i_def.

clear i_def.

endloop.

case i_mandt.

when space.

• Creating structurized export-table.

clear: h_length, h_offset.

h_cnt = i_header_lines. "exclusion of the header lines

while h_cnt le i_end_row.

add 1 to h_cnt.

loop at intern where row eq h_cnt.

read table i_def index intern-col.

h_field+i_def-offset(i_def-length) = intern-value.

endloop.

if not h_field is initial.

append h_field to table.

clear h_field.

endif.

endwhile.

when others.

• Creating structurized export table.

• The output needs to be shifted for mandt.

clear: h_length, h_offset.

h_cnt = i_header_lines. "exclusion of the header lines

while h_cnt le i_end_row.

add 1 to h_cnt.

loop at intern where row eq h_cnt.

clear h_col.

h_col = intern-col + 1. "shift 1 to the right

read table i_def index h_col.

h_field+i_def-offset(i_def-length) = intern-value.

endloop.

if not h_field is initial.

append h_field to table.

clear h_field.

endif.

endwhile.

endcase.

endif.

ENDFUNCTION.

Regards,

Michael.

[Wolfgang_Mayer]

Hi Michael,

could you pls clarify some more things like:

- fields of structure "ZZEXCELL_STRUCTURE"

- type of table "table"

- type pool where type "KCDE_SENDER" is defined

- declaration of "gc_hex_tab"

Thanks & regards

Wolfgang

[Former Member]

Structure ZZEXCELL_STRUCTURE:

row (numc-4)

col (numc-4)

value (char-32)

"table": no type

Definition in the TOP structure of function pool:

TYPE-POOLS: ole2, kcde, kcdu.

• value of excel-cell

TYPES: ty_d_itabvalue TYPE ZZEXCELL_STRUCTURE-value,

• internal table containing the excel data

ty_t_itab TYPE ZZEXCELL_STRUCTURE OCCURS 0,

• line type of sender table

BEGIN OF ty_s_senderline,

line(4096) TYPE c,

END OF ty_s_senderline,

• sender table

ty_t_sender TYPE ty_s_senderline OCCURS 0.

CONSTANTS: gc_hex_tab TYPE x VALUE 9,

gc_esc VALUE '"'.

You'll probably have to create an include with this code as well:

1 ----

---

2 ***INCLUDE LZ_CRM001F01 .

3 ----

---

4 FORM separated_to_intern_convert

5 TABLES i_tab TYPE kcdu_srecs

6 i_intern TYPE zzexcel_structure

7 USING i_separator TYPE c.

8

9 DATA: l_sic_tabix LIKE sy-tabix,

10 l_sic_col LIKE kcdehead-col.

11 DATA: l_fdpos LIKE sy-fdpos.

12

13 REFRESH i_intern.

14

15 LOOP AT i_tab.

16 l_sic_tabix = sy-tabix.

17 l_sic_col = 0.

18 WHILE i_tab CA i_separator.

19 l_fdpos = sy-fdpos.

20 l_sic_col = l_sic_col + 1.

21 PERFORM line_to_cell_separat TABLES i_intern

22 USING i_tab l_sic_tabix l_sic_col

23 i_separator l_fdpos.

24 ENDWHILE.

25 IF i_tab <> space.

26 CLEAR i_intern.

27 i_intern-row = l_sic_tabix.

28 i_intern-col = l_sic_col + 1.

29 i_intern-value = i_tab.

30 APPEND i_intern.

31 ENDIF.

32 ENDLOOP.

33 ENDFORM.

34

35 ----

---

36 FORM line_to_cell_separat TABLES i_intern TYPE kcde_intern

37 USING i_line

38 i_row LIKE sy-tabix

39 ch_cell_col LIKE kcdehead-col

40 i_separator TYPE c

41 i_fdpos LIKE sy-fdpos.

42 DATA: l_string TYPE kcde_sender_struc .

43 DATA l_sic_int TYPE i.

44

45 CLEAR i_intern.

46 l_sic_int = i_fdpos.

47 i_intern-row = i_row.

48 l_string = i_line.

49 i_intern-col = ch_cell_col.

50 * csv Dateien mit separator in Zelle: --> ;"abc;cd";

51 IF ( i_separator = ';' OR i_separator = ',' ) AND

52 l_string(1) = gc_esc.

53 PERFORM line_to_cell_esc_sep USING l_string

54 l_sic_int

55 i_separator

56 i_intern-value.

57 ELSE.

58 IF l_sic_int > 0.

59 i_intern-value = i_line(l_sic_int).

60 ENDIF.

61 ENDIF.

62 IF l_sic_int > 0.

63 APPEND i_intern.

64 ENDIF.

65 l_sic_int = l_sic_int + 1.

66 i_line = i_line+l_sic_int.

67 ENDFORM.

68

69 ----

---

70 FORM line_to_cell_esc_sep USING i_string

71 i_sic_int TYPE i

72 i_separator TYPE c

73 i_intern_value TYPE kcde_intern_value.

74 DATA: l_int TYPE i,

75 l_cell_end(2).

76 FIELD-SYMBOLS: <l_cell>.

77 l_cell_end = gc_esc.

78 l_cell_end+1 = i_separator .

79

80 IF i_string CS gc_esc.

81 i_string = i_string+1.

82 IF i_string CS l_cell_end.

83 l_int = sy-fdpos.

84 ASSIGN i_string(l_int) TO <l_cell>.

85 i_intern_value = <l_cell>.

86 l_int = l_int + 2.

87 i_sic_int = l_int.

88 i_string = i_string+l_int.

89 ELSEIF i_string CS gc_esc.

90 * letzte Celle

91 l_int = sy-fdpos.

92 ASSIGN i_string(l_int) TO <l_cell>.

93 i_intern_value = <l_cell>.

94 l_int = l_int + 1.

95 i_sic_int = l_int.

96 i_string = i_string+l_int.

97 l_int = strlen( i_string ).

98 IF l_int > 0 . MESSAGE x001(kx) . ENDIF.

99 ELSE.

100 MESSAGE x001(kx) . "was ist mit csv-Format

101 ENDIF.

102 ENDIF.

Michael.