当前位置:Gxlcms > 数据库问题 > 一步步实现ABAP后台导入EXCEL到数据库【2】

一步步实现ABAP后台导入EXCEL到数据库【2】

时间:2021-07-01 10:21:17 帮助过:3人阅读

OPEN DATASET dset
  FOR { INPUT | OUTPUT | APPENDING | UPDATE }
  IN { { BINARY MODE }
     | { TEXT MODE ENCODING { DEFAULT
                            | {UTF-8 [SKIPPING|WITH BYTE-ORDER MARK]}
                            | NON-UNICODE }
                   [WITH {NATIVE|SMART|UNIX|WINDOWS} LINEFEED]  }
     | { LEGACY BINARY MODE [{BIG|LITTLE} ENDIAN]
                            [CODE PAGE cp] }
     | { LEGACY TEXT MODE [{BIG|LITTLE} ENDIAN]
                          [CODE PAGE cp]
                          [WITH {NATIVE|SMART|UNIX|WINDOWS} LINEFEED] } }
  [AT POSITION pos]
  [TYPE attr]
  [FILTER opcom]
  [MESSAGE msg]
  [IGNORING CONVERSION ERRORS]
  [REPLACEMENT CHARACTER rc].

效果

  打开由dset指定的应用服务器上的文件。

附加项

    • FOR { INPUT | OUTPUT | APPENDING | UPDATE }
      以输入、输出、追加、更新模式打开文件.

 

    • IN [LEGACY] { BINARY | TEXT } MODE
      以普通的二进制或者文本模式打开文件, 或者以LEGACY二进制或文本模式. LEGACY模式可以指定字节顺序和内码表。在Unicode系统中,LEGACY关键字可能会影响到以东亚文字写入的字段的内容。因此,建议只在没有使用LEGACY附加项的打开的文本中写入内容。

 

    • ENCODING {DEFAULT|UTF-8|NON-UNICODE}
      决定了使用何种字符表现形式来操作文件的内容:

      DEFAULT - Unicode系统中的UTF-8; 非Unicode系统则不转换.

      UTF-8 - UTF-8.

      NON-UNICODE -在Unicode系统中,内码表与非Unicode系统文本环境(text evirionment)一致; 非Unicode系统则不转换.

tips: text evirionment

