Flat File CSV parsing with comma in the field

, ,
1

Hi all,

I’m having a problem with csv files. I’ve got the ffs/ffd setup and it works fine until it encounters a comma in a field (i.e. “,“word, word”,”). I can’t test for fields without quotes as there are only quotes on fields that contain text. Numeric fields do not have quotes.

Any advice would be greatly appreciated.

2

Can you provide the real record with which you’ve encountered trouble? Also, can you share your schema definition? I assume you’ve specified a quoted release character in your schema.

There are a few rules that need to be followed when creating a flat file. One reference that describes an approach is at [url]http://tools.ietf.org/html/rfc4180[/url]

Numeric fields that contain a delimiter must be quoted. The quotes are not considered to be part of the data and will be stripped by the parser.

Here are rules that I’ve used that work with the FF parser in IS:

  1. Records are terminated by a record delimiter, including the last record.
  2. Each record contains one or more fields separated by a field delimiter.
  3. Empty trailing fields may be omitted (e.g. no need for ,)
  4. Each field may be enclosed with a quoted release character, typically ". These are not considered part of the data.
  5. If data within a field contains a field or record delimiter the field must be enclosed in quoted release characters.
  6. A quoted release character within a field must be preceded by another quoted release character. E.g. “aaa”|“b”“bbb”|"ccc"CRLF

HTH

3

DOH! I wasn’t using the quoted identifier.