String _to_Solix =>Email send

[Former Member]

Hi guys,

I am creating an excel file as attachment with the email,but the values(separators i,e ',and .') tend to change depending on client settings.

SO i need to convert the numbers in excel to string after the file is being created so that the value remains same on all systems.

In excel there is a functionality to add apostrophe'

and then the number converts to a string.

Can we achieve this via ABAP coding?

I tried to concatenate apostrophe' with my rates but it does not work as the value is not converted and the ' appears with the numbers in the excel file.

Example: 1.02125 is noted as '1.02125.

Attaching the code snippet for the above code:

* Variables Declarations

   TYPES: BEGIN OF ty_me_mail,

             rate_type     TYPE kurst_curr,

             from_curr     TYPE fcurr_curr,

             to_currncy    TYPE tcurr_curr,

             valid_from    TYPE gdatu_cur,

             exch_rate     TYPE p LENGTH 16 DECIMALS 5,"string,

             from_factor   TYPE ffact_curr,

             to_factor     TYPE tfact_curr,

             exch_rate_v   TYPE p LENGTH 16 DECIMALS 5,"string,

             from_factor_v TYPE ffact_curr,

             to_factor_v   TYPE tfact_curr,

           END OF ty_me_mail.

    DATA: gv_str1        TYPE char4,

               gv_str2             TYPE string,

               gv_str3             TYPE string,

               gv_str4             TYPE string,

               gv_str5             TYPE string,

               gv_str6             TYPE string,

               gv_str7             TYPE string,

               gv_str8             TYPE string,

               gv_me_mail      TYPE string,

               gt_me_mail       TYPE STANDARD TABLE OF ty_me_mail.

IF NOT gt_me_mail IS INITIAL.

      CONCATENATE 'Rate Type' gc_tab 'From-Currency' gc_tab 'To-Currency'

      gc_tab 'Direct Exchage Rate' gc_tab 'Indirect Exchange Rate'

      gc_tab 'Valid From' gc_tab 'From-Factor' gc_tab 'To-Factor' gc_crlf

        INTO gv_me_mail.

      LOOP AT gt_me_mail INTO gw_me_mail.

        gv_str1 = gw_me_mail-rate_type.

        gv_str2 = gw_me_mail-from_curr.

        gv_str3 = gw_me_mail-to_currncy.

        IF NOT gw_me_mail-exch_rate IS INITIAL.

          gv_str4 = gw_me_mail-exch_rate.

        ELSE.

          gv_str8 = gw_me_mail-exch_rate_v.

        ENDIF.

        gv_str5 = gw_me_mail-valid_from.

        IF NOT gw_me_mail-from_factor IS INITIAL.

          gv_str6 = gw_me_mail-from_factor.

        ELSE.

          gv_str6 = gw_me_mail-from_factor_v.

        ENDIF.

        IF NOT gw_me_mail-to_factor IS INITIAL.

          gv_str7 = gw_me_mail-to_factor.

        ELSE.

          gv_str7 = gw_me_mail-to_factor_v.

        ENDIF.

        CONCATENATE gv_me_mail

                     gv_str1 gc_tab

                     gv_str2 gc_tab

                     gv_str3 gc_tab

                     gv_str4 gc_tab

                     gv_str8 gc_tab

                     gv_str5 gc_tab

                     gv_str6 gc_tab

                     gv_str7 gc_crlf

                     INTO gv_me_mail.

        CLEAR: gv_str1, gv_str2, gv_str3, gv_str3, gv_str4, gv_str5,

               gv_str6 ,gv_str7,gw_me_mail,gv_str8.

      ENDLOOP.

    ENDIF.

* Attachment for  E-mail

    IF NOT gv_me_mail IS INITIAL.

      TRY.

          cl_bcs_convert=>string_to_solix(

            EXPORTING

              iv_string   = gv_me_mail

              iv_codepage = gc_4103

              iv_add_bom  = gc_x

            IMPORTING

              et_solix  = gt_binary4

              ev_size   = gv_size1 ).

        CATCH cx_bcs.

          MESSAGE e445(so).

      ENDTRY.

    ENDIF

Thanks & Regards in Advance.

Sunit Kelkar

[Sandra_Rossi]

You may also try with a text as a formula: =314159/100000 for getting 3.14159 or 3,14159 depending on client setting.

[Former Member]

Hi Sandra,

Thanks for the quick reply, but these are the foreign exchange rates that are being uploaded everyday, so the values are dynamic and have to resemble the same as they are in the file that is being uploaded .

All my values are uploaded but the problem persists with the number to string conversion in excel file.

Example : If some exchange rate is 1.1356 it shows up like 11.356 which is a huge difference or it also maybe 0.11356.

Regards

Sunit Kelkar

[Sandra_Rossi]

I think I don't understand your question. Is it that you don't know how to add an apostrophe in front of a number, in your variables GV_STR*?

[Former Member]

Hi Sandra,

I have already concatenated apostrophe in the string gv_str, but when the value is displayed in the excel file it is denoted as '1.0987(exchange rate).

In excel if we add an apostrophe in front of a number it converts it to a string automatically which i want to achieve via abap coding which in turn will help me to keep all the value static irrespective of any settings.

Thanks and Regards

Sunit Kelkar

[Sandra_Rossi]

Did you try =10987/10000 instead of '1.0987 ?

It's a trick for being valid whatever the regional settings of Windows are.

There are some screenshots in this wiki : https://wiki.scn.sap.com/wiki/display/ABAP/CSV+tests+of+encoding+and+column+separator

[Former Member]

Hi sandra,

I did not follow what you meant by Did you try =10987/10000 instead of '1.0987 ?

The decimal places for all values may differ and I need the exact values coming from the file for processing,

These exchange rate values are coming from SAP PI system into the production system via abap proxy and updates the TCURR table.

The code snippet above is the code from the abap proxy implementing class which gets triggered automatically when the SAP PI system triggers the file.

Regards

Sunit Kelkar

[Sandra_Rossi]

I mean, in ABAP, try:

wv_str1 = '=10987/10000'.

Instead of:

wv_str1 = '''1.0987'.

Of course, you'll have to adapt it to your code.

[Sandra_Rossi]

By the way, did you get a chance to have a look at the wiki, it shows that "=15522/100" is displayed (when the CSV file is saved with character encoding UTF-16LE with BOM, and with tab as delimitation character) as "155,22" for French systems, and as "155.22" for English systems.

[alok_patra]

Hi Sunil,

I have not tried any of the below options, it might not work at all but you can give it a try:

1. Before assigning exchange rate to string variable, do another assignment to a char variable. Then assign character variable to string.

2. In the character variable, concatenate a space to the exchange rate before assigning to string.

[Former Member]

Hi Sunit,

I have also faced the same problem, and I have resolved it by converting number in String in ABAP code itself.

Use Function Module 'HRCM_AMOUNT_TO_STRING_CONVERT' to convert number to string.

Sample code : 

CALL FUNCTION 'HRCM_AMOUNT_TO_STRING_CONVERT'

      EXPORTING      

           betrg  = ld_number

      IMPORTING       

         string = ld_string.

Then you can concatenate this ld_string in your final string.

Hope this helps.

[Former Member]

Hi Hemika,

My values are already in string format as I have I have declared the variables as string type.

DATA:   gv_str1             TYPE char4,

               gv_str2             TYPE string,

               gv_str3             TYPE string,

               gv_str4             TYPE string,

               gv_str5             TYPE string,

               gv_str6             TYPE string,

               gv_str7             TYPE string,

               gv_str8             TYPE string,

               gv_me_mail      TYPE string.

These are the variables where my final values are getting populated.

Regards & Thanks

Sunit Kelkar

[Former Member]

Hi,

For comma there is one solution, remove all commas and then pass it in excel it will be treated as a string and number will be proper.

I don't find any solution for decimals but for comma you can try this out..