XLR3 Data Extractor help

INTRODUCTION

XLR3 is a solution developped to face the common tasks of data extraction and data injection in a SAP R/3 system. This solution is based on a tight integration of SAP R/3 with Microsoft Office, and more specifically Microsoft Excel, in order to ease the output (data extraction) and input (data injection) of data.

XLR3 Data Extractor (XLR3Dex) focuses on SAP tables and views and proposes a straight and easy way of uploading them into Excel, Access as well as into simple text files. Several tables and views may be downloaded in one shot no matter whether they lie within one unique system or not. XLR3Dex not only downloads the content of tables and views, it also downloads their structure (their fields). This feature is especially useful when documenting tables and views.

XLR3Dex is an helpful tool for SAP R/3 developers, customizers, advanced users and all persons working with SAP R/3 who require easy access to raw data.

TECHNICAL REQUIREMENTS

XLR3Dex has been designed to work on personal computers running Windows and SAP GUI. Following versions of Windows are supported and have been tested:

• Windows 98 SE
• Windows 2000
• Windows XP

Windows 95, 98 (first edition) and ME have not been tested but should not cause any problems.

XLR3Dex being fully integrated within Excel, the latest is required in order to run XLR3Dex. Following versions of Excel have been tested:

• Excel 97
• Excel 2000
• Excel 2002
• Excel 2003

Other versions of Excel have not been tested but all versions starting from 97 shoud work.

INSTALLATION

The SAPRFC.INI file

Description

The SAPRFC.INI file is a special file defined by SAP that contains information about the SAP systems you are going to connect to through external systems using the RFC protocol.

XLR3 makes use of this connection solution since it is the most comfortable way of storing connection information and is also the most evolutive and flexible approach.

The first thing to do before using XLR3Dex is to check whether an environment variable called RFC_INI already exists in your system. This variable must define the path to the SAPRFC.INI file that contains the connection information for the SAP R/3 systems. In Windows, the simpliest way to verify whether the variable exists, and to create it if it doesn't, is by going through the System Settings in the Control Panel (see advanced settings in the system settings).

Attention: the environment variable RFC_INI is not specific to XLR3 but is part of the RFC communication protocol defined by SAP. This means the value stored in this environment variable may be used by other RFC applications. That's why it is adviseable that you place your SAPRFC.INI file in a central place on your computer and define the environment variable RFC_INI once for all. You should then have only one SAPRFC.INI file on your computer and this file should be complete, i.e. contain the communication data necessary to log on to all SAP R/3 systems available to you.

Definition of the SAPRFC.INI file

Defining the content of your saprfc.ini file is the first thing to do before starting using the XLR3 solution.

You have to create this file in the directory specified by the environment variable RFC_INI.

We won’t describe here all the possibilities of the saprfc.ini file connection methods but rather explain the basics of defining SAP systems entries to log on using the load balancing and direct specification of application server methods. These two methods are the most common methods currently used to log on to the R/3 systems but, given the evolutive nature of the saprfc.ini file connection method, other connection methods are likely to appear in the future. We ask you to consult the SAP R/3 online help for further details on connection to SAP R/3 using the saprfc.ini file. The website http://help.sap.com/ is the entry point for the online help of SAP R/3. You should find help on the saprfc.ini file in the section dedicated to the RFC API.

Connection to R/3 using the load balancing method

Using this method, the application server is determined at run time based on the work load of the different application servers.

The following parameters must be mentioned in the saprfc.ini file:

• DEST =
• TYPE = B
• HSHOST =

The following parameters are optional:

• R3NAME = , Default: equals DEST value
• GROUP = , Default: PUBLIC
• RFC_TRACE = <0/1, OFF/ON>, Default: 0, OFF
• ABAP_DEBUG = <0/1, OFF/ON>, Default: 0, OFF
• USE_SAPGUI = <0/1, OFF/ON>, Default: 0, OFF

Important:

It may be that the host name of the message server has to be defined in the system file ‘hosts’ in order for the host name to be recognized. In this case, you should add an entry similar to the following one:

127.0.0.1 localhost

The system file ‘services’ must contain an entry corresponding to the SAP system you defined in your saprfc.ini file. The entry should be named . Example:

sapmsSYS 3600/tcp

sapmsSY2 3601/tcp

The system file ‘sapmsg.ini’ must contain an entry for the message server / R/3 system combination. Example:

Please note that in most cases the SAP systems to which you will want to connect will already be accessible from your computer without any modification of the system files since these systems will already be accessed by the SAP GUI through the online connections operated through saplogon and its saplogon.ini file.

Connection to R/3 mentioning a specific application server

Mentioning the application server host name or IP address in the saprfc.ini file is easier than using the load balancing feature since you don’t have to update so many system files. But keep in mind that this way of connecting to R/3 is not recommended (unless there is only one application server for the R/3 system or the system you are connecting to is not much used) since no workload distribution is operated.

