How do I TRULY remove the CRLF (#) from the last field read in CSV dataset?

[former_member210148]

Good day, everyone!

PLEASE NOTE: I spent most of yesterday searching SDN and reading all kinds of threads on this topic. Yes, I know, there ARE other threads out there regarding this topic, but I spent all of yesterday afternoon trying every solution posted and nothing worked. So, to my knowledge, nobody has yet to post a definite solution to this problem.

I am reading a comma-delimited CSV file from our Application Server. It was originally in Microsoft Excel but saved as a CSV file. I open the file as follows:

OPEN DATASET p_fname FOR INPUT IN TEXT MODE ENCODING DEFAULT.

Here is my loop to read the entire file into an internal table, splitting it into individual fields:

READ DATASET p_fname INTO wa_unsplit.

WHILE sy-subrc EQ 0.

ADD 1 TO w_unsplit_tot.

SPLIT wa_unsplit AT w_comma INTO:

wa_split-massn

wa_split-massg

wa_split-curr

wa_split-persg

wa_split-pernr

wa_split-persid

wa_split-persk

wa_split-stat2

wa_split-fisc_year

wa_split-funds_center

wa_split-plans

wa_split-orgeh

wa_split-abkrs

wa_split-werks

wa_split-sem_posit

wa_split-ansal

wa_split-bsgrd

wa_split-adm_adj_amt

wa_split-hourly_rate.

APPEND wa_split TO it_split.

CLEAR: wa_unsplit,

wa_split.

READ DATASET p_fname INTO wa_unsplit.

ENDWHILE.

The problem is that the last field, wa_split-hourly_rate (defined as character length 17) gets a '#' appended to the end of it. This happens with each record, and it appears to be the CR/LF character (the value of it in hex is the same as cl_abap_char_utilities=>cr_lf).

I've tried everything previously recommended to strip this character from my hourly_rate field. I've tried another STRIP command. I've tried REPLACE and TRANSLATE and a whole slew of things. Despite all the threads that exist on SDN about this, I've yet to find something that truly works.

Certainly I can't be the first person reading a file from the Application Server and having this issue.

Please do NOT post links to solutions that DO NOT WORK! Full points will be awarded to anyone who can solve this challenge.

Thanks everyone!

[LucianoBentiveg]

You can split wa_split-hourly_rate using cl_abap_char_utilities=>cr_lf :

SPLIT wa_split-hourly_rate AT cl_abap_char_utilities=>cr_lf INTO: wa_split-hourly_rate aux.

Regards

[former_member210148]

I don't understand what you mean by "wa_split-hourly_rate aux". The "aux" part is particularly confusing. Can you clarify? Thanks!

[RichHeilman]

What he is saying is that you can take the field and split it again using the CR_LF value, this will leave the real value in the wa_split-hourly_rate and nothing in the AUX field, and will get rid of the CRLF(#).

Of course you will need to define the AUX field.

data: aux type string.

This may also work as well.

shift wa_split-hourly_rate right deleting trailing space.
shift wa_split-hourly_rate right deleting trailing cl_abap_char_utilities=>CR_LF.

Regards,

Rich Heilman

[former_member207763]

Thank you it was very helpful.

[naimesh_patel]

Hello,

if you are not on unicode system, then

CONSTANTS: con_cret TYPE x VALUE '0D'.

and if on unicode system

constants:

con_cret type c value cl_abap_char_utilities=>CR_LF.

then split your string.

regards,

Naimesh

[Former Member]

instead of wa_split-hourly_rate in the first SPLIT,

use a variable v_STRING.

then again split V_STRING at '#'.

SPLIT V_STRING AT '#' INTO wa_split-hourly_rate

V_temp1.

regards

srikanth

[Former Member]

Hi,

Check the no of comma separated columns (fields) that your dataset contains and the fields that you are splitting. they should match each of them. Your case may occur if you miss any one/more of them.

Raju

[athavanraja]

you just wanted to remove the # (CRLF) which is coming at the end of each record right?

why not do the following just before starting the split at xxx into fileds operation.

replace all occurrences of cl_abap_char_utilities=>cr_lf in wa_unsplit with ` ` .

Regards

Raja

[former_member210148]

Success! Peluka and Naimesh, I had to combine your two recommendations, and I finally got it to work!

Thanks for the clarification, Rich. I ended up calling my "aux" field "junk" for lack of a better word, but in the end you confirmed what I thought it was.

For anyone else out there who has struggled finding a successful answer to this challenge, here's what worked for me:

CONSTANTS: c_comma(1) TYPE c VALUE ',',

c_crlf(1) TYPE c VALUE cl_abap_char_utilities=>cr_lf.

DATA: w_junk TYPE string.

READ DATASET p_fname INTO wa_unsplit.

WHILE sy-subrc EQ 0.

ADD 1 TO w_unsplit_tot.

SPLIT wa_unsplit AT c_comma INTO:

wa_split-massn

wa_split-massg

wa_split-curr

wa_split-persg

wa_split-pernr

wa_split-persid

wa_split-persk

wa_split-stat2

wa_split-fisc_year

wa_split-funds_center

wa_split-plans

wa_split-orgeh

wa_split-abkrs

wa_split-werks

wa_split-sem_posit

wa_split-ansal

wa_split-bsgrd

wa_split-adm_adj_amt

wa_split-hourly_rate.

• And here is the line that removes the CR/LF character from the end of the Hourly Rate field!

SPLIT wa_split-hourly_rate AT c_crlf INTO:

wa_split-hourly_rate w_junk.

APPEND wa_split TO it_split.

CLEAR: wa_unsplit,

wa_split.

READ DATASET p_fname INTO wa_unsplit.

ENDWHILE.

Thanks so much, everyone! Once again, SDN folks save the day. Points awarded!

[Former Member]

Thanks that helped me too!!

[b1jdm]

OPEN DATASET p_fname FOR INPUT IN TEXT MODE ENCODING DEFAULT  with smart linefeed. 

depending of your abap version/release, opening the dataset in this manner should take care of the issue.

This should recognize both type types of line feeds  CR and CRLF

[former_member182550]

And I don't suppose Vic has made it across from the other place yet.....

[former_member233636]

I solved same problem only with option with smart linefeed.