Sstrhsrtj

My file is a comma delimited file and text qualifier is ~, but my requirement is to find and replace comma delimited file with |(pipe) delimited file and remove text qualifier ~ with nothing, however,
I should not remove quote or double quotes or any special character within the data present in text qualifier. eg: ~abc",~ I need it as abc",

Below is the content of my source file and the way i expect the output or manipulated file to be.

Source file:

364034,2015652205,26722,2015,4,~C25753-4~,~TC25753,~,~2WD Double Cab 144.2" SLT,~,~Y~,40506.16,43555.00,1095.00,~043,005,006,007,003,008,016,041,012,029,068,027,028,033~,3,~2WD Double Cab 144.2"~,~SLT~,6,4,~N~,~S~,~N~,~S~,~N~,~N~,~N~,~~,~ ~,~Confirmed~,~w2015k65m22t5~,~Sierra 2500HD~,~Double Cab Standard Box 2-Wheel Drive SLT~,~Rear Wheel Drive~,~Extended Cab Pickup - Standard Bed~

After cleansing I need the file like this:

364034|2015652205|26722|2015|4|C25753-4|TC25753,|2WD Double Cab 144.2" SLT,|Y|40506.16|43555.00|1095.00|043|005|006|007|003|008|016|041|012|029|068|027|028|033|3|2WD Double Cab 144.2"|SLT|6|4|N|S|N|S|N|N|N|| |Confirmed|w2015k65m22t5|Sierra 2500HD|Double Cab Standard Box 2-Wheel Drive SLT|Rear Wheel Drive|Extended Cab Pickup - Standard Bed

I tried sed -i -e with multiple options but the output is not 100% right.

I tried following but it's not giving right result that i wanted

sed -i -e 's/,~/|/g' file_name

sed -i -e 's/~,/|/g' file_name

sed -i -e 's/~//g' file_name

sed -i -e 's/([0-9],[0-9])/|/g' file_name

sed -i -e 's/r//g' file_name

ESC=$(printf '33')

RED="${ESC}[0;31m"

 NC="${ESC}[0m"



sed -e '

   /./!b

   /[^[:space:]]/!b



   s/.*/

&,/



   :loop

      h

      s/(n),/|1/;                                                  # An empty field

      s/(n)([+-]{0,1}[.][0-9]{1,}),/2|1/;                   # +-.NNN

      s/(n)([+-]{0,1}[0-9]{1,}([.][0-9]*){0,1}),/2|1/;  # +-NNN.MMM +-NNN. +-NNN

      s/(n)~([0-9][0-9]*),/2|1/;                                # ~NNN

      s/(n)([0-9][0-9]*)~,/2|1/;                                # NNN~

      s/(n)~([^~]*)~,/2|1/;                                     # ~...~

      x;G

      /^(.*)n1$/{

         g;'"s/n([^,]*)/${RED}1${NC}/"'

         i

***'"${RED}ERROR${NC}"'*** Unable to process the field shown colored.



Cause of error: What this means is that this particular field is not 



           Fix: You should add to the sed code in the :loop label to 

                digest the able to be processed by the sed code as it stands.



The record with the offending field shown colored red:



         q

      }

      g; # all clear: recover and carry on...

   /n$/!bloop



   s/..$//

' csv.data

Working

• We base the solution on the various types of fields.


• Skip empty or blank lines.


• Append a "," to simplify regex used, we'll take it away at the end.


• To set the ball rolling we place a marker, n, at the beginning of line. This marker will travel from left to right, jumping a field processed a time.


• The action begins in the do-while loop, in the body of which, we process a field at a time. The field beginning is signaled by n and we process the various varieties of fields that can occur. Everytime, we bring the field processed away to the left of n and replace the , by a |.


• The looping stops when the marker n hits the end of line /n$/ and we then take away the marker as well as the dummy , we had placed in the beginning.

Results

364034|2015652205|26722|2015|4|C25753-4|TC25753,|2WD Double Cab 144.2" SLT,|Y|40506.16|43555.00|1095.00|043|005|006|007|003|008|016|041|012|029|068|027|028|033|3|2WD Double Cab 144.2"|SLT|6|4|N|S|N|S|N|N|N|| |Confirmed|w2015k65m22t5|Sierra 2500HD|Double Cab Standard Box 2-Wheel Drive SLT|Rear Wheel Drive|Extended Cab Pickup - Standard Bed

I'd suggest using a dedicated CSV parser such as perl's Text::CSV

perl -MText::CSV -lne '

    BEGIN{ $csv = Text::CSV->new({ quote_char => "~" , escape_char => "~" , allow_whitespace => 1}) } 

    print join "|", $csv->fields() if $csv->parse($_)

  ' file_name

364034|2015652205|26722|2015|4|C25753-4|TC25753,|2WD Double Cab 144.2" SLT,|Y|40506.16|43555.00|1095.00|043,005,006,007,003,008,016,041,012,029,068,027,028,033|3|2WD Double Cab 144.2"|SLT|6|4|N|S|N|S|N|N|N|| |Confirmed|w2015k65m22t5|Sierra 2500HD|Double Cab Standard Box 2-Wheel Drive SLT|Rear Wheel Drive|Extended Cab Pickup - Standard Bed