Flat File Reader Error - Bad Quote Format?

[hneff1]

Hi. I am using the flat file reader component to read a pipe delimited text file. I have a attached a screen shot of my settings. For the record shown below I am getting a bad quote format error.

|Parsing error: Bad quote format in record 32, field 64 (“_64”), metadata “I_ConcurFileData_220”; value: ‘DETAIL|2883|2017-07-10|32|00055280|Houglan|Catherine|A|XY-Corp-US|901476|XY-Corp-US||||||||5B0E28901338406D9A2A|433491|XYZ US- JD|USD|UNITED STATES|2017-06-26|2017-05-31|2017-07-10|May 9-25, 2017 Expenses|Biomet Policy|||||ZB-Corp-US|901476||||||||||||||||||||||5093226|CHD|Employee Meals (Lunch)|2017-05-10|USD|1.00|M|N|“Coaching in the Moment” Workshop/Lunch||HONEYBAKED HAM #8402|6|800035|ZB-Corp-US|||||||||||||||||||||||||||||||||||||||US||||0.00|74.81|74.81|74.81|74.81||1343888|EMP76123|Employee|EMPONE|Smith|Doug|Xyz|Design Sr Engineer I|0.00|0.00|USD|||||||||||||||||||||12.47|12.47|12.47|1|N||||||||US|US-OH|Canton|||||||||||||||||||||||||||||||||||||||||||||||||||||||||\r\n’|/mnt/efs/ftp-data/xyz/inbound/extract_attendee_detail_20170710162534.txt

I was reading the clover documentation at: http://doc.cloveretl.com/documentation/ … -file.html says the following:
"Alternatively, use the Quote char combo box to select which kind of quotation marks should be removed from string fields. Do not forget to click Reparse after you select one of the options: " or ’ or Both " and '. Quotation marks have to form a pair and selecting one kind of Quote char results in ignoring the other one (e.g. if you select " then they will be removed from each field while all ’ characters are treated as common strings). If you need to retain the actual quote character in the field, it has to be escaped, e.g. “” - this will be extracted as a single ". Delimiters (selected in Delimiter) surrounded by quotes are ignored. What is more, you can enter your own delimiter into the combo box as a single character, e.g. the pipe - type only | (no quotes around).

Examples:
“person” - will be extracted as person (Quote char set to " or Both " and ')
“address"1 - will not be extracted and the field will show an error; the reason is the delimiter is expected right after the quotes (“address”; would be fine with ; as the delimiter)
first"Name” - will be extracted as first"Name" - if there is no quotation mark at the beginning of the field, the whole field is regarded as a common string
“‘doubleQuotes’” (Quote char set to " or Both " and ') - will be extracted as ‘doubleQuotes’ as only the outer quotation marks are always removed and the rest of the field is left untouched
“unpaired - will not be extracted as quotation marks have be in pair; this would be an error
‘delimiter;’ (with Quote char set to ’ or Both " and ’ and Delimiter set to - will be extracted as delimiter; as the delimiter inside quotation marks is ignored”

I have my quote char setting set to " so like the first"Name" example above which would be similar to “Coaching in the Moment” data in column 64 in my record. With this setting, I do not understand why I am getting an error. I am also attaching my meta data format below:

Thanks for any info you can provide.

Heather

• DataReaderSettings.jpeg

[Vladimir_Barton]

Hi Heather,
thank you for the detailed explanation of the issue. When it comes to matching your example with the examples in our documentation, however, I beg to differ. The string that causes the parsing error in your graph (“Coaching in the Moment” Workshop/Lunch) looks a lot like the “address"1 example from our documentation (as opposed to the first"Name” example). The reason for this is that the pipe (|) delimiter is not placed right after the quotes (for example “Coaching in the Moment Workshop/Lunch” would flow through the graph nicely) so the parsing error is actually justified here.
From looking at your screenshot, it appears that you have the Data Policy property of the FlatFileReader set to ‘Controlled’. Therefore, I would suggest taking advantage of the second port and design an alternative thread that would deal with bad parsing error records. You could use the Reformat component for the purpose of removing the quotes from the original record, for example by applying the Replace function in the following fashion:

function integer transform() {
	$out.0.field1 = replace($in.0.originalData,'"',"");

	return ALL;
}

Regards,

[hneff1]

Vladimir,

Thanks for your help! I took advantage of second port to fix problem records containing quotes. Then read those records again to either put the fixed records back into the processing stream or send email with any additional errors. Worked great!

Heather