Skip to Content

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.

 

  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

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.

ERP_TO_HANA_001.png

ERP_TO_HANA_002.png

ERP_TO_HANA_003.png

With the two files ready, we simply copy and paste in an SQL Editor of SAP HANA and let it run 😉

ERP_TO_HANA_004.png

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…

ERP_TO_HANA_005.png

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 🙂

To report this post you need to login first.

16 Comments

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

    1. Alvaro Tejada Galindo Post author

      Vladislav:

      That’s why I used a 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.

      Also, there’s Data Services, Sybase Replication and many other “official” ways to it…mine is just a “fanboy project” 😉

      Greetings,

      Blag.

      (0) 
  1. Mark Walker

    Hey Blag

    Cool program!  Maybe if you have a couple of seconds, you could adapt it to use a secondary db connection in abap to connect straight to hana and dump your tables 😉

    Cheers

    Mark

    (0) 
    1. Alvaro Tejada Galindo Post author

      Mark:

      I’m lazy 😛 also by giving it away on a blog, I give to chance for anyone to improve it 🙂

      It’s not in my plans to make any further modifications to it 🙂

      Greetings,

      Blag.

      (0) 
  2. Matt Potts

    Alvaro,

    This is a great tool, especially for proof of concepts on HANA One or Developer Edition in the Amazon Cloud where you can’t use BODS or SLT (from a legal licensing perspective) to create the tables or load data from SAP ERP.

    Thanks,

    Matt

    (0) 
  3. shishupalreddy ramreddy

    Hi Alvaro,

    This is really nice writeup to avoid writing SQL within HANA dev studio to create table definitions . as you conveyed that this may not be good idea to have all inserts to update the table in HANA .I feel for data replication for the tables from ERP to HANA , we can have a different generic program which takes source as input file and then by using SLT process of Initial load and replication we can replicate the data as well atleast for the initial load of data .this is just an idea , not yet realized .

    however thanks a lot for sharing the information which was really helpful atleast to have some idea of ERP to HANA replication ….

    Have a great day .

    Regards,

    Shishupal.

    (0) 

Leave a Reply