Correct not escaped double quotes in double quoted cells in CSV file

mcgint · Apr 20, 2009#12009-04-20T22:22+00:00

Hi, all. I have a csv file that's choking my db manager, mostly I believe because it's comma-delimited with double quotes for text qualifiers and scattered records have double quotes within the data. For example:

2000,"Thomas "Tom" Jefferson","etc."

Is there a way to search with regular expressions for text between two commas that has more than three or four quotation marks? (The trick, I guess, is to exclude commas and line ending characters from your wildcard search, but I can't figure out how to say everything but commas.)

Thanks in advance.

Tom

P.S. Why does ANYONE store data in comma-delimited format? Argh.

Mofi · Apr 21, 2009#22009-04-21T08:07+00:00

A double quote inside a double quoted text must be escaped with a double quote. For details see Wikipedia - Comma-separated values.

The following macro replaces with several replace all commands all double quote characters at start or end of a cell / value with {QuOtE}. Then it corrects the remaining single double quotes by duplicating every occurrence. Last it converts {QuOtE} back to a double quote character. Now your DB manager should have no problem to import the data in the CSV file.

The macro property Continue if search string not found must be checked for this macro.

InsertMode
ColumnModeOff
HexOff
UnixReOff
Top
Find RegExp "%""
Replace All "{QuOtE}"
Find RegExp ""$"
Replace All "{QuOtE}"
Find ",""
Replace All ",{QuOtE}"
Find "","
Replace All "{QuOtE},"
Find """
Replace All """"
Find MatchCase "{QuOtE}"
Replace All """

The result of this macro for your example is:

2000,"Thomas ""Tom"" Jefferson","etc."

mcgint · Apr 21, 2009#32009-04-21T15:20+00:00

That worked great. Thanks a lot for the help.