Disclaimer: This is a personal project. It's not endorsed or supported by SAP in any means. It's not aimed or supposed to replace any SAP migration tool. It's just something I did for fun and it's still on beta phase. Use it at your own risk.
In my daytime job, I don't need to use ABAP...but after 11 years...it's hard not to use it sometimes
As everything is SAP HANA, I decided (some time ago) to build a small ABAP program to move tables from the ERP to SAP HANA. It's still on Beta and of course it's not the best way of doing this kind of job, because as you will see later, for each record I generate an INSERT clause...and the SAP HANA editor has a limit of lines. Why didn't I generate an CSV file and upload with SAP HANA Studio? Because...this is a personal project...and I'm sharing it only because someone might find it useful...
I need to thank my friend Kumar Mayuresh who took some quality time to beta test and send me all the error he found so I could fix them.
Here's the source code...
| 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 *
*&---------------------------------------------------------------------*
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 *
*&---------------------------------------------------------------------*
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 *
*&---------------------------------------------------------------------*
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 *
*&---------------------------------------------------------------------*
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 *
*&---------------------------------------------------------------------*
FORM DELETE_ZEROS CHANGING P_VALUE.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
EXPORTING
INPUT = P_VALUE
IMPORTING
OUTPUT = P_VALUE.
ENDFORM. "DELETE_ZEROS
|
The usage is very simple...we execute it and we need to provide the Schema, Table and the Folder were we're going to store the files. We can download the Structure or the Data.
With the two files ready, we simply copy and paste in an SQL Editor of SAP HANA and let it run.
Of course...the downside is that we need to copy the records in batch mode...meaning...100 lines or something like that...run them...and then continue with the other 100 or so...
As you can see...this is only for fun and for small testing...and might not work with all the tables...hope you like it anyway -:)
Greetings,
Blag.
No hay comentarios:
Publicar un comentario