主页 / unix / 问题 / 750551
Accepted
ehammer
Asked: 2023-07-04 10:20:56 +0800 CST

使用 awk/sed 更改 CSV 中的非标准日期时间戳格式

  • 772

我有一个包含几十万行的 csv，我正在尝试更改第二个字段中的日期格式。我还应该添加第二个字段有时根本没有填充。可悲的输入格式是DayofWeek MonthofYear DayofMonth Hour:Minute:Second Timezone Year

例子：

Mon Jul 03 14:48:54 EDT 2023

我想要的输出格式是YYYY-MM-DD HH:MM:SS 示例：

2023-07-03 14:48:54

我熟悉 sed，所以我得到了这个 sed 正则表达式替换行以使其格式几乎正确，但月份不是数字是一个问题。

sed -E "s/[A-Za-z]{3}\s([A-Za-z]{3})\s([0-9]{2})\s([0-9]{2}:[0-9]{2}:[0-9]{2})\s[A-Z]+\s([0-9]{4})/\4-\1-\2 \3/"

我认为不可能使用捕获组 1 在 sed 替换部分中运行 date 命令（但如果我错了，请纠正我）。

我不知道如何在 sed 命令完成后引用月份并使用 date 命令解析它，并且我认为最好在不将整个输出通过管道传输到另一个命令的情况下进行处理。该命令只是用于格式化其余数据的一长串管道命令中的一个。

看起来也许 awk 可以一次完成整个格式化，但我真的不知道如何很好地使用 awk。

将时间戳转换为正确格式的最有效方法是什么？

只是为了解决一些带有更多背景信息的评论：

此数据由将 csv 日志数据输出到文件的应用程序生成。这不是我的应用程序，并且没有对应用程序日志记录方式的配置控制。CSV 未引用（即使字段中的数据包含空格）并且空字段不包含任何内容。

我将 csv 数据直接加载到 mysql 数据库中。虽然时区通常是一个好主意，但该数据始终带有本地时间时间戳，并且在可视化数据（grafana）时，我不需要将其存储在 UTC 中，然后转换为 EDT 只是为了查看（为什么将时间转换为 UTC只是将其转换回 EDT）。另外，每个 csv 行都包含经度和纬度（因此，如果我想返回并将时间戳更改为 UTC，则不可能找出当地时间）。

我所做的额外格式化并不多，可能可以使用 awk 完成（同样，我不太熟悉那里的语法）。原始数据需要添加 ID 列，并且 qoutes 放置一些字段，并且有两种不同格式的两个日期时间字段，这并没有帮助。所以我的又长又可怕的管道通常看起来像这样：

cat file | add ID column | format timestamp in second csv field | format timestamp in third csv field | qoute any field with spaces | replace empty fields with \N > output file

我在 mysql 和空字段方面遇到了一些问题，所以我添加了显式的空字符。肯定有更好的方法来做到这一点，一旦我让整个过程正常工作，我将回顾并简化。

我非常感谢大家的回应。

awk

