AWK格式化CSV文件| Unix | Solaris | awk [英] Awk to format csv files | unix | Solaris | awk

问题描述

我有多个csv文件，如下所示:

I have multiple csv files like below:

~/Prod/Jcs/BIN/Dash_PPLP/load$ ls -lt *csv
-rw-rw-r--   1 tellus   tellus        81 Sep  7 14:27 extraction_MBBSCS_PPL_USAGE_IMPORT.csv
-rw-rw-r--   1 tellus   tellus        83 Sep  7 14:27 extraction_MBBSCS_PPL_INVOICE_IMPORT.csv
-rw-rw-r--   1 tellus   tellus        71 Sep  7 14:27 extraction_INVOICE.csv
-rw-rw-r--   1 tellus   tellus        69 Sep  7 14:27 extraction_USGRERUN.csv
-rw-rw-r--   1 tellus   tellus        69 Sep  7 14:27 extraction_USG.csv
-rw-rw-r--   1 tellus   tellus        72 Sep  7 14:27 extraction_LIA.csv
-rw-rw-r--   1 tellus   tellus        74 Sep  7 14:27 extraction_MSISDN.csv

通过打开一个文件

cat extraction_LIA.csv
PPL_LIABILITY,2468705,Fri Sep 01 06:56:41 2017,Fri Sep 01 06:58:33 2017

我要监视的每个流的格式是名称，行，start_time和end_time，以使它们可加载"到ORACLE表中.

The format is name, rows, start_time and end_time for each flow I want to monitor, in order to make them "loadable" to an ORACLE table.

我制作了一个这样的脚本来进行转换并覆盖每个脚本，如下所示:

I have made a script like this to do the transform and overwrite them each one, like below:

cat transform_to_load.bash
#!/bin/bash
csv_files=$(ls *.csv)
for i in $csv_files
do
x=$(nawk 'BEGIN { OFS=","; FS=","} {split($3,a," ");split($3,b," ")}
{$3=a[3]"/"a[2]"/"a[5]" "a[4];$4=b[3]"/"b[2]"/"b[5]" "b[4]}
{print}' $i)
echo $x > $i
done

问题出在我的牙齿上

x=$(nawk 'BEGIN { OFS=","; FS=","} {split($3,a," ");split($3,b," ")}
    {$3=a[3]"/"a[2]"/"a[5]" "a[4];$4=b[3]"/"b[2]"/"b[5]" "b[4]}
    {print}' $i)

产生以下内容(开始时间与结束时间相同)

which produces the below (start time is the same as end time)

tellus@proetl01:~/Prod/Jcs/BIN/Dash_PPLP/load$ cat extraction_LIA.csv
PPL_LIABILITY,2468705,01/Sep/2017 06:56:41,01/Sep/2017 06:56:41

我想要实现的是对每个人分别使用nawk(SunOS)对其进行相应的格式化:

What I want to achieve is to format it accordingly with nawk (SunOS) like this for each one :

PPL_LIABILITY,2468705,01/Sep/2017 06:56:41,01/Sep/2017 06:58:33

您能帮我的忙吗输出正确的格式?

Could you please help me with my nawk to output the correct format?

非常感谢！

推荐答案

您已接近目标，需要纠正一点点

You're almost near to your aim, need to correct little bit

原因:

cause :

这是因为在您的代码中，您拥有

Its because in your code you have,

{split($3,a," "); split($3,b," ")}
                         ^
                     So you get same result in end time

按如下所示进行纠正

解决方案:

Solution :

{split($3,a," "); split($4,b," ")}
                         ^
                      Fourth Column will be used

同时，如果您有兴趣，可以像下面这样简化

Meanwhile if you are interested, you can simplify like below,

不需要

No need of

• csv_files=$(ls *.csv)
• x=$(nawk '{..}')
• echo $x > $i

• csv_files=$(ls *.csv)
• x=$(nawk '{..}')
• echo $x > $i

简化版

$ cat test.sh
#!usr/bin/env bash

for i in *.csv; do

# Better Prefer 
# /usr/xpg4/bin/awk or /usr/xpg6/bin/awk 

    nawk '
          BEGIN{
                FS=OFS=","
          }
          function format_dt(v, a){
              split($v,a,/ /); 
              $v=a[3]"/"a[2]"/"a[5]" "a[4]
          }
          { 
              format_dt(3); 
              format_dt(4) 
          }1
         ' "$i" >tmpfile && mv tmpfile "$i"
done

这篇关于AWK格式化CSV文件| Unix | Solaris | awk的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！