替换excel文件中转换成CSV后遗留的回车换行符号

specialco

RT， excel文件中包含了软回车，转换成csv后有存在软回车，导致形成多行，并用" 包含，数据如下

Prctnr Cnum Id,Asgnmt Id,Strt Dt,End Dt,Cre T,Asgnmt Catgry Cd,Project Name,Client Name,Rem Stat Txt
060069672,"10,636,278.",10/08/13,11/30/13,09/25/13 03:09 AM,BIL,"255U-1B2M74J, ICBC PF,C.02420.002",ICBC,
060069672,"10,846,782.",11/14/13,12/13/13,11/13/13 04:07 AM,BIL,"WM-FZUCP74, ICBC PF PCR",INDUSTRIAL AND COMMERCIAL BANK OF,"C.02420.004, WM-FZUCP74,ICBC PF PCR
ICBC PF PCR
OS fulfilled and closed"
060112672,"10,135,402.",05/06/13,12/31/13,05/20/13 10:10 AM,PIL,CIS Prototype development,YOKOHAMA CITY UNIVERSITY HOSPITAL,CN-13-04243



有高手能用sed 处理一下 ，去掉这个软回车，目标格式如下：

Prctnr Cnum Id,Asgnmt Id,Strt Dt,End Dt,Cre T,Asgnmt Catgry Cd,Project Name,Client Name,Rem Stat Txt
060069672,"10,636,278.",10/08/13,11/30/13,09/25/13 03:09 AM,BIL,"255U-1B2M74J, ICBC PF,C.02420.002",ICBC,
060069672,"10,846,782.",11/14/13,12/13/13,11/13/13 04:07 AM,BIL,"WM-FZUCP74, ICBC PF PCR",INDUSTRIAL AND COMMERCIAL BANK OF,"C.02420.004, WM-FZUCP74,ICBC PF PCR ICBC PF PCR  OS fulfilled and closed"
060112672,"10,135,402.",05/06/13,12/31/13,05/20/13 10:10 AM,PIL,CIS Prototype development,YOKOHAMA CITY UNIVERSITY HOSPITAL,CN-13-04243

总共合并为4行， 第一行是prc开头的column name,  下面3行是数据

这个冬天不冷

1. dos2unix filename

复制代码

specialco

回复 2# 这个冬天不冷

这样不能合并行吧。




   

specialco

sed 'N;s/\n/:/g' filename    (这个命令只会处理每2行之间的换行，比如1和2， 3和4， 但是2和3之间就没处理）

但是加上标签

sed ':a;N;s/\n/:/g;ba' filename 又会报sed: The label  is greater than eight characters.

yestreenstars

去软回车用dos2unix，合并用awk：

1. [root@localhost ~]# awk --re-interval -vRS='[0-9]{9},' 'NF+=0{$0=t$0;t=RT}1' i
   
2. Prctnr Cnum Id,Asgnmt Id,Strt Dt,End Dt,Cre T,Asgnmt Catgry Cd,Project Name,Client Name,Rem Stat Txt
   
3. 060069672,"10,636,278.",10/08/13,11/30/13,09/25/13 03:09 AM,BIL,"255U-1B2M74J, ICBC PF,C.02420.002",ICBC,
   
4. 060069672,"10,846,782.",11/14/13,12/13/13,11/13/13 04:07 AM,BIL,"WM-FZUCP74, ICBC PF PCR",INDUSTRIAL AND COMMERCIAL BANK OF,"C.02420.004, WM-FZUCP74,ICBC PF P OS fulfilled and closed"
   
5. 060112672,"10,135,402.",05/06/13,12/31/13,05/20/13 10:10 AM,PIL,CIS Prototype development,YOKOHAMA CITY UNIVERSITY HOSPITAL,CN-13-04243
   
6. [root@localhost ~]#

复制代码

specialco

回复 5# yestreenstars

谢谢兄弟，但是我是想用sed命名去掉软回车， 可以用 sed  ’s/^M//g' filename 去除多少的回车符，但是就是不知道怎么合并

   

yestreenstars

回复 6# specialco
去软件回车和合并行一起完成：

1. [root@localhost ~]# sed -nr '/^[0-9]{9},/{h;:1;${g;p};n;/^[0-9]{9},/{x;s/\r\n/ /g;p;x;h;b1};H;b1};p' i
   
2. Prctnr Cnum Id,Asgnmt Id,Strt Dt,End Dt,Cre T,Asgnmt Catgry Cd,Project Name,Client Name,Rem Stat Txt
   
3. 060069672,"10,636,278.",10/08/13,11/30/13,09/25/13 03:09 AM,BIL,"255U-1B2M74J, ICBC PF,C.02420.002",ICBC,
   
4. 060069672,"10,846,782.",11/14/13,12/13/13,11/13/13 04:07 AM,BIL,"WM-FZUCP74, ICBC PF PCR",INDUSTRIAL AND COMMERCIAL BANK OF,"C.02420.004, WM-FZUCP74,ICBC PF PCR ICBC PF PCR OS fulfilled and closed"
   
5. 060112672,"10,135,402.",05/06/13,12/31/13,05/20/13 10:10 AM,PIL,CIS Prototype development,YOKOHAMA CITY UNIVERSITY HOSPITAL,CN-13-04243
   
6. [root@localhost ~]#

复制代码

specialco

回复 7# yestreenstars

兄弟，怎么解决 执行命令后是is greater than eight characters.的错误，我的aix


   

yestreenstars

回复 8# specialco
aix比较奇葩，改成这样试试：

1. sed -nr -e '/^[0-9]{9},/{h' -e ':1' -e '${g;p};n;/^[0-9]{9},/{x;s/\n/ /g;p;x;h;b1};H;b1};p'

复制代码