ABAP-1-会计凭证批量数据导入本地ACCESS

上一版本出现问题:

A.若TXT数据条目超过800万(大概1.3G),则将TXT导入ACCESS过程不成功,ACCESS数据表为空。(Access单表最大容量2G)

B.数据量过大造成内存溢出,需要BASIS优化服务器内存容量。

经过反复测试,发现还是SQL语句的问题:

1.SQL问题

 insert into fan_tab(va1,va2....) select F1 as va1,F2 as va2... FROM [Text;FMT=Delimited;HDR=No;IMEX=1;DATABASE=C:\;].[A010#txt];

 上面语句中过多使用AS别名,insert into ... select ... FROM ... 

2.调整思路

 A.TXT文件中第一行存放标题

 B.ID编号字段放在TXT文件中

 C.SQL语句调整为:select * into fan_tab FROM [Text;FMT=Delimited;HDR=No;IMEX=1;DATABASE=C:\;].[A010#txt];

  直接将TXT数据转换为ACCESS数据表。

3.测试

 

 

 上述400多万的记录执行时间大概4分钟

4.代码部分

 ZFIR0035_ACCOUNTDOC_EXPORT

 1 *&---------------------------------------------------------------------* 
 2 *& Report  ZFIR0035_ACCOUNTDOC_EXPORT 
 3 *& 
 4 *&---------------------------------------------------------------------* 
 5 *& 
 6 *& 
 7 *&---------------------------------------------------------------------* 
 8  
 9 report zfir0035_accountdoc_export. 
10  
11 include zapi0001_prg_info. 
12  
13 include zfir0035_accountdoc_export_top.  "define 
14  
15 include zfir0035_accountdoc_export_scr.  "screen 
16  
17 include zfir0035_accountdoc_export_pro.  "processing 
18  
19 include zfir0035_accountdoc_export_frm.  "subroutine
View Code

 ZFIR0035_ACCOUNTDOC_EXPORT_TOP

 1 *&---------------------------------------------------------------------* 
 2 *&  包含                ZFIR0035_ACCOUNTDOC_EXPORT_TOP 
 3 *&---------------------------------------------------------------------* 
 4  
 5 include ole2incl. 
 6  
 7 tables: 
 8   mkpf, 
 9   mseg. 
10  
11 constants: 
12   gc_provider type string value 'Provider=Microsoft.Jet.OLEDB.4.0', 
13   gc_security type string value 'Persist Security Info=False', 
14   gc_datasour type string value 'Data Source='. 
15  
16 constants: 
17   gc_char  type c  value ',', 
18   gc_char3 type c  value '''', 
19   gc_char1 type c  value '_', 
20   gc_char2 type c  value ';', 
21   gc_char4 type c  value '.', 
22   gc_check type c  value 'X'. 
23  
24 data: 
25   begin of wa_inf, 
26     count type i,       "总记录数 
27     dburl type string,  "数据库路径 
28     tabnm type string,  "表名 
29     qydat type datum,   "获取数据-日期 
30     qytim type uzeit,   "获取数据-时间 
31     txdat type datum,   "下载TXT-日期 
32     txtim type uzeit,   "下载TXT-时间 
33     acdat type datum,   "导入DB-日期 
34     actim type uzeit,   "导入DB-时间 
35     fhdat type datum,   "执行结束-日期 
36     fhtim type uzeit,   "执行结束-时间 
37     ustim type uzeit,   "总用时 
38     mod   type i, 
39     div   type string, 
40   end of wa_inf. 
41  
42 data: 
43   begin of wa_xsz, 
44   id type i. 
45   include structure zsfi0018_xsz. 
46 data:end of wa_xsz. 
47  
48 data: 
49   go_conn type ole2_object, 
50   go_rset type ole2_object, 
51   go_err  type ole2_object, 
52   go_rtab type ole2_object. 
53  
54 data: 
55   gt_str  type table of string, 
56   gt_imp  type table of string, 
57   gt_data type table of zsfi0018_xsz, 
58   gt_xsz  like table of wa_xsz, 
59   gt_inf  like table of wa_inf. 
60  
61 data: 
62   gv_txurl  type string, 
63   gv_tabnm  type string, 
64   gv_zchar  type c, 
65   gv_count  type i, 
66   gv_datasour type string. 
67  
68 data: 
69   gs_inf    like wa_inf. 
70  
71 field-symbols: 
72   <fs_str>  type string, 
73   <fs_imp>  type string, 
74   <fs_inf>  like wa_inf, 
75   <fs_xsz>  like wa_xsz, 
76   <fs_data> type zsfi0018_xsz.
View Code

ZFIR0035_ACCOUNTDOC_EXPORT_SCR

 1 *&---------------------------------------------------------------------* 
 2 *&  包含                ZFIR0035_ACCOUNTDOC_EXPORT_SCR 
 3 *&---------------------------------------------------------------------* 
 4  
 5 selection-screen begin of block block1 with frame title text-001. 
 6   parameter: 
 7     p_bukrs type bukrs obligatory. 
 8   select-options: 
 9     s_budat for  mkpf-budat obligatory. 
10 selection-screen end of block block1. 
11  
12 selection-screen begin of block block2 with frame title text-002. 
13   parameters: 
14     p_dburl type rlgrap-filename obligatory. 
15  
16   selection-screen skip. 
17  
18   parameters: 
19     p_deltx as checkbox default 'X'. 
20 selection-screen end of block block2.
View Code

ZFIR0035_ACCOUNTDOC_EXPORT_PRO

 1 *&---------------------------------------------------------------------* 
 2 *&  包含                ZFIR0035_ACCOUNTDOC_EXPORT_PRO 
 3 *&---------------------------------------------------------------------* 
 4  
 5 initialization. 
 6   "初始-公司代码 
 7   perform frm_get_auth_bukrs using p_bukrs. 
 8  
 9 at selection-screen on value-request for p_dburl. 
10   "获取数据库(.MDB)路径 
11   perform frm_get_filepath_dburl. 
12  
13 start-of-selection. 
14   "权限检查 
15   perform frm_auth_check_bukrs. 
16   "获取数据 
17   perform frm_get_data. 
18   "下载TXT文件 
19   perform frm_txt_download. 
20   "导入数据库表 
21   perform frm_txt_to_access. 
22   "删除TXT文件 
23   if p_deltx is not initial. 
24     perform frm_txt_delete_file. 
25   endif. 
26  
27 end-of-selection. 
28   "输出执行信息 
29   perform frm_exec_info.
View Code

 ZFIR0035_ACCOUNTDOC_EXPORT_FRM

  1 *&---------------------------------------------------------------------* 
  2 *&  包含                ZFIR0035_ACCOUNTDOC_EXPORT_FRM 
  3 *&---------------------------------------------------------------------* 
  4 *&---------------------------------------------------------------------* 
  5 *&      Form  FRM_GET_FILEPATH_DBURL 
  6 *&---------------------------------------------------------------------* 
  7 *       text 
  8 *----------------------------------------------------------------------* 
  9 *  -->  p1        text 
 10 *  <--  p2        text 
 11 *----------------------------------------------------------------------* 
 12 form frm_get_filepath_dburl . 
 13   call function 'WS_FILENAME_GET' 
 14     exporting 
 15       def_filename     = text-004 
 16       def_path         = 'D:\' 
 17       mask             = ',*.mdb,.' 
 18       mode             = 'O' 
 19       title            = text-003 
 20     importing 
 21       filename         = p_dburl 
 22     exceptions 
 23       inv_winsys       = 1 
 24       no_batch         = 2 
 25       selection_cancel = 3 
 26       selection_error  = 4 
 27       others           = 5. 
 28   case sy-subrc. 
 29     when 0. 
 30     when others. 
 31       exit. 
 32   endcase. 
 33 endform. 
 34 *&---------------------------------------------------------------------* 
 35 *&      Form  FRM_GET_DATA 
 36 *&---------------------------------------------------------------------* 
 37 *       text 
 38 *----------------------------------------------------------------------* 
 39 *  -->  p1        text 
 40 *  <--  p2        text 
 41 *----------------------------------------------------------------------* 
 42 form frm_get_data . 
 43   data: 
 44     lv_index type string, 
 45     lv_dmbtr type string, 
 46     lv_wrbtr type string, 
 47     lv_pswbt type string, 
 48     gv_zchar type c. 
 49  
 50   call function 'SAPGUI_PROGRESS_INDICATOR' 
 51     exporting 
 52       percentage = 30 
 53       text       = text-005. 
 54  
 55   gs_inf-qydat = sy-datum. 
 56   gs_inf-qytim = sy-uzeit. 
 57   gs_inf-dburl = p_dburl. 
 58  
 59   select single zchar 
 60     into gv_zchar 
 61     from ztfi0034_char. 
 62   if sy-subrc <> 0. 
 63     message e001(00) with text-042. 
 64   endif. 
 65  
 66   call function 'ZFI_01_ACCOUNTDOC_EXPORT' 
 67     exporting 
 68       p_bukrs  = p_bukrs 
 69     tables 
 70       it_budat = s_budat 
 71       et_data  = gt_data. 
 72  
 73   do 4000000 times. 
 74     read table gt_data assigning <fs_data> index 1. 
 75     if sy-subrc = 0. 
 76       append <fs_data> to gt_data. 
 77     endif. 
 78  
 79   enddo. 
 80  
 81   "增加标题字符 
 82   append initial line to gt_str assigning <fs_str>. 
 83   concatenate 
 84   gv_zchar 'ID'    gv_zchar gc_char 
 85   gv_zchar 'BUKRS' gv_zchar gc_char 
 86   gv_zchar 'BELNR' gv_zchar gc_char 
 87   gv_zchar 'GJAHR' gv_zchar gc_char 
 88   gv_zchar 'MONAT' gv_zchar gc_char 
 89   gv_zchar 'BLART' gv_zchar gc_char 
 90   gv_zchar 'BLDAT' gv_zchar gc_char 
 91   gv_zchar 'BUDAT' gv_zchar gc_char 
 92   gv_zchar 'CPUDT' gv_zchar gc_char 
 93   gv_zchar 'CPUTM' gv_zchar gc_char 
 94   gv_zchar 'USNAM' gv_zchar gc_char 
 95   gv_zchar 'TCODE' gv_zchar gc_char 
 96   gv_zchar 'STBLG' gv_zchar gc_char 
 97   gv_zchar 'STJAH' gv_zchar gc_char 
 98   gv_zchar 'BKTXT' gv_zchar gc_char 
 99   gv_zchar 'XSTOV' gv_zchar gc_char 
100   gv_zchar 'STGRD' gv_zchar gc_char 
101   gv_zchar 'PPNAM' gv_zchar gc_char 
102   gv_zchar 'UMSKZ' gv_zchar gc_char 
103   gv_zchar 'SHKZG' gv_zchar gc_char 
104   gv_zchar 'DMBTR' gv_zchar gc_char 
105   gv_zchar 'WRBTR' gv_zchar gc_char 
106   gv_zchar 'PSWBT' gv_zchar gc_char 
107   gv_zchar 'KOKRS' gv_zchar gc_char 
108   gv_zchar 'KOSTL' gv_zchar gc_char 
109   gv_zchar 'VBELN' gv_zchar gc_char 
110   gv_zchar 'VBEL2' gv_zchar gc_char 
111   gv_zchar 'HKONT' gv_zchar gc_char 
112   gv_zchar 'TXT20' gv_zchar gc_char 
113   gv_zchar 'XNEGP' gv_zchar gc_char 
114   into <fs_str>. 
115   unassign <fs_str>. 
116  
117   "增加标题文本 
118   append initial line to gt_str assigning <fs_str>. 
119   lv_index = lv_index + 1. 
120   concatenate 
121   gv_zchar lv_index gv_zchar gc_char 
122   gv_zchar text-011 gv_zchar gc_char 
123   gv_zchar text-012 gv_zchar gc_char 
124   gv_zchar text-013 gv_zchar gc_char 
125   gv_zchar text-014 gv_zchar gc_char 
126   gv_zchar text-015 gv_zchar gc_char 
127   gv_zchar text-016 gv_zchar gc_char 
128   gv_zchar text-017 gv_zchar gc_char 
129   gv_zchar text-018 gv_zchar gc_char 
130   gv_zchar text-019 gv_zchar gc_char 
131   gv_zchar text-020 gv_zchar gc_char 
132   gv_zchar text-021 gv_zchar gc_char 
133   gv_zchar text-022 gv_zchar gc_char 
134   gv_zchar text-023 gv_zchar gc_char 
135   gv_zchar text-024 gv_zchar gc_char 
136   gv_zchar text-025 gv_zchar gc_char 
137   gv_zchar text-026 gv_zchar gc_char 
138   gv_zchar text-027 gv_zchar gc_char 
139   gv_zchar text-028 gv_zchar gc_char 
140   gv_zchar text-029 gv_zchar gc_char 
141   gv_zchar text-030 gv_zchar gc_char 
142   gv_zchar text-031 gv_zchar gc_char 
143   gv_zchar text-032 gv_zchar gc_char 
144   gv_zchar text-033 gv_zchar gc_char 
145   gv_zchar text-034 gv_zchar gc_char 
146   gv_zchar text-035 gv_zchar gc_char 
147   gv_zchar text-036 gv_zchar gc_char 
148   gv_zchar text-037 gv_zchar gc_char 
149   gv_zchar text-038 gv_zchar gc_char 
150   gv_zchar text-039 gv_zchar 
151   into <fs_str>. 
152   unassign <fs_str>. 
153  
154   "数据明细 
155   loop at gt_data assigning <fs_data>. 
156     clear:lv_dmbtr,lv_wrbtr,lv_pswbt. 
157  
158     lv_index = lv_index + 1. 
159     lv_dmbtr = <fs_data>-dmbtr. 
160     lv_wrbtr = <fs_data>-wrbtr. 
161     lv_pswbt = <fs_data>-pswbt. 
162  
163     append initial line to gt_str assigning <fs_str>. 
164     concatenate 
165     gv_zchar lv_index          gv_zchar gc_char 
166     gv_zchar <fs_data>-bukrs   gv_zchar gc_char 
167     gv_zchar <fs_data>-belnr   gv_zchar gc_char 
168     gv_zchar <fs_data>-gjahr   gv_zchar gc_char 
169     gv_zchar <fs_data>-monat   gv_zchar gc_char 
170     gv_zchar <fs_data>-blart   gv_zchar gc_char 
171     gv_zchar <fs_data>-bldat   gv_zchar gc_char 
172     gv_zchar <fs_data>-budat   gv_zchar gc_char 
173     gv_zchar <fs_data>-cpudt   gv_zchar gc_char 
174     gv_zchar <fs_data>-cputm   gv_zchar gc_char 
175     gv_zchar <fs_data>-usnam   gv_zchar gc_char 
176     gv_zchar <fs_data>-tcode   gv_zchar gc_char 
177     gv_zchar <fs_data>-stblg   gv_zchar gc_char 
178     gv_zchar <fs_data>-stjah   gv_zchar gc_char 
179     gv_zchar <fs_data>-bktxt   gv_zchar gc_char 
180     gv_zchar <fs_data>-xstov   gv_zchar gc_char 
181     gv_zchar <fs_data>-stgrd   gv_zchar gc_char 
182     gv_zchar <fs_data>-ppnam   gv_zchar gc_char 
183     gv_zchar <fs_data>-umskz   gv_zchar gc_char 
184     gv_zchar <fs_data>-shkzg   gv_zchar gc_char 
185     gv_zchar lv_dmbtr          gv_zchar gc_char 
186     gv_zchar lv_wrbtr          gv_zchar gc_char 
187     gv_zchar lv_pswbt          gv_zchar gc_char 
188     gv_zchar <fs_data>-kokrs   gv_zchar gc_char 
189     gv_zchar <fs_data>-kostl   gv_zchar gc_char 
190     gv_zchar <fs_data>-vbeln   gv_zchar gc_char 
191     gv_zchar <fs_data>-vbel2   gv_zchar gc_char 
192     gv_zchar <fs_data>-hkont   gv_zchar gc_char 
193     gv_zchar <fs_data>-txt20   gv_zchar gc_char 
194     gv_zchar <fs_data>-xnegp   gv_zchar 
195     into <fs_str>. 
196     unassign <fs_str>. 
197   endloop. 
198  
199   describe table gt_str lines gs_inf-count. 
200 endform. 
201 *&---------------------------------------------------------------------* 
202 *&      Form  FRM_TXT_DOWNLOAD 
203 *&---------------------------------------------------------------------* 
204 *       text 
205 *----------------------------------------------------------------------* 
206 *  -->  p1        text 
207 *  <--  p2        text 
208 *----------------------------------------------------------------------* 
209 form frm_txt_download . 
210   data: 
211     lv_filetype type char10         value 'DAT',  "DAT 
212     lv_codepage type abap_encoding  value '8404'. 
213  
214   call function 'SAPGUI_PROGRESS_INDICATOR' 
215     exporting 
216       percentage = 60 
217       text       = text-006. 
218  
219   gs_inf-txdat = sy-datum. 
220   gs_inf-txtim = sy-uzeit. 
221  
222   concatenate 
223   p_bukrs       gc_char1 
224   s_budat-low   gc_char1 
225   s_budat-high 
226   into gv_tabnm. 
227  
228   gs_inf-tabnm = gv_tabnm. 
229  
230   concatenate 
231   'd:\' 
232   gv_tabnm 
233   '.TXT' 
234   into gv_txurl. 
235  
236   "内表数据保存本地 
237   call function 'GUI_DOWNLOAD' 
238     exporting 
239       confirm_overwrite     = gc_check      "如果文件存在 弹出是否覆盖文件的对话框 
240       write_field_separator = space         "加入字段分隔符 TAB 
241       filename              = gv_txurl      "文件名 必须为 STRING 类型 
242       filetype              = lv_filetype 
243       codepage              = lv_codepage 
244     tables 
245       data_tab              = gt_str   "内表 
246     exceptions 
247       file_write_error      = 1 
248       file_not_found        = 2. 
249  
250 endform. 
251 *&---------------------------------------------------------------------* 
252 *&      Form  FRM_TXT_TO_ACCESS 
253 *&---------------------------------------------------------------------* 
254 *       text 
255 *----------------------------------------------------------------------* 
256 *  -->  p1        text 
257 *  <--  p2        text 
258 *----------------------------------------------------------------------* 
259 form frm_txt_to_access . 
260   data: 
261     lv_conn type string, 
262     lv_fild type string, 
263     lv_hstr type string, 
264     lv_sqlh type string, 
265     lv_sqli type string, 
266     lv_crtb type string, 
267     lv_detb type string, 
268     lv_qytb type string, 
269     lv_infd type string, 
270     lv_col  type string, 
271     lv_txt  type string, 
272     lv_cont type i. 
273  
274   call function 'SAPGUI_PROGRESS_INDICATOR' 
275     exporting 
276       percentage = 80 
277       text       = text-007. 
278  
279   gs_inf-acdat = sy-datum. 
280   gs_inf-actim = sy-uzeit. 
281  
282   concatenate 
283   gc_datasour 
284   p_dburl 
285   into gv_datasour. 
286  
287   concatenate 
288   gc_provider gc_char2 
289   gc_security gc_char2 
290   gv_datasour 
291   into lv_conn. 
292  
293   "TXT文件 
294   concatenate 
295   'FROM [Text;FMT=CSVDelimited;HDR=Yes;IMEX=1;DATABASE=D:\;].[' 
296   gv_tabnm 
297   '#txt];' 
298   into lv_txt. 
299  
300   "Excel文件 
301 *  concatenate 
302 *  'FROM [Excel 8.0;DATABASE=C:\' 
303 *  gv_tabnm 
304 *  '.xls].[Sheet1]' 
305 *  into lv_txt. 
306  
307   "删除表 
308   concatenate 
309   'DROP TABLE' 
310   gv_tabnm 
311   into lv_detb separated by space. 
312  
313   concatenate 
314   'VALUES (' 
315   gc_char3 text-011 gc_char3 gc_char 
316   gc_char3 text-012 gc_char3 gc_char 
317   gc_char3 text-013 gc_char3 gc_char 
318   gc_char3 text-014 gc_char3 gc_char 
319   gc_char3 text-015 gc_char3 gc_char 
320   gc_char3 text-016 gc_char3 gc_char 
321   gc_char3 text-017 gc_char3 gc_char 
322   gc_char3 text-018 gc_char3 gc_char 
323   gc_char3 text-019 gc_char3 gc_char 
324   gc_char3 text-020 gc_char3 gc_char 
325   gc_char3 text-021 gc_char3 gc_char 
326   gc_char3 text-022 gc_char3 gc_char 
327   gc_char3 text-023 gc_char3 gc_char 
328   gc_char3 text-024 gc_char3 gc_char 
329   gc_char3 text-025 gc_char3 gc_char 
330   gc_char3 text-026 gc_char3 gc_char 
331   gc_char3 text-027 gc_char3 gc_char 
332   gc_char3 text-028 gc_char3 gc_char 
333   gc_char3 text-029 gc_char3 gc_char 
334   gc_char3 text-030 gc_char3 gc_char 
335   gc_char3 text-031 gc_char3 gc_char 
336   gc_char3 text-032 gc_char3 gc_char 
337   gc_char3 text-033 gc_char3 gc_char 
338   gc_char3 text-034 gc_char3 gc_char 
339   gc_char3 text-035 gc_char3 gc_char 
340   gc_char3 text-036 gc_char3 gc_char 
341   gc_char3 text-037 gc_char3 gc_char 
342   gc_char3 text-038 gc_char3 gc_char 
343   gc_char3 text-039 gc_char3 
344   ')' 
345   into lv_hstr. 
346  
347   "创建数据表-SQL语句 
348   concatenate 
349   'Create TABLE' 
350   gv_tabnm 
351   '(' 
352   'ID COUNTER NOT NULL,' 
353   'BUKRS TEXT(4),' 
354   'BELNR TEXT(15),' 
355   'GJAHR TEXT(4),' 
356   'MONAT TEXT(4),' 
357   'BLART TEXT(4),' 
358   'BLDAT TEXT(12),' 
359   'BUDAT TEXT(12),' 
360   'CPUDT TEXT(12),' 
361   'CPUTM TEXT(10),' 
362   'USNAM TEXT(12),' 
363   'TCODE TEXT(20),' 
364   'STBLG TEXT(15),' 
365   'STJAH TEXT(6),' 
366   'BKTXT TEXT(25),' 
367   'XSTOV TEXT(10),' 
368   'STGRD TEXT(4),' 
369   'PPNAM TEXT(12),' 
370   'UMSKZ TEXT(6),' 
371   'SHKZG TEXT(4),' 
372   'DMBTR TEXT(14),' 
373   'WRBTR TEXT(14),' 
374   'PSWBT TEXT(14),' 
375   'KOKRS TEXT(4),' 
376   'KOSTL TEXT(10),' 
377   'VBELN TEXT(15),' 
378   'VBEL2 TEXT(15),' 
379   'HKONT TEXT(10),' 
380   'TXT20 TEXT(20),' 
381   'XNEGP TEXT(6)' 
382   ')' 
383   into lv_crtb separated by space. 
384  
385   "数据写入-SQL语句-Insert字段明细 
386   concatenate 
387   gv_tabnm 
388   '(' 
389   'BUKRS'  gc_char 
390   'BELNR'  gc_char 
391   'GJAHR'  gc_char 
392   'MONAT'  gc_char 
393   'BLART'  gc_char 
394   'BLDAT'  gc_char 
395   'BUDAT'  gc_char 
396   'CPUDT'  gc_char 
397   'CPUTM'  gc_char 
398   'USNAM'  gc_char 
399   'TCODE'  gc_char 
400   'STBLG'  gc_char 
401   'STJAH'  gc_char 
402   'BKTXT'  gc_char 
403   'XSTOV'  gc_char 
404   'STGRD'  gc_char 
405   'PPNAM'  gc_char 
406   'UMSKZ'  gc_char 
407   'SHKZG'  gc_char 
408   'DMBTR'  gc_char 
409   'WRBTR'  gc_char 
410   'PSWBT'  gc_char 
411   'KOKRS'  gc_char 
412   'KOSTL'  gc_char 
413   'VBELN'  gc_char 
414   'VBEL2'  gc_char 
415   'HKONT'  gc_char 
416   'TXT20'  gc_char 
417   'XNEGP' 
418   ')' 
419   into lv_infd. 
420  
421   "数据导入-SQL语句-Select字段明细 
422   concatenate 
423   'F1  AS ID'    gc_char 
424   'F2  AS BUKRS' gc_char 
425   'F3  AS BELNR' gc_char 
426   'F4  AS GJAHR' gc_char 
427   'F5  AS MONAT' gc_char 
428   'F6  AS BLART' gc_char 
429   'F7  AS BLDAT' gc_char 
430   'F8  AS BUDAT' gc_char 
431   'F9  AS CPUDT' gc_char 
432   'F10 AS CPUTM' gc_char 
433   'F11 AS USNAM' gc_char 
434   'F12 AS TCODE' gc_char 
435   'F13 AS STBLG' gc_char 
436   'F14 AS STJAH' gc_char 
437   'F15 AS BKTXT' gc_char 
438   'F16 AS XSTOV' gc_char 
439   'F17 AS STGRD' gc_char 
440   'F18 AS PPNAM' gc_char 
441   'F19 AS UMSKZ' gc_char 
442   'F20 AS SHKZG' gc_char 
443   'F21 AS DMBTR' gc_char 
444   'F22 AS WRBTR' gc_char 
445   'F23 AS PSWBT' gc_char 
446   'F24 AS KOKRS' gc_char 
447   'F25 AS KOSTL' gc_char 
448   'F26 AS VBELN' gc_char 
449   'F27 AS VBEL2' gc_char 
450   'F28 AS HKONT' gc_char 
451   'F29 AS TXT20' gc_char 
452   'F30 AS XNEGP' 
453   into lv_fild. 
454  
455   "SQL语句-查询记录数 
456  
457   "SQL语句-导入已存在表记录-数据抬头 
458   concatenate 
459   'INSERT INTO' 
460   lv_infd 
461   lv_hstr 
462   into lv_sqlh separated by space. 
463  
464   "SQL语句--创建表同时导入数据 
465   concatenate 
466   'SELECT * ' 
467   "lv_fild 
468   'INTO' 
469   gv_tabnm 
470   lv_txt 
471   into lv_sqli separated by space. 
472  
473   "SQL语句--导入已存在表记录-数据明细 
474 *  concatenate 
475 *  'INSERT INTO' 
476 *  lv_infd 
477 *  'SELECT' 
478 *  lv_fild 
479 *  lv_txt 
480 *  into lv_sqli separated by space. 
481  
482   "数据库连接 
483   create object go_conn 'ADODB.Connection'. 
484   set property of go_conn 'Provider'          = gc_provider. 
485   set property of go_conn 'ConnectionString'  = lv_conn. 
486   call method of go_conn 'Open'. 
487  
488   call method of go_conn 'BeginTrans' = lv_cont. 
489  
490 *  "创建表 
491 *  call method of go_conn 'Execute' = go_rset 
492 *    exporting 
493 *      #1 = lv_crtb. 
494 * 
495 *  "写入标题数据 
496 *  call method of go_conn 'Execute' = go_rset 
497 *    exporting 
498 *      #1 = lv_sqlh. 
499  
500   "写入明细数据 
501   call method of go_conn 'Execute' = go_rset 
502     exporting 
503       #1 = lv_sqli. 
504  
505   call method of go_conn 'CommitTrans' = lv_cont. 
506  
507   "关闭及释放 
508   call method of go_rset 'Close'. 
509   call method of go_conn 'Close'. 
510   call method of go_rtab 'Close'. 
511   free object go_rset. 
512   free object go_rtab. 
513   free object go_conn. 
514  
515   "完成时间 
516   gs_inf-fhdat = sy-datum. 
517   gs_inf-fhtim = sy-uzeit. 
518  
519   if gs_inf-fhdat = gs_inf-qydat. 
520     gs_inf-ustim = gs_inf-fhtim - gs_inf-qytim. 
521   else. 
522     gs_inf-ustim = ( gs_inf-fhdat - gs_inf-qydat ) * 240000 - gs_inf-fhtim + gs_inf-qytim. 
523   endif. 
524  
525 endform. 
526 *&---------------------------------------------------------------------* 
527 *&      Form  FRM_TXT_DELETE_FILE 
528 *&---------------------------------------------------------------------* 
529 *       text 
530 *----------------------------------------------------------------------* 
531 *  -->  p1        text 
532 *  <--  p2        text 
533 *----------------------------------------------------------------------* 
534 form frm_txt_delete_file . 
535   data:lv_rc type i. 
536  
537   call function 'SAPGUI_PROGRESS_INDICATOR' 
538     exporting 
539       percentage = 100 
540       text       = text-008. 
541  
542   call method cl_gui_frontend_services=>file_delete 
543     exporting 
544       filename = gv_txurl 
545     changing 
546       rc       = lv_rc. 
547  
548 endform. 
549 *&---------------------------------------------------------------------* 
550 *&      Form  FRM_EXEC_INFO 
551 *&---------------------------------------------------------------------* 
552 *       text 
553 *----------------------------------------------------------------------* 
554 *  -->  p1        text 
555 *  <--  p2        text 
556 *----------------------------------------------------------------------* 
557 form frm_exec_info . 
558   skip. 
559   write:/10 text-050. 
560   write:/10(60) sy-uline. 
561   write:/20 text-051,35 p_bukrs. 
562   write:/20 text-052,35 s_budat-low,50 s_budat-high. 
563   write:/20 text-053,35 p_dburl. 
564   write:/20 text-054,35 gs_inf-tabnm. 
565   write:/20 text-055,35 sy-uname. 
566   skip. 
567   write:/10 text-056. 
568   write:/10(60) sy-uline. 
569   write:/20 text-057,35 gs_inf-count. 
570   write:/20 text-058,35 gs_inf-qydat,50 gs_inf-qytim. 
571   write:/20 text-059,35 gs_inf-txdat,50 gs_inf-txtim. 
572   write:/20 text-060,35 gs_inf-acdat,50 gs_inf-actim. 
573   write:/20 text-061,35 gs_inf-fhdat,50 gs_inf-fhtim. 
574   write:/20 text-062,35 gs_inf-ustim. 
575  
576 endform. 
577 *&---------------------------------------------------------------------* 
578 *&      Form  FRM_GET_AUTH_BUKRS 
579 *&---------------------------------------------------------------------* 
580 *       text 
581 *----------------------------------------------------------------------* 
582 *      -->P_P_BUKRS  text 
583 *----------------------------------------------------------------------* 
584 form frm_get_auth_bukrs  using fv_bukrs. 
585   data: 
586     lt_value type table of usvalues. 
587   field-symbols: 
588     <fs_value> type usvalues. 
589  
590   call function 'SUSR_USER_AUTH_FOR_OBJ_GET' 
591     exporting 
592       user_name           = sy-uname 
593       sel_object          = 'F_BKPF_BUK' 
594     tables 
595       values              = lt_value 
596     exceptions 
597       user_name_not_exist = 1 
598       not_authorized      = 2 
599       internal_error      = 3 
600       others              = 4. 
601   if sy-subrc = 0 . 
602     loop at lt_value assigning <fs_value> 
603     where field = 'BUKRS' 
604       and von ne '*'. 
605       select single count(*) 
606         from t001 
607        where bukrs = <fs_value>-von. 
608       if sy-subrc = 0. 
609         fv_bukrs  = <fs_value>-von. 
610       endif. 
611     endloop. 
612   endif. 
613  
614 endform. 
615 *&---------------------------------------------------------------------* 
616 *&      Form  FRM_AUTH_CHECK_BUKRS 
617 *&---------------------------------------------------------------------* 
618 *       text 
619 *----------------------------------------------------------------------* 
620 *  -->  p1        text 
621 *  <--  p2        text 
622 *----------------------------------------------------------------------* 
623 form frm_auth_check_bukrs . 
624   authority-check 
625   object 'F_BKPF_BUK' 
626   id 'BUKRS' 
627   field p_bukrs. 
628   if sy-subrc ne 0. 
629     message e001(00) with p_bukrs text-009. 
630   endif. 
631  
632   select single count(*) 
633     from t001 
634    where bukrs = p_bukrs. 
635   if sy-subrc <> 0. 
636     message e001(00) with p_bukrs text-040. 
637   endif. 
638  
639   if s_budat-low  is initial 
640   or s_budat-high is initial. 
641     message e001(00) with text-041. 
642   endif. 
643 endform.
View Code

评论关闭
IT干货网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!