如何用sed或awk交换CSV文件第一、二列的日与月？

我有一份逗号分隔的CSV文件，需要交换文件中第一、第二列日期的日和月。已尝试使用sed命令实现单日期的日月交换，但无法应用到整个CSV文件，请问该如何操作？

输入CSV示例 #

01/04/2022,02/04/2022,UPI/45/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
01/04/2022,02/04/2022,UPI/234234325435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
01/04/2022,02/04/2022,UPI/45435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
01/04/2022,02/04/2022,UPI/435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
01/04/2022,02/04/2022,UPI/435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
01/04/2022,02/04/2022,UPI/234234325435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
02/04/2022,02/04/2022,UPI/4545/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
03/04/2022,04/04/2022,UPI/234234325435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/04/2022,04/04/2022,UPI/67657/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
05/04/2022,05/04/2022,UPI/787/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
06/04/2022,06/04/2022,UPI/7878/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08

尝试的命令 #

$ sed -E 's,^([0-9]+)/([0-9]+),\2/\1,'
$ echo  01/04/2022 | cut -d, -f2 | sed -E 's,^([0-9]+)/([0-9]+),\2/\1,'
04/01/2022

期望输出示例 #

04/01/2022,04/02/2022,UPI/45/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/01/2022,04/02/2022,UPI/234234325435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/01/2022,04/02/2022,UPI/45435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/01/2022,04/02/2022,UPI/435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/01/2022,04/02/2022,UPI/435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/01/2022,04/02/2022,UPI/234234325435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/02/2022,04/02/2022,UPI/4545/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/03/2022,04/04/2022,UPI/234234325435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/04/2022,04/04/2022,UPI/67657/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/05/2022,04/05/2022,UPI/787/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/06/2022,04/06/2022,UPI/7878/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08

解决方案 #

方法1：使用sed命令 #

你之前的sed命令仅处理了日期的前两位，未覆盖完整日期格式，也未处理第二列。可以通过两次正则替换，分别处理第一列和第二列的日期：

sed -E 's/^([0-9]{2})\/([0-9]{2})\/([0-9]{4}),/\2\/\1\/\3,/; s/,([0-9]{2})\/([0-9]{2})\/([0-9]{4}),/,\2\/\1\/\3,/' input.csv

• 第一个替换规则：匹配行首的DD/MM/YYYY,格式，交换日和月，替换为MM/DD/YYYY,
• 第二个替换规则：匹配第二列开头的,DD/MM/YYYY,格式，同样交换日月，确保只修改第二列的日期

若要直接修改原文件，可添加-i参数（macOS下需写-i ''）：

# Linux
sed -i -E 's/^([0-9]{2})\/([0-9]{2})\/([0-9]{4}),/\2\/\1\/\3,/; s/,([0-9]{2})\/([0-9]{2})\/([0-9]{4}),/,\2\/\1\/\3,/' input.csv

# macOS
sed -i '' -E 's/^([0-9]{2})\/([0-9]{2})\/([0-9]{4}),/\2\/\1\/\3,/; s/,([0-9]{2})\/([0-9]{2})\/([0-9]{4}),/,\2\/\1\/\3,/' input.csv

方法2：使用awk命令 #

awk处理CSV列更直观，不易出错，适合结构化表格数据：

awk -F, 'BEGIN{OFS=","} {split($1,d,"/"); $1=d[2]"/"d[1]"/"d[3]; split($2,d,"/"); $2=d[2]"/"d[1]"/"d[3]; print}' input.csv

• -F,：以逗号作为列分隔符
• BEGIN{OFS=","}：设置输出的列分隔符同样为逗号
• split($1,d,"/")：将第一列日期按斜杠分割到数组d中，d[1]为日，d[2]为月，d[3]为年
• 重新赋值第一、第二列为交换日月后的日期，最后打印整行

若要将结果保存到新文件，直接重定向即可：

awk -F, 'BEGIN{OFS=","} {split($1,d,"/"); $1=d[2]"/"d[1]"/"d[3]; split($2,d,"/"); $2=d[2]"/"d[1]"/"d[3]; print}' input.csv > output.csv

内容的提问来源于stack exchange，提问作者kuruvi