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

公司会计凭证导入ACCESS数据库，需要发送给审计，原先的方案是采用DEPHI开发的功能（调用函数获取会计凭证信息，然后INSERT到ACCESS数据表），运行速度非常慢，业务方要求对该功能进行优化，需要对原先方案进行整体评估并出具解决方案。

1.方案评估

1.1原方案分析

原先采用的是DEPHI开发工具，通过调用RFC接口获取会计凭证数据，然后通过OLEDB连接ACCESS数据库，将会计凭证信息转换为数据集RECORDSET，最后循环Recordset并通过Insert into tab(…..) values() 到ACCESS数据表。

运行速度慢主要在这两部分：SAP-RFC接口，因每月有千万级的数据量，运行速度慢而且容易导致内存溢出；采用OLEDB通过INSERT写入ACCESS数据表速度过慢。

1.2业务数据量分析

业务操作习惯是查询每月千万级数据量并导出，但是运行过程中将数据写入内表经常导致内存溢出，这个只能这样处理：

a.根据过账日期缩小查询范围，降低数据量，分多次执行。

b.需要BASIS去优化服务器内存，扩大容量。

1.3导入ACCESS方式分析

https://stackoverflow.com/questions/7070011/writing-large-number-of-records-bulk-insert-to-access-in-net-c

02.8 seconds:

Use DAO, use
DAO.Field
‘s to refer to the table columns
02.8 seconds:

Write out to a text file, use Automation to import the text into Access
11.0 seconds:

Use DAO, use the column index to refer to the table columns.
17.0 seconds:

Use DAO, refer to the column by name
79.0 seconds:

Use ADO.NET, generate INSERT statements for each row
86.0 seconds:

Use ADO.NET, use DataTable to an DataAdapter for “batch” insert

参考大神的测试记录，发现用DAO及TXT文本导入的方式是最快的，采用ADO通过INSERT方式效率基本最低（果断弃用）。

最后的DAO与TXT导入方式进行抉择，两种方式测试结果：

A.应用DAO

DAO并非COM组件，在SAP里面无法直接Creat object调用，为此想到的是通过VB将DAO相关方法封装生成DLL文件，在系统中注册后供ABAP进行调用，但是最后还是败给了表数据的传输（将ABAP内表数据传输给DLL方法中的DATATABLE传入参数），无法找到解决方式，然后想到通过字符串的方式解决传参问题（在ABAP中将内表数据进行拼接，传输到DLL中通过特定字符进行拆解，转化成数据集），通过字符串传参方式执行速度也很慢（10W数据执行3SEC），传参问题无法解决，也放弃了。（若有人解决ABAP内表数据与DLL传参问题，请详细告知，不胜感激）

B.TXT导入

执行SAP程序，通过将SAP内表数据导出到本地TXT文件，然后创建ADO对象将数据导入到ACCESS数据表。这个过程中也出现一些问题，后续会讲述。

1.4方案确认

一切为了效率，最后决定采用TXT文件导入的方式来处理。

2.TXT导入ACCESS

2.1获取会计凭证接口

SAP提供获取会计凭证的RFC接口，具体代码如下：

 1 function zfi_01_accountdoc_export.
 2 *"----------------------------------------------------------------------
 3 *"*"本地接口：
 4 *"  IMPORTING
 5 *"     VALUE(P_BUKRS) TYPE  BUKRS
 6 *"  TABLES
 7 *"      IT_BUDAT STRUCTURE  FAGL_RANGE_BUDAT
 8 *"      ET_DATA STRUCTURE  ZSFI0018_XSZ OPTIONAL
 9 *"----------------------------------------------------------------------
