如何将特定列中的csv日期转换为unix日期

Question

我有一个带有以下列的csv文件：

"Weight","Impedance","Units","User","Timestamp","PhysiqueRating"
"58.75","5.33","kg","7","2020-7-11 19:29:29","5"

当然，我可以转换日期命令：date -d '2020-7-11 19:29:29' +%s

结果: 1594488569

如何在bash脚本中替换csv文件中的此日期？

Answer 1

用GNU sed

sed -E '2,$ s/(("[^"]*",){4})("[^"]+")(.*)/echo \x27\1"\x27$(date -d \3 +%s)\x27"\4\x27/e'

• 2,$将跳过标题，从获取processed
• (("[^"]*",){4})前四条columns
• ("[^"]+")第五条column
• (.*)剩余的line
• echo \x27\1"\x27和\x27"\4\x27保存前四列和第五列后的行，同时添加双引号到date conversion
• $(date -d \3 +%s)调用shell命令的结果中，第五列值为

。

注意，如果输入可以包含单引号，则此命令将失败。这可以通过使用s/\x27/\x27\\&\x27/g来解决。

​

您可以看到使用-n选项和pe标志执行的命令。

sed -nE '2,$ s/(("[^"]*",){4})("[^"]+")(.*)/echo \x27\1"\x27$(date -d \3 +%s)\x27"\4\x27/pe'

会给

echo '"58.75","5.33","kg","7","'$(date -d "2020-7-11 19:29:29" +%s)'","5"'

​

对于58.25,5.89, kg, 7,2020 / 7/12 11:23:46, "5"格式，请尝试

sed -E '2,$ s/(([^,]*,){4})([^,]+)(.*)/echo \x27\1\x27$(date -d "\3" +%s)\x27\4\x27/e'

或(改编自https://stackoverflow.com/a/62862416)

awk 'BEGIN{FS=OFS=","} NR>1{$5=mktime(gensub(/[:\/]/, " ", "g", $5))} 1'

​

注意：用于sed解决方案，如果输入可以来自外部源，则必须注意避免评论中提到的恶意意图。一种方法是使用[0-9: -]+或类似的方法匹配第五列。

Answer 2

使用GNU awk：

$ gawk '
BEGIN {
    FS=OFS=","
}
{
    n=split($5,a,/[-" :]/)
    if(n==8)
        $5="\"" mktime(sprintf("%s %s %s %s %s %s",a[2],a[3],a[4],a[5],a[6],a[7])) "\""
}1' file

输出：

"Weight","Impedance","Units","User","Timestamp","PhysiqueRating"
"58.75","5.33","kg","7","1594484969","5"

Answer 3

与GNU awk一起用于gensub()和mktime()：

$ awk 'BEGIN{FS=OFS="\""} NR>1{$10=mktime(gensub(/[-:]/," ","g",$10))} 1' file
"Weight","Impedance","Units","User","Timestamp","PhysiqueRating"
"58.75","5.33","kg","7","1594513769","5"