The following parameters must be mentioned in the saprfc.ini file:

• DEST =
• TYPE = A
• ASHOST =
• SYSNR=

The following parameters are optional:

• GWHOST = , Default: gateway on application server
• GWSERV = , Default: gateway on application server
• RFC_TRACE = <0/1, OFF/ON>, Default: 0, OFF
• ABAP_DEBUG = <0/1, OFF/ON>, Default: 0, OFF
• USE_SAPGUI = <0/1, OFF/ON>, Default: 0, OFF

Important

It may be that the host name of the R/3 application server has to be defined in the system file ‘hosts’ in order for the host name to be recognized. In this case, you should add an entry similar to the following one:

127.0.0.1 localhost

The system file ‘services’ must contain an entry corresponding to the SAP system you defined in your saprfc.ini file. The entry should be named . Example:

sapdpSYS 3200/tcp

sapdpSY2 3201/tcp

Similarly, the host name of the SAP gateway may require to be defined in the ‘hosts’ file while the service name of the SAP gateway must be defined in the ‘services’ file in the form .

Loading the XLR3Dex add-in into Excel.

XLR3Dex is an XLL addin for Excel. Like any XLL addin, it may be loaded by Excel through different ways. The way you will load it is up to you but should basically depend on whether you want the addin to be loaded automatically or not at Excel start-up.

You want to open XLR3Dex by opening it in Excel just like you open any other Excel document (through the 'File - Open' menu) or by double clicking XLR3Dex.xll directly from Windows.

This solution is possible while not recommended since it is not very handy. It is mainly useful to test the software in a straightforward way.

When proceeding this way, XLR3Dex may be stored in any directory. Just locate the XLR3Dex.xll file and open it.

You want to open XLR3Dex automatically each time you launch Excel.

For this to work, you need to have your XLR3Dex.xll file (or a shortcut to it) in you Excel start-up directory. You should check the exact name of this directory in your Excel documentation (in Excel 2002 English version, it is XLStart).

You use the Excel Add-in Manager to decide whether you load or not XLR3Dex.

Using the Excel Add-in Manager is the 'normal' way of working with addins within Excel. This manager lets you select the addins you want to activate. The first time you install an addin on your computer, you have to register it within the addin Manager. This task is similar to the Open command of the File menu except it has to be done only once.

After this, the addin will be opened automatically each time you launch Excel as long as you have marked it as active in the Addin Manager.

HOW TO USE XLR3 DATA EXTRACTOR - USER GUIDE

XLR3Dex has been designed to be easy to use. This is mainly achieved thanks to an intuitive interface that presents commands logically.

This little user guide explains the normal way of working using XLR3Dex and points out the specificities, limitations, ... of the software when needed.

Once XLR3Dex.xll has been loaded into Excel as described in the previous section, you remark that a menu called XLR3 has been added to the Excel menu bar before the Help menu title and that the commands 'Data Extractor' and 'Exit Data Extractor' have been added under this menu.

Selecting the 'Data Extractor' menu command opens the XLR3Dex main dialog box. Selecting the Exit menu command terminates the XLR3Dex application (it does not close the dialog box since this is achieved through the classic cross in the upper right corner of the dialog box).

Once the XLR3Dex dialog box is visible, you see in its upper left side a list that will contain the identifications of the tables and views you will be downloading. This list is managed through the four buttons which are located in the upper right side of the dialog box. These buttons allow the addition, modification and deletion of the tables and views in the list.

Below these four buttons are two more buttons whose goal is to restrict the selection of records in SAP tables (the restriction does not apply to views) and to specify the fields to output from the downloaded tables and views.

At the bottom of the dialog box lie the commands used to save and load the current configuration, configure the output, get information about the product and, last but not least, execute the data extraction.

All these actions are detailed hereafter.

Add a table or view

Adding a table or view to the tables list is certainly the first thing you will want to do after loading XLR3Dex. When this command is selected, a sub-dialog is opened which lets you specify the name of the table to download as well as the system name and client (SAP mandant) in which the table or view lies. The system name you specify here must correspond to an entry in the SAPRFC.INI file.

If you don't know the name of the table or view to download, you can use the search function. This function lets you specify a part of the table name and/or two table descriptions (case sensitive) using the '%' character in place of unknown characters and ‘_’ in place of one unknown character. If you are searching for the character ‘%’ or ‘_’, please mention the character ‘\’ before each of these characters. For example, if you indicate the table name ‘V\_%’, you will get all tables and views starting with ‘V_’.

If you search for example all tables and views related to dunning, you should leave the table name empty and mention %dunning% in the first description field and %Dunning% in the second one.

Now, if you know you are searching for a customer master data table, you could restrict further your search by specifying a table name starting with KN and mention KN% as table name.

For each table to download, it is possible to specify whether:

