How to handle newline within quotes in csv

[Former Member]

Hello experts,

I have an issue with reading a csv file which contains newline as a part of one of the fields (within double quotes).

I'm using the file content conversion in sender file adapter with the following

NameA.fieldNames = field1, field2, field3, field4

NameA.fieldSeparator = ,

NameA.processFieldNames = fromConfiguration

NameA.enclosureSign = "

NameA.enclosureSignEscape = ""

NameA.endSeparator = 'nl'

This doesn't work. The sender channel treats the newline within double quotes as a new record and is not reading the file correctly.

I though the NameA.enclosureSign and NameA.enclosureSignEscape parameters would do the trick and ignore text that's between the double quotes?

An I missing some Content conversion cofiguration here? Some parameter that I need to set?

Please help resolve this.

Thanks

Karthik

[phanikumar_akella]

Hi Karthik,

Can you paste few sample records here.

[Former Member]

For eg.

1a,1b,1c,1d

2a,2b,2c,2d

3a,"3b has line break

line break herre

and here....and here

some text here",3d

4a,4b,4c,4d

5a,5b,5c,5d

Here...the 3rd record, 2nd field has a line break in it within double quotes. The entire text within quotes is one field.

Hope this explains my scenario.

Thanks

Karthik

[Former Member]

Hi Karthik,

NameA.fieldNames = field1, field2, field3, field4

NameA.fieldSeparator = ,

NameA.processFieldNames = fromConfiguration

NameA.enclosureSign = " -> This will work

NameA.enclosureSignEscape = "" -> Remove this

NameA.endSeparator = 'nl'

Remove the entry of : NameA.enclosureSignEscape

Refer:

http://help.sap.com/saphelp_nw04/helpdata/en/2c/181077dd7d6b4ea6a8029b20bf7e55/frameset.htm

Regards,

Aravind

[Former Member]

Aravind,

I tried that parameter setting and it didn't work. PI is taking the newline as the new record. Strange, I thought it should work too.

Any other suggestions?

Thanks

Karthik

[Former Member]

I have tried lot of option in File content conversion parameters, but I am not able to do this.

I don't think that we can do this.

It looks like PI internally reading line by line and then looking at FCC parameter to convert to XML

[Former Member]

Chetan,

That sucks. I thought The encloseSign parameter will direct PI to ignore any text between the double quotes.

Looking for more suggestions. Anybody?

Thanks

Karthik

[Former Member]

Hi,

I have a scenario similar to this.

ClaimsRecords.fieldNames : CompanyName,Invoice,InvoiceDate....,Location Name,...

ClaimsRecords.fieldSeparator : ,

ClaimsRecords.enclosureSign : "

ClaimsRecords.endSeparator : 'nl'

The sample file will look like this:

XYZ,123,21122011,......,"Delhi

India",312,...

ABC,234,22122011,......,"Bangalore

India",432,....

The new line character comes inside the value of the LocationName field. But the above entries made in FCC will handle this kind of file correctly.

Recheck the FCC entries and even check if the file is coming in correct format.

Regards,

Aravind

[Former Member]

Aravind,

I have the exact same parameter settings and it doesn't work. I created a new csv file and manually entered records, with newline in one of the fields...didn't work.

Its treating the newline as a new record even though its within quotes.

Anything else I should try??

Thanks

Karthik

[Former Member]

Hi,

You can try writing a script to remove the line breaks in the value of the fields in a csv file.

And run the script using "run OS command before message processing" in the processing tab of communication channel.

Regards,

Aravind