SAP MDG · REPLICATION INBOUND
How do I read an Excel file into an internal table in ABAP using OLE and clipboard import?
Use OLE to open the Excel file, select the required range, copy it to the clipboard, import the clipboard into an ABAP table, and then split each line into cell values. The sample also shows handling of quoted separators and cleanup of OLE objects.
SOLUTION SUMMARY · SHORT ANSWER
Use OLE to open the Excel file, select the required range, copy it to the clipboard, import the clipboard into an ABAP table, and then split each line into cell values. The sample also shows handling of quoted separators and cleanup of OLE objects.
General guidance — verify in your SAP release and implementation. A common ABAP pattern for Excel upload is to open the file in Excel via OLE, select the target range, copy it to the clipboard, and import the clipboard content into an internal table. The imported text is then split into rows and columns using the tab separator, with extra handling for quoted values when the separator appears inside a cell.
Process flow
- Check the input range parameters and raise `inconsistent_parameters` if the bounds are invalid.
- Create or reuse the Excel OLE application object.
- Open the file using `Workbooks->Open`.
- Read the active worksheet and mark the target cell range with `Cells` and `Range`.
- Copy the selected range to the clipboard.
- Import clipboard contents with `CL_GUI_FRONTEND_SERVICES=>CLIPBOARD_IMPORT` into `excel_tab`.
- Convert the tab-delimited lines into internal table rows.
- Handle quoted separators such as `;"abc;cd";` in the cell-splitting logic if needed.
CONFIGURATIONSource-backed paths
Referenced tables
| Object | Purpose |
|---|---|
ty_t_sender | Clipboard row table holding lines copied from Excel |
ty_t_itab | Internal table receiving parsed row and cell values |
ty_s_senderline | Single line buffer used while splitting separator-delimited data |
ILLUSTRATIVE ABAP SAMPLE
Source ABAP example
Exact relevant implementation excerpt from the knowledge document.
1*----------------------------------------------------------------------*
2***INCLUDE CREATE_KEY_MAPPING_EXCEL_TABLE .
3*----------------------------------------------------------------------*
4*&---------------------------------------------------------------------*
5*& Form EXCEL_TO_INTERNAL_TABLE
6*&---------------------------------------------------------------------*
7* text
8*----------------------------------------------------------------------*
9* -->P_LT_TEMP_EXCEL_FILE text
10* -->P_I_FILENAME text
11* -->P_I_BEGIN_COL text
12* -->P_I_BEGIN_ROW text
13* -->P_I_END_COL text
14* -->P_I_END_ROW text
15* -->P_ENDFORM text
16*----------------------------------------------------------------------*
17FORM excel_to_internal_table TABLES intern
18 USING filename
19 i_begin_col
20 i_begin_row
21 i_end_col
22 i_end_row.
23
24 DATA: excel_tab TYPE ty_t_sender.
25 DATA: ld_separator TYPE c.
26 DATA: application TYPE ole2_object,
27 workbook TYPE ole2_object,
28 range TYPE ole2_object,
29 worksheet TYPE ole2_object.
30 DATA: h_cell TYPE ole2_object,
31 h_cell1 TYPE ole2_object.
32 DATA:
33 ld_rc TYPE i.
34* Rückgabewert der Methode "clipboard_export "
35
36* Makro für Fehlerbehandlung der Methods
37 DEFINE m_message.
38 case sy-subrc.
39 when 0.
40 when 1.
41 message id sy-msgid type sy-msgty number sy-msgno
42 with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
43 when others. raise upload_ole.
44 endcase.
45 END-OF-DEFINITION.
46
47* CHECK parameters.
48 IF i_begin_row > i_end_row. RAISE inconsistent_parameters. ENDIF.
49 IF i_begin_col > i_end_col. RAISE inconsistent_parameters. ENDIF.
50
51* Get TAB-sign for separation of fields
52 CLASS cl_abap_char_utilities DEFINITION LOAD.
53 ld_separator = cl_abap_char_utilities=>horizontal_tab.
54
55* open file in Excel
56 IF application-header = space OR application-handle = -1.
57 CREATE OBJECT application 'Excel.Application'.
58 TRY .
59 m_message.
60* CATCH SYSTEM-EXCEPTIONS.
61 ENDTRY.
62 IF sy-subrc <> 0.
63 ENDIF.
64 ENDIF.
65 CALL METHOD OF
66 application
67 'Workbooks' = workbook.
68 m_message.
69 CALL METHOD OF
70 workbook
71 'Open'
72
73 EXPORTING
74 #1 = filename.
75 m_message.
76* set property of application 'Visible' = 1.
77* m_message.
78 GET PROPERTY OF application 'ACTIVESHEET' = worksheet.
79 m_message.
80
81* mark whole spread sheet
82 CALL METHOD OF
83 worksheet
84 'Cells' = h_cell
85 EXPORTING
86 #1 = i_begin_row
87 #2 = i_begin_col.
88 m_message.
89 CALL METHOD OF
90 worksheet
91 'Cells' = h_cell1
92 EXPORTING
93 #1 = i_end_row
94 #2 = i_end_col.
95 m_message.
96
97 CALL METHOD OF
98 worksheet
99 'RANGE' = range
100 EXPORTING
101 #1 = h_cell
102 #2 = h_cell1.
103 m_message.
104 CALL METHOD OF
105 range
106 'SELECT'.
107 m_message.
108
109* copy marked area (whole spread sheet) into Clippboard
110 CALL METHOD OF
111 range
112 'COPY'.
113 m_message.
114
115* read clipboard into ABAP
116 CALL METHOD cl_gui_frontend_services=>clipboard_import
117 IMPORTING
118 data = excel_tab
119 EXCEPTIONS
120 cntl_error = 1The remaining configuration, implementation details, and testing guidance continue from this answer more…
Related questions and keywords
Alternative questions
- How can I convert an Excel range into ABAP internal table rows?
- How do I parse Excel data into internal table lines in ABAP?
- How does the OLE clipboard-based Excel upload work in ABAP?
Possible questions
- How do I read an Excel file into an internal table in ABAP using OLE and clipboard import?
- How can I convert an Excel range into ABAP internal table rows?
- How do I parse Excel data into internal table lines in ABAP?
- What is the ABAP pattern for Excel upload with separator handling?
- How do I handle quoted separators in ABAP CSV/Excel upload parsing?