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

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

9
NewbieNewbie
9

    Apr 20, 2009#1

    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.

    6,603548
    Grand MasterGrand Master
    6,603548

      Apr 21, 2009#2

      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."
      Best regards from an UC/UE/UES for Windows user from Austria

      9
      NewbieNewbie
      9

        Apr 21, 2009#3

        That worked great. Thanks a lot for the help.