CSV File with Comma in Quotes

[Former Member]

Hi,

I am having a pretty common problem but the recommended solution doesn't seem to work. We are using SAP PI 7.1 EHP1

We have a CSV file that has one field with a comma in the field. The field has quotes around it like this "address, name"

I have searched and I should be able to use:

Header.fieldSeparator = ,

Header.endSeparator = \r\n

Header.enclosureSign = "

Header.enclosureSignEscape = ""

Header.enclosureConversion = YES

and this should resolve my issue.

I have tried this and it is not working. In FCC the comma in the field is still been seen as a field separator.

Has anyone else tried this and it hasn't worked for them? Am I doing something incorrect?

Thanks,
Gareth

[ambrish_mishra]

Hi Gareth,

IMO, the config done is correct.

I wanted to suggest that after you have entered the values, press enter to make sure the values are reflected. Sometimes the editor needs a kick.

I am sure you must be knowing this but just making sure

Hope it helps!

Ambrish

[Former Member]

Hi Ambrish,

I have just tried pressing enter and it hasn't worked.

Gareth

[Former Member]

Hello,

I hope you have used double quotes (instead of typing 2 single quotes)?

and use only these parameters:

Header.fieldSeparator = ,

Header.endSeparator = \r\n

Header.enclosureSign = "

BTW, what's the error u r getting?

Thanks

Amit Srivastava

[Former Member]

Hi.

I tried your suggestion and still the same.

I am not getting an error, the data in the field in question is being split because FCC sees the comma as a field separator.

[ambrish_mishra]

Gareth,

please share the data type, FCC screenshot and one row of data.

thanks

Ambrish

[Former Member]

Data type:

FCC:

And sample data.

Client Name,Debt Account Number,Amount,MABS Office Name,Date,Creditor ID,Creditor Name,Creditor Account Number,EFT_ID,Payment ID

"Name","<1234>","80.00","address","03-JUL-13","246462","company","81900087","3782189","3768482"

"Name 2","<5433>","3.75","address 2,","03-JUL-13","246462","different company","81900087","3782189","3763751"

The sample data is a little confusing here but it is basically Comma separated with double quotes around each value.

[Former Member]

Should have said, on the last row of the sample data there is a field with "address 2,"

Its the comma in that field that is causing the problem.

[ambrish_mishra]

Hi Gareth,

Did not see the 2 statements in FCC. Am I missing something ?

enclosureSignEscape = ""

enclosureConversion = YES

Ambrish

[Former Member]

I removed those two lines at Amit's suggestion.

With or without those lines the same problem happens.

[Former Member]

Hello,

Below set of parameters are working fine for me:

Header.fieldSeparator = ,

Header.endSeparator = \r\n

Header.enclosureSign = "

Thanks

Amit Srivastava

[Former Member]

This is hardly a Service Pack level issue? I think we are on SP7...

[Former Member]

So I think i have figured out why this is not working for me, its not a solution but seems to be a problem with SAP PI.

I am using a KeyField to differentiate between Header and Detail. It looks like if the comma is in a field before the Keyfield in the record then PI cannot find the Keyfield. But if the comma is in a field after the keyfield it work.

I have tested this with a couple of values and can reproduce it.

[ambrish_mishra]

Hi Gareth,

This is interesting!

However there is a workaround.

Do you actually need to read the first line? It just seems like column names.

If you can avoid reading the header, then I suggest you use document offset as 1 to start reading from details. specify the structure as detail,*. don't give any key field name or key field value and use the enclosuresign parameters to read the content.

hope it works!

Ambrish