I'm not aware of any tool that does that, but it isn't hard to find the problematic lines, as these will have more delimiters in them than the number of columns. All other lines are trivially decodable and pose no problem, as the quotes are unnecessary there.

For the problematic lines, I would parse the complete line and aim for ," to start a quoted string and ", to end said string. I would also log all these problematic lines for later human inspection. You could also consider to ask a human about these problematic lines instead of spilling one interpretation into the DB.

Corion,

I think the suggestion to focus on the "problem lines" is a good one. If I have to pre-parse the file by hand, I think I'll do that.

Mark Stosberg

user@machine$ perl -pe 's/(?<=.)(?<!,)"(?!,|$)/\\"/g' 
"call from "friend"","call from "friend"","call from "friend""
"call from \"friend\"","call from \"friend\"","call from \"friend\""
[download]

Looks good for me. Some special cases with escaped commas inside the strings might not match correctly. You should check this.

mscharrer,

I didn't mean for anyone else to do my work for me, but I certainly don't mind the help! Thank you! That's the kind of code I would need to write if the new release of Text::CSV_XS turns out not to work for some reason.

Mark Stosberg

Are you by any chance working together with Michael Fowler? He asks almost the same thing in this RT entry.

See my comments there.

I am not. I'm working with an export of an older version of the "TimeMatters" software.

Regarding what Text::CSV_XS can do, in summary are you saying that one of the "allow_loose_quotes" or "allow_loose_escapes" option can or will be able to handle this?

That would certainly be the easiest path for me. Thanks so much for the fast and helpful response!

Mark Stosberg

Yes. You'd have to play with allow_loose_quotes, allow_loose_escapes and/or setting escape_char to undef to find your best combination. The upcoming 0.43 has a new util examples/csv-check, which will tell you line number and position in-line of where the first failure happens


Enjoy, Have FUN! H.Merijn

OK, I nailed it. C<allow_loose_quotes> now also works inside quoted fields, under the condition that the C<escape_char> is not equal to the C<quote_char>. Code like that is very unlikely to have some valid escape sequence anyway. From 0.43 onward, you can parse those lines with

my $csv = Text::CSV_XS->new ({
    binary             => 1,
    allow_loose_quotes => 1,
    escape_char        => undef,
    });
[download]

Code speaks loader than words, and I got tired of explaining over and over and over again why bad CSV is bad and how to 'try to' parse invalid data-streams anyway, so I now have included a script that parses CSV streams, and reports lines that are wrong and then re-parses that line in `allow-mode'.

I'll release shortly.

Why not try Text::xSV ?

... then you don't have to bother about such things.

From experience, the only thing that can't handle is a CSV export by Excel where a field is extraordinarily large (such that the field is split ... by Excel) ?

There might be several reasons for choosing the right   module. See here for a more thorrough discussion of the differences between the available CSV parsing modules.

Note that this initial post is somewhat outdated, and some of the +/-'s might have changed. e.g. Text::CSV_XS not can deal very well with NULL (undef) values.


Enjoy, Have FUN! H.Merijn