• the table must be uploaded into Excel.
• the table meta data (table structure) must be uploaded into Excel.
• the table must be saved into an Access database.
• the table must be saved into a flat text file.

For Excel uploads, it is possible to choose the name of the Excel sheet to be created or let the system define it (="Auto Naming").

For Access uploads, it is possible to define the name of the Access table that will be created within the database whose name is defined in the output options, or let the system define it (="Auto Naming").

For text files output, the name of the file can be specified or automatic naming selected. In all cases, the output directory is specified in the output options, so you should mention only the name of the file without the path information at this place.

The settings we have just seen are defined for each table entry. These settings are overwritten by the settings specified in the output options we will see later on.

Modify a table or view

The modification operation lets you modify the settings of a table/view but not the table/view name, system and client information. These are fixed as soon as the table has been added.

Remove a table or view from the list

If a table/view name, system or client information is erroneous or if this table/view does not need to be downloaded, it can be deleted from the tables list.

Clear all tables and views from the list

This options empties the tables list.

Specify the selection condition

It is possible to restrict the records which will be read from a table by specifying conditions that these records must meet. These conditions have the same syntax as the WHERE clause of SELECT statements as defined in the OPEN SQL used in ABAP/IV. We ask you to refer to SAP R/3 online documentation in order to get an exhaustive explanation of the WHERE clause of the SELECT command. We will only give you hereafter the basics necessary to be able to specify simple clauses.

The conditions specified in the WHERE clause are very much like logical expressions specified in classical IF statements, loops, etc. Indeed, the classical form of a condition is to compare a field from the table to a given value thanks to a comparison operator.

The common comparison operators are:

• = or EQ (Equals)
• <> or >< or NE (Not Equal)
• < or LT (Lower Than)
• <= or LE (Lower or Equal)
• > or GT (Greater Than)
• >= or GE (Greater or Equal)

Examples:

NAME = ‘Company XYZ’

YEAR < ‘2004’

Special comparison operators exist for specific purposes.

Comparison of strings

The operator LIKE checks the matching of a field’s content with a specified pattern. The pattern may use special characters ‘%’ and ‘_’ to respectively replace a group of unknown characters or one single unknown character.>

Example:

COUNTRY LIKE ‘_elgi%’

In this example, all words such as Belgium, belgium, Belgian, belgian, … meet the pattern as the first character may be any character and the end of the character string may be anything.

Intervals

The operator BETWEEN is used to check whether a field’s content lies within two values defining an interval.

Example:

YEAR BETWEEN ‘2000’ AND ‘2003’.

All years from 2000 to 2003 included satisfy this condition.

Lists of values

Example:

COUNTRY IN (‘BE’, ‘FR’, ‘US’)

Countries BE, FR and US satisfy this condition.

Check against null values

The IS NULL clause tests a field against a null value.

Linked conditions

The two linkage operators AND and OR may be used to specify complex conditions made up of several simple conditions linked together using a logical AND or a logical OR operation.

Negative conditions

The NOT operator inverses the result of a condition.

Examples:

COUNTRY = ‘FR’ OR ( COUNTRY = ‘BE’ AND LANGU = ‘FR’ )

Output fields

The output fields command lets you specify which fields will be considered when processing the output of data, no matter the target support - Excel, Access or Text files. All fields selected in the selection dialog will be considered for output. The other fields will be left aside.

We remember here the basics of selecting items in a list view dialog which is the type of control used to select the output fields:

• You can select one item by clicking on it.
• You can select additional items by clicking on them and pressing the CTRL key at the same time.
• You can select a block of items by clicking on the first item of the block and, while pressing the SHIFT key, clicking on the last item of the block.

Output options

Some parameters may be defined for the three output destinations.

For Excel uploads, you have the opportunity to disable the upload of the table content.

Independently of this parameter, you can define whether you want to download the table meta data or not.

If you choose to upload the table content into Excel, you can further specify whether the table should have an header row with the technical field names and/or one with the fields descriptions.

It is also possible to upload a table on several Excel sheets and to specify the maximum number of records to output on each Excel sheet.

When uploading to Access, the output options are used to specify the Access database which will be created with the content of the tables and views.

For the export of tables and views to text files, the directory in which the text files will be saved is mentionned in the output options as well as a fields separator which can be empty. A header line with the fields names may also be requested. This will be created only if the fields separator is not empty.

Saving and loading the configuration

The table list as well as all parameters defined in the program may be saved in a file for later reuse. The files created have an extension ‘.dex’.

DISTRIBUTION OF XLR3 DATA EXTRACTOR BETA

XLR3 Data Extractor beta is free. It may be redistributed to anyone as long as it is not modified in any way (this also includes the retrieval of files) before redistribution and as long as the redistribution is made free (or covers only the distribution costs). Any commercial redistribution of this software is however forbidden without the prior consent of the author.