Skip to Content
Technical Articles
Author's profile photo Stefan Schnell

How To Export SAP Table Content to SQLite Data Containers

SQLite is a platform independent database engine. So it is interesting to make SAP table data available for this engine. Here a way to export SAP table content via ABAP to SQL statements. After the export the content of the table is available as SQL statements in a text file. You can read this text file with sqlite.exe and create your SQLite database on this way. To use this way comfortable from ABAP I programmed a class with the public method GetTable2SQlCmdFile – to export the table indirect via an SQL command file, which can be imported to an SQLite database.

Here the class:

"-Begin-----------------------------------------------------------------
"-
"- Class to export SAP tables as SQL strings in text files, to import
"- the data in SQLite via read or other SQL databases
"-
"- Author: Stefan Schnell
"- Version: 1.08
"- Date: 2019-05-04
"-
"- Checked with SAP 7.50 SP 14 - Different tables, import SQLite 3.28
"- Checked with SAP 7.40 SP 12 - Table SFLIGHT
"- Checked with SAP 7.31 SP  4 - Different tables
"- Checked with SAP 7.02 SP  6 - Different tables
"-
"-----------------------------------------------------------------------

CLASS zsqlite DEFINITION PUBLIC CREATE PUBLIC .

  PUBLIC SECTION.

    CONSTANTS:
      true    TYPE i VALUE 1,
      false   TYPE i VALUE 0,
      crlf(2) TYPE c VALUE cl_abap_char_utilities=>cr_lf.

    "! Get a table in a text file as SQL commands
    "!
    "! @parameter iv_TableName        | Name of the table
    "! @parameter iv_WhereClause      | Where clause of select
    "! @parameter iv_FileName         | Name of the text file
    "! @parameter iv_SQLiteSQL        | Flag to use SQLite specific data types
    "! @parameter iv_OverWriteConfirm | Flag for request to overwrite an existing file
    METHODS gettable2sqlcmdfile
      IMPORTING
        iv_tablename        TYPE string
        iv_whereclause      TYPE string
        iv_filename         TYPE string
        iv_sqlitesql        TYPE i DEFAULT 1
        iv_overwriteconfirm TYPE c DEFAULT SPACE.

  PROTECTED SECTION.

  PRIVATE SECTION.

    "! Get the structure of a table as SQL string
    "!
    "! @parameter iv_TableName | Name of the table
    "! @parameter iv_SQLiteSQL | Flag to use SQLite specific data types
    "!
    "! @parameter cv_SQLCmd    | SQL command to create a table
    METHODS gettabstruc
      IMPORTING
        iv_tablename TYPE string
        iv_sqlitesql TYPE i
      CHANGING
        cv_sqlcmd    TYPE string.

    "! Get the data of a table as a table of SQL strings
    "!
    "! @parameter iv_TableName    | Name of the table
    "! @parameter iv_WhereClause  | Where clause of select
    "!
    "! @parameter SQLCmds         | SQL commands
    METHODS gettabdata
      IMPORTING
        iv_tablename   TYPE string
        iv_whereclause TYPE string
      CHANGING
        ct_sqlcmds     TYPE STANDARD TABLE.

    "! Delete leading zeros in a string
    "!
    "! @parameter cv_NumChar | Numeric string
    METHODS trimleadingzeros
      CHANGING
        cv_numchar TYPE string.

ENDCLASS.



