http://www.perlmonks.org?node_id=623388

dwhite20899 has asked for the wisdom of the Perl Monks concerning the following question:

I have some CSV data, and one field has embedded commas. I'd like to convert it to PIPE (|) separated data. I've come up with something that works, without needing to install extra modules, but there's got to be a better way.

Given the example data below, is there some concise way to do this? (not requiring module installation is a plus, but it's not out of the question)

"00000142988AFA836117B1B572FAE4713F200567","9B3702B0E788C6D62996392FE3 +C9786A","05E566DF","J0180794.JPG",32768,3290,"WIN","" "000005EE5E3F6961B78CE4549270DE5D05CBC0CB","8D025B6AE1994A40FCBB5AEC2E +F273F9","5E8D7D42","WabIab, and more.bor",4760,4616,"WIN","" "0000085FC602CD8AD4793A874A47D286DACB0F6A","8BA8BC04896C421A704282E9B8 +7B5520","8D89A85D","fpSDt,Finder,Link.gif",1161,2988,"Solaris","" "00000FF9D0ED9A6B53BC6A9364C07074DE1565F3","A5D49D6DA9D78FD1E7C32D58BC +7A46FB","2D729A1E","cmnres,pdb.dll",76800,1550,"WIN",""

Here's what I'm running now. I know that only the 4th field ($p [ 3 ]) may contain embedded commas. I split on commas; if none are embedded I join with PIPE. If some (possibly multiple) commas are embedded, I join the three leading fields with PIPE, the four trailing fields with PIPE, rejoin the 4th field with commas and print that.

perl -n -e '@p=split(/,/,$_); if (scalar(@p) == 8) { \ print join("|",@p);} else { $a = join("|",$p[0],$p[1],$p[2]); \ shift(@p); shift(@p); shift(@p); $c = join("|", $p[$#p-3],$p[$#p-2], + \ $p[$#p-1],$p[$#p]); pop(@p); pop(@p); pop(@p); pop(@p); \ print "$a|",join(",",@p),"|$c";}' A/NSRLFile.txt > /tmp/A-File.psv