Unix : Find and replace consecutive commas to consecutive pipelines

user_9806115 0
Viewed 127

I'm converting a double quoted CSV to pipeline delimited txt file in Unix. I have used the following sed command to replace "," into | then remove starting and ending double quote.

sed -e 's/","/|/g' -e 's/"//g' filenm.csv > filenm.txt

But the file seems to have consecutive commas without double quotes and they are not getting replaced.

Col1|col2|col3|col4|col5|col6|col7|col8
Val1|val2|val3,,,,val7|val8

Now I want to convert all these consecutive commas to consecutive pipelines as they indicate empty or null fields.

And other fields also have commas inside field values which should not be altered.

I tried using below for that, but not working.

sed -e 's/,{1,\}/|{1,\}/g' filenm.csv > filenm.txt

sample csv file opened in notepad:

"ID","Name","DOB","Age","Address","City","State","Country","Phone number"
"123","ABC","12/20/2020","15","No.38,3rd st, RRR NNN, TRT",,,,"9999999999"
"456","DEF","12/20/2020",,,,,"test-country","9999999999"
"465","XYZ",,,"No.38,3rd st, RRR NNN, TRT",,,,"9999999999"

I hope this helps to reproduce the issue and resolve.

Thanks in advance....

sed awk unix linux regex
4 Answers

This might work for you (GNU sed):

sed -E ':a;s/^(("[^",]*",+)*"[^",]*),/\1\n/;ta;y/,\n/|,/' file

Iteratively replace ,'s between "'s with newlines, then translate ,'s for |'s and newlines for ,'s.

user_9674920

You can use perl:

perl -pe 's/"([^"]*)"|,/defined($1) ? $1 : "|"/ge' filenm.csv > filenm.txt

Details:

  • "([^"]*)"|, - the regex pattern that matches ", then captures into Group 1 any zero or more chars other than " and then matches a ", or just matches a , in all other contexts
  • defined($1) ? $1 : "|" - RHS, replacement, that replaces the match either with Group 1 value (if Group 1 was matched) or with a | (if the , was matched)
  • ge - g stands for global (replaces all occurrences) and e makes Perl treat the RHS as a Perl expression.

See an online test:

#!/bin/bash
s='"ID","Name","DOB","Age","Address","City","State","Country","Phone number"
"123","ABC","12/20/2020","0","No.38,3rd st, RRR NNN, TRT",,,,"9999999999"'
perl -pe 's/"([^"]*)"|,/defined($1) ? $1 : "|"/ge' <<< "$s"

Output:

ID|Name|DOB|Age|Address|City|State|Country|Phone number
123|ABC|12/20/2020|0|No.38,3rd st, RRR NNN, TRT||||9999999999
user_38329700

Using awk:

awk -F \" '{ for(i=1;i<=NF;i++) { if ($i ~ /^[,]{2,}$/) { $i="," } } OFS="\"";gsub("\",\"","\"|\"",$0)}1' sample.csv

Explanation:

awk -F \" '{  # Set the field delimiter to double quote
             for(i=1;i<=NF;i++) { 
               if ($i ~ /^[,]{2,}$/) { 
                  $i="," # Loop through each field and if is contains 2 or more commas, set that field to one comma
               } 
             } 
             OFS="\"";
             gsub("\",\"","\"|\"",$0) # Substitute "," for "|"
           }1' sample.csv
user_78404400

I would use GNU AWK for that following way. Let file.txt content be

"ID","Name","DOB","Age","Address","City","State","Country","Phone number"
"123","ABC","12/20/2020","15","No.38,3rd st, RRR NNN, TRT",,,,"9999999999"
"456","DEF","12/20/2020",,,,,"test-country","9999999999"
"465","XYZ",,,"No.38,3rd st, RRR NNN, TRT",,,,"9999999999"

then

awk 'BEGIN{FS="\"";OFS=""}{for(i=1;i<=NF;i+=2){$i=gensub(/,/,"|","g",$i)};print $0}' file.txt

output

ID|Name|DOB|Age|Address|City|State|Country|Phone number
123|ABC|12/20/2020|15|No.38,3rd st, RRR NNN, TRT||||9999999999
456|DEF|12/20/2020|||||test-country|9999999999
465|XYZ|||No.38,3rd st, RRR NNN, TRT||||9999999999

I assumed that first and last column is never empty. I use " as field separator and then in every odd field (these contain solely ,) I change all , to |. Finally I print whole such altered line.

(tested in GNU Awk 5.0.1)

user_107859750
Related