10 
11   data:
12     lt_bkpf like table of bkpf,
13     lt_bseg like table of bseg,
14     lt_skat like table of skat.
15   field-symbols:
16     <fs_bseg> type bseg,
17     <fs_bkpf> type bkpf,
18     <fs_skat> type skat,
19     <fs_data> type zsfi0018_xsz.
20 
21   select *
22     into table lt_bkpf
23     from bkpf
24    where bukrs = p_bukrs
25      and budat in it_budat
26      and bstat not in ('V','W','Z').
27 
28   sort lt_bkpf by bukrs belnr gjahr.
29 
30   if lt_bkpf[] is not initial.
31      select *
32       into corresponding fields of table et_data
33       from bseg
34       for all entries in lt_bkpf
35      where bukrs = p_bukrs
36        and belnr = lt_bkpf-belnr
37        and gjahr = lt_bkpf-gjahr.
38 
39     loop at et_data assigning <fs_data>.
40       append initial line to lt_skat assigning <fs_skat>.
41       <fs_skat>-saknr = <fs_data>-hkont.
42       unassign <fs_skat>.
43     endloop.
44 
45     sort lt_skat by saknr.
46     delete adjacent duplicates from lt_skat.
47 
48     loop at lt_skat assigning <fs_skat>.
49       select single txt20
50         into <fs_skat>-txt20
51         from skat
52        where spras = sy-langu
53          and ktopl = 'GLCN'
54          and saknr = <fs_skat>-saknr.
55     endloop.
56   endif.
57 
58   sort et_data by bukrs belnr gjahr.
59   sort lt_skat by saknr.
60 
61   loop at et_data assigning <fs_data>.
62     read table lt_bkpf
63     assigning <fs_bkpf>
64     with key
65     bukrs = <fs_data>-bukrs
66     belnr = <fs_data>-belnr
67     gjahr = <fs_data>-gjahr
68     binary search.
69     if sy-subrc = 0.
70       <fs_data>-monat = <fs_bkpf>-monat.
71       <fs_data>-cpudt = <fs_bkpf>-cpudt.
72       <fs_data>-cputm = <fs_bkpf>-cputm.
73       <fs_data>-blart = <fs_bkpf>-blart.
74       <fs_data>-bldat = <fs_bkpf>-bldat.
75       <fs_data>-budat = <fs_bkpf>-budat.
76       <fs_data>-usnam = <fs_bkpf>-usnam.
77       <fs_data>-tcode = <fs_bkpf>-tcode.
78       <fs_data>-stblg = <fs_bkpf>-stblg.
79       <fs_data>-stjah = <fs_bkpf>-stjah.
80       <fs_data>-bktxt = <fs_bkpf>-bktxt.
81       <fs_data>-xstov = <fs_bkpf>-xstov.
82       <fs_data>-stgrd = <fs_bkpf>-stgrd.
83       <fs_data>-ppnam = <fs_bkpf>-ppnam.
84     endif.
85 
86     read table lt_skat
87     assigning <fs_skat>
88     with key
89     saknr = <fs_data>-hkont
90     binary search.
91     if sy-subrc = 0.
92       <fs_data>-txt20 = <fs_skat>-txt20.
93     endif.
94     unassign:<fs_skat>,<fs_bkpf>.
95   endloop.
96 
97 endfunction.

View Code

2.2SAP执行程序开发

程序名：ZFIR0035_ACCOUNTDOC_EXPORT

report zfir0035_accountdoc_export.

include zapi0001_prg_info.

include zfir0035_accountdoc_export_top.  "define

include zfir0035_accountdoc_export_scr.  "screen

include zfir0035_accountdoc_export_pro.  "processing

include zfir0035_accountdoc_export_frm.  "subroutine

ZFIR0035_ACCOUNTDOC_EXPORT_TOP程序：

*&---------------------------------------------------------------------*
*&  包含                ZFIR0035_ACCOUNTDOC_EXPORT_TOP
*&---------------------------------------------------------------------*

include ole2incl.

tables:
  mkpf,
  mseg.

constants:
  gc_provider type string value 'Provider=Microsoft.Jet.OLEDB.4.0',
  gc_security type string value 'Persist Security Info=False',
  gc_datasour type string value 'Data Source='.

