Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a large number of large semicolon-separated data files. All string fields are surrounded by double quotes. In some of the files, there are extra quotes in the string fields, which messes up the subsequent importing of the data for analysis (I'm importing to Stata).

This code allows me to see the problematic quotes using gnu-awk:

echo '"This";"is";1;"line" of" data";""with";"extra quotes""' | awk 'BEGIN { FPAT = "([^;]+)|(\"[^\"]+\")"}; {for ( i=1 ; i<=NF ; i++ ) if ($i ~ /^"(.*".*)+"$/) {print NR, $i}}'
1 "line" of" data"
1 ""with"
1 "extra quotes""

but I do not know how to replace them.

I was thinking of doing the replace manually, but it turns out that there are several hundred matches in some of the files. I know about awk's -sub-, -gsub-, and -match- functions, but I am not sure how to design a search and replace for this specific problem.

In the example above, the respective fields should be "This", "is", 1, "line of data", "with", "extra quotes", that is: all semicolons are separators, and all quotes except for the outermost quotes should be removed.

Should I may be use -sed-, or is -awk- the right tool? Hope you can help me out!

Thanks,

Matthijs

share|improve this question
    
The case ""with";"extra quotes"" is not clear: your awk script prints out it as two separate lines, while your explanation tells that you would like finally get it as "with;extra quotes". Am I correct? –  Serge Oct 9 '12 at 7:48
    
@Serge No, the field delimiting is correct. I want all quotes except for the ones at the beginning or end of the field removed. It should be two fields: "with" and "extra quotes" . –  Matthijs Oct 9 '12 at 9:15
    
The -gensub- in -awk- works for me, except for some pathetic cases. echo '"This";"is";1;"line" of" data";""with";"extra quotes""' | awk 'BEGIN { FPAT = "([^;]+)|(\"[^\"]+\")"}; {print gensub(/([^;])["]+([^;])/, "\\1\\2", "g")};'. An example of a pathetic case would be "ab""c"d". For now, though, this solution is fine for me. –  Matthijs Oct 9 '12 at 9:27
    
then could you please clarify. 1) Is this correct that wherever you encounter a semicolon it is a field separator? Do you want all quotes to be removed and only 'outbound' quotes to remain for any field satisfying the rule 1) and that has any quotes? –  Serge Oct 9 '12 at 11:02
    
@Serge Edited the question, hopefully more clear now. –  Matthijs Oct 9 '12 at 12:13

3 Answers 3

up vote 1 down vote accepted

My own solution, using only sed, stripping away all semicolons that are not juxtaposed with delimiters or numerical fields (the awk command is only there for expositional clarity):

echo '"This";"is";1;"line" of" data";""without";"extra quotes""' | sed -E 's/([^;])"+([^;])/\1\2/g' | awk 'BEGIN { FPAT = "([^;]+)|(\"[^\"]+\")"}; {for ( i=1 ; i<=NF ; i++ ) print $i}'
"This"
"is"
1
"line of data"
"without"
"extra quotes"

I think it's faster since it works on the full lines rather than splitting the line up in fields.

share|improve this answer
    
Yes a good simple solution; why didn't I think of that? You can use time to compare solutions and see what's faster. You may be surprised, sometimes these global substitutions can take a while. But if I were you, I'd probably still accept this answer based on it's simplicity. –  Steve Oct 10 '12 at 9:53
    
One issue is an empty text field at the end of the line (i.e., ";"""). My solution strips the last quote. I use sed -E 's/;"([^"]*)$/;"\1"/g' to fix that; takes another run though. –  Matthijs Oct 10 '12 at 13:15

One way using GNU awk and FPAT:

awk 'BEGIN { FPAT = "([^;]+)|(\"[^\"]+\")" } { for (i=1; i<=NF; i++) if (substr($i,0,1) == "\"" && substr($i,length($i),1) == "\"") { gsub(/"/, "", $i); printf "\"%s\"\n", $i } else { gsub(/"/, "", $i); print $i } }'

Testing:

echo '"This";"is";1;"line" of" data";""with";"extra quotes""' | awk 'BEGIN { FPAT = "([^;]+)|(\"[^\"]+\")" } { for (i=1; i<=NF; i++) if (substr($i,0,1) == "\"" && substr($i,length($i),1) == "\"") { gsub(/"/, "", $i); printf "\"%s\"\n", $i } else { gsub(/"/, "", $i); print $i } }'

Results:

"This"
"is"
1
"line of data"
"with"
"extra quotes"

One way using GNU awk, FPAT and GNU sed:

sed -e '/^".*"$/ { s/"//g; s/.*/"&"/ }' -e '/^".*"$/!s/"//g'

Testing:

echo '"This";"is";1;"line" of" data";""with";"extra quotes""' | awk 'BEGIN { FPAT = "([^;]+)|(\"[^\"]+\")" } { for (i=1; i<=NF; i++) print $i }' | sed -e '/^".*"$/ { s/"//g; s/.*/"&"/ }' -e '/^".*"$/!s/"//g'

Results:

"This"
"is"
1
"line of data"
"with"
"extra quotes"
share|improve this answer
    
Personally, I like the sed pipe. It's surprisingly easier to read IMO. Both solutions work the same; If the line contains beginning and end quotes, remove them all. And add them back in at print time. Otherwise just remove them all. HTH. –  Steve Oct 9 '12 at 13:38
    
Thanks, nice solutions. Going through all the fields of all the lines with awk takes some time though, this being one of many data files between 100-1000MB in size. –  Matthijs Oct 10 '12 at 9:10

I would rather go with coreutils and sed (GNU versions):

<<< '"This";"is";1;"line" of" data";""with";"extra quotes""' \
| tr ';' '\n' | sed -r 's/(.)"(.)/\1\2/g' | tr '\n' ';'

Output:

"This";"is";1;"line of data";"with";"extra quotes";

It leaves an extra semicolon, and drops the newline, insert head -c -1 before the second tr and append ; echo to fix:

tr ';' '\n' | sed -r 's/(.)"(.)/\1\2/g' | head -c -1 | tr '\n' ';'; echo

Output:

"This";"is";1;"line of data";"with";"extra quotes"
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.