Importing an Excel Sheet into an Internal Table

Nov 11th, 2009 | By Ole | Category: Featured, SAP ABAP, SAP Tables & Views

In the example we will show how simple it is to import an Excel spreadsheet into an internal table.

For the reason of simplicity we have chosen to use a predefined structure with 26 fields to hold the imported spreadsheet. You could simply add more columns if needed, or better, use a dynamic internal table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157

FUNCTION Z0OV_LOAD_EXCEL.
*"----------------------------------------------------------------------
*"*"Local Interface:
*" IMPORTING
*"     REFERENCE(IM_FILENAME) TYPE STRING
*" EXPORTING
*"     REFERENCE(EX_STATUS) TYPE STRING
*"----------------------------------------------------------------------

* The simple definition of our internal table.
* I the real world this could be a dynamic table, but we are using this
* simple version for the reason of simplicity.
TYPES: BEGIN OF t_excel,
         row TYPE I,
         a   TYPE char50,
         b   TYPE char50,
         c   TYPE char50,
         d   TYPE char50,
         e   TYPE char50,
         f   TYPE char50,
         g   TYPE char50,
         h   TYPE char50,
         i   TYPE char50,
         j   TYPE char50,
         k   TYPE char50,
         l   TYPE char50,
         m   TYPE char50,
         n   TYPE char50,
         o   TYPE char50,
         p   TYPE char50,
         q   TYPE char50,
         r   TYPE char50,
         s   TYPE char50,
         t   TYPE char50,
         u   TYPE char50,
         v   TYPE char50,
         w   TYPE char50,
         x   TYPE char50,
         y   TYPE char50,
         z   TYPE char50,
       END OF t_excel.

DATA:
it_import   TYPE TABLE OF ALSMEX_TABLINE,
wa_import   TYPE ALSMEX_TABLINE,

it_excel    TYPE SORTED TABLE OF t_excel WITH UNIQUE KEY row,
wa_excel    LIKE LINE OF it_excel,

l_filename TYPE RLGRAP-FILENAME,

l_prev_row TYPE I VALUE 1,
l_curr_row TYPE I VALUE 1,
l_counter   TYPE I VALUE 0,
l_lines     TYPE I.

* Cast the file name to the type RLGRAP-FILENAME
l_filename = im_filename.

* Load our Excel sheet into the internal table IT_IMPORT
* This table does NOT hold the imported Excel sheet in rows/columns. We
* will convert this table to rows/columns in the next step.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
    FILENAME                = l_filename
    I_BEGIN_COL             = 1
    I_BEGIN_ROW             = 1
    I_END_COL               = 50
    I_END_ROW               = 10000
TABLES
    INTERN                  = it_import
EXCEPTIONS
    INCONSISTENT_PARAMETERS = 1
    UPLOAD_OLE              = 2
    OTHERS                  = 3.

" Any errors?
IF SY-SUBRC <> 0.
ex_status = 'Error'.
ELSE.

" Append a dummy row to the imported table.
" Makes it easier to loop the result set.
wa_import-row = 9999.
APPEND wa_import
TO it_import.

" Sort our imported sheet.
SORT it_import BY row col.

" Check how many lines we have imported.
DESCRIBE TABLE it_import LINES l_lines.

" Loop all our imported data.
LOOP AT it_import INTO wa_import.

    " First row? Then remember the first row number (not always 1)
    IF l_counter = 0.
      l_prev_row   = wa_import-row.
      wa_excel-row = wa_import-row.
    ENDIF.

    l_counter = l_counter + 1.

    " Did we reach a new row number or the final row in IT_IMPORT?
    IF wa_import-row > l_prev_row OR l_counter = l_lines.

      " Append all found columns to the current row in internal table.
      APPEND wa_excel
      TO it_excel.

      " Get ready for a new row.
      CLEAR wa_excel.

      " Remember the current row number.
      l_prev_row   = wa_import-row.
      wa_excel-row = wa_import-row.
    ENDIF.

    " Assign the current value to the correct column.
    " If we should implement a dynamic internal table, we will have to change this.
    CASE wa_import-col.
      WHEN 0001. wa_excel-a = wa_import-value.
      WHEN 0002. wa_excel-b = wa_import-value.
      WHEN 0003. wa_excel-c = wa_import-value.
      WHEN 0004. wa_excel-d = wa_import-value.
      WHEN 0005. wa_excel-e = wa_import-value.
      WHEN 0006. wa_excel-f = wa_import-value.
      WHEN 0007. wa_excel-g = wa_import-value.
      WHEN 0008. wa_excel-h = wa_import-value.
      WHEN 0009. wa_excel-i = wa_import-value.
      WHEN 0010. wa_excel-j = wa_import-value.
      WHEN 0011. wa_excel-k = wa_import-value.
      WHEN 0012. wa_excel-l = wa_import-value.
      WHEN 0013. wa_excel-m = wa_import-value.
      WHEN 0014. wa_excel-n = wa_import-value.
      WHEN 0015. wa_excel-o = wa_import-value.
      WHEN 0016. wa_excel-p = wa_import-value.
      WHEN 0017. wa_excel-q = wa_import-value.
      WHEN 0018. wa_excel-r = wa_import-value.
      WHEN 0019. wa_excel-s = wa_import-value.
      WHEN 0020. wa_excel-t = wa_import-value.
      WHEN 0021. wa_excel-u = wa_import-value.
      WHEN 0022. wa_excel-v = wa_import-value.
      WHEN 0023. wa_excel-w = wa_import-value.
      WHEN 0024. wa_excel-x = wa_import-value.
      WHEN 0025. wa_excel-y = wa_import-value.
      WHEN 0026. wa_excel-z = wa_import-value.
    ENDCASE.

ENDLOOP.

ex_status = 'OK'.

ENDIF.

ENDFUNCTION.

Leave Comment

You must be logged in to post a comment.