5

I need to extract data from a SAP ABAP system in a format that can then be loaded into an Oracle database (xlsx,csv,dmp.. etc)

Once the data is extracted I'll use Pentaho to upload it into the Oracle database.

Is there a way to extract the data from SAP? I will also need to automate it (the extraction) but that is not too much of a problem right now, I can figure/worry about that part later.

If it is not possible to do so, an explanation why would be helpful!

Improve this question

3 Answers 3

Reset to default
8

You have a number of options to do this.

If you are running SAP BW, there are many standard tools to help you do extractions and automate the processes.

Otherwise, you can write a simple ABAP program (type 1) to read data from tables and put it into a flat file.

Otherwise, you could write a remote-enabled function module (RFC) and call it using SAP's RFC library.

You could also wrap your RFC function with a web service and call it via SOAP/HTTP.

Lastly, if you have access to the database, you might even be able to write a script to extract the data you need.

A simple example of a program to extract something from a DB table:

report ZEXTRACT_EXAMPLE.

data: lt_t001 type table of t001.
data: ls_t001 type t001.
data: lv_filename type string value '/tmp/outfile.txt'.

select * from t001 into table lt_t001.

open dataset lv_filename for output in text mode encoding default.

loop at lt_t001 into ls_t001.
  transfer ls_t001-bukrs to lv_filename.
endloop.

close dataset lv_filename.

This is really primitive, but you get the idea. It selects data from a DB table into an internal table (in memory) and writes it to a file called /tmp/outfile.txt on the server, from where you can pick it up. (You would have to alter the output to be in your required format).

You could then schedule your program with SM36 to run periodically as a background job.

Improve this answer
3
  • 2
    @mydoghasworms: Well, this program will hardly work if the table has for example 10 million records. One should rather use OPEN CURSOR statement for selecting the data and than successively write them to a file.
    – Jagger
    Commented Feb 2, 2012 at 18:01
  • 1
    @mydoghasworms: One more thing... if the table contains some fields that are non-character fields you will get different length for, for example, integers or decimals in the text file.
    – Jagger
    Commented Feb 2, 2012 at 18:07
  • @Jagger, You are absolutely right. You wouldn't want to read millions of records into memory. You also wouldn't want to simply export non-character fields without applying consistent formatting to them. As for the lengths, if your file uses a delimiter rather than being fixed-width, that won't matter so much. Thanks for the observation.
    – mydoghasworms
    Commented Feb 2, 2012 at 18:17
3

You can also use the remote enabled function module 'RFC_READ_TABLE', you can give it any table name and a separator and it will return the internal table nicely formatted for you.

Improve this answer
2
  • RFC_READ_TABLE has several short-comings, though: - it works only if the table has all CHAR fields (one INT or FLOAT and you are busted), - maximum table width is 512, - it works only in Non-Unicode (if the backend is Unicode, the internal alignment of the fields will get corrupted...) - and finally it is a big security flaw, possibly bypassing the business level authorizations that the table in question may be protected with... In other words: never use it!
    – Lanzelot
    Commented Mar 5, 2018 at 14:33
  • Agreed. Except that I have used RFC_READ_TABLE on Unicode systems without issue?
    – tomdemuyt
    Commented Mar 5, 2018 at 15:31
-1

One option to solve this is to find an JDBC driver to the SAP ERP data that gathers the tables by looking at the SAP logical table level.

Once you have the right JDBC driver you can use that from Pentaho.

I have tried this kind of JDBC over SAP ERP solution with Talend and it works great.

RFC_READ_TABLE as pointed out by previous comments is pretty limited in terms of the number of table fields and also the number of records, not to mention conversion issues.

Improve this answer
1
  • RFC_READ_TABLE indeed is bad. But this solution looks even worse: would you really hand out the password of you SAP database to some JDBC script that's running somewhere and might easily get compromised?? It might amount to handing over the crown jewels of your company to any old hacker (or untrust-worthy employee), who manage to find that script...
    – Lanzelot
    Commented Mar 5, 2018 at 14:37

Not the answer you're looking for? Browse other questions tagged or ask your own question.