问:老师,我要导出SAP的好多个表的全部表内容,请问有没有合适的程序给我用用呢?
答:这个程序很简单,随便写一下就行了:
REPORT zexport_table NO STANDARD PAGE HEADING.
TABLES dd02l.
DATA: gt_tabs TYPE TABLE OF tabname WITH HEADER LINE.
DATA: go_table TYPE REF TO data.
DATA: gv_lfile TYPE string.
FIELD-SYMBOLS: <gt_table> TYPE STANDARD TABLE.
SELECT-OPTIONS s_tabnam FOR dd02l-tabname.
START-OF-SELECTION.
SELECT tabname INTO TABLE gt_tabs FROM dd02l
WHERE tabname IN s_tabnam AND
as4local EQ 'A' AND
tabclass IN ('TRANSP','CLUSTER','POOL','VIEW').
LOOP AT gt_tabs.
CREATE DATA go_table TYPE TABLE OF (gt_tabs).
ASSIGN go_table->* TO <gt_table>.
SELECT * FROM (gt_tabs) INTO TABLE <gt_table>.
CHECK sy-subrc = 0.
gv_lfile = 'D:\tmp\' && gt_tabs && '.txt'.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = gv_lfile
filetype = 'DAT'
confirm_overwrite = ' '
TABLES
data_tab = <gt_table>
EXCEPTIONS
OTHERS = 22.
IF sy-subrc NE 0.
MESSAGE i000(oo) WITH '文件下载错误:' gt_tabs.
ENDIF.
ENDLOOP.
问:太好了!小小的代码大大的能量,果然导出来了。可是导出的文件没有列名呢,对数非常不方便啊!
答:那就加上字段名作为列名吧,除了程序中用的的方法,也可以在表DD03L中取列名。
TABLES dd02l.
DATA: gt_tabs TYPE TABLE OF tabname WITH HEADER LINE.
DATA: go_table TYPE REF TO data.
DATA: gv_lfile TYPE string.
DATA: go_tdescr TYPE REF TO cl_abap_tabledescr,
go_sdescr TYPE REF TO cl_abap_structdescr,
gs_comps LIKE LINE OF cl_abap_structdescr=>components,
gt_field TYPE TABLE OF dd03l.
FIELD-SYMBOLS: <gt_table> TYPE STANDARD TABLE.
SELECT-OPTIONS s_tabnam FOR dd02l-tabname.
START-OF-SELECTION.
SELECT tabname INTO TABLE gt_tabs FROM dd02l
WHERE tabname IN s_tabnam AND
as4local EQ 'A' AND
tabclass IN ('TRANSP','CLUSTER','POOL','VIEW').
LOOP AT gt_tabs.
CREATE DATA go_table TYPE TABLE OF (gt_tabs).
ASSIGN go_table->* TO <gt_table>.
SELECT * FROM (gt_tabs) INTO TABLE <gt_table>.
CHECK sy-subrc EQ 0.
CLEAR gt_field[].
go_tdescr ?= cl_abap_typedescr=>describe_by_data( <gt_table> ).
go_sdescr ?= go_tdescr->get_table_line_type( ).
LOOP AT go_sdescr->components INTO gs_comps.
APPEND gs_comps-name TO gt_field.
ENDLOOP.
gv_lfile = 'D:\tmp\' && gt_tabs && '.txt'.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = gv_lfile
filetype = 'DAT'
confirm_overwrite = ' '
TABLES
data_tab = <gt_table>
fieldnames = gt_field
EXCEPTIONS
OTHERS = 22.
IF sy-subrc NE 0.
MESSAGE i000(oo) WITH '文件下载错误:' gt_tabs.
ENDIF.
ENDLOOP.
问:太好啦!可是这种前台导出太慢了,导出一个几十万数据的表都要好久呢,等的花儿都谢了!
答:确实是这样,谁知道你要导出这么大的表呢!前台用GUI_DOWNLOAD导出数据性能是比较差的,我给你改成后台运行吧,然后把数据存到服务器上,再从服务器下载就非常非常快了。程序执行完在JOB日志可以看的文件名,然后可以用CG3Y的BIN格式下载,或者写一个程序下载。文件放到应用服务器的DIR_HOME目录哦,用AL11可以看到存放的文件。
TABLES dd02l.
DATA: gt_tabs TYPE TABLE OF tabname WITH HEADER LINE.
DATA: go_table TYPE REF TO data.
DATA: gv_line TYPE string.
DATA: gv_afile TYPE string.
DATA: go_tdesc TYPE REF TO cl_abap_tabledescr,
go_sdesc TYPE REF TO cl_abap_structdescr,
gs_comps LIKE LINE OF cl_abap_structdescr=>components.
FIELD-SYMBOLS: <gt_table> TYPE STANDARD TABLE,<gs_table>,<gv_fld>.
SELECT-OPTIONS s_tabnam FOR dd02l-tabname.
START-OF-SELECTION.
SELECT tabname INTO TABLE gt_tabs FROM dd02l
WHERE tabname IN s_tabnam AND
as4local EQ 'A' AND
tabclass IN ('TRANSP','CLUSTER','POOL','VIEW').
LOOP AT gt_tabs.
CREATE DATA go_table TYPE TABLE OF (gt_tabs).
ASSIGN go_table->* TO <gt_table>.
SELECT * FROM (gt_tabs) INTO TABLE <gt_table>.
CHECK sy-subrc EQ 0.
CLEAR gv_line.
go_tdesc ?= cl_abap_typedescr=>describe_by_data( <gt_table> ).
go_sdesc ?= go_tdesc->get_table_line_type( ).
LOOP AT go_sdesc->components INTO gs_comps.
gv_line = gv_line && %_horizontal_tab && gs_comps-name.
ENDLOOP.
SHIFT gv_line.
gv_afile = gt_tabs && '_' && '_' && sy-dbcnt && '.TXT'.
OPEN DATASET gv_afile FOR OUTPUT IN TEXT MODE
ENCODING UTF-8 WITH WINDOWS LINEFEED.
TRANSFER gv_line TO gv_afile.
LOOP AT <gt_table> ASSIGNING <gs_table>.
CLEAR gv_line.
DO.
ASSIGN COMPONENT sy-index OF STRUCTURE <gs_table> TO <gv_fld>.
IF sy-subrc NE 0.
EXIT.
ENDIF.
gv_line = gv_line && %_horizontal_tab.
gv_line = gv_line && <gv_fld>.
ENDDO.
SHIFT gv_line.
TRANSFER gv_line TO gv_afile.
ENDLOOP.
CLOSE DATASET gv_afile.
MESSAGE s000(oo) WITH gv_afile.
ENDLOOP.
问:太棒了老师!运行快多了。可是我在下载一个超大表的时候程序崩掉了呢,数据量太大了,把内表撑爆了!
答:你都是导出啥表啊竟然能撑爆内表!到目前为止呢,我们都是一次性取出数据库表的数据到内表,如果数据量非常大,超过了系统参数所限定的内存大小就DUMP了。这样吧,我们取数据改为使用游标的方式,每次取出来100000条数据,等保存完后再继续取下一个100000条,如此往复,就不怕内表撑爆了。另外,我知道你还会遇到另外的问题,所以咱们先把文件在程序内转成XSTRING再存放,至于为啥等你遇到下一个问题就知道啦
TABLES dd02l.
DATA gt_tabs TYPE TABLE OF tabname WITH HEADER LINE.
DATA go_table TYPE REF TO data.
DATA gv_cursor TYPE cursor.
DATA gv_afile TYPE string.
DATA gv_header TYPE string.
DATA gv_line TYPE string.
DATA gv_lines TYPE string.
DATA gv_xstr TYPE xstring.
DATA go_tdesc TYPE REF TO cl_abap_tabledescr.
DATA go_sdesc TYPE REF TO cl_abap_structdescr.
DATA gs_comps LIKE LINE OF cl_abap_structdescr=>components.
FIELD-SYMBOLS: <gt_table> TYPE STANDARD TABLE,<gs_table>,<gv_fld>.
SELECT-OPTIONS s_tabnam FOR dd02l-tabname.
START-OF-SELECTION.
SELECT tabname INTO TABLE gt_tabs FROM dd02l
WHERE tabname IN s_tabnam AND
as4local EQ 'A' AND
tabclass IN ('TRANSP','CLUSTER','POOL','VIEW').
LOOP AT gt_tabs.
CREATE DATA go_table TYPE TABLE OF (gt_tabs).
ASSIGN go_table->* TO <gt_table>.
CLEAR gv_header.
go_tdesc ?= cl_abap_typedescr=>describe_by_data( <gt_table> ).
go_sdesc ?= go_tdesc->get_table_line_type( ).
LOOP AT go_sdesc->components INTO gs_comps.
gv_header = gv_header && %_horizontal_tab && gs_comps-name.
ENDLOOP.
SHIFT gv_header.
OPEN CURSOR gv_cursor FOR SELECT * FROM (gt_tabs).
DO.
FETCH NEXT CURSOR gv_cursor INTO TABLE <gt_table> PACKAGE SIZE 100000.
IF sy-subrc NE 0. EXIT. ENDIF.
gv_afile = gt_tabs && '_' && sy-index && '_' && sy-dbcnt && '.TXT'.
OPEN DATASET gv_afile FOR OUTPUT IN BINARY MODE.
gv_lines = gv_header && %_cr_lf.
LOOP AT <gt_table> ASSIGNING <gs_table>.
CLEAR gv_line.
DO.
ASSIGN COMPONENT sy-index OF STRUCTURE <gs_table> TO <gv_fld>.
IF sy-subrc NE 0. EXIT. ENDIF.
gv_line = gv_line && %_horizontal_tab.
gv_line = gv_line && <gv_fld>.
ENDDO.
SHIFT gv_line.
gv_line = gv_line && %_cr_lf.
gv_lines = gv_lines && gv_line.
ENDLOOP.
CALL FUNCTION 'SCMS_STRING_TO_XSTRING'
EXPORTING
text = gv_lines
mimetype = 'UTF-8'
IMPORTING
buffer = gv_xstr.
TRANSFER gv_xstr TO gv_afile.
CLOSE DATASET gv_afile.
MESSAGE s000(oo) WITH gv_afile.
ENDDO.
CLOSE CURSOR gv_cursor.
ENDLOOP.
问:哇哈哈,果然内表不会爆掉了!不过我真的又遇到问题了呢,文件太大了,把应用服务器给撑爆了,磁盘空间都没有啦!老师你刚才说会遇到新的问题,是不是这个问题呢?
答:就是这个问题,哈哈哈,我们之所以把数据转为XSTRING再存放就是为了解决这个问题。我们知道SAP数据这种二维表的数据冗余是非常严重的,稍微压缩一下就能有一个很好的压缩比,有的时候能压缩到原大小的十分之一甚至百分之一呢!
TABLES dd02l.
DATA gt_tabs TYPE TABLE OF tabname WITH HEADER LINE.
DATA go_zip TYPE REF TO cl_abap_zip.
DATA go_table TYPE REF TO data.
DATA gv_cursor TYPE cursor.
DATA gv_afile TYPE string.
DATA gv_fname TYPE string.
DATA gv_head TYPE string.
DATA gv_line TYPE string.
DATA gv_lines TYPE string.
DATA gv_xstr TYPE xstring.
DATA go_tdesc TYPE REF TO cl_abap_tabledescr.
DATA go_sdesc TYPE REF TO cl_abap_structdescr.
DATA gs_comps LIKE LINE OF cl_abap_structdescr=>components.
FIELD-SYMBOLS: <gt_table> TYPE STANDARD TABLE,<gs_table>,<gv_fld>.
SELECT-OPTIONS s_tabnam FOR dd02l-tabname.
START-OF-SELECTION.
SELECT tabname INTO TABLE gt_tabs FROM dd02l
WHERE tabname IN s_tabnam AND
as4local EQ 'A' AND
tabclass IN ('TRANSP','CLUSTER','POOL','VIEW').
LOOP AT gt_tabs.
CREATE DATA go_table TYPE TABLE OF (gt_tabs).
ASSIGN go_table->* TO <gt_table>.
CLEAR gv_head.
go_tdesc ?= cl_abap_typedescr=>describe_by_data( <gt_table> ).
go_sdesc ?= go_tdesc->get_table_line_type( ).
LOOP AT go_sdesc->components INTO gs_comps.
gv_head = gv_head && %_horizontal_tab && gs_comps-name.
ENDLOOP.
SHIFT gv_head.
OPEN CURSOR gv_cursor FOR SELECT * FROM (gt_tabs).
DO.
FETCH NEXT CURSOR gv_cursor INTO TABLE <gt_table> PACKAGE SIZE 100000.
IF sy-subrc NE 0. EXIT. ENDIF.
gv_afile = gt_tabs && '_' && sy-index && '_' && sy-dbcnt && '.ZIP'.
gv_fname = gt_tabs && '_' && sy-index && '_' && sy-dbcnt && '.TXT'.
OPEN DATASET gv_afile FOR OUTPUT IN BINARY MODE.
gv_lines = gv_head && %_cr_lf.
LOOP AT <gt_table> ASSIGNING <gs_table>.
CLEAR gv_line.
DO.
ASSIGN COMPONENT sy-index OF STRUCTURE <gs_table> TO <gv_fld>.
IF sy-subrc NE 0. EXIT. ENDIF.
gv_line = gv_line && %_horizontal_tab.
gv_line = gv_line && <gv_fld>.
ENDDO.
SHIFT gv_line.
gv_line = gv_line && %_cr_lf.
gv_lines = gv_lines && gv_line.
ENDLOOP.
CALL FUNCTION 'SCMS_STRING_TO_XSTRING'
EXPORTING
text = gv_lines
mimetype = 'UTF-8'
IMPORTING
buffer = gv_xstr.
CONCATENATE cl_abap_char_utilities=>byte_order_mark_utf8
gv_xstr INTO gv_xstr IN BYTE MODE.
CREATE OBJECT go_zip.
go_zip->add( name = gv_fname
content = gv_xstr ).
gv_xstr = go_zip->save( ).
FREE go_zip.
TRANSFER gv_xstr TO gv_afile.
CLOSE DATASET gv_afile.
MESSAGE s000(oo) WITH gv_afile.
ENDDO.
CLOSE CURSOR gv_cursor.
ENDLOOP.
问:破费科特!老师棒棒的!一下子文件就小了很多,一边取数一边在服务器下载然后删除,文件不会撑爆服务器了。不过用CG3Y下载好烦啊,老师你不是说可以写一个下载程序。。。。
答:算了,救人救到底送佛送到西,我给你整理成一个完整的下载程序吧,把DB下载到服务器和服务器下载到本地放到一个程序里面,这样就能愉快的下着数据唱着歌了。
*&---------------------------------------------------------------------*
*& Report ZEXPORT_TABLE
*&
*&---------------------------------------------------------------------*
*& 导出多个表数据
*& Baitianzhen
*&---------------------------------------------------------------------*
REPORT zexport_table NO STANDARD PAGE HEADING.
TABLES: dd02l,flsysrqst.
DATA: gv_afile TYPE string,
gt_files TYPE TABLE OF string.
PARAMETERS: pr_sele RADIOBUTTON GROUP g1 USER-COMMAND u1 DEFAULT 'X'.
PARAMETERS: pr_down RADIOBUTTON GROUP g1.
SELECT-OPTIONS s_tabnam FOR dd02l-tabname MODIF ID a.
SELECT-OPTIONS s_dbfile FOR flsysrqst-file_name NO INTERVALS MODIF ID b.
PARAMETERS p_down AS CHECKBOX MODIF ID a.
PARAMETERS p_dele AS CHECKBOX DEFAULT 'X' MODIF ID b.
AT SELECTION-SCREEN OUTPUT.
%_pr_sele_%_app_%-text = '保存表数据到服务器文件'.
%_pr_down_%_app_%-text = '下载服务器文件到本地'.
%_s_tabnam_%_app_%-text = '表/视图'.
%_s_dbfile_%_app_%-text = '服务器文件'.
%_p_down_%_app_%-text = '直接下载到本地'.
%_p_dele_%_app_%-text = '下载后删除服务器文件'.
LOOP AT SCREEN.
CASE 'X'.
WHEN pr_sele.
IF screen-group1 CA 'B'.
screen-active = '0'.
ENDIF.
WHEN pr_down.
IF screen-group1 CA 'A'.
screen-active = '0'.
ENDIF.
ENDCASE.
MODIFY SCREEN.
ENDLOOP.
AT SELECTION-SCREEN.
IF sy-ucomm = 'ONLI' AND pr_sele = 'X' AND s_tabnam[] IS INITIAL.
SET CURSOR FIELD 'S_TABNAM-LOW'.
MESSAGE e000(oo) WITH '表名必输'.
ENDIF.
START-OF-SELECTION.
CASE 'X'.
WHEN pr_sele.
PERFORM getdata.
WHEN pr_down.
LOOP AT s_dbfile.
gv_afile = s_dbfile-low.
COLLECT gv_afile INTO gt_files.
ENDLOOP.
PERFORM download.
ENDCASE.
*&---------------------------------------------------------------------*
*& Form getdata
*&---------------------------------------------------------------------*
FORM getdata.
DATA lo_tdesc TYPE REF TO cl_abap_tabledescr.
DATA lo_sdesc TYPE REF TO cl_abap_structdescr.
DATA lo_zip TYPE REF TO cl_abap_zip.
DATA lo_table TYPE REF TO data.
DATA ls_comps LIKE LINE OF cl_abap_structdescr=>components.
DATA lt_tabs TYPE TABLE OF tabname WITH HEADER LINE.
DATA lv_cursor TYPE cursor.
DATA lv_fname TYPE string.
DATA lv_head TYPE string.
DATA lv_line TYPE string.
DATA lv_lines TYPE string.
DATA lv_xstr TYPE xstring.
FIELD-SYMBOLS: <lt_table> TYPE STANDARD TABLE,<ls_table>,<lv_fld>.
SELECT tabname INTO TABLE lt_tabs FROM dd02l
WHERE tabname IN s_tabnam AND
as4local EQ 'A' AND
tabclass IN ('TRANSP','CLUSTER','POOL','VIEW').
LOOP AT lt_tabs.
CREATE DATA lo_table TYPE TABLE OF (lt_tabs).
ASSIGN lo_table->* TO <lt_table>.
CLEAR lv_head.
lo_tdesc ?= cl_abap_typedescr=>describe_by_data( <lt_table> ).
lo_sdesc ?= lo_tdesc->get_table_line_type( ).
LOOP AT lo_sdesc->components INTO ls_comps.
lv_head = lv_head && %_horizontal_tab && ls_comps-name.
ENDLOOP.
SHIFT lv_head.
OPEN CURSOR lv_cursor FOR SELECT * FROM (lt_tabs).
DO.
FETCH NEXT CURSOR lv_cursor INTO TABLE <lt_table> PACKAGE SIZE 100000.
IF sy-subrc NE 0. EXIT. ENDIF.
gv_afile = lt_tabs && '_' && sy-index && '_' && sy-dbcnt && '.ZIP'.
lv_fname = lt_tabs && '_' && sy-index && '_' && sy-dbcnt && '.TXT'.
OPEN DATASET gv_afile FOR OUTPUT IN BINARY MODE.
lv_lines = lv_head && %_cr_lf.
LOOP AT <lt_table> ASSIGNING <ls_table>.
CLEAR lv_line.
DO.
ASSIGN COMPONENT sy-index OF STRUCTURE <ls_table> TO <lv_fld>.
IF sy-subrc NE 0. EXIT. ENDIF.
lv_line = lv_line && %_horizontal_tab.
lv_line = lv_line && <lv_fld>.
ENDDO.
SHIFT lv_line.
lv_line = lv_line && %_cr_lf.
lv_lines = lv_lines && lv_line.
ENDLOOP.
CALL FUNCTION 'SCMS_STRING_TO_XSTRING'
EXPORTING
text = lv_lines
mimetype = 'UTF-8'
IMPORTING
buffer = lv_xstr.
CONCATENATE cl_abap_char_utilities=>byte_order_mark_utf8
lv_xstr INTO lv_xstr IN BYTE MODE.
CREATE OBJECT lo_zip.
lo_zip->add( name = lv_fname
content = lv_xstr ).
lv_xstr = lo_zip->save( ).
FREE lo_zip.
TRANSFER lv_xstr TO gv_afile.
CLOSE DATASET gv_afile.
APPEND gv_afile TO gt_files.
MESSAGE s000(oo) WITH gv_afile.
ENDDO.
CLOSE CURSOR lv_cursor.
ENDLOOP.
IF p_down = 'X' AND sy-batch = ''.
PERFORM download.
ENDIF.
ENDFORM. "getdata
*&---------------------------------------------------------------------*
*& Form down
*&---------------------------------------------------------------------*
FORM download.
DATA lv_file TYPE string.
DATA lt_btab TYPE TABLE OF w3mime WITH HEADER LINE.
DATA lv_xstr TYPE xstring.
DATA lv_blen TYPE i.
LOOP AT gt_files INTO gv_afile.
OPEN DATASET gv_afile FOR INPUT IN BINARY MODE.
IF sy-subrc = 0.
READ DATASET gv_afile INTO lv_xstr LENGTH lv_blen.
CLOSE DATASET gv_afile.
CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
EXPORTING
buffer = lv_xstr
IMPORTING
output_length = lv_blen
TABLES
binary_tab = lt_btab.
lv_file = 'D:\tmp\' && gv_afile.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
bin_filesize = lv_blen
filename = lv_file
filetype = 'BIN'
confirm_overwrite = ' '
TABLES
data_tab = lt_btab
EXCEPTIONS
OTHERS = 22.
IF sy-subrc = 0.
IF p_dele = 'X' OR p_down = 'X'.
DELETE DATASET gv_afile.
ENDIF.
ELSE.
MESSAGE i000(oo) WITH '文件下载错误:' gv_afile.
ENDIF.
ELSE.
MESSAGE i000(oo) WITH '服务器文件打开错误:' gv_afile.
ENDIF.
ENDLOOP.
CLEAR gt_files.
ENDFORM. "download