导出SAP系统表结构及数据供HANA使用
该ABAP程序作用是，在没有SLT工具情况下，通过ABAP程序导出SAP ECC系统表结构及数据，生成SQLScript语句的TXT文件，如果表数据量太大不行（该程序是一位大神写的！分享给大家）*&---------------------------------------------------------------------*
*& ReportZERP_TO_HANA
*&
*&---------------------------------------------------------------------*
*&-created by dikang 2013.06.24
*&测试用
*&---------------------------------------------------------------------*
REPORTZERP_TO_HANA.
TYPES: BEGIN OF TY_DD03L,
FIELDNAME TYPE DD03L-FIELDNAME,
POSITION TYPE DD03L-POSITION,
KEYFLAG TYPE DD03L-KEYFLAG,
ROLLNAME TYPE DD03L-ROLLNAME,
DATATYPE TYPE DD03L-DATATYPE,
LENG TYPE DD03L-LENG,
DECIMALS TYPE DD03L-DECIMALS,
DOMNAME TYPE DD03L-DOMNAME,
END OF TY_DD03L.
TYPES: BEGIN OF TY_DD04L,
ROLLNAME TYPE DD04L-ROLLNAME,
DOMNAME TYPE DD04L-DOMNAME,
OUTPUTLEN TYPE DD04L-OUTPUTLEN,
END OF TY_DD04L.
TYPES: BEGIN OF TY_LINES,
LINE TYPE STRING,
END OF TY_LINES.
TYPES: BEGIN OF TY_TYPES,
ERP TYPE STRING,
HANA TYPE STRING,
END OF TY_TYPES.
DATA: T_DD03L TYPE TABLE OF TY_DD03L,
T_DD04L TYPE TABLE OF TY_DD04L,
T_LINES TYPE TABLE OF TY_LINES,
T_TYPES TYPE TABLE OF TY_TYPES.
DATA: V_FILENAME TYPE STRING.
FIELD-SYMBOLS: <FS_DD03L> LIKE LINE OF T_DD03L,
<FS_DD04L> LIKE LINE OF T_DD04L,
<FS_LINES> LIKE LINE OF T_LINES,
<FS_TYPES> LIKE LINE OF T_TYPES.
SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-T01.
PARAMETERS:
P_SCHEMA(12) TYPE C OBLIGATORY,
P_TABLE TYPE DATABROWSE-TABLENAME OBLIGATORY,
P_FOLDER TYPE STRING OBLIGATORY,
P_STRUC RADIOBUTTON GROUP RDN DEFAULT 'X',
P_DATA RADIOBUTTON GROUP RDN.
SELECTION-SCREEN END OF BLOCK B1.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FOLDER.
CALL METHOD CL_GUI_FRONTEND_SERVICES=>DIRECTORY_BROWSE
EXPORTING
WINDOW_TITLE = 'Select a Folder'
INITIAL_FOLDER= 'C:\'
CHANGING
SELECTED_FOLDER = P_FOLDER.
START-OF-SELECTION.
PERFORM GET_TYPES.
IF P_STRUC EQ 'X'.
PERFORM GET_STRUCTURE USING P_TABLE.
ELSE.
PERFORM GET_DATA USING P_TABLE.
ENDIF.
FORM GET_TYPES.
SELECT FIELDNAME POSITION KEYFLAG ROLLNAME
DATATYPE LENG DECIMALS DOMNAME
INTO TABLE T_DD03L
FROM DD03L
WHERE TABNAME EQ P_TABLE.
SORT T_DD03L BY POSITION ASCENDING.
SELECT ROLLNAME DOMNAME OUTPUTLEN
INTO TABLE T_DD04L
FROM DD04L
FOR ALL ENTRIES IN T_DD03L
WHERE ROLLNAME EQ T_DD03L-ROLLNAME
AND DOMNAME EQ T_DD03L-DOMNAME.
"NVARCHAR
APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
<FS_TYPES>-ERP = 'CLNT'.
<FS_TYPES>-HANA = 'NVARCHAR'.
APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
<FS_TYPES>-ERP = 'CHAR'.
<FS_TYPES>-HANA = 'NVARCHAR'.
APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
<FS_TYPES>-ERP = 'NUMC'.
<FS_TYPES>-HANA = 'NVARCHAR'.
APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
<FS_TYPES>-ERP = 'UNIT'.
<FS_TYPES>-HANA = 'NVARCHAR'.
APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
<FS_TYPES>-ERP = 'CUKY'.
<FS_TYPES>-HANA = 'NVARCHAR'.
"INTEGER
APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
<FS_TYPES>-ERP = 'INT4'.
<FS_TYPES>-HANA = 'INTEGER'.
"DECIMAL
APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
<FS_TYPES>-ERP = 'QUAN'.
<FS_TYPES>-HANA = 'DECIMAL'.
APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
<FS_TYPES>-ERP = 'DEC'.
<FS_TYPES>-HANA = 'DECIMAL'.
"FLOAT
APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
<FS_TYPES>-ERP = 'FLTP'.
<FS_TYPES>-HANA = 'FLOAT'.
"TINYINT
APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
<FS_TYPES>-ERP = 'INT1'.
<FS_TYPES>-HANA = 'TINYINT'.
ENDFORM. " GET_TYPES
FORM GET_STRUCTURE USING P_TABLE.
DATA: PKEY TYPE STRING,
L_TYPE TYPE STRING.
CONCATENATE P_FOLDER '\' P_TABLE '_STRUCT.txt'
INTO V_FILENAME.
APPEND INITIAL LINE TO T_LINES ASSIGNING <FS_LINES>.
CONCATENATE 'CREATE COLUMN TABLE' P_SCHEMA
INTO <FS_LINES>-LINE SEPARATED BY SPACE.
CONCATENATE <FS_LINES>-LINE '."' P_TABLE'" (' INTO <FS_LINES>-LINE.
LOOP AT T_DD03L ASSIGNING <FS_DD03L>.
FIND REGEX '\.' IN <FS_DD03L>-FIELDNAME.
IF SY-SUBRC EQ 0.
CONTINUE.
ENDIF.
APPEND INITIAL LINE TO T_LINES ASSIGNING <FS_LINES>.
READ TABLE T_TYPES ASSIGNING <FS_TYPES>
WITH KEY ERP = <FS_DD03L>-DATATYPE.
L_TYPE = <FS_TYPES>-HANA.
PERFORM DELETE_ZEROS CHANGING <FS_DD03L>-LENG.
READ TABLE T_DD04L ASSIGNING <FS_DD04L>
WITH KEY ROLLNAME = <FS_DD03L>-ROLLNAME
DOMNAME = <FS_DD03L>-DOMNAME.
IF SY-SUBRC EQ 0 AND NOT <FS_DD04L> IS INITIAL.
PERFORM DELETE_ZEROS CHANGING <FS_DD04L>-OUTPUTLEN.
IF <FS_DD04L>-OUTPUTLEN GT <FS_DD03L>-LENG.
<FS_DD03L>-LENG = <FS_DD04L>-OUTPUTLEN.
ENDIF.
ENDIF.
PERFORM DELETE_ZEROS CHANGING <FS_DD03L>-DECIMALS.
CASE L_TYPE.
WHEN 'NVARCHAR' OR 'FLOAT' OR 'TINYINT'.
CONCATENATE L_TYPE '(' <FS_DD03L>-LENG ')'
INTO L_TYPE.
WHEN 'DECIMAL'.
CONCATENATE L_TYPE '(' <FS_DD03L>-LENG ',' <FS_DD03L>-DECIMALS ')'
INTO L_TYPE.
ENDCASE.
FIND REGEX '\/' IN <FS_DD03L>-FIELDNAME.
IF SY-SUBRC EQ 0.
CONCATENATE '"' <FS_DD03L>-FIELDNAME '"'
INTO <FS_DD03L>-FIELDNAME.
ENDIF.
CONCATENATE <FS_DD03L>-FIELDNAME L_TYPE
INTO <FS_LINES>-LINE SEPARATED BY SPACE.
CONCATENATE <FS_LINES>-LINE ',' INTO <FS_LINES>-LINE.
IF <FS_DD03L>-KEYFLAG EQ 'X'.
CONCATENATE PKEY '"' <FS_DD03L>-FIELDNAME '",'
INTO PKEY.
ENDIF.
ENDLOOP.
REPLACE REGEX ',\Z' IN PKEY WITH SPACE.
APPEND INITIAL LINE TO T_LINES ASSIGNING <FS_LINES>.
CONCATENATE 'PRIMARY KEY (' PKEY '));'
INTO <FS_LINES>-LINE.
PERFORM DOWNLOAD_FILE USING V_FILENAME T_LINES.
ENDFORM. " GET_STRUCTURE
FORM GET_DATA USING P_TABLE.
DATA: L_TABLE TYPE REF TO DATA,
L_LINE TYPE STRING,
L_LINEAUX TYPE STRING,
L_WHERE TYPE STRING,
L_TYPE TYPE STRING.
FIELD-SYMBOLS: <FS_TABLE> TYPE ANY TABLE,
<FS_TABLE_HEADER> TYPE ANY,
<FS_LINE>.
CONCATENATE P_FOLDER '\' P_TABLE '_DATA.txt'
INTO V_FILENAME.
CREATE DATA L_TABLE TYPE TABLE OF (P_TABLE).
ASSIGN L_TABLE->* TO <FS_TABLE>.
read table t_dd03l ASSIGNING <fs_dd03l>
with key domname = 'SPRAS'.
IF SY-SUBrC EQ 0.
CONCATENATE <fs_dd03l>-FIELDNAME 'EQ ''E''' INTO L_WHERE
SEPARATED BY SPACE.
SELECT *
FROM (P_TABLE)
INTO TABLE <FS_TABLE>
WHERE (L_WHERE).
ELSE.
SELECT *
FROM (P_TABLE)
INTO TABLE <FS_TABLE>.
ENDIF.
LOOP AT <FS_TABLE> ASSIGNING <FS_TABLE_HEADER>.
APPEND INITIAL LINE TO T_LINES ASSIGNING <FS_LINES>.
CONCATENATE 'insert into "' P_SCHEMA '"."' P_TABLE '" values(' into <FS_LINES>-LINE.
LOOP AT T_DD03L ASSIGNING <FS_DD03L>.
FIND REGEX '\.' IN <FS_DD03L>-FIELDNAME.
IF SY-SUBRC EQ 0.
CONTINUE.
DELETE T_LINES FROM <FS_LINES>.
ENDIF.
CONCATENATE '<FS_TABLE_HEADER>-' <FS_DD03L>-FIELDNAME
INTO L_LINE.
ASSIGN (L_LINE) TO <FS_LINE>.
MOVE <FS_LINE> TO L_LINEAUX.
CONDENSE L_LINEAUX NO-GAPS.
READ TABLE T_TYPES ASSIGNING <FS_TYPES>
WITH KEY ERP = <FS_DD03L>-DATATYPE.
L_TYPE = <FS_TYPES>-HANA.
CASE L_TYPE.
WHEN 'NVARCHAR'.
CONCATENATE <FS_LINES>-LINE '''' L_LINEAUX ''',' into <FS_LINES>-LINE.
WHEN 'DECIMAL' OR 'INTEGER' OR 'TINYINT' OR 'FLOAT'.
CONDENSE L_LINEAUX NO-GAPS.
CONCATENATE <FS_LINES>-LINE L_LINEAUX ',' into <FS_LINES>-LINE.
ENDCASE.
ENDLOOP.
REPLACE REGEX ',\Z' IN <FS_LINES>-LINE WITH ');'.
ENDLOOP.
PERFORM DOWNLOAD_FILE USING V_FILENAME
T_LINES.
ENDFORM. " GET_DATA
FORM DOWNLOAD_FILE USING P_FILENAME P_TABLE.
DATA: SIZE TYPE I.
CALL METHOD CL_GUI_FRONTEND_SERVICES=>GUI_DOWNLOAD
EXPORTING
BIN_FILESIZE = SIZE
FILENAME = P_FILENAME
FILETYPE = 'ASC'
CHANGING
DATA_TAB = P_TABLE.
ENDFORM. "download_file
FORM DELETE_ZEROS CHANGING P_VALUE.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
EXPORTING
INPUT= P_VALUE
IMPORTING
OUTPUT = P_VALUE.
ENDFORM. "DELETE_ZEROS
页:
[1]