导出SAP系统表结构及数据供HANA使用

demn

　　该ABAP程序作用是，在没有SLT工具情况下，通过ABAP程序导出SAP ECC系统表结构及数据，生成SQLScript语句的TXT文件，如果表数据量太大不行（该程序是一位大神写的！分享给大家）
　　*&---------------------------------------------------------------------*
*& Report  ZERP_TO_HANA
*&
*&---------------------------------------------------------------------*
*&-created by dikang 2013.06.24
*&测试用
*&---------------------------------------------------------------------*
REPORT  ZERP_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