constants:
  gc_char  type c  value ',',
  gc_char3 type c  value '''',
  gc_char1 type c  value '_',
  gc_char2 type c  value ';',
  gc_check type c  value 'X'.

data:
  begin of wa_inf,
    count type i,       "总记录数
    dburl type string,  "数据库路径
    tabnm type string,  "表名
    qydat type datum,   "获取数据-日期
    qytim type uzeit,   "获取数据-时间
    txdat type datum,   "下载TXT-日期
    txtim type uzeit,   "下载TXT-时间
    acdat type datum,   "导入DB-日期
    actim type uzeit,   "导入DB-时间
    fhdat type datum,   "执行结束-日期
    fhtim type uzeit,   "执行结束-时间
    ustim type uzeit,   "总用时
  end of wa_inf.

data:
  go_conn type ole2_object,
  go_rset type ole2_object,
  go_rtab type ole2_object.

data:
  gt_str  type table of string,
  gt_data type table of zsfi0018_xsz,
  gt_inf  like table of wa_inf.

data:
  gv_txurl  type string,
  gv_tabnm  type string,
  gv_datasour type string.

data:
  gs_inf    like wa_inf.

field-symbols:
  <fs_str>  type string,
  <fs_inf>  like wa_inf,
  <fs_data> type zsfi0018_xsz.

View Code

ZFIR0035_ACCOUNTDOC_EXPORT_SCR程序：

*&---------------------------------------------------------------------*
*&  包含                ZFIR0035_ACCOUNTDOC_EXPORT_SCR
*&---------------------------------------------------------------------*

selection-screen begin of block block1 with frame title text-001.
  parameter:
    p_bukrs type bukrs obligatory.
  select-options:
    s_budat for  mkpf-budat obligatory.
selection-screen end of block block1.

selection-screen begin of block block2 with frame title text-002.
  parameters:
    p_dburl type rlgrap-filename obligatory.
selection-screen end of block block2.

View Code

ZFIR0035_ACCOUNTDOC_EXPORT_PRO程序：

*&---------------------------------------------------------------------*
*&  包含                ZFIR0035_ACCOUNTDOC_EXPORT_PRO
*&---------------------------------------------------------------------*

initialization.
  "初始-公司代码
  perform frm_get_auth_bukrs using p_bukrs.

at selection-screen on value-request for p_dburl.
  "获取数据库(.MDB)路径
  perform frm_get_filepath_dburl.

start-of-selection.
  "权限检查
  perform frm_auth_check_bukrs.
  "获取数据
  perform frm_get_data.
  "下载TXT文件
  perform frm_txt_download.
  "导入数据库表
  perform frm_txt_to_access.
  "删除TXT文件
  perform frm_txt_delete_file.

end-of-selection.
  "输出执行信息
  perform frm_exec_info.

View Code

ZFIR0035_ACCOUNTDOC_EXPORT_FRM程序：

*&---------------------------------------------------------------------*
*&  包含                ZFIR0035_ACCOUNTDOC_EXPORT_FRM
*&---------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*&      Form  FRM_GET_FILEPATH_DBURL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form frm_get_filepath_dburl .
  call function 'WS_FILENAME_GET'
    exporting
      def_filename     = text-004
      def_path         = 'D:\'
      mask             = ',*.mdb,.'
      mode             = 'O'
      title            = text-003
    importing
      filename         = p_dburl
    exceptions
      inv_winsys       = 1
      no_batch         = 2
      selection_cancel = 3
      selection_error  = 4
      others           = 5.
  case sy-subrc.
    when 0.
    when others.
      exit.
  endcase.
endform.
*&---------------------------------------------------------------------*
*&      Form  FRM_GET_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form frm_get_data .
  data:
    lv_index type string,
    lv_dmbtr type string,
    lv_wrbtr type string,
    lv_pswbt type string,
    lv_zchar type c.

  call function 'SAPGUI_PROGRESS_INDICATOR'
    exporting
      percentage = 30
      text       = text-005.

  gs_inf-qydat = sy-datum.
  gs_inf-qytim = sy-uzeit.
  gs_inf-dburl = p_dburl.

  call function 'ZFI_01_ACCOUNTDOC_EXPORT'
    exporting
      p_bukrs  = p_bukrs
    tables
      it_budat = s_budat
      et_data  = gt_data.

  "lv_index = 1.

  select single zchar
    into lv_zchar
    from ztfi0034_char.
  if sy-subrc <> 0.
    message e001(00) with text-042.
  endif.

  loop at gt_data assigning <fs_data>.
    clear:lv_dmbtr,lv_wrbtr,lv_pswbt.

    "lv_index = lv_index + 1.
    lv_dmbtr = <fs_data>-dmbtr.
    lv_wrbtr = <fs_data>-wrbtr.
    lv_pswbt = <fs_data>-pswbt.

    append initial line to gt_str assigning <fs_str>.

    concatenate
    "lv_index        gc_char
    lv_zchar <fs_data>-bukrs   lv_zchar gc_char
    lv_zchar <fs_data>-belnr   lv_zchar gc_char
    lv_zchar <fs_data>-gjahr   lv_zchar gc_char
    lv_zchar <fs_data>-monat   lv_zchar gc_char
    lv_zchar <fs_data>-blart   lv_zchar gc_char
    <fs_data>-bldat   gc_char
    <fs_data>-budat   gc_char
    <fs_data>-cpudt   gc_char
    <fs_data>-cputm   gc_char
    lv_zchar <fs_data>-usnam   lv_zchar gc_char
    lv_zchar <fs_data>-tcode   lv_zchar gc_char
    lv_zchar <fs_data>-stblg   lv_zchar gc_char
    lv_zchar <fs_data>-stjah   lv_zchar gc_char
    lv_zchar <fs_data>-bktxt   lv_zchar gc_char
    lv_zchar <fs_data>-xstov   lv_zchar gc_char
    lv_zchar <fs_data>-stgrd   lv_zchar gc_char
    lv_zchar <fs_data>-ppnam   lv_zchar gc_char
    lv_zchar <fs_data>-umskz   lv_zchar gc_char
    lv_zchar <fs_data>-shkzg   lv_zchar gc_char
    lv_dmbtr          gc_char
    lv_wrbtr          gc_char
    lv_pswbt          gc_char
    lv_zchar <fs_data>-kokrs   lv_zchar gc_char
    lv_zchar <fs_data>-kostl   lv_zchar gc_char
    lv_zchar <fs_data>-vbeln   lv_zchar gc_char
    lv_zchar <fs_data>-vbel2   lv_zchar gc_char
    lv_zchar <fs_data>-hkont   lv_zchar gc_char
    lv_zchar <fs_data>-txt20   lv_zchar gc_char
    lv_zchar <fs_data>-xnegp   lv_zchar
    into <fs_str>.


*    concatenate
*    "lv_index        gc_char
*    gc_char3 <fs_data>-bukrs gc_char3 gc_char
*    gc_char3 <fs_data>-belnr gc_char3 gc_char
*    gc_char3 <fs_data>-gjahr gc_char3 gc_char
*    gc_char3 <fs_data>-monat gc_char3 gc_char
*    gc_char3 <fs_data>-blart gc_char3 gc_char
*    gc_char3 <fs_data>-bldat gc_char3 gc_char
*    gc_char3 <fs_data>-budat gc_char3 gc_char
*    gc_char3 <fs_data>-cpudt gc_char3 gc_char
*    gc_char3 <fs_data>-cputm gc_char3 gc_char
*    gc_char3 <fs_data>-usnam gc_char3 gc_char
*    gc_char3 <fs_data>-tcode gc_char3 gc_char
*    gc_char3 <fs_data>-stblg gc_char3 gc_char
*    gc_char3 <fs_data>-stjah gc_char3 gc_char
*    gc_char3 <fs_data>-bktxt gc_char3 gc_char
*    gc_char3 <fs_data>-xstov gc_char3 gc_char
*    gc_char3 <fs_data>-stgrd gc_char3 gc_char
*    gc_char3 <fs_data>-ppnam gc_char3 gc_char
*    gc_char3 <fs_data>-umskz gc_char3 gc_char
*    gc_char3 <fs_data>-shkzg gc_char3 gc_char
*    gc_char3 lv_dmbtr        gc_char3 gc_char
*    gc_char3 lv_wrbtr        gc_char3 gc_char
*    gc_char3 lv_pswbt        gc_char3 gc_char
*    gc_char3 <fs_data>-kokrs gc_char3 gc_char
*    gc_char3 <fs_data>-kostl gc_char3 gc_char
*    gc_char3 <fs_data>-vbeln gc_char3 gc_char
*    gc_char3 <fs_data>-vbel2 gc_char3 gc_char
*    gc_char3 <fs_data>-hkont gc_char3 gc_char
*    gc_char3 <fs_data>-txt20 gc_char3 gc_char
*    gc_char3 <fs_data>-xnegp gc_char3
*    into <fs_str>.

    unassign <fs_str>.
  endloop.

  describe table gt_str lines gs_inf-count.
endform.
*&---------------------------------------------------------------------*
*&      Form  FRM_TXT_DOWNLOAD
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form frm_txt_download .
  data:
    lv_filetype type char10         value 'DAT',  "DAT
    lv_codepage type abap_encoding  value '8404'.

  call function 'SAPGUI_PROGRESS_INDICATOR'
    exporting
      percentage = 60
      text       = text-006.

  gs_inf-txdat = sy-datum.
  gs_inf-txtim = sy-uzeit.

  concatenate
  p_bukrs       gc_char1
  s_budat-low   gc_char1
  s_budat-high
  into gv_tabnm.

  gs_inf-tabnm = gv_tabnm.

  concatenate
  'c:\'
  gv_tabnm
  '.TXT'
  into gv_txurl.

  "内表数据保存本地
  call function 'GUI_DOWNLOAD'
    exporting
      confirm_overwrite     = gc_check      "如果文件存在 弹出是否覆盖文件的对话框
      write_field_separator = space         "加入字段分隔符 TAB
      filename              = gv_txurl      "文件名 必须为 STRING 类型
      filetype              = lv_filetype
      codepage              = lv_codepage
    tables
      data_tab              = gt_str   "内表
    exceptions
      file_write_error      = 1
      file_not_found        = 2.

*  data:lv_url type rlgrap-filename.
*  lv_url = gv_txurl.
*
*  call function 'SAP_CONVERT_TO_XLS_FORMAT'
*    exporting
**     I_FIELD_SEPERATOR =
**     I_LINE_HEADER     =
*      i_filename        = lv_url
**     I_APPL_KEEP       = ' '
*    tables
*      i_tab_sap_data    = gt_data
** CHANGING
**     I_TAB_CONVERTED_DATA       =
*    exceptions
*      conversion_failed = 1
*      others            = 2.
*  if sy-subrc <> 0.
** Implement suitable error handling here
*  endif.

endform.
*&---------------------------------------------------------------------*
*&      Form  FRM_TXT_TO_ACCESS
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form frm_txt_to_access .
  data:
    lv_conn type string,
    lv_fild type string,
    lv_hstr type string,
    lv_sqlh type string,
    lv_sqli type string,
    lv_crtb type string,
    lv_detb type string,
    lv_qytb type string,
    lv_infd type string,
    lv_col  type string,
    lv_txt  type string,
    lv_cont type i.

  call function 'SAPGUI_PROGRESS_INDICATOR'
    exporting
      percentage = 80
      text       = text-007.

  gs_inf-acdat = sy-datum.
  gs_inf-actim = sy-uzeit.

  concatenate
  gc_datasour
  p_dburl
  into gv_datasour.

  concatenate
  gc_provider gc_char2
  gc_security gc_char2
  gv_datasour
  into lv_conn.

  "TXT文件
  concatenate
  'FROM [Text;FMT=CSVDelimited;HDR=No;IMEX=1;DATABASE=C:\;].['
  gv_tabnm
  '#txt];'
  into lv_txt.

  "Excel文件
*  concatenate
*  'FROM [Excel 8.0;DATABASE=C:\'
*  gv_tabnm
*  '.xls].[Sheet1]'
*  into lv_txt.

  "删除表
  concatenate
  'DROP TABLE'
  gv_tabnm
  into lv_detb separated by space.

  concatenate
  'VALUES ('
  gc_char3 text-011 gc_char3 gc_char
  gc_char3 text-012 gc_char3 gc_char
  gc_char3 text-013 gc_char3 gc_char
  gc_char3 text-014 gc_char3 gc_char
  gc_char3 text-015 gc_char3 gc_char
  gc_char3 text-016 gc_char3 gc_char
  gc_char3 text-017 gc_char3 gc_char
  gc_char3 text-018 gc_char3 gc_char
  gc_char3 text-019 gc_char3 gc_char
  gc_char3 text-020 gc_char3 gc_char
  gc_char3 text-021 gc_char3 gc_char
  gc_char3 text-022 gc_char3 gc_char
  gc_char3 text-023 gc_char3 gc_char
  gc_char3 text-024 gc_char3 gc_char
  gc_char3 text-025 gc_char3 gc_char
  gc_char3 text-026 gc_char3 gc_char
  gc_char3 text-027 gc_char3 gc_char
  gc_char3 text-028 gc_char3 gc_char
  gc_char3 text-029 gc_char3 gc_char
  gc_char3 text-030 gc_char3 gc_char
  gc_char3 text-031 gc_char3 gc_char
  gc_char3 text-032 gc_char3 gc_char
  gc_char3 text-033 gc_char3 gc_char
  gc_char3 text-034 gc_char3 gc_char
  gc_char3 text-035 gc_char3 gc_char
  gc_char3 text-036 gc_char3 gc_char
  gc_char3 text-037 gc_char3 gc_char
  gc_char3 text-038 gc_char3 gc_char
  gc_char3 text-039 gc_char3
  ')'
  into lv_hstr.

  "创建数据表-SQL语句
  concatenate
  'Create TABLE'
  gv_tabnm
  '('
  'ID COUNTER NOT NULL,'
  'BUKRS TEXT(4),'
  'BELNR TEXT(15),'
  'GJAHR TEXT(4),'
  'MONAT TEXT(4),'
  'BLART TEXT(4),'
  'BLDAT TEXT(12),'
  'BUDAT TEXT(12),'
  'CPUDT TEXT(12),'
  'CPUTM TEXT(10),'
  'USNAM TEXT(12),'
  'TCODE TEXT(20),'
  'STBLG TEXT(15),'
  'STJAH TEXT(6),'
  'BKTXT TEXT(25),'
  'XSTOV TEXT(10),'
  'STGRD TEXT(4),'
  'PPNAM TEXT(12),'
  'UMSKZ TEXT(6),'
  'SHKZG TEXT(4),'
  'DMBTR TEXT(14),'
  'WRBTR TEXT(14),'
  'PSWBT TEXT(14),'
  'KOKRS TEXT(4),'
  'KOSTL TEXT(10),'
  'VBELN TEXT(15),'
  'VBEL2 TEXT(15),'
  'HKONT TEXT(10),'
  'TXT20 TEXT(20),'
  'XNEGP TEXT(6)'
  ')'
  into lv_crtb separated by space.

  "数据写入-SQL语句-Insert字段明细
  concatenate
  gv_tabnm
  '('
  'BUKRS'  gc_char
  'BELNR'  gc_char
  'GJAHR'  gc_char
  'MONAT'  gc_char
  'BLART'  gc_char
  'BLDAT'  gc_char
  'BUDAT'  gc_char
  'CPUDT'  gc_char
  'CPUTM'  gc_char
  'USNAM'  gc_char
  'TCODE'  gc_char
  'STBLG'  gc_char
  'STJAH'  gc_char
  'BKTXT'  gc_char
  'XSTOV'  gc_char
  'STGRD'  gc_char
  'PPNAM'  gc_char
  'UMSKZ'  gc_char
  'SHKZG'  gc_char
  'DMBTR'  gc_char
  'WRBTR'  gc_char
  'PSWBT'  gc_char
  'KOKRS'  gc_char
  'KOSTL'  gc_char
  'VBELN'  gc_char
  'VBEL2'  gc_char
  'HKONT'  gc_char
  'TXT20'  gc_char
  'XNEGP'
  ')'
  into lv_infd.

  "数据导入-SQL语句-Select字段明细
  concatenate
  'F1  AS BUKRS' gc_char
  "'iif(F2 = true,CStr(F2,10),CStr(F2,10)) AS BELNR ' gc_char
  'F2  AS BELNR' gc_char
  'F3  AS GJAHR' gc_char
  'F4  AS MONAT' gc_char
  'F5  AS BLART' gc_char
  'F6  AS BLDAT' gc_char
  'F7  AS BUDAT' gc_char
  'F8  AS CPUDT' gc_char
  'F9  AS CPUTM' gc_char
  'F10 AS USNAM' gc_char
  'F11 AS TCODE' gc_char
  'F12 AS STBLG' gc_char
  'F13 AS STJAH' gc_char
  'F14 AS BKTXT' gc_char
  'F15 AS XSTOV' gc_char
  'F16 AS STGRD' gc_char
  'F17 AS PPNAM' gc_char
  'F18 AS UMSKZ' gc_char
  'F19 AS SHKZG' gc_char
  'F20 AS DMBTR' gc_char
  'F21 AS WRBTR' gc_char
  'F22 AS PSWBT' gc_char
  'F23 AS KOKRS' gc_char
  'F24 AS KOSTL' gc_char
  'F25 AS VBELN' gc_char
  'F26 AS VBEL2' gc_char
  'F27 AS HKONT' gc_char
  'F28 AS TXT20' gc_char
  'F29 AS XNEGP'
  into lv_fild.

  "SQL语句-导入已存在表记录-数据抬头
  concatenate
  'INSERT INTO'
  lv_infd
  lv_hstr
  into lv_sqlh separated by space.

  "SQL语句--创建表同时导入数据
*  concatenate
*  'SELECT'
*  lv_fild
*  'INTO'
*  gv_tabnm
*  lv_txt
*  into lv_sqli separated by space.

  "SQL语句--导入已存在表记录-数据明细
  concatenate
  'INSERT INTO'
  lv_infd
  'SELECT'
  lv_fild
  lv_txt
  into lv_sqli separated by space.

  "数据库连接
  create object go_conn 'ADODB.Connection'.
  set property of go_conn 'Provider'          = gc_provider.
  set property of go_conn 'ConnectionString'  = lv_conn.
  call method of go_conn 'Open'.

*  "查询表
*  call method of go_conn 'Execute' = go_rtab
*    exporting
*      #1 = lv_qytb.
*
*  call method of go_rtab 'RecordCount' = lv_cont.

*  clear:lv_detb.
*  concatenate
*  'delete from'
*  gv_tabnm
*  into lv_detb
*  separated by space.

  "删除表
*  call method of go_conn 'Execute' = go_rtab
*    exporting
*      #1 = lv_detb.
*  call method of go_rtab 'Close'.
*  free object go_rtab.

  "创建表
  call method of go_conn 'Execute' = go_rset
    exporting
      #1 = lv_crtb.

  "写入标题数据
  call method of go_conn 'Execute' = go_rset
    exporting
      #1 = lv_sqlh.

  "写入明细数据
  call method of go_conn 'Execute' = go_rset
    exporting
      #1 = lv_sqli.

  "关闭及释放
  call method of go_rset 'Close'.
  call method of go_conn 'Close'.
  call method of go_rtab 'Close'.
  free object go_rset.
  free object go_rtab.
  free object go_conn.

endform.
*&---------------------------------------------------------------------*
*&      Form  FRM_TXT_DELETE_FILE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form frm_txt_delete_file .
  data:lv_rc type i.

  call function 'SAPGUI_PROGRESS_INDICATOR'
    exporting
      percentage = 100
      text       = text-008.

  call method cl_gui_frontend_services=>file_delete
    exporting
      filename = gv_txurl
    changing
      rc       = lv_rc.

  gs_inf-fhdat = sy-datum.
  gs_inf-fhtim = sy-uzeit.

  if gs_inf-fhdat = gs_inf-qydat.
    gs_inf-ustim = gs_inf-fhtim - gs_inf-qytim.
  else.
    gs_inf-ustim = ( gs_inf-fhdat - gs_inf-qydat ) * 240000 - gs_inf-fhtim + gs_inf-qytim.
  endif.
endform.
*&---------------------------------------------------------------------*
*&      Form  FRM_EXEC_INFO
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form frm_exec_info .
  skip.
  write:/10 text-050.
  write:/10(60) sy-uline.
  write:/20 text-051,35 p_bukrs.
  write:/20 text-052,35 s_budat-low,50 s_budat-high.
  write:/20 text-053,35 p_dburl.
  write:/20 text-054,35 gs_inf-tabnm.
  write:/20 text-055,35 sy-uname.
  skip.
  write:/10 text-056.
  write:/10(60) sy-uline.
  write:/20 text-057,35 gs_inf-count.
  write:/20 text-058,35 gs_inf-qydat,50 gs_inf-qytim.
  write:/20 text-059,35 gs_inf-txdat,50 gs_inf-txtim.
  write:/20 text-060,35 gs_inf-acdat,50 gs_inf-actim.
  write:/20 text-061,35 gs_inf-fhdat,50 gs_inf-fhtim.
  write:/20 text-062,35 gs_inf-ustim.

endform.
*&---------------------------------------------------------------------*
*&      Form  FRM_GET_AUTH_BUKRS
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_P_BUKRS  text
*----------------------------------------------------------------------*
form frm_get_auth_bukrs  using fv_bukrs.
  data:
    lt_value type table of usvalues.
  field-symbols:
    <fs_value> type usvalues.

  call function 'SUSR_USER_AUTH_FOR_OBJ_GET'
    exporting
      user_name           = sy-uname
      sel_object          = 'F_BKPF_BUK'
    tables
      values              = lt_value
    exceptions
      user_name_not_exist = 1
      not_authorized      = 2
      internal_error      = 3
      others              = 4.
  if sy-subrc = 0 .
    loop at lt_value assigning <fs_value>
    where field = 'BUKRS'
      and von ne '*'.
      select single count(*)
        from t001
       where bukrs = <fs_value>-von.
      if sy-subrc = 0.
        fv_bukrs  = <fs_value>-von.
      endif.
    endloop.
  endif.

endform.
*&---------------------------------------------------------------------*
*&      Form  FRM_AUTH_CHECK_BUKRS
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form frm_auth_check_bukrs .
  authority-check
  object 'F_BKPF_BUK'
  id 'BUKRS'
  field p_bukrs.
  if sy-subrc ne 0.
    message e001(00) with p_bukrs text-009.
  endif.

  select single count(*)
    from t001
   where bukrs = p_bukrs.
  if sy-subrc <> 0.
    message e001(00) with p_bukrs text-040.
  endif.

  if s_budat-low  is initial
  or s_budat-high is initial.
    message e001(00) with text-041.
  endif.
endform.

View Code

程序开发过程中出现如下问题：

a.部分会计凭证编号缺失（#数值!）：

在导入TXT到ACCESS过程中，ACCESS数据表针对会计凭证号默认为数字类型，导致到4600000000以上的号码段全部都是空值，需要设置TXT文件中的会计凭证编号为文本类型，即为凭证编号增加双引号（”460000000”），但是双引号为ABAP注释字符（心酸），然后度娘去查找ABAP特殊字符替代方案（看到的都是换行\n、回车\#)，没有发现双引号的替代编码，正在纠结过程中忽然想到，既然代码不能直接用双引号去拼接，

可以将双引号设置为变量，然后通过变量方式去拼接字符串

。

创建表：ZTFI0034_CHAR