Extracting data from the SAP system - Part 1 - Table data (1)

Veröffentlicht von Shortcut IT in Sc4SAP · 24 Juli 2023
Tags: SAP automation;SAP data extract;SAP data download

In practice, it is often necessary to extract data from the SAP system, whether for further processing outside the SAP system, for transfer to other SAP systems, or for other purposes. SAP offers several possibilities via the SAPGUI. Probably everyone has already exported a list to one's computer or downloaded a spool request.

This works quite well - more or less - but it is a manual task. If you need this to be done automatically on a periodic basis, in the SAP standard you have to spend additional effort. Typically, you then end up developing one or more ABAP programs, using some function modules and creating the file(s) with OPEN DATASET, TRANSFER and so on. This will satisfy the need to have a file on the SAP system or on a file server, mounted to the SAP system. If you need the file on your (frontend) computer, you are a little bit lost in case you want to have an automatic process.

How you can automate this without developing ABAPs, you can read here.

It is typically about content of tables or of output of ABAP programs. Let's have a look at table content first. Table content can be extracted with the "View table data" function as well as with the "Download / Upload table data" function of "Shortcut for SAP systems". This article describes how to do it using the "View table data" function. The 2nd part is more about using the "Download / Upload table data" function, especially about the available file formats, and in the 3rd part we will deal with the output of programs (lists).

Table content

Nearly every table in the SAP system can be accessed via transaction SE16 (there are few exceptions only, where this is explicitely deactivated). You will receive a list, depending on your settings in SE16 als plain text or as an ALV list or ALV grid.

Using this in background mode (as it is still our approach to automate this) has some hurdles. You can use the ABAP program for scheduling it in a job (the name will be offered in SE16 > System > Status. Usually it starts with '/1BCDWB/DB', followed by the name of the table). But the report might be generated again, with other selection criteria or another selection of fields to be given out in the list. This can be triggered by any user, accessing the same table. Therefore, there is no reliability for getting the data in the same format as it was when the job was set up. If you want to have this for sure, you have to develop a dedicated ABAP program.

Or you use "Shortcut for SAP systems".

There are several functions that can be used for it, and all of them can easily be used for automation.

"View table data"

With the "View table data" function the whole table can be read, each field of the table will be given out in the list.

If you want, you can specify an upper limit of records to be read. A value '0' means, there is no limit, so the whole table will be read.

Using the dialog function you will receive the list in a dialog window:

"View table data" function: result / table content

Using the "Export to file" the list can be exported to your (frontend) computer or an accessible network directory.

There are 2 file types available: CSV (comma separated values) can easily be used in spreadsheet applications like MS Excel.

In case you chose the CSV format, the data will be exported including header information for the fields.

Same file in a spreadsheet application:

When using the other file type - TXT - the file will look like this:

This format is more suitable for further computer processing. The fields and their lengths correspond to the table definition and if the table definition is not changed, the structure of the data remains the same.

Ok, so far we used the GUI application of "Shortcut for SAP systems". Now let's see how we can automate this data extraction. For this we can use the supplied command line tool. It is located in the same directory and is named "Sc4SAPCmd.exe".

Shortcut for SAP systems command line tool

Nearly each function available in the GUI application (= each function where this makes sense) can also be processed using the command line tool. There are 2 methods to call the command line tool:

1) Calling sc4sapcmd.exe with function and variant as arguments (variant = variant in Sc4SAP).

When storing the values for the function in a Variant...

naming it "PX1/100 - table AGR_USERS" and wanting to receive the data in a file "PX1 role assignments.csv", the call of the command line tool would be as following:

For getting the data in a TXT file we would use file extension .txt.

This one-liner can be used in a batch file, in an automation software or whatever. The command line tool will be executed on OS level without any further requirements, execute the "View table data" and write the data into the given file.

Let's test this by using the console:

The 2nd option to call the command line tool:

2) Calling sc4sapcmd.exe with an XML file as argument, containing all information of the function to be executed.

This offers more flexibility than using variants. We don't have to specify a variant, the XML file can be created either with a text editor or also by another application (for example a Chatbot can create an XML file for unlocking a user as a result from a chat with him). All information needed for doing the job is in the XML file.

The above used data, put into an XML file, would look as following:

<Task>

<Comment>Get the role assignments of PX1/100 in a file</Comment>

<Table>agr_users</Table>

<OutputFile>E:\SAP\Reports\PX1 Role assignments.csv</OutputFile>

</ViewTableData>

</Task>

</ExecutionChain>

Again we have a one-liner that can be used in a batch file, in an automation software or whatever.

After calling the command line tool with the XML file as argument we will receive similar output to the one created by using function and variant:

Output of the command line tool (using an XML file)

After the execution the XML file will be enriched with some information regarding the success of the execution and the result:

Result information in the XML file after execution

So, this is a fast solution to extract table data into a CSV or a TXT file from the SAP system to your frontend or a network storage location. You do not have to develop ABAP reports, it can be easily implemented and scheduled on a regular basis.

Part 2: Extracting data from the SAP system - Part 2 - Table data (2)

Part 3: Extracting data from the SAP system - Part 3 - Output of reports

Es gibt noch keine Rezension.