ABAP-2-会计凭证批量数据导入本地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

posted on 2019-01-31 12:36 ricoo 阅读(1197) 评论(0) 编辑收藏举报