SAP BW: Create a fixed length file with Open Hub Destination

Posted by Xavier Hacking

At my current project we are using SAP BW and its Open Hub Destination (Open Hub) object to create a number of flat file interfaces to export data from SAP BW to other systems. An Open Hub creates a comma separated value (CSV) file which can be placed on a local directory or on an application server. The Open Hub uses a standard transformation and DTP to transform and load data from an InfoCube, DSO or InfoObject. If you are exporting to an application server you can also use Process Chains for scheduling.

Now we got this fancy old-school requirement to create an interface in which the files should be fixed length instead of CSV. Great. Since the Open Hub uses a mandatory separator (the comma, or in most cases a semicolon) to separate the fields and it does not have any options to determine the exact position of each field in a record in the file, a workaround is needed to make this work.

Update: Thomas Brandt mentioned in the comments below this post that with SAP BW 7.3 it now is possible to export to CSV, ASCII and XML. Check SAP Help for more info. If your BW hasn’t been upgraded to 7.3 you can still use the workaround.

In this example I will create an Open Hub called ZFIXED on top of the 0PERSON InfoObject which generates a fixed length file. The file will contain 4 fields according to the specs below:

  1. Person, position 1 to 10;
  2. City, position 11 to 20;
  3. Country, position 21 to 30;
  4. Date of birth, position 31 to 38.

Let’s see how this works:1. First create your Open Hub. Do not select a template. We don’t need this.

2. Choose Destination Type File and enter the file directory in which you want the file to be exported. For this example I just use a local directory. In a production scenario you should use the Application Server option since you then can export the file to an application server and use logical file names. Also it is not possible to use the DTP between your source object and the Open Hub in a Process Chain if you use a local directory.

3. Go to the Field Def. tab. Here you enter a name for your field (i.e. FIELD), use Type STRG and 38 for Length. Now Activate the Open Hub.

4. Create a transformation from 0ZPERSON to the Open Hub ZFIXED. Connect the InfoObjects you want to use from 0PERSON to ZFIXED. I connected 0PERSON, 0CITY, 0COUNTRY and 0DATEBIRTH.

5. Change the Rule Type into Routine. We need some simple ABAP coding to merge the 4 fields and set the correct starting positions for each field. Scroll down until you see “RESULT = .” and add the code below.

    DATA: lv_char TYPE c LENGTH 38.

lv_char(10) = SOURCE_FIELDS-PERSON.
lv_char+10(10) = SOURCE_FIELDS-CITY.
lv_char+20(10) = SOURCE_FIELDS-COUNTRY.
lv_char+30(8) = SOURCE_FIELDS-DATEBIRTH.

RESULT = lv_char.

 

6. Activate the transformation, create a DTP and Execute it!

7. You can now open the file in Notepad.exe. It should look something like this:

HackingSAP.com - Mar 28, 2012 | Knowledge sharing, SAP BW
Tagged | , ,

10 comments

  1. Ashalatha
    March 29, 2012

    Its very good and useful documents

    Thanks

    Reply
  2. Thomas
    April 2, 2012

    The fix length ASCII should be possible with new BW release 7.30. There you can choose between CSV and fix length ASCII.

    Thomas

    Reply
    • Xavier Hacking
      April 5, 2012

      Thanks Thomas, I added your comment to the post!

      Reply
  3. Mani
    May 30, 2012

    Very useful….
    ThanQ xavier

    Reply
  4. Dillip
    June 4, 2012

    Thnx bro..
    it z very useful for me.

    Reply
  5. choco from davao
    March 9, 2013

    What i can suggest instead of offsetting technique you can use

    CONCATENATE source_field-person
    source_field-city
    source_field-country
    source_field-datebirth
    INTO LV_STRING
    SEPARATED BY ‘,’
    RESPECTING BLANK.

    effect for a text file that is a comma delimited it will show the leading spaces on it.

    WERKS BUKRS

    QW 0001
    QWERT 0002

    Reply
  6. Pujari
    June 27, 2013

    .

    Reply
  7. nere
    July 2, 2013

    Hi Xavier,
    thats a really good tutorial.
    Nevertheless i have some questions.
    Is there any posibility to edit the data before creating a file?
    And i’d like to limit the number of records in consideration of requestID and/or posting date.

    Thanks in advance.

    Reply
    • Xavier Hacking
      July 2, 2013

      Hi Nere,

      You can use the BW transformation to adjust and enrich data, just like you would do in a transformation between two DSO’s.

      Reply
  8. Bernd
    August 20, 2013

    Xavier, Is there a blank line in notepad at the end of the file whith this method? When opening a text file from an open hub destination, we notice a blank line at the end of a file. This isn’t there in BW or Excel…..

    Reply

Leave a Reply to Dillip Cancel reply