TCL Regex to match unescaped quotes in CSV

user_12241229 0
Viewed 253

i've been through several Q&A here to address this problem with a CSV, i have.

Here's an example of a CSV with unescaped quotes at 5"-

"522","-1","12345678","12345678","Completed","","","","height 5' 5" and weight 170lbs","","","9876543","ABCD","2016-06-12T23:54:00-05:00","2016-06-12T23:59:00-05:00"

I've already used this suggestion from here : Match unescaped quotes in quoted csv and (?<!^|",)"(?!,|$) regex works beautifully, but in the PHP context.

My goal is to match a single double quote (") that's being quoted between double-quotes (") but in the TCL context. Any suggestions? Many thanks!

tcl csv quotes regex
2 Answers

Since the question you link to is about removing the "wild" double quotation marks inside CSV fields, all you need to do is to revamp the lookbehind into a capturing group with a negated bracket expression:

set a {"522","-1","12345678","12345678","Completed","","","","height 5' 5" and weight 170lbs","","","9876543","ABCD","2016-06-12T23:54:00-05:00","2016-06-12T23:59:00-05:00"}
set result [regsub -all {([^,])"(?=[^,])} $a "\\1"]
puts $result

See the online Tcl demo, output:

"522","-1","12345678","12345678","Completed","","","","height 5' 5 and weight 170lbs","","","9876543","ABCD","2016-06-12T23:54:00-05:00","2016-06-12T23:59:00-05:00"

The ([^,])"(?=[^,]) regex matches

  • ([^,]) - Group 1 (referred to later with \1): any char but ,
  • " - a double quote
  • (?=[^,]) - a positive lookahead that requires a char other than a comma to appear immediately to the right of the current location.
user_38329700

You might want to split instead, so you can also perform additional checks on your csv line. I would suggest something like the following with the help of the default csv package:

package require csv

set input {"522","-1","12345678","12345678","Completed","","","","height 5' 5" and weight 170lbs","","","9876543","ABCD","2016-06-12T23:54:00-05:00","2016-06-12T23:59:00-05:00"}

# Quick check the line, if it's not complete...
if {![::csv::iscomplete $input]}    
    # Replace all 'quote comma quote' with null character (or some other character you are certain is 
    # not in your csv line
    set intermediate [regsub -all {","} $input \0]

    # Split on null character (or the character you picked on the previous line)
    set columns [split $intermediate \0]

    # Ensure that the csv line contains the expected number of columns
    if {[llength $columns] == 15} {
        # Replace the quotes in each element of the list of columns
        set columns [lmap x $columns {string map {{"} {}} $x}]
    } else {
        # Do further checks otherwise to see what's wrong
    }
    puts "Split data:"
    puts $columns        
} else {
    set columns [::csv::split -alternate $input]
}
set output [::csv::join $columns "," always]
puts "\nRejoined (if you still need it):"
puts $output

Outputs:

Split data:
522 -1 12345678 12345678 Completed {} {} {} {height 5' 5 and weight 170lbs} {} {} 9876543 ABCD 2016-06-12T23:54:00-05:00 2016-06-12T23:59:00-05:00

Rejoined (if you still need it):
"522","-1","12345678","12345678","Completed","","","","height 5' 5 and weight 170lbs","","","9876543","ABCD","2016-06-12T23:54:00-05:00","2016-06-12T23:59:00-05:00"

That way you have more control over what's happening and potentially finding any other issues with the csv.

user_15786040
Related