Replace only certain double quotes in data file

Question

We extract data from a database to a flat file, we use a pipe for column delimiter and double quotes for text delimiter.

Data file looks Something like this:

"164829" | "collection 1" | "wood plank 2" x 4" long" | "23.5" 
"2017"|"S"|"221318"|"WE"|"20170118"|"Someones name"|"20170215"|"1785"|"009"|"20170215"|"182339"|"99536"|"00090"|"LOCAL00"|"930N"|"2017"|"6100"|"0000880"|1.000|0.000|"EA"|" "|" "|" "|" "|"005"|"00000000"|" "|" "|"1785"|"50228"|"R"|"2017"|"NMT CAUTION| 5" X 3" NAT ON BLK"|" "|" "|"USD"|"7444"|" "|"000"|"COIN"|"04"|35.00|"00"

What command/script could find every case where the data (not including the text delimiter) can be escaped with a second double quote?

The end result should look like this:

"164829" | "collection 1" | "wood plank 2"" x 4"" long" | "23.5"
"2017"|"S"|"221318"|"WE"|"20170118"|"Someones name"|"20170215"|"1785"|"009"|"20170215"|"182339"|"99536"|"00090"|"LOCAL00"|"930N"|"2017"|"6100"|"0000880"|1.000|0.000|"EA"|" "|" "|" "|" "|"005"|"00000000"|" "|" "|"1785"|"50228"|"R"|"2017"|"NMT CAUTION| 5"" X 3"" NAT ON BLK"|" "|" "|"USD"|"7444"|" "|"000"|"COIN"|"04"|35.00|"00"

Answer 1

This is a little tricky with just regular expressions, but it can be done in several steps. Here's the perl script that I used for this (sed can't be used because I used lookaheads):

perl -pe 's/(?:(?:\||^)[ ]*"(.*?)\"[ ]*(?=\||$))/~~\1~~/gm;s/"/""/g;s/~~(.*?)~~/~~"\1"~~/g;s/~~~~/|/g;s/~~//g' inputfile.txt

(use perl -pi -e if you want to edit the input file)

The script does the following steps:

1. Look for everything that either is inside of |"{...}"|, (start of line)"{...}"|, or |"{...}"(end of line), ignoring spaces outside of the text. Replace the outside bits with ~~ (I used something that is known not to be inside of the text)
2. Replace all remaining quotation marks with doubled-up ones
3. Replace all internal ~~{...}~~ sequences with ~~"{...}"~~
4. Replace all ~~~~ sequences (which will all be internal) to |
5. Remove all remaining ~~ sequences (which will be at the beginning and end of the lines)

Running each step given the following test text:

"164829" | "collection 1" | "wood plank 2" x 4" long" | "23.5"
"939485"|"collect "name""|"more items with | " and ""|"294.5""

We get the following output after each step:

$ perl -pe 's/(?:(?:\||^)[ ]*"(.*?)\"[ ]*(?=\||$))/~~\1~~/gm;' testinput.txt                       
~~164829~~~~collection 1~~~~wood plank 2" x 4" long~~~~23.5~~
~~939485~~~~collect "name"~~~~more items with | " and "~~~~294.5"~~

$ perl -pe 's/(?:(?:\||^)[ ]*"(.*?)\"[ ]*(?=\||$))/~~\1~~/gm;s/"/""/g;' testinput.txt
~~164829~~~~collection 1~~~~wood plank 2"" x 4"" long~~~~23.5~~
~~939485~~~~collect ""name""~~~~more items with | "" and ""~~~~294.5""~~

$ perl -pe 's/(?:(?:\||^)[ ]*"(.*?)\"[ ]*(?=\||$))/~~\1~~/gm;s/"/""/g;s/~~(.*?)~~/~~"\1"~~/g;' testinput.txt
~~"164829"~~~~"collection 1"~~~~"wood plank 2"" x 4"" long"~~~~"23.5"~~
~~"939485"~~~~"collect ""name"""~~~~"more items with | "" and """~~~~"294.5"""~~

$ perl -pe 's/(?:(?:\||^)[ ]*"(.*?)\"[ ]*(?=\||$))/~~\1~~/gm;s/"/""/g;s/~~(.*?)~~/~~"\1"~~/g;s/~~~~/|/g;' testinput.txt
~~"164829"|"collection 1"|"wood plank 2"" x 4"" long"|"23.5"~~
~~"939485"|"collect ""name"""|"more items with | "" and """|"294.5"""~~

$ perl -pe 's/(?:(?:\||^)[ ]*"(.*?)\"[ ]*(?=\||$))/~~\1~~/gm;s/"/""/g;s/~~(.*?)~~/~~"\1"~~/g;s/~~~~/|/g;s/~~//g' testpipe.txt
"164829"|"collection 1"|"wood plank 2"" x 4"" long"|"23.5"
"939485"|"collect ""name"""|"more items with | "" and """|"294.5"""

Answer 2

Your double quote are not real double quote (“ vs ").
With real double quote " , You can try this sed (assume there is no @ in your data)

sed 's/" | "/@/g;s/"/""/g;s/^"//;s/"$//;s/@/" | "/g' infile

Answer 3

Bahrat from this forum posted an answer that was very close to resolving the issue, somehow it was removed from this post (code below). The only issue is it cannot handle a pipe | within a string (ie "25"|"to concatenate data a | must be used"|"addendum 1"|20|"last entry") He was supposed to post today but the thread was deleted ??

awk -v FS='|' -v OFS='|' '{for(i=1;i<=NF;i++){gsub(/"/,"\"\"",$i);sub(/"/,"",$i);sub(/"[^"]*$/,"",$i)}print}' myfile > myfile3

Answer 4

Input -

 "2017"|"S"|"221318"|"WE"|"20170118"|"Someones name"|"20170215"|"1785"|"009"|"20170215"|"182339"|"99536"|"00090"|"LOCAL00"|"930N"|"2017"|"6100"|"0000880"|1.000|0.000|"EA"|" "|" "|" "|" "|"005"|"00000000"|" "|" "|"1785"|"50228"|"R"|"2017"|"NMT CAUTION| 5" X 3" NAT ON BLK"|" "|" "|"USD"|"7444"|" "|"000"|"COIN"|"04"|35.00|"00" 

CMD:

  awk -v RS='[[:blank:]]*[[:blank:]]*[|][[:blank:]]*|[[:blank:]]*[\n][[:blank:]]*' '{ if ($0 !~ /(^"([^"]|"")*"$)/) { gsub(/\"/,"\"\"");sub(/^"/,"");sub(/"$/,"") } printf "%s%s", $0, RT}' file.txt

Output -

 "2017"|"S"|"221318"|"WE"|"20170118"|"Someones name"|"20170215"|"1785"|"009"|"20170215"|"182339"|"99536"|"00090"|"LOCAL00"|"930N"|"2017"|"6100"|"0000880"|1.000|0.000|"EA"|" "|" "|" "|" "|"005"|"00000000"|" "|" "|"1785"|"50228"|"R"|"2017"|"NMT CAUTION| 5"" X 3"" NAT ON BLK"|" "|" "|"USD"|"7444"|" "|"000"|"COIN"|"04"|35.00|"00"