Remove double quotes from a 'string with comma' inside csv

Question

i'm converting xls to csv. Since i'm having commas in a single column, i'm getting csv as below:

AMP FAN,Yes,Shichi,PON Seal,,"Brass, Silver"
AMP FAN,Yes,Shichi,PON Seal,,"Platinum, Gel"

If you see double quote is coming for the last column as it has comma inside. Now i'm reading this csv in tcl file and i'm sending to my target system. In target system this value is getting saved with double quotes (means exactly like "Brass, Silver"). But the user doesn't want that double quotes. So i want to set like Brass, Silver . is there any way i can avoid that double quotes. below is the current script i'm using.

while {[gets $fileIn sLine] >= 0} {
        #using regex to handle multiple commas in a single column

        set matches [regexp -all -inline -- {("[^\"]+"|[^,]*)(?:$|,)} $sLine]
        set lsLine {}
        foreach {a b} $matches {lappend lsLine $b}
            set sType [lindex $lsLine 0]    
            set sIsOk [lindex $lsLine 1]
            set sMaterial [lindex $lsLine 5]

            #later i'm setting sMaterial to some attribute
    }

Kindly help me.

Note : I will not be able to use csv package as the user don't have that in their environment and i can't add there myself.

Jerry Jerry · Accepted Answer · 2015-10-20T08:57:34

You can remove them from the token after getting each element, like this:

while {[gets $fileIn sLine] >= 0} {
    #using regex to handle multiple commas in a single column

    set matches [regexp -all -inline -- {("[^\"]+"|[^,]*)(?:$|,)} $sLine]
    set lsLine {}
    foreach {a b} $matches {
        # Remove the quotes here
        lappend lsLine [string map {\" {}} $b]
    }
    set sType [lindex $lsLine 0]    
    set sIsOk [lindex $lsLine 1]
    set sMaterial [lindex $lsLine 5]

    #later i'm setting sMaterial to some attribute
}

Remove double quotes from a 'string with comma' inside csv

2 Answers