CSV File with values having single quote within quote text qualifier

Question

I am trying to parse a CSV file which has single quote as text qualifier. The problem here is that some values with single quote text qualifier itself contains single quote e-g:

'Fri, 24 Feb 2017 17:44:57 +0700','th01ham000tthxs','/','','Writer's Tools Data','7.1.0.0',

I am struggling to parse the file as after this row, all of the remaining rows get displaced.

I tried working with OpenCSV, UnivocityParsers but didn't get any luck. If I place the above row in excel (Excel Image) and provide text qualifier as single quote, it give correct result without any displacement of rows.

if you got it working in excel sounds like you found a solution — Scott Holtzman, Jan 03 '19 at 14:59
But I dont know how excel handles this. I was thinking to achieve something like that from OpenCSV or UnivocityParsers as perhaps these libraries handle such sort of scenerios — Muhammad Salman, Jan 03 '19 at 19:32

score 0 · Answer 1 · answered Jan 03 '19 at 22:32

If using java, the JRecord library should handle the File.

How it works: if a field starts with a quote (e.g. ,') specifically look for ', or ''', or ''''', or ' etc (an odd number of quotes followed by either a comma or end-of-line marker). This approach breaks down if:

The embedded quote is the last character in a field i.e. 'Field with quote '',
White space between the quote and comma i.e. 'Field' , or , '

Here is the line in ReCsvEditor

Also in the ReCsvEditor when editing the file, if you select Generate >>> Java Code >>> ... it will generate Java/JRecord Code to read the file.

Disclaimer: I am the author of JRecord / ReCvEditor. Also the ReCsvEditor Generate function is new and needs more work

score 0 · Answer 2 · answered Jan 08 '19 at 13:46

Try configuring univocity-parsers to handle the unescaped quote according to your scenario. 'Writer's Tools Data' has an unescaped quote. From your input, I can see you want to use STOP_AT_CLOSING_QUOTE as the strategy to work around these values.

Add this line to your code and it should work fine:

parserSettings.setUnescapedQuoteHandling(UnescapedQuoteHandling.STOP_AT_CLOSING_QUOTE);

Hope this helps.

CSV File with values having single quote within quote text qualifier

2 Answers2