从ERP到SAP HANA（小的ABAP应用）[翻译后转载]
原文链接：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、表和我们要存储这些文件的文件夹。我们可以下载结构或数据。
有了这两个文件，我们只要复制和粘贴在SAP HANA的SQL编辑器里并让它运行。 😉
当然……以下是我们需要通过批处理模式复制记录……也就是说……100行或者类似的……运行它们……然后继续执行另一个100行或者诸如此类……
正如你所见……这只是为了好玩和小小的试验……有可能不是对所有表起作用……不管怎样希望你喜欢它 🙂