FCC for reading CSV file

[former_member223432]

Hi All,

Can you please help with the FCC parameters to read the below CSV format file?

"AAA,BBB,"1111,2222,3333", DDD, EEE, FFF"

No of fields : A,B,C,D, E,F and the line starts and ends with " and also in between we have fields with comma separated within double quote.

I gave the parameter

Record.enclosureSign "

Record.fieldSeparator ,

Record.endSeparator 'nl'

Record.fieldNames

but this is taking the complete row in the first field

and if i don,t mention this Record.enclosureSign " then this "1111,2222,3333" splitting into 3 fields.

Can you please advise how can ignore the " in the beginning and end of the line and also achieve non splitting of this field 1111,2222,3333"

Thanks,

[JaySchwendemann · Accepted solution]

I would make a bold statement and say the CSV is not (very) well formed, see https://datatracker.ietf.org/doc/html/rfc4180

If you can, try to persuade the provider of the CSV to not have double quotes at line start and line end but either have 'em on each field or only have it on field C ("1111,2222,3333")

If it is not possible to influence the CSV, you may want to consider the following options

1. dual scenario, where you first eleminate the leading / trailing quotes per line, e.g. via java mapping in the first scenario. Then in second scenario, do you FCC thing
2. assuming field C can contain a "flexible" ammount of values, like 1111, 2222, 3333 or 1111, 2222, 3333, 4444 you could go for "get each row" and then using e.g. UDFs in message mapping to split on value. But that will be a rather hard nut to crack

Cheers