CLASS ZSQLITE IMPLEMENTATION.



  METHOD gettabdata."---------------------------------------------------
    "-
    "- Get the data of a table as a table of SQL strings
    "-
    "- Arguments: iv_TableName   = Name of the table as string
    "-            iv_WhereClause = Where clause of select
    "- Changing:  ct_sqlcmds     = Table with SQL commands
    "-
    "-------------------------------------------------------------------

    TYPES: BEGIN OF tabstruc,
             tabname   TYPE dd03l-tabname,
             fieldname TYPE dd03l-fieldname,
             position  TYPE dd03l-position,
             inttype   TYPE dd03l-inttype,
             intlen    TYPE dd03l-intlen,
             datatype  TYPE dd03l-datatype,
             decimals  TYPE dd03l-decimals,
           END OF tabstruc.

    "-Maximum length of a record----------------------------------------
    DATA: BEGIN OF tablebuffer,
            buffer(65535),
          END OF tablebuffer.

    DATA:
      lt_tabstruc      TYPE STANDARD TABLE OF tabstruc,
      ls_tabstruc      TYPE tabstruc,
      lv_strtemp       TYPE string,
      lv_fieldcontent  TYPE string,
      lv_ifieldcontent TYPE i,
      lv_position      TYPE i,
      lv_i             TYPE i.

    FIELD-SYMBOLS:
      <tabstruc>      LIKE LINE OF lt_tabstruc,
      <table>         TYPE any,
      <fieldcontent>  TYPE any,
      <pfieldcontent> TYPE p,
      <cfieldcontent> TYPE c,
      <nfieldcontent> TYPE n,
      <xfieldcontent> TYPE x,
      <ifieldcontent> TYPE i.

    SELECT tabname fieldname position inttype intlen datatype
      decimals INTO CORRESPONDING FIELDS OF TABLE lt_tabstruc
      FROM dd03l WHERE tabname = iv_tablename ORDER BY position.

    ASSIGN tablebuffer TO <table> CASTING TYPE (iv_tablename).

    SELECT * FROM (iv_tablename) INTO <table> WHERE (iv_whereclause).

      lv_strtemp = `INSERT INTO ` && iv_tablename && ` VALUES (`.

      LOOP AT lt_tabstruc INTO ls_tabstruc.
        CASE ls_tabstruc-inttype.

          WHEN 'P'."-Packed numbers-------------------------------------
            ASSIGN COMPONENT ls_tabstruc-fieldname
              OF STRUCTURE <table> TO <pfieldcontent> CASTING.
            MOVE <pfieldcontent> TO lv_fieldcontent.
            lv_strtemp = lv_strtemp && ` ` && lv_fieldcontent && ` ,`.

          WHEN 'C'."-Characters-----------------------------------------
            ASSIGN COMPONENT ls_tabstruc-fieldname
              OF STRUCTURE <table> TO <cfieldcontent> CASTING.
            MOVE <cfieldcontent> TO lv_fieldcontent.
            REPLACE ALL OCCURRENCES OF '''' IN lv_fieldcontent WITH ''''''.
            lv_fieldcontent = `'` && lv_fieldcontent && `'`.
            lv_strtemp = lv_strtemp && ` ` && lv_fieldcontent && ` ,`.

          WHEN 'N'."-Numeric characters---------------------------------
            ASSIGN COMPONENT ls_tabstruc-fieldname
              OF STRUCTURE <table> TO <nfieldcontent> CASTING.
            MOVE <nfieldcontent> TO lv_fieldcontent.
            CALL METHOD me->trimleadingzeros
              CHANGING
                cv_numchar = lv_fieldcontent.
            lv_fieldcontent = `'` && lv_fieldcontent && `'`.
            lv_strtemp = lv_strtemp && ` ` && lv_fieldcontent && ` ,`.

          WHEN 'X'."-Byte sequence--------------------------------------
            CASE ls_tabstruc-datatype.
              WHEN 'INT1' OR 'INT2'.
                ASSIGN COMPONENT ls_tabstruc-fieldname
                  OF STRUCTURE <table> TO <xfieldcontent> CASTING.
                MOVE <xfieldcontent> TO lv_ifieldcontent.
                MOVE lv_ifieldcontent TO lv_fieldcontent.
              WHEN 'INT4'.
                ASSIGN COMPONENT ls_tabstruc-fieldname
                  OF STRUCTURE <table> TO <ifieldcontent> CASTING.
                MOVE <ifieldcontent> TO lv_ifieldcontent.
                MOVE lv_ifieldcontent TO lv_fieldcontent.
                CALL FUNCTION 'CLOI_PUT_SIGN_IN_FRONT'
                  CHANGING
                    value = lv_fieldcontent.
              WHEN OTHERS.
                ASSIGN COMPONENT ls_tabstruc-fieldname
                  OF STRUCTURE <table> TO <xfieldcontent> CASTING.
                MOVE <xfieldcontent> TO lv_fieldcontent.
                lv_fieldcontent = `'` && lv_fieldcontent && `'`.
            ENDCASE.
            lv_strtemp = lv_strtemp && ` ` && lv_fieldcontent && ` ,`.

          WHEN 'D'."-Date-----------------------------------------------
            ASSIGN COMPONENT ls_tabstruc-fieldname
              OF STRUCTURE <table> TO <fieldcontent>.
            MOVE <fieldcontent> TO lv_fieldcontent.
            "- To compensate empty or 00000000 date fields--------------
            IF lv_fieldcontent IS INITIAL OR lv_fieldcontent = '00000000'.
              lv_fieldcontent = '0'.
            ENDIF.
            lv_strtemp = lv_strtemp && ` ` && lv_fieldcontent && ` ,`.

          WHEN space."-Structure----------------------------------------
            "-
            "- To compensate structures
            "-
            "-----------------------------------------------------------

          WHEN OTHERS."-All other---------------------------------------
            ASSIGN COMPONENT ls_tabstruc-fieldname
              OF STRUCTURE <table> TO <fieldcontent>.
            MOVE <fieldcontent> TO lv_fieldcontent.
            lv_strtemp = lv_strtemp && ` ` && lv_fieldcontent && ` ,`.

        ENDCASE.
      ENDLOOP.

      lv_i = strlen( lv_strtemp ).
      lv_i = lv_i - 1.
      MOVE lv_strtemp+0(lv_i) TO lv_strtemp.
      lv_strtemp = lv_strtemp && ` );`.
      APPEND lv_strtemp TO ct_sqlcmds.

    ENDSELECT.

  ENDMETHOD.



  METHOD gettable2sqlcmdfile."------------------------------------------
    "-
    "- Get a table in a text file as SQL commands
    "-
    "- Arguments: lv_TableName        = Name of the table as string
    "-            lv_WhereClause      = Where clause of select
    "-            lv_FileName         = Name of the text file as string
    "-            lv_SQLiteSQL        = Flag to use SQLite specific data types
    "-                                  as integer (0 or 1)
    "-            lv_OverWriteConfirm = Flag for request to overwrite
    "-                                  an existing file as char
    "-                                  (Space or X)
    "-
    "-------------------------------------------------------------------

    DATA:
      lv_sqlcmd  TYPE string,
      lt_sqlcmds TYPE STANDARD TABLE OF string.

    "-Get the structure of the table------------------------------------
    CALL METHOD me->gettabstruc
      EXPORTING
        iv_tablename = iv_tablename
        iv_sqlitesql = iv_sqlitesql
      CHANGING
        cv_sqlcmd    = lv_sqlcmd.

    APPEND lv_sqlcmd TO lt_sqlcmds.

    "-Get the data of the table-----------------------------------------
    CALL METHOD me->gettabdata
      EXPORTING
        iv_tablename   = iv_tablename
        iv_whereclause = iv_whereclause
      CHANGING
        ct_sqlcmds     = lt_sqlcmds.

    "-Write the data to the output file---------------------------------
    CALL METHOD cl_gui_frontend_services=>gui_download
      EXPORTING
        filename          = iv_filename
        confirm_overwrite = iv_overwriteconfirm
      CHANGING
        data_tab          = lt_sqlcmds
      EXCEPTIONS
        OTHERS            = 1.

  ENDMETHOD.



  METHOD gettabstruc."--------------------------------------------------
    "-
    "- Get the structure of a table as SQL string
    "-
    "- Arguments: lv_TableName = Name of the table as string
    "-            lv_SQLiteSQL = Flag to use SQLite specific data types
    "-                           as integer (0 or 1)
    "- Return:    cv_SQLCmd    = Command to create a table
    "-
    "-------------------------------------------------------------------

    TYPES: BEGIN OF tabstruc,
             tabname   TYPE dd03l-tabname,
             fieldname TYPE dd03l-fieldname,
             position  TYPE dd03l-position,
             inttype   TYPE dd03l-inttype,
             datatype  TYPE dd03l-datatype,
             leng      TYPE dd03l-leng,
             decimals  TYPE dd03l-decimals,
           END OF tabstruc.

    DATA:
      lt_tabstruc TYPE STANDARD TABLE OF tabstruc,
      ls_tabstruc TYPE tabstruc,
      lv_i        TYPE i,
      lv_len      TYPE string,
      lv_dec      TYPE string.

    SELECT tabname fieldname position inttype datatype leng decimals
      INTO CORRESPONDING FIELDS OF TABLE lt_tabstruc FROM dd03l
      WHERE tabname = iv_tablename ORDER BY position.

    cv_sqlcmd = `CREATE TABLE ` && iv_tablename && ` (`.

    LOOP AT lt_tabstruc INTO ls_tabstruc.
      lv_i = lv_i + 1.
      lv_len = ls_tabstruc-leng.
      CALL METHOD me->trimleadingzeros CHANGING cv_numchar = lv_len.
      CASE ls_tabstruc-inttype.

        WHEN 'C' OR 'N'."-----------------------------------------------
          "-
          "- Convert C (Character string) or N (Character string with
          "- digits only) to SQL CHAR or SQLite TEXT
          "-
          "-------------------------------------------------------------
          cv_sqlcmd = cv_sqlcmd && ` ` && ls_tabstruc-fieldname.
          IF iv_sqlitesql = false.
            cv_sqlcmd = cv_sqlcmd && ` CHAR(`.
          ELSE.
            cv_sqlcmd = cv_sqlcmd && ` TEXT(`.
          ENDIF.
          cv_sqlcmd = cv_sqlcmd && lv_len && ')'.
          IF lv_i <> sy-dbcnt.
            cv_sqlcmd = cv_sqlcmd && ','.
          ENDIF.

        WHEN 'D' OR 'T'."-----------------------------------------------
          "-
          "- Convert D (Date) or T (Time) to SQL NUMERIC or SQLite
          "- NUMERIC
          "-
          "-------------------------------------------------------------
          cv_sqlcmd = cv_sqlcmd && ` ` && ls_tabstruc-fieldname.
          cv_sqlcmd = cv_sqlcmd && ` NUMERIC(` && lv_len && ')'.
          IF lv_i <> sy-dbcnt.
            cv_sqlcmd = cv_sqlcmd && ','.
          ENDIF.

        WHEN 'I'."------------------------------------------------------
          "-
          "- Convert I (Integer number) to SQL NUMERIC or
          "- SQLite INTEGER
          "-
          "-------------------------------------------------------------
          cv_sqlcmd = cv_sqlcmd && ` ` && ls_tabstruc-fieldname.
          IF iv_sqlitesql = false.
            cv_sqlcmd = cv_sqlcmd && ` NUMERIC(`.
          ELSE.
            cv_sqlcmd = cv_sqlcmd && ` INTEGER(`.
          ENDIF.
          cv_sqlcmd = cv_sqlcmd && lv_len && ')'.
          IF lv_i <> sy-dbcnt.
            cv_sqlcmd = cv_sqlcmd && ','.
          ENDIF.

        WHEN 'F' OR 'P'."-----------------------------------------------
          "-
          "- Convert F (Floating point number) or P (Packed number)
          "- to SQL NUMERIC or SQLite REAL
          "-
          "-------------------------------------------------------------
          cv_sqlcmd = cv_sqlcmd && ` ` && ls_tabstruc-fieldname.
          IF iv_sqlitesql = false.
            cv_sqlcmd = cv_sqlcmd && ` DECIMAL(`.
          ELSE.
            cv_sqlcmd = cv_sqlcmd && ` REAL(`.
          ENDIF.
          lv_dec = ls_tabstruc-decimals.
          CALL METHOD me->trimleadingzeros
            CHANGING
              cv_numchar = lv_dec.
          cv_sqlcmd = cv_sqlcmd && lv_len && ',' && lv_dec && ')'.
          IF lv_i <> sy-dbcnt.
            cv_sqlcmd = cv_sqlcmd && ','.
          ENDIF.

        WHEN 'X'."------------------------------------------------------
          "-
          "- Convert X (Byte sequence) to SQL NUMERIC or
          "- SQLite INTEGER or SQL CHAR or SQLite TEXT
          "-
          "-------------------------------------------------------------
          cv_sqlcmd = cv_sqlcmd && ` ` && ls_tabstruc-fieldname.
          IF iv_sqlitesql = false.
            CASE ls_tabstruc-datatype.
              WHEN 'INT1' OR 'INT2' OR 'INT4'.
                cv_sqlcmd = cv_sqlcmd && ` NUMERIC(`.
              WHEN OTHERS.
                cv_sqlcmd = cv_sqlcmd && ` CHAR(`.
            ENDCASE.
          ELSE.
            CASE ls_tabstruc-datatype.
              WHEN  'INT1' OR 'INT2' OR 'INT4'.
                cv_sqlcmd = cv_sqlcmd && ` INTEGER(`.
              WHEN OTHERS.
                cv_sqlcmd = cv_sqlcmd && ` TEXT(`.
            ENDCASE.
          ENDIF.
          cv_sqlcmd = cv_sqlcmd && lv_len && ')'.
          IF lv_i <> sy-dbcnt.
            cv_sqlcmd = cv_sqlcmd && ','.
          ENDIF.

      ENDCASE.

    ENDLOOP.

    cv_sqlcmd = cv_sqlcmd && ` );`.

  ENDMETHOD.



  METHOD trimleadingzeros."---------------------------------------------
    "-
    "- Delete leading zeros in a string
    "-
    "- Arguments: NumChar = Numeric string
    "- Return: Numeric string without leading zeros
    "-
    "-------------------------------------------------------------------

    DATA:
      lv_numcharlen TYPE i,
      lv_newlen     TYPE i,
      lv_pos        TYPE integer,
      lv_char(1)    TYPE c.

    lv_numcharlen = strlen( cv_numchar ).
    DO lv_numcharlen TIMES.
      lv_pos = sy-index - 1.
      MOVE cv_numchar+lv_pos(1) TO lv_char.
      IF lv_char <> '0'.
        EXIT.
      ENDIF.
    ENDDO.
    lv_newlen = lv_numcharlen - lv_pos.
    MOVE cv_numchar+lv_pos(lv_newlen) TO cv_numchar.

  ENDMETHOD.



ENDCLASS.

And here a report to test the class with different tables from SAPBC_DATAMODEL package:

REPORT zsqlite.

"-Begin-----------------------------------------------------------------
"-
"- An example how to use zSQLite class to export SAP tables to an
"- SQLite database as a collection of SQL statements in a text file.
"- This must be imported in SQLite via .read command.
"-
"- Author: Stefan Schnell
"- Date: 2019-05-04
"-
"-----------------------------------------------------------------------

DATA:
  lo_sqlite           TYPE REF TO zsqlite,
  lt_tablenames       TYPE STANDARD TABLE OF string,
  lv_tablename        TYPE string,
  lv_whereclause      TYPE string,
  lv_filename         TYPE string,
  lv_sqlitesql        TYPE i VALUE 1,
  lv_confirmoverwrite TYPE abap_bool VALUE abap_true.

lt_tablenames = VALUE #(
                         ( `SFLIGHT` )
                         ( `SFLIGHTS` )
                         ( `SAIRPORT` )
                         ( `SAPLANE` )
                         ( `SBOOK` )
                         ( `SBUSPART` )
                         ( `SCARPLAN` )
                         ( `SCARR` )
                         ( `SCURR` )
                         ( `SPFLI` )
                       ).

CREATE OBJECT lo_sqlite.

LOOP AT lt_tablenames INTO lv_tablename.

  lv_filename = 'C:\Dummy\' && lv_tablename && '.sql'.

  CALL METHOD lo_sqlite->gettable2sqlcmdfile
    EXPORTING
      iv_tablename        = lv_tablename
      iv_whereclause      = lv_whereclause
      iv_filename         = lv_filename
      iv_sqlitesql        = lv_sqlitesql
      iv_overwriteconfirm = lv_confirmoverwrite.

ENDLOOP.

"-End-------------------------------------------------------------------

You need the SQLite library to import the data from here.

To import all these tables into one SQLite database you can use this command set:

sqlite3.exe SFLIGHT.sqlite ".read SAIRPORT.sql"
sqlite3.exe SFLIGHT.sqlite ".read SAPLANE.sql"
sqlite3.exe SFLIGHT.sqlite ".read SBOOK.sql"
sqlite3.exe SFLIGHT.sqlite ".read SBUSPART.sql"
sqlite3.exe SFLIGHT.sqlite ".read SCARPLAN.sql"
sqlite3.exe SFLIGHT.sqlite ".read SCARR.sql"
sqlite3.exe SFLIGHT.sqlite ".read SCURR.sql"
sqlite3.exe SFLIGHT.sqlite ".read SFLIGHT.sql"
sqlite3.exe SFLIGHT.sqlite ".read SFLIGHTS.sql"
sqlite3.exe SFLIGHT.sqlite ".read SPFLI.sql"

And after that step you find all the tables with its content inside the SFLIGHT.sqlite database.

Enjoy the possibility to export SAP tables into SQLite databases. With this way you can use the content easily on different platforms and in different environments,

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.