文章/答案/技术大牛

发布

社区首页 >问答首页 >在Linux (UBUNTU)上使用AWK将csv文件中的格式从%d/%m/%y改为%Y-%m-%d

问在Linux (UBUNTU)上使用AWK将csv文件中的格式从%d/%m/%y改为%Y-%m-%d
EN

Stack Overflow用户

提问于 2016-09-25 09:22:23

回答 2查看 123关注 0票数 0

我有数百个日期格式为%d / %m / %y %H:%M:%S的csv文件，但我想将它们更改为%Y-%m-%d %H:%M:%S格式

INPUT_FILE.csv (date format == %d/%m/%y %H:%M:%S )
13/05/87 4:00:00,1.27470,1.27530,1.27460,1.27480,101926,356
14/05/87 4:01:00,1.27490,1.27520,1.27310,1.27490,102419,493
15/05/87 4:02:00,1.27490,1.27540,1.27440,1.27530,102846,427
16/05/87 4:03:00,1.27520,1.27570,1.27490,1.27550,103202,356
17/05/87 4:04:00,1.27550,1.27640,1.27510,1.27590,103528,326
......
......
......
24/02/09 4:00:00,1.27470,1.27530,1.27460,1.27480,101926,356
25/02/09 4:01:00,1.27490,1.27520,1.27310,1.27490,102419,493
26/02/09 4:02:00,1.27490,1.27540,1.27440,1.27530,102846,427
27/02/09 4:03:00,1.27520,1.27570,1.27490,1.27550,103202,356
28/02/09 4:04:00,1.27550,1.27640,1.27510,1.27590,103528,326


REQUIRED_OUTPUT.csv   (date format == %Y-%m-%d  %H:%M:%S )

1987-05-13 4:00:00,1.27470,1.27530,1.27460,1.27480,101926,356
1987-05-14 4:01:00,1.27490,1.27520,1.27310,1.27490,102419,493
1987-05-15 4:02:00,1.27490,1.27540,1.27440,1.27530,102846,427
1987-05-16 4:03:00,1.27520,1.27570,1.27490,1.27550,103202,356
1987-05-17 4:04:00,1.27550,1.27640,1.27510,1.27590,103528,326
......
......
......   
2009-02-24 4:00:00,1.27470,1.27530,1.27460,1.27480,101926,356
2009-02-25 4:01:00,1.27490,1.27520,1.27310,1.27490,102419,493
2009-02-26 4:02:00,1.27490,1.27540,1.27440,1.27530,102846,427
2009-02-27 4:03:00,1.27520,1.27570,1.27490,1.27550,103202,356
2009-02-28 4:04:00,1.27550,1.27640,1.27510,1.27590,103528,326

我尝试了几个AWK变体，但我无法使它工作。任何帮助

更新:我的错误，我应该提到从1981年到2016的日期或年份。

以下是我到目前为止尝试过的：

awk -F, '{ gsub("/","-");  split($1, f, " "); print >  ("my_data_" f[1]"v" ".csv")}' INPUT_FILE.csv

我把文件分成几个部分

my_data_13-05-87v.csv
my_data_14-05-87v.csv
my_data_15-05-87v.csv

的文件内容如下

# for my_data_13-05-87v.csv
13-05-87 4:00:00,1.27470,1.27530,1.27460,1.27480,101926,356

# for my_data_14-05-87v.csv
14-05-87 4:01:00,1.27490,1.27520,1.27310,1.27490,102419,493

# for my_data_15-05-87v.csv
15-05-87 4:02:00,1.27490,1.27540,1.27440,1.27530,102846,427

注:从1981年的到2016年的

我想把文件分成几个部分

my_data_1987-05-13v.csv
my_data_1987-05-13v.csv
my_data_1987-05-13v.csv

的文件内容如下

# for my_data_1987-05-13v.csv
1987-05-13 4:00:00,1.27470,1.27530,1.27460,1.27480,101926,356

# for my_data_1987-05-14v.csv
1987-05-14 4:01:00,1.27490,1.27520,1.27310,1.27490,102419,493

# for my_data_1987-05-15v.csv
1987-05-15 4:02:00,1.27490,1.27540,1.27440,1.27530,102846,427

第二个格式问题:我也有一个不同的格式

 INPUT_FILE.csv (date format == %d.%m.%y %H:%M:%S )
 13.05.1987 4:00:00.000,1.27470,1.27530,1.27460,1.27480,101926,356
 14.05.1987 4:01:00.000,1.27490,1.27520,1.27310,1.27490,102419,493
 15.05.1987 4:02:00.000,1.27490,1.27540,1.27440,1.27530,102846,427
 16.05.1987 4:03:00.000,1.27520,1.27570,1.27490,1.27550,103202,356
 17.05.1987 4:04:00.000,1.27550,1.27640,1.27510,1.27590,103528,326


 REQUIRED_OUTPUT.csv   (date format == %Y-%m-%d  %H:%M:%S )

 1987-05-13 4:00:00.000,1.27470,1.27530,1.27460,1.27480,101926,356
 1987-05-14 4:01:00.000,1.27490,1.27520,1.27310,1.27490,102419,493
 1987-05-15 4:02:00.000,1.27490,1.27540,1.27440,1.27530,102846,427
 1987-05-16 4:03:00.000,1.27520,1.27570,1.27490,1.27550,103202,356
 1987-05-17 4:04:00.000,1.27550,1.27640,1.27510,1.27590,103528,326

linux

bash

csv

awk

回答 2

Stack Overflow用户

回答已采纳

发布于 2016-09-25 14:01:34

您只需要将输入字段分隔符重新定义为/，并重新排序前三个字段。另外，如果年份字段有一个值>16，则假设20世纪，否则是21世纪。同时，它将这些行写入到按日期命名的文件中：

$ cat script.awk
{
    print ($3>16?"19":"20") $3 "-" $2 "-" $1, $4 > my_data_$1"-"$2"-"$3".csv"
}

运行它：

$ awk -F'[/ ]' -f script.awk INPUT_FILE.csv

票数 1

Stack Overflow用户

发布于 2016-09-25 09:39:51

一种简单的方法是使用substr

$ awk '{ $1 = sprintf("20%s-%s-%s",
                      substr($1, 7, 2),
                      substr($1, 4, 2),
                      substr($1, 1, 2))
       } 1' input.csv
2009-02-24 4:00:00,1.27470,1.27530,1.27460,1.27480,101926,356
2009-02-25 4:01:00,1.27490,1.27520,1.27310,1.27490,102419,493
2009-02-26 4:02:00,1.27490,1.27540,1.27440,1.27530,102846,427
2009-02-27 4:03:00,1.27520,1.27570,1.27490,1.27550,103202,356
2009-02-28 4:04:00,1.27550,1.27640,1.27510,1.27590,103528,326

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/39685282

复制

相似问题

问在Linux (UBUNTU)上使用AWK将csv文件中的格式从%d/%m/%y改为%Y-%m-%d
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Linux (UBUNTU)上使用AWK将csv文件中的格式从%d/%m/%y改为%Y-%m-%dEN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Linux (UBUNTU)上使用AWK将csv文件中的格式从%d/%m/%y改为%Y-%m-%d
EN