text evirionment是ABAP程序的运行时环境的一部分,由语言、locale和系统内码表组成。同一个内部会话中的所有程序有一个共同的文本环境。默认情况下,内部会话的text environment由登录语言决定,并且也可以被语句set locale指定。当前的text environment包含在系统字段sy-langu中

 

    • WITH {NATIVE|SMART|UNIX|WINDOWS} LINEFEED
      决定文本文件的行尾选项.

 

    • SKIPPING|WITH BYTE-ORDER MARK
      控制UTF-8文件中的字节顺序标记(BOM)操作.

 

    • {BIG|LITTLE} ENDIAN
      决定文件中数值型数据对象被操作的顺序.

 

    • CODE PAGE cp
      决定文件中字符型数据对象以指定的内码表cp操作.

 

    • AT POSITION pos
      将文件指针设置到指定的位置pos.

 

    • TYPE attr
      为文件设置操作系统参数,或者控制文本文件的行尾选项.

 

    • FILTER opcom
      将一个语句转换给操作系统.

 

    • MESSAGE msg
      如果发生了错误,可以在msg中返回操作系统信息.

 

    • IGNORING CONVERSION ERRORS
      如果会话错误发生,该语句可以阻止异常.

 

    • REPLACEMENT CHARACTER rc
      指定一个字符rc替换无法转换的字符。如果未指定rc,则使用“#”.

   使用OPEN DATASET来实现上传数据到应用服务器,即程序中的output_data子进程:

  1. <span style="color: #808080;">*</span><span style="color: #808080;">&---------------------------------------------------------------------*</span><span style="color: #808080;">
  2. *</span><span style="color: #808080;">& Form OUTPUT_DATA</span><span style="color: #808080;">
  3. *</span><span style="color: #808080;">&---------------------------------------------------------------------*</span><span style="color: #808080;">
  4. *</span><span style="color: #808080;"> 将数据写入到Applacation Server</span><span style="color: #808080;">
  5. *</span><span style="color: #808080;">----------------------------------------------------------------------*</span><span style="color: #808080;">
  6. *</span><span style="color: #808080;"> --> p1 text</span><span style="color: #808080;">
  7. *</span><span style="color: #808080;"> <-- p2 text</span><span style="color: #808080;">
  8. *</span><span style="color: #808080;">----------------------------------------------------------------------*</span>
  9. <span style="color: #0000ff;">FORM</span><span style="color: #000000;"> output_data .
  10. </span><span style="color: #0000ff;">DATA</span> s_file <span style="color: #0000ff;">TYPE</span> rlgrap-<span style="color: #000000;">filename.
  11. </span><span style="color: #0000ff;">PERFORM</span> generate_filename_in_server <span style="color: #0000ff;">USING</span><span style="color: #000000;"> s_file.
  12. </span><span style="color: #0000ff;">OPEN</span> <span style="color: #0000ff;">DATASET</span> s_file <span style="color: #0000ff;">FOR</span> INPUT <span style="color: #0000ff;">IN</span> <span style="color: #0000ff;">TEXT</span><span style="color: #000000;"> MODE ENCODING DEFAULT.
  13. </span><span style="color: #0000ff;">IF</span> sy-subrc = <span style="color: #800080;">0</span><span style="color: #000000;"> .
  14. </span><span style="color: #0000ff;">LOOP</span> <span style="color: #0000ff;">AT</span> itab <span style="color: #0000ff;">INTO</span><span style="color: #000000;"> wa.
  15. </span><span style="color: #0000ff;">TRANSFER</span> wa <span style="color: #0000ff;">TO</span><span style="color: #000000;"> s_file.
  16. </span><span style="color: #0000ff;">IF</span> sy-subrc <> <span style="color: #800080;">0</span><span style="color: #000000;"> .
  17. </span><span style="color: #0000ff;">MESSAGE</span> <span style="color: #800000;">‘</span><span style="color: #008000;">ERROR!!</span><span style="color: #800000;">‘</span> <span style="color: #0000ff;">TYPE</span> <span style="color: #800000;">‘</span><span style="color: #008000;">E</span><span style="color: #800000;">‘</span><span style="color: #000000;">.
  18. </span><span style="color: #0000ff;">ENDIF</span><span style="color: #000000;">.
  19. </span><span style="color: #0000ff;">ENDLOOP</span><span style="color: #000000;">.
  20. </span><span style="color: #0000ff;">CLOSE</span> <span style="color: #0000ff;">DATASET</span><span style="color: #000000;"> s_file.
  21. </span><span style="color: #0000ff;">WRITE</span>: /<span style="color: #800000;">‘</span><span style="color: #008000;">写入服务器文件成功</span><span style="color: #800000;">‘</span><span style="color: #000000;">.
  22. </span><span style="color: #0000ff;">PERFORM</span> submit_insert_program <span style="color: #0000ff;">USING</span> s_file. <span style="color: #808080;">"</span><span style="color: #808080;">调用导入程序</span>
  23. <span style="color: #0000ff;">ELSE</span><span style="color: #000000;">.
  24. </span><span style="color: #0000ff;">WRITE</span>: /<span style="color: #800000;">‘</span><span style="color: #008000;">写入服务器文件失败</span><span style="color: #800000;">‘</span><span style="color: #000000;"> .
  25. </span><span style="color: #0000ff;">ENDIF</span><span style="color: #000000;">.
  26. </span><span style="color: #0000ff;">ENDFORM</span><span style="color: #000000;">.
  27. </span><span style="color: #808080;">*</span><span style="color: #808080;">&---------------------------------------------------------------------*</span><span style="color: #808080;">
  28. *</span><span style="color: #808080;">& Form GET_file_name_in_server</span><span style="color: #808080;">
  29. *</span><span style="color: #808080;">&---------------------------------------------------------------------*</span><span style="color: #808080;">
  30. *</span><span style="color: #808080;"> text</span><span style="color: #808080;">
  31. *</span><span style="color: #808080;">----------------------------------------------------------------------*</span><span style="color: #808080;">
  32. *</span><span style="color: #808080;"> --> p1 text</span><span style="color: #808080;">
  33. *</span><span style="color: #808080;"> <-- p2 text</span><span style="color: #808080;">
  34. *</span><span style="color: #808080;">----------------------------------------------------------------------*</span>
  35. <span style="color: #0000ff;">FORM</span> generate_filename_in_server <span style="color: #0000ff;">USING</span><span style="color: #000000;"> s_file .
  36. </span><span style="color: #0000ff;">DATA</span> s_name <span style="color: #0000ff;">TYPE</span> <span style="color: #0000ff;">string</span><span style="color: #000000;">.
  37. </span><span style="color: #0000ff;">CALL</span> <span style="color: #0000ff;">FUNCTION</span> <span style="color: #800000;">‘</span><span style="color: #008000;">SO_SPLIT_FILE_AND_PATH</span><span style="color: #800000;">‘</span>
  38. <span style="color: #0000ff;">EXPORTING</span><span style="color: #000000;">
  39. full_name </span>=<span style="color: #000000;"> p_source
  40. </span><span style="color: #0000ff;">IMPORTING</span><span style="color: #000000;">
  41. stripped_name </span>=<span style="color: #000000;"> s_name
  42. </span><span style="color: #808080;">*</span><span style="color: #808080;"> FILE_PATH =</span>
  43. <span style="color: #0000ff;">EXCEPTIONS</span><span style="color: #000000;">
  44. x_error </span>= <span style="color: #800080;">1</span><span style="color: #000000;">
  45. OTHERS </span>= <span style="color: #800080;">2</span><span style="color: #000000;">.
  46. </span><span style="color: #0000ff;">IF</span> sy-subrc <> <span style="color: #800080;">0</span><span style="color: #000000;">.
  47. </span><span style="color: #808080;">*</span><span style="color: #808080;"> Implement suitable error handling here</span>
  48. <span style="color: #0000ff;">ENDIF</span><span style="color: #000000;">.
  49. </span><span style="color: #0000ff;">GET</span> <span style="color: #0000ff;">TIME</span><span style="color: #000000;">.
  50. </span><span style="color: #0000ff;">CONCATENATE</span> <span style="color: #800000;">‘</span><span style="color: #008000;">/tmp/</span><span style="color: #800000;">‘</span> sy-datum sy-uzeit s_name <span style="color: #800000;">‘</span><span style="color: #008000;">.txt</span><span style="color: #800000;">‘</span> <span style="color: #0000ff;">INTO</span><span style="color: #000000;"> s_file.
  51. </span><span style="color: #0000ff;">TRANSLATE</span> s_file <span style="color: #0000ff;">TO</span> <span style="color: #0000ff;">LOWER</span> <span style="color: #0000ff;">CASE</span><span style="color: #000000;">.
  52. </span><span style="color: #0000ff;">ENDFORM</span>.

 

