Unix : 查找和替换连续的逗号至连续的管道

2 人关注

我在Unix中把一个双引号的CSV文件转换为管道分隔的txt文件。我使用了下面的sed命令，将", "替换成|，然后删除开始和结束的双引号。

sed -e 's/","/|/g' -e 's/"//g' filenm.csv > filenm.txt
但该文件似乎有连续的逗号，没有双引号，它们没有被替换。
Col1|col2|col3|col4|col5|col6|col7|col8
Val1|val2|val3,,,,val7|val8
现在我想把所有这些连续的逗号转换成连续的管道，因为它们表示空或空字段。
而其他字段的字段值里面也有逗号，不应该被改变。
我试着用下面的方法来做，但没有效果。
sed -e 's/,{1,\}/|{1,\}/g' filenm.csv > filenm.txt
用记事本打开的样本csv文件。
"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"
我希望这有助于再现该问题并加以解决。
预先感谢....


           
            
             
              
               当然，我已经添加了样本文件。希望这对你有帮助!谢谢


           
            
             
              
               @WiktorStribiżew 这个perl命令工作得很好，但有一个小问题--当一个字段里有0的时候，它会跳过这个字段，字段内容会被移动一个字段......也就是说，当文件看起来像这样。
               
                "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 cmd给出了以下结果。
               
                ID|Name|DOB|Age|Address|City|State|Country|Phone number 123|ABC|12/20/2020|||No.38,3rd st, RRR NNN, TRT||||9999999999
               
               。请在Excel中导入这个样本以获得良好的参考。


           
            
             
              
               是的，我只是在尝试和分析这个问题....。


           
            
             
              
               不，我所放置的是Unix的输出

awk

sed


          
           
            
             
              This might work for you (GNU sed):
             
             sed -E ':a;s/^(("[^",]*",+)*"[^",]*),/\1\n/;ta;y/,\n/|,/' file
将,在"之间迭代为换行，然后将,翻译为|，换行翻译为,。


          
           
            
             
              
               
               
                Wiktor Stribiżew
               
              
              
               发布于
               
               2021-01-09


          
           
            
             
              
               You can use
               
                perl
               
               :
              
              perl -pe 's/"([^"]*)"|,/defined($1) ? $1 : "|"/ge' filenm.csv > filenm.txt
"([^"]*)"|, - 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.
看到一个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


          
           
            
             
              
               
                
                 
                  
                  
                   Raman Sailopal
                  
                 
                 
                  发布于
                  
                  2021-01-09


          
           
            
             
              
               
                
                 
                  使用awk。
                 
                 awk -F \" '{ for(i=1;i<=NF;i++) { if ($i ~ /^[,]{2,}$/) { $i="," } } OFS="\"";gsub("\",\"","\"|\"",$0)}1' sample.csv
解释一下。
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


          
           
            
             
              
               
                
                 
                  
                   
                    我会用GNU
                    
                     AWK
                    
                    的方式来做这个事情。让
                    
                     file.txt
                    
                    的内容为
                   
                   "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"
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