CSV with use of delimiter occasionally within the data

[former_member540174]

We have an interesting interface.  The sending vendor system is using a comma to delimit their fields.  Unfortunately, they also have a name field buried within their data and yes you guessed it - the names "could" have a comma.  So it is a comma delimited file with an occasional extra comma.

Any guidance on how you would handle this situation?

[engswee]

Hi Diane

If the sender is sending a comma value as part of a field, then it should be enclosed in double quotes. If it does not, there is no way you can implement any logic to determine when a comma should be considered a delimiter and when it should be considered a value.

Refer item 6 of the specification for CSV regarding fields that contain commas.

RFC 4180 - Common Format and MIME Type for Comma-Separated Values (CSV) Files

Rgds

Eng Swee

[former_member186851]

Hello Diane,

The file creating program/system should take care of it while creating the file.

[anupam_ghosh2]

Hi Diane,

                Suddenly extra comma means extra field to FCC. This will result in error because the sender side XSD has a fixed structure and FCC would try to insert extra field value in existing structure resulting in error. From the screen shot, it is impossible for PI  to figure out whether it is a field name or field value. If you are using FCC then I would suggest you to request the sender not to send extra field than what was agreed upon.

Other way round is to use a custom adapter module in sender channel to remove the extra fields before FCC begins.

Another way is to remove the FCC and use a java mapping to create the target XSD directly from mapping after filtering the extra values.

Regards

Anupam