Skip to Content

原文链接:http://scn.sap.com/community/developer-center/hana/blog/2013/01/13/from-erp-to-sap-hana-small-abap-app

免责声明:这是一个个人项目。没有经SAP以任何方式授权或支持。它的目的不是为了活着企图替代任何SAP迁移工具。它只是我做了好玩的而且还在测试阶段。使用它需谨慎。

在我的白天工作中,我不需要使用ABAP……但是11年后……不太可能不使用它了 😉

由于到处都有SAP HANA,我决定(前些时间)创建一个小的ABAP程序来转移ERP表到SAP HANA上。这还是试用阶段,当然它不是这么做的最好的办法,因为你之后会看到,对每一条记录我都生成一个INSERT语句……而且SAP HANA编辑器对于行数有限制。为什么我不生成一个CSV文件,并且用SAP HANA Studio上传它?因为……这是一个个人项目……我共享它只是因为有些人可能觉得它有用……

我需要感谢我的朋友Kumar Mayuresh花了一些宝贵的时间来做测试,并且发送给我所有他找到的错误,因此我得以修正它们。

这里是源代码……

 

ZERP_TO_HANA

*&———————————————————————————————-*

*& Report  ZERP_TO_HANA                                                        *

*&———————————————————————————————-*

*& Author: Alvaro “Blag” Tejada Galindo.                                       *

*& Developer Experience                                                              *

*& Company: SAP Labs Montreal.                                                *

*& Date: June 04, 2012.                                                               *

*&———————————————————————————————-*

*& This program comes with no warranty. Use it at your own risk.    *

*& This is just a personal project no aimed for productive                 *

*& environments and not sponsored or supported by SAP.               *

*& I’m not responsible for any caused damage.                               *

*&———————————————————————————————–*

*& Reviewed on: December 11, 2012.                                             *

*& Reviewer: Alvaro “Blag” Tejada Galindo.                                     *

*& Reason: Definition of the Data download structure.                      *

*&———————————————————————————————–*

*& Reviewed on: January 11, 2013.                                                *

*& Reviewer: Alvaro “Blag” Tejada Galindo.                                     *

*& Reason: Definition of the Struct/Data download structure.            *

*&———————————————————————————————–*

*& Reviewed on: January 13, 2013.                                                *

*& Reviewer: Alvaro “Blag” Tejada Galindo.                                     *

*& Reason: Check the Outputlen of the Domain.                             *

*&———————————————————————————————–*

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 keydomname = ‘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

使用很方便……我们执行它而且需要提供Schema、表和我们要存储这些文件的文件夹。我们可以下载结构或数据。/wp-content/uploads/2013/03/erp_to_hana_001_174098.png

ERP_TO_HANA_002.png

ERP_TO_HANA_003.png

有了这两个文件,我们只要复制和粘贴在SAP HANASQL编辑器里并让它运行。 😉

ERP_TO_HANA_004.png

        当然……以下是我们需要通过批处理模式复制记录……也就是说……100行或者类似的……运行它们……然后继续执行另一个100行或者诸如此类……ERP_TO_HANA_005.png

正如你所见……这只是为了好玩和小小的试验……有可能不是对所有表起作用……不管怎样希望你喜欢它 🙂

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply