Comma problem in CSV file upload from application server

[Former Member]

HI,

I am uploading CSV format data from Application server using Split at ',' command .

For example my I/p data is like

123, 456, "Example1,Example2",987,"SAP,ABAP". That means whenever we had comma in a field then we will

put the value in Double quotes Here the problem is that whenever any valuu contains COMMA then the Split functionality is not working properly. Can anybody throw some light abt how to solve this?

Regards,

Kumar.

<MOVED BY MODERATOR TO THE CORRECT FORUM>

Edited by: Alvaro Tejada Galindo on Jul 8, 2009 11:00 AM

[RichHeilman]

Remove those commas from the file or use a different delimiter.

Regards,

Rich Heilman

[Former Member]

Hi Kumar,

try to first move the entire row in to one string.

try try to use the below command ..

Replace all occurances ',' in w_string with ' " '.

Regards,

Prabhudas

[former_member189420]

Hi,

Is it possible for you give sample code to understand better.

Thanks & Regards,

Anand Patil

[Former Member]

If we use REPLACE ALL OCCURENCES of ',' with ' " ' then this will not solve the problem because for our I/p File, Comma is the delimiter.So this will not work.

I am attaching the Code that I had written .

• Open the file in application server.

OPEN DATASET p_pfile FOR INPUT IN TEXT MODE ENCODING DEFAULT.

IF sy-subrc = 0.

DO.

• Read the input file data record-by-record

READ DATASET p_pfile INTO lv_csv_data.

IF sy-subrc = 0.

REPLACE ALL OCCURENCES OF ' " ' with Space in lv_csv_data.

Condense lv_csv_data.

*Split the data at ',' and populate all fields.

SPLIT lv_csv_data AT ',' INTO gw_input_data-hdate

gw_input_data-lname gw_input_data-mname

gw_input_data-fname gw_input_data-suffix

gw_input_data-addres gw_input_data-addr2

gw_input_data-bnum gw_input_data-union

gw_input_data-carallow.

• Append the data into the internal table.

APPEND gw_input_data TO gt_input_data.

Let us suppose we have to update the followingh file in Appl.server with the following data.

123, "Test1,test2", 789,"Example~of".

As per my code the reult in the string will be divided ino 6 values instead of 5 values due to Comma in the Value field.

May be wehave to read this character by charcter and need to Check exactly where this " will start and end.

[Former Member]

Is there is any alternative way of solving this prolem other than checking the file with "".

[Former Member]

Please see Rich's reply. Using a different delimiter is the simplest solution; otherwise, you will end up trying to parse the data after the split and trying to re-combine it.

Rob

[former_member189420]

>

> If we use REPLACE ALL OCCURENCES of ','  with ' " ' then this will not solve the problem because for our  I/p File, Comma is the delimiter.So this will not work.
> 
> I am attaching the Code that I had written . 
> 
> * Open the file in application server.
>     OPEN DATASET p_pfile FOR INPUT IN TEXT MODE ENCODING DEFAULT.
>     IF sy-subrc = 0.
>       DO.
> * Read the input file data record-by-record
>         READ DATASET p_pfile INTO lv_csv_data.
>         IF sy-subrc = 0.
>         REPLACE ALL OCCURENCES OF ' " ' with Space in lv_csv_data.
>         Condense lv_csv_data.
> *Split the  data at ',' and populate all fields.
>           SPLIT lv_csv_data AT ',' INTO gw_input_data-hdate
>           gw_input_data-lname     gw_input_data-mname
>           gw_input_data-fname     gw_input_data-suffix
>           gw_input_data-addres    gw_input_data-addr2
>           gw_input_data-bnum      gw_input_data-union
>           gw_input_data-carallow.
> 
> *  Append the data into the internal table.
>           APPEND gw_input_data TO gt_input_data.
> 
> Let us suppose we have to update the followingh file  in Appl.server with the following data.
>     123, "Test1,test2", 789,"Example~of".
> 
> As per my code the reult in the string will  be divided ino 6 values instead of 5 values due to Comma in the Value field.
> May be wehave to read this character by charcter and need to Check exactly where this " will start and end.

Hi,

I understand that you can not change the delimiter of the input file. Are you sure of how many quotes will be there?

If yes, What you can do is:

1. Spilt the input file based on ".

2. Based on what you get after step 1. Change only those , with some character say ~ that is rarely used.

3. Concatenate the split into a string and split based on ,

4. Replace some character i.e. ~ (we had considered in step 2) by ,

After step 4 you will be having data split based only on the delimiter say ,

Hope this helps. Let me know.

Thanks & Regards,

Anand Patil

[Former Member]

You are correct. But the file is coming from Taleo and it is not possible to delimit the Comma seperator. We have to use the string parser. Can u please tell me how to solve this problem using string parseing.

[Former Member]

Hi,

check the class CL_ABAP_CHAR_UTILITIES in se24..

and see the attributes and check anyone will help you..

Prabhu

[former_member189420]

Hi,

Below code is to replace , within the double quotes by #. So that you can go about using your logic of split at ,(comma) .

wa_ifile is the work area after you read a record from the file using read dataset.

data: strlength type i,
         i type i,
        start.

strlength = strlen ( wa_ifile ).

do strlength times.

if wa_file+i(1) = '"' ----> double quotes

        if start is initial.

             start = 'X'.

        else.

             clear start.

         endif.

endif.

if not start is initial.

      if wa_file+i(1) = ','.    -----------> comma

           wa_file+i(1) = '#'.        -----> this is upto you to decide. , between the quotes will be replace by what type of character.

      endif.

endif.

add 1 to i.

enddo.

clear i.

Hope this helps.

Thanks & Regards,

Anand Patil

[Former Member]

Thanks  Anand...

Please let me know whether you got the points or not..

[former_member788380]

Thank you Bharath for asking and Anand for your input. I browsed extensively for this issue and I must say, this is the best solution so far. It helped me to implement the same. I am able to use it as it is, but, it is failing with the below error message at line wa_file+i(1) = '#'.  :

"At the write position, you cannot use offset and length specifications 

with fields of type "STRING" or "XSTRING"."

data: strlength type i,

         i type i,

        start.

strlength = strlen ( wa_ifile ).

do strlength times.

if wa_file+i(1) = '"' ----> double quotes

        if start is initial.

             start = 'X'.

        else.

             clear start.

         endif.

endif.

if not start is initial.

      if wa_file+i(1) = ','.    -----------> comma

           wa_file+i(1) = '#'.        -----> Faling here with the above error message

      endif.

endif.

add 1 to i.

enddo.

clear i.

Please help.

Thanks & Regards,

Pavan

[amarjit_yanglem]

Hi Guys,

Please have a look of my approach.
I am considering that only the fields with comma in its value is coming encapsulated with - " 
and we are not doing any REPLACE_OCCURENCES, Instead we are splitting all values with comma into internal tables and finding the the values containing - " from where we determine starting and ending of the field value.

This works for any number of fields that has comma in its values. But the only catch is the values with comma has to come encapsulated with  - ".