2,实现导入程序

  上传到服务器的程序已经实现,现在新建一个程序ztest_import,实现导入到数据库的功能。同样使用OPEN DATASET语句来实现:

  1. <span style="color: #0000ff;">REPORT</span><span style="color: #000000;"> ztest_import.
  2. </span><span style="color: #0000ff;">SELECTION-SCREEN</span>: <span style="color: #0000ff;">BEGIN</span> <span style="color: #0000ff;">OF</span> <span style="color: #0000ff;">BLOCK</span><span style="color: #000000;"> blk.
  3. </span><span style="color: #0000ff;">PARAMETERS</span>: s_file <span style="color: #0000ff;">TYPE</span> rlgrap-<span style="color: #000000;">filename .
  4. </span><span style="color: #0000ff;">SELECTION-SCREEN</span> <span style="color: #0000ff;">END</span> <span style="color: #0000ff;">OF</span> <span style="color: #0000ff;">BLOCK</span><span style="color: #000000;"> blk.
  5. </span><span style="color: #0000ff;">DATA</span>: <span style="color: #0000ff;">BEGIN</span> <span style="color: #0000ff;">OF</span><span style="color: #000000;"> wa,
  6. col1(</span><span style="color: #800080;">30</span>) <span style="color: #0000ff;">TYPE</span><span style="color: #000000;"> c,
  7. col2(</span><span style="color: #800080;">30</span>) <span style="color: #0000ff;">TYPE</span><span style="color: #000000;"> c,
  8. col3(</span><span style="color: #800080;">30</span>) <span style="color: #0000ff;">TYPE</span><span style="color: #000000;"> c,
  9. </span><span style="color: #0000ff;">END</span> <span style="color: #0000ff;">OF</span><span style="color: #000000;"> wa.
  10. </span><span style="color: #0000ff;">FIELD-SYMBOLS</span>: <hex_container> <span style="color: #0000ff;">TYPE</span><span style="color: #000000;"> c.
  11. </span><span style="color: #0000ff;">TRANSLATE</span> s_file <span style="color: #0000ff;">TO</span> LOWER <span style="color: #0000ff;">CASE</span><span style="color: #000000;">.
  12. </span><span style="color: #0000ff;">OPEN</span> <span style="color: #0000ff;">DATASET</span> s_file <span style="color: #0000ff;">FOR</span> INPUT <span style="color: #0000ff;">IN</span> <span style="color: #0000ff;">TEXT</span><span style="color: #000000;"> MODE ENCODING DEFAULT.
  13. </span><span style="color: #0000ff;">IF</span> sy-subrc = <span style="color: #800080;">0</span><span style="color: #000000;">.
  14. </span><span style="color: #0000ff;">ASSIGN</span> wa <span style="color: #0000ff;">TO</span> <hex_container><span style="color: #000000;"> CASTING.
  15. </span><span style="color: #0000ff;">DO</span><span style="color: #000000;">.
  16. </span><span style="color: #808080;">"</span><span style="color: #808080;">由于没有使用MAXIMUM LENGTH选项,所以每次读取的最大字节数</span>
  17. <span style="color: #808080;">"</span><span style="color: #808080;">由<hex_container>所占字节数决定</span>
  18. <span style="color: #0000ff;">READ</span> <span style="color: #0000ff;">DATASET</span> s_file <span style="color: #0000ff;">INTO</span> <hex_container><span style="color: #000000;">.
  19. </span><span style="color: #0000ff;">IF</span> sy-subrc = <span style="color: #800080;">0</span><span style="color: #000000;">.
  20. </span><span style="color: #808080;">*</span><span style="color: #808080;">"ztestly是在SE11中新建的表,结构和上传的excel相符,过程省略</span>
  21. <span style="color: #0000ff;">INSERT</span> ztestly <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> wa.
  22. </span><span style="color: #0000ff;">WRITE</span><span style="color: #000000;"> wa.
  23. </span><span style="color: #0000ff;">ELSE</span><span style="color: #000000;">.
  24. </span><span style="color: #0000ff;">EXIT</span><span style="color: #000000;">.
  25. </span><span style="color: #0000ff;">ENDIF</span><span style="color: #000000;">.
  26. </span><span style="color: #0000ff;">ENDDO</span><span style="color: #000000;">.
  27. </span><span style="color: #0000ff;">CLOSE</span> <span style="color: #0000ff;">DATASET</span><span style="color: #000000;"> s_file.
  28. </span><span style="color: #0000ff;">DELETE</span> <span style="color: #0000ff;">DATASET</span> s_file. <span style="color: #808080;">"</span><span style="color: #808080;">成功执行导入后,删除服务器上的文件</span>
  29. <span style="color: #0000ff;">ELSE</span><span style="color: #000000;">.
  30. </span><span style="color: #0000ff;">MESSAGE</span> <span style="color: #800000;">‘</span><span style="color: #008000;">open failed</span><span style="color: #800000;">‘</span> <span style="color: #0000ff;">TYPE</span> <span style="color: #800000;">‘</span><span style="color: #008000;">I</span>

3,在上传程序中调用导入程序

  如果你看过我博客中有关submit的文章,可能会记得,submit语句是可以安排程序作为后台作业进行的。下面我们就使用这个特性,在上传程序ztest_upload的子程序submit_insert_program中调用导入程序ztest_import,代码如下:

  1. <span style="color: #808080;">*</span><span style="color: #808080;">&---------------------------------------------------------------------*</span><span style="color: #808080;">
  2. *</span><span style="color: #808080;">& Form SUBMIT_INSERT_PROGRAM</span><span style="color: #808080;">
  3. *</span><span style="color: #808080;">&---------------------------------------------------------------------*</span><span style="color: #808080;">
  4. *</span><span style="color: #808080;"> text</span><span style="color: #808080;">
  5. *</span><span style="color: #808080;">----------------------------------------------------------------------*</span><span style="color: #808080;">
  6. *</span><span style="color: #808080;"> --> p1 text</span><span style="color: #808080;">
  7. *</span><span style="color: #808080;"> <-- p2 text</span><span style="color: #808080;">
  8. *</span><span style="color: #808080;">----------------------------------------------------------------------*</span>
  9. <span style="color: #0000ff;">FORM</span> submit_insert_program <span style="color: #0000ff;">USING</span><span style="color: #000000;"> s_file.
  10. </span><span style="color: #0000ff;">DATA</span>: w_jobid <span style="color: #0000ff;">TYPE</span> tbtcjob-<span style="color: #000000;">jobcount,
  11. w_stepnm </span><span style="color: #0000ff;">TYPE</span> tbtcjob-<span style="color: #000000;">stepcount,
  12. p_job_nm </span><span style="color: #0000ff;">TYPE</span> tbtcjob-jobname VALUE <span style="color: #800000;">‘</span><span style="color: #008000;">ZTEST_IMPORT</span><span style="color: #800000;">‘</span><span style="color: #000000;">.
  13. </span><span style="color: #0000ff;">CLEAR</span><span style="color: #000000;">:w_jobid.
  14. </span><span style="color: #0000ff;">CALL</span> <span style="color: #0000ff;">FUNCTION</span> <span style="color: #800000;">‘</span><span style="color: #008000;">JOB_OPEN</span><span style="color: #800000;">‘</span>
  15. <span style="color: #0000ff;">EXPORTING</span>
  16. <span style="color: #808080;">*</span><span style="color: #808080;"> DELANFREP = ‘ ‘</span><span style="color: #808080;">
  17. *</span><span style="color: #808080;"> JOBGROUP = ‘ ‘</span>
  18. jobname =<span style="color: #000000;"> p_job_nm
  19. </span><span style="color: #808080;">*</span><span style="color: #808080;"> SDLSTRTDT = NO_DATE</span><span style="color: #808080;">
  20. *</span><span style="color: #808080;"> SDLSTRTTM = NO_TIME</span>
  21. <span style="color: #0000ff;">IMPORTING</span><span style="color: #000000;">
  22. jobcount </span>=<span style="color: #000000;"> w_jobid
  23. </span><span style="color: #0000ff;">EXCEPTIONS</span><span style="color: #000000;">
  24. cant_create_job </span>= <span style="color: #800080;">1</span><span style="color: #000000;">
  25. invalid_job_data </span>= <span style="color: #800080;">2</span><span style="color: #000000;">
  26. jobname_missing </span>= <span style="color: #800080;">3</span><span style="color: #000000;">
  27. OTHERS </span>= <span style="color: #800080;">4</span><span style="color: #000000;">.
  28. </span><span style="color: #0000ff;">SUBMIT</span> ztest_import <span style="color: #0000ff;">WITH</span> s_file = s_file VIA JOB p_job_nm <span style="color: #0000ff;">NUMBER</span> w_jobid <span style="color: #0000ff;">AND</span><span style="color: #000000;"> RETURN.
  29. </span><span style="color: #0000ff;">IF</span> sy-subrc = <span style="color: #800080;">0</span><span style="color: #000000;">.
  30. </span><span style="color: #0000ff;">CALL</span> <span style="color: #0000ff;">FUNCTION</span> <span style="color: #800000;">‘</span><span style="color: #008000;">JOB_CLOSE</span><span style="color: #800000;">‘</span>
  31. <span style="color: #0000ff;">EXPORTING</span><span style="color: #000000;">
  32. jobcount </span>=<span style="color: #000000;"> w_jobid
  33. jobname </span>=<span style="color: #000000;"> p_job_nm
  34. strtimmed </span>= <span style="color: #800000;">‘</span><span style="color: #008000;">X</span><span style="color: #800000;">‘</span>
  35. <span style="color: #0000ff;">EXCEPTIONS</span><span style="color: #000000;">
  36. cant_start_immediate </span>= <span style="color: #800080;">1</span><span style="color: #000000;">
  37. invalid_startdate </span>= <span style="color: #800080;">2</span><span style="color: #000000;">
  38. jobname_missing </span>= <span style="color: #800080;">3</span><span style="color: #000000;">
  39. job_close_failed </span>= <span style="color: #800080;">4</span><span style="color: #000000;">
  40. job_nosteps </span>= <span style="color: #800080;">5</span><span style="color: #000000;">
  41. job_notex </span>= <span style="color: #800080;">6</span><span style="color: #000000;">
  42. lock_failed </span>= <span style="color: #800080;">7</span><span style="color: #000000;">
  43. OTHERS </span>= <span style="color: #800080;">8</span><span style="color: #000000;">.
  44. </span><span style="color: #0000ff;">IF</span> sy-subrc <> <span style="color: #800080;">0</span><span style="color: #000000;">.
  45. </span><span style="color: #0000ff;">WRITE</span>: /<span style="color: #800000;">‘</span><span style="color: #008000;">后台作业发布失败</span><span style="color: #800000;">‘</span><span style="color: #000000;">.
  46. </span><span style="color: #0000ff;">ELSE</span><span style="color: #000000;">.
  47. </span><span style="color: #0000ff;">WRITE</span>: /<span style="color: #800000;">‘</span><span style="color: #008000;">后台作业发布成功</span><span style="color: #800000;">‘</span><span style="color: #000000;">.
  48. </span><span style="color: #0000ff;">ENDIF</span><span style="color: #000000;">.
  49. </span><span style="color: #0000ff;">ENDIF</span><span style="color: #000000;">.
  50. </span><span style="color: #0000ff;">ENDFORM</span>.

  运行zterst_upload,上传一个有效的excel文件,我们便可以在上一篇文章提到的自有作业界面看到后台作业执行成功,并且在SE16中查到自建表zestly中已经插入了相关数据。如果要改变后台作业的计划时间,可以在JOB_OPEN函数中设置相关参数。

一步步实现ABAP后台导入EXCEL到数据库【2】

标签:

人气教程排行