Invalid Character Removal In BW - Different Scenarios

[DebjitSingha]

Introduction:

Invalid characters in BW data feed is quite common. Some time BW extracted data consist of special characters (!@#$%^&*()+_|}{":;) etc. Some time business need to as part of text field so BW allow them by allowing special characters via TCode - RSKC.

Then there are another kind of invalid characters that may appear as # when displayed in BW screen, though their ascii values are entirely different than the special characters mentioned above. BW is unable to interpret the character and hence end up displaying it as # (even though it is not #). These character can become a part of transaction due to any reason. Say end user copy paste text/ comment from webpage and end up copying unsupported characters. Some time validation in the source system take care of correction, but not always.
Whatever may be the reason of invalid character entering into transaction, it becomes a pain for BW ETL activity.

Easiest way to address these issues, is via Transformation Field Routine. Write a piece of code to filter out unwanted characters. Though depending upon scenarios code selection may change.

This blog is trying to summaries couple of scenarios with sample code.

Scenarios:

Remove invalid character from date fields -

Sometime we receive invalid characters in date fields. Replace VALUE_DATE field with your source field.

************************************************************************

* Remove special characters using function module

************************************************************************





    IF SOURCE_FIELDS-VALUE_DATE IS NOT INITIAL.

      IF NOT SOURCE_FIELDS-VALUE_DATE CO '1234567890'.

        "means its got bad chars

        RESULT = ''.

      ELSE.

        CALL FUNCTION 'DATE_CHECK_PLAUSIBILITY'

          EXPORTING

            DATE                      = SOURCE_FIELDS-VALUE_DATE

          EXCEPTIONS

            PLAUSIBILITY_CHECK_FAILED = 1

            OTHERS                    = 2.

        IF SY-SUBRC <> 0.

          RESULT = ''.

        ELSE.

          RESULT = SOURCE_FIELDS-VALUE_DATE.

        ENDIF.

      ENDIF.

    ENDIF.

************************************************************************

*    End Of Code

************************************************************************

 

Remove invalid characters from text field-

Only allowing characters and symbols and converting result into upper case. Replace SGTXT with your source field.

************************************************************************

* Remove special characters from text fields

************************************************************************



    DATA: T_ALL(100) TYPE C,

    T_VAR1(45) TYPE C VALUE

    'ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopq',

    T_VAR2(50) TYPE C VALUE 'rstuvwxyz 0123456789 @$%^&*()_-+=:;\/?',

    TEMP(60) TYPE C,

    CNT TYPE I,

    LENGTH TYPE I.



    CLEAR : CNT,TEMP,LENGTH.



    TEMP = SOURCE_FIELDS-SGTXT.

    CONCATENATE T_VAR1 T_VAR2 INTO T_ALL.

    CNT = 0.

    LENGTH = STRLEN( TEMP ).

    IF NOT TEMP CO T_ALL.

      WHILE CNT < LENGTH.

        IF NOT TEMP+CNT(1) CO T_ALL.

          CLEAR: TEMP+CNT(1).

        ENDIF.

        CNT = CNT + 1.

      ENDWHILE.

    ENDIF.

    TRANSLATE TEMP TO UPPER CASE.

    RESULT = TEMP.



************************************************************************

*    End Of Code

************************************************************************

 

Remove invalid character and allowing non-English language strings-

Sometime transaction fields consists of text and comment in different languages (end users are allowed to update comments in their native language(Spanish, Japanese, Chinese, French...). We are not suppose to remove non-English characters and above code would do just that.

Usual way of checking and comparing characters with set of alphabets and symbols will not work. Here instead of comparing individual character with allowed list, we need to find and replace only not allowed characters.

CL_ABAP_CHAR_UTILITIES is an ABAP Class for processing characters. Characters that have hexadecimal display between HEX00 and HEX1F are not permitted, in the scenario # falls between this range. So with the help of this ABAP Class we filter out invalid characters and leave non-English alphabets untouched.

Replace SGTXT in below code with your source field.

*****************************************************************

*       Remove invalid characters from non-English text

****************************************************************

DATA:T_VAR1(45) TYPE C VALUE '#!%^*=+~`?|\/→',

     TEMP(60) TYPE C,

     CNT TYPE I,

     LENGTH TYPE I.



    CLEAR : CNT,TEMP,LENGTH.



    TEMP = SOURCE_FIELDS-SGTXT.

    CNT = 0.

    LENGTH = STRLEN( TEMP ).

    IF NOT TEMP NA T_VAR1.

      WHILE CNT < LENGTH.

        IF NOT TEMP+CNT(1) NA T_VAR1.

          CLEAR: TEMP+CNT(1).

        ENDIF.

        CNT = CNT + 1.

      ENDWHILE.

    ENDIF.

    TRANSLATE TEMP TO UPPER CASE.



************************************************************************



REPLACE ALL OCCURRENCES OF cl_abap_char_utilities=>CR_LF IN TEMP With

space.

REPLACE ALL OCCURRENCES OF cl_abap_char_utilities=>CR_LF(1) IN TEMP With

space.

REPLACE ALL OCCURRENCES OF cl_abap_char_utilities=>CR_LF+1(1) IN TEMP

With space.

REPLACE ALL OCCURRENCES OF cl_abap_char_utilities=>horizontal_tab IN

TEMP With space.

REPLACE ALL OCCURRENCES OF cl_abap_char_utilities=>VERTICAL_TAB IN TEMP

With space.

REPLACE ALL OCCURRENCES OF cl_abap_char_utilities=>NEWLINE IN TEMP With

space.

REPLACE ALL OCCURRENCES OF cl_abap_char_utilities=>FORM_FEED IN TEMP

With space.

REPLACE ALL OCCURRENCES OF cl_abap_char_utilities=>BACKSPACE IN TEMP

With space.



RESULT = TEMP.





****************************************************************

*         End Of Code

****************************************************************

 

Remove invalid characters from key fields and take care of front and trailing spaces-

Sometime we end up with spaces in our key fields. Happens quite often during flat file loads.
Replace NAME1 field with your source field.

***********************************************************

* Remove invalid characters and spaces from source field

***********************************************************

 DATA: T_ALL(100) type C,

    T_VAR1(45) type C value

    'ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopq',

    T_VAR2(50) type C Value 'rstuvwxyz 0123456789 @$%^&*()_-+=:;\/?',

    temp(60) type c,

    cnt type i,

    length type i.

    clear : cnt,temp,length.

    temp = SOURCE_FIELDS-NAME1.

    CONCATENATE T_VAR1 T_VAR2 into T_ALL.

    cnt = 0.

    length = STRLEN( temp ).

    IF NOT temp CO T_ALL.

      WHILE cnt < length.

        IF NOT temp+cnt(1) CO T_ALL.

          CLEAR: temp+cnt(1).

        ENDIF.

        cnt = cnt + 1.

      ENDWHILE.

    ENDIF.

   condense temp.

   RESULT = temp.

****************************************************************

*        End Of Code

****************************************************************

 

Conclusion:

Invalid characters in BW extract is always remained pain area. Situation didn't changed from BW 3.x till date BW4HANA. We still see questions from BW consultants on how to fix these issue. These questions are expected in future as well, unless new version of BW provide something out of the box. Till then simple code like above will save the day..

 

References:

SAP Note 173241 – Allowed characters in the BW System;

SAP Note 1075403 – Unalloyed characters and ALL_CAPITAL;

SAP Note 1373402 – Issues with invalid characters or SID Generation;

SQLScript based error handling -  handling of invalid characters in AMDP Transformation routines

 

[Jörg_Brandeis]

Hi Debjit,

thank you for your article. I wrote a similar article about the handling of invalid characters in AMDP Transformation routines. The same topic in SQLScript.

Regards,
Jörg

[DebjitSingha]

Thanks for sharing your blogpost link. I add that in reference section of this blogpost. Hope that is ok 🙂

[Jörg_Brandeis]

Of course that is ok.

[DebjitSingha]

Done. thanks!

[VirajSaha]

Thanks Debjit for sharing this summary of codes.

[DebjitSingha]

you are welcome.