Replace #(hex value) with space

[ramesh_putta]

Hello,

I have a program which uploads the text from excel.

In excel, if one cell have multiple lines (separated by Alt + enter) , the program separates them with '#' .

I know the '#' is a hexadecimal value, but how could i replace this value with space ?

when i checked the hex value of '#' in debugger, it has the length of more than 2000.

Please suggest how i can replace '#' with space.

Thanks for your help.

[Former Member]

Hi Ramesh,

Try the following code.

data: IN_TEXT type string.
 
data: lv_lf type c value CL_ABAP_CHAR_UTILITIES=>CR_LF.
 
replace all occurrences of lv_lf in IN_TEXT with space.

Regards,

Kinshuk

[ramesh_putta]

Kinshuk,

Thanks for your response.

I tried your solution before, but it didnt work.

Here , the value of CL_ABAP_CHAR_UTILITIES=>CR_LF will be '0D00', but the value of '#' which iam getting from excel is not ''0D00'.

[Former Member]

Hi,

Try this.

data: IN_TEXT type string. 
data: lv_lf type c value cl_abap_char_utilities=>HORIZONTAL_TAB. 
replace all occurrences of lv_lf in IN_TEXT with space.

If that doesnt work try with

data: lv_lf type c value cl_abap_char_utilities=>VERTICAL_TAB

or

data: lv_lf type c value cl_abap_char_utilities=>NEWLINE

[Former Member]

btw. CL_ABAP_CHAR_UTILITIES=>CR_LF corresponds to 0D00 0A00 (Carriage Return + Line Feed) .

If you only want the Carriage Return you can use CL_ABAP_CHAR_UTILITIES=>CR_LF+0(1) which yields 0D00

[Former Member]

Hi Ramesh,

If you are using GUI_UPLOAD to load the excel file, use the "REPLACEMENT" parameter in the IMPORT parameter, update this parameter to use space, by defalut it is '#'.

Note: If you are using any other method to upload the file let me know.

Regards,

Chen

[Former Member]

Hi Ramesh,

"REPLACEMENT" in GUI_UPLOAD won't work, for "#" is a placeholder for several not printable characters in SAP. The attributes in CL_ABAP_CHAR_UTILITIES should be the better way, if you can find one!

Regards,

Klaus

[Former Member]

Hi Klaus,

Hope you can help me understand this better, the reason why Ramesh has '#' in place of 'ALT+ENTER' in the ITAB is because (i guess) 'GUI_UPLOAD' was not able find a printable character for the same. Now if he tries to "FIND" the same using CR_LF, how would it find the same?

Ramesh - I see that CL_ABAP_CHAR_UTILITIES=>CR_LF is a two character field, may you can try CR and LF separately as given below, as i am not sure on if 'ALT+ENTER' is translated/converted to CR or LF.

CR

replace all occurrences of CL_ABAP_CHAR_UTILITIES=>CR_LF(1) in 'your string' with space.

LF

replace all occurrences of CL_ABAP_CHAR_UTILITIES=>CR_LF+1(1) in 'your string' with space.

Regards,

Chen

[Chella82]

Thanks 

replace all occurrences of CL_ABAP_CHAR_UTILITIES=>CR_LF(1) in 'your string' with space.

This worked for me 🙂

[SuhaSaha]

Hello Ramesh,

I am using MS Office 2007 excel & have a cell has multiple lines (created by Alt+Enter). When i check the HEX contents of the cell it shows a line feed(LF) ('0A').

I use the following statement which replaces the '#' with space:

REPLACE ALL OCCURRENCES OF cl_abap_char_utilities=>newline
      IN wa_data WITH ` `

Hope this helps.

BR,

Suhas

[ramesh_putta]

Thanks guys..cl_abap_char_utilities=>NEWLINE did the trick. Appriciate your help.

[ramesh_putta]

Guys,

I had to re-open this thread as i have similar problem again.

Now Iam working on a program which downloads material texts to excel.

And Iam using CL_ABAP_CHAR_UTILITIES=>NEWLINE, in order to split the text lines into multiple lines within the same excel Cell.

To do this, using only CL_ABAP_CHAR_UTILITIES=>NEWLINE is not enough, I had to put the whole text between double quotes.

For example ... to download 'line1' 'line2' into same excel cell..

Concatenate 'line1' 'line2' into v_text separated by CL_ABAP_CHAR_UTILITIES=>NEWLINE .

concatenate ' " ' v_text ' " ' into v_text.

This works fine. But when the text itself has the double quotes in it, then the text jumping to the next cell.

And i have lot of material texts which have double quotes in their text itself..

Any ideas to solve this ?

Edited by: Ramesh Putta on Apr 11, 2011 3:57 PM

[ramesh_putta]

Similar problem again

[former_member205174]

Try this 2 lines for the material text field

REPLACE ALL OCCURRENCES OF `'` in material_text with '`'.

REPLACE ALL OCCURRENCES OF '"' in material_text  with `""`.

Please refer the sample report below

*&---------------------------------------------------------------------*
*& Report  ZCB_TEST
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT ZCB_TEST.

data: it TYPE TABLE OF string,
      wa TYPE string,
      seperator TYPE string VALUE cl_abap_char_utilities=>newline.

DO 5 TIMES.
CONCATENATE 'line1' seperator 'l"ine2' seperator 'line3' INTO wa.
REPLACE ALL OCCURRENCES OF `'` in wa with '`'.
REPLACE ALL OCCURRENCES OF '"' in wa with `""`.
CONCATENATE '"' wa '"' INTO wa.
APPEND wa to it.
ENDDO.

CALL FUNCTION 'GUI_DOWNLOAD'
  EXPORTING
    FILENAME                        = 'C:\test.csv'
   FILETYPE                        = 'DAT'
  TABLES
    DATA_TAB                        = it
          .
IF SY-SUBRC <> 0.
* Implement suitable error handling here
ENDIF.

[navdeep_thakur12]

Hi

check in debugging

if hex value for # is 000D

then use:

REPLACE ALL OCCURANCE OF cl_abap_char_utilities=>cr_lf(1) IN string WITH space.

if hex value is 000A

then use:

REPLACE ALL OCCURANCE OF cl_abap_char_utilities=>cr_lf+1(1) IN string WITH space.

similary you can check for HORIZONTAL_TAB, VERTICAL_TAB etc.

hope this help

Regards