4 个回答

  1. 你可以这样做：

    LC_ALL=C sed '
  s/$/;Jan01Feb02Mar03Apr04May05Jun06Jul07Aug08Sep09Oct10Nov11Dec12/
  s/[A-Z][a-z][a-z] \([A-Z][a-z][a-z]\) \([0-9][0-9]\) \([0-2][0-9]:[0-5][0-9]:[0-5][0-9]\) [A-Z]\{3,\} \([0-9]\{4\}\)\(.*;.*\1\([01][0-9]\)[^;]*\)$/\4-\6-\2 \3\5/
  s/;[^;]*$//'

    我们首先将月份名称附加到行末尾的数字转换表中（用 分隔;），然后使用正则表达式使用反向引用（为此我们需要 BRE，而不是 ERE）来查找给定月份名称的...\([A-Z][a-z][a-z]\)...;.*\1\([01][0-9]\)...数字，因此\1背面引用文本中捕获的月份名称，其后的两位数字以 结尾\6。

    然后我们删除翻译表。

    如果每一行可能有多个时间戳需要转换，则将其更改为：

    LC_ALL=C sed '
  s/$/;Jan01Feb02Mar03Apr04May05Jun06Jul07Aug08Sep09Oct10Nov11Dec12/
  :1
    s/[A-Z][a-z][a-z] \([A-Z][a-z][a-z]\) \([0-9][0-9]\) \([0-2][0-9]:[0-5][0-9]:[0-5][0-9]\) [A-Z]\{3,\} \([0-9]\{4\}\)\(.*;.*\1\([01][0-9]\)[^;]*\)$/\4-\6-\2 \3\5/
  t1
  s/;[^;]*$//'

    仅当成功替换时才分支到标签，这是t1在.:1sed

    对于任意无标头 CSV，仅重新格式化第一个字段：

    mlr --csv -N put '$1 = strftime(strptime($1, "%a %b %d %H:%M:%S %Z %Y"), "%F %T")'

    （改编自@Kusalananda对How can I Transform Dateexpressed with Month Names to numeric Month Designations? 的回答）。

    米勒strptime()会抱怨时间戳无法解码，但显然在字段为空的情况下不会。

    %Z不在标准strptime()认可的指令之列，但 GNU 实现至少识别并忽略它（并从输入中消耗；考虑到这些和 co 随着时间的推移和对不同的人有不同的含义，\s*\S*它对此无能为力）。EDT

    1 尽管某些sed实现（包括sed您可能在使用\sGNUism 时使用的 GNU）支持 ERE 以及标准的扩展。

    • 7

  2. 使用 GNU sed，您可以使用s///e修饰符来执行结果字符串：

    s/.*/date -d "&" +"%F %T"/e

    不过，比这更好的是使用 GNU date-f标志，它本身可以处理输入行，而不是为每一行生成一个新进程：

    $ TZ=UTC0 date -f /dev/stdin +'%F %T' <<<$'Mon Jul 03 14:48:54 EDT 2023\nTue, 04 Jul 2023 11:30:45 +0100'
2023-07-03 18:48:54
2023-07-04 10:30:45

    如果您无法信任输入，这也会更安全。

    • 7
  3. Best Answer

    你确实提到过：

    我正在尝试更改第二个字段中的日期格式。我还应该添加第二个字段有时根本没有填充。

    以下awk脚本将满足要求。将其另存为date.awk（感谢@EdMorton 的挑剔）：

    BEGIN {
  FS = OFS =","
  months = "JanFebMarAprMayJunJulAugSepOctNovDec" 
}

$2 != "" {
  split($2, date, / /)

  month = sprintf("%02d", (index(months, date[2]) + 2) / 3)
  $2 = sprintf("%04d-%02d-%02d %s", date[6], month, date[3], date[4])

  print
}

    然后awk使用脚本执行：

    awk -f date.awk input.csv

    原答案

    您可以使用date命令轻松更改日期格式。例如：

    $ date -d "Mon Jul 03 14:48:54 EDT 2023" +"%Y-%m-%d %H:%M:%S"
2023-07-03 14:48:54

    然后，您可以使用awk仅转换特定列（在本例中为$1）：

    awk 'BEGIN {FS=OFS=","} {"date -d \"" $1 "\" +\"%Y-%m-%d %H:%M:%S\"" | getline res; $1=res; print}' file.csv

    结果将是您当地时间，因此如果您想转换时区，只需TZ=EDT在 之前添加（或任何时区）即可date。

    然而，正如@StéphaneChazelas 在评论中提到的，如果行中的字段以某种方式包含恶意命令，则很容易受到命令注入的攻击，并且运行速度会很慢，因为它需要针对每一行sh执行date。

    • 4

  4. 如果考虑效率，最好使用脚本语言，因为不会过多调用外部命令。

    这是一个Python脚本示例，仅供参考

    from datetime import datetime
import re
import csv


def convert_datetime(dt):
    # as `EDT`` isn't in zoneinfo, it would need to be removed
    date_string = re.sub("(\w+ \w+ \d+ \d+:\d+:\d+) \w+ (\w+)", r"\1 \2", dt)
    date_obj = datetime.strptime(date_string, "%a %b %d %H:%M:%S %Y")
    return date_obj.strftime("%Y-%m-%d %H:%M:%S")


with open("original.csv", "r") as infile, open("processed.csv", "w") as outfile:
    reader = csv.reader(infile)
    writer = csv.writer(outfile)
    header = next(reader, None)
    if header:
        writer.writerow(header)
    for row in reader:
        # convert datetime in the second field
        try:
            row[1] = convert_datetime(row[1])
        except ValueError:
            pass
        writer.writerow(row)
    • 2

相关问题