☰

通过将前一行与当前和次要计算进行比较来转换现有的 .CSV

tag-icon • tag-icon awk csv
通过将前一行与当前和次要计算进行比较来转换现有的 .CSV

再会！我有一个包含 9 列的 .CSV 文件。我需要将其内容解析到一个新文件中，执行一些小计算并在此过程中创建一个新行。我认为最好通过显示现有的 .CSV 和所需的输出来说明这一点：

现有 .CSV 内容（9 列）

$cat file
Trans #,Type,Date,Num,Name,Memo,Account,Debit,Credit
1321,General Journal,1/4/2013,1127,,Consulting,Cash on Hand,,20.61
,,,,,,IT Services,20.61,
,,,,,,,20.61,20.61
,,,,,,,,
1322,General Journal,7/3/2013,1128,,Utilities,Cash on Hand,,105.5
,,,,,,Utilities,105.5,
,,,,,,,105.5,105.5
,,,,,,,,
1323,General Journal,4/3/2013,1129,,Bell,Cash on Hand,,466.69
,,,,,,Telephone,466.69,
,,,,,,,466.69,466.69
,,,,,,,,
1324,General Journal,1/3/2013,1130,,Consulting,Cash on Hand,,20.61
,,,,,,IT Services,20.61,
,,,,,,,20.61,20.61
,,,,,,,,
1325,General Journal,6/3/2013,1131,,Utilities,Cash on Hand,,79.09
,,,,,,Utilities,79.09,
,,,,,,,79.09,79.09
,,,,,,,,

所需输出

Trans #,Type,Date,Num,Name,Memo,Account,Debit,Credit
1321,Expense,1/4/2013,1127,0,Consulting,Cash on Hand,,20.61
1321,Expense,1/4/2013,1127,0,Consulting,IT Services,18.24,
1321,Expense,1/4/2013,1127,0,Consulting,HST - Input tax,2.37,
1321,Expense,1/4/2013,1127,0,,,20.61,20.61
1322,Expense,7/3/2013,1128,0,Utilities,Cash on Hand,,105.5
1322,Expense,7/3/2013,1128,0,Utilities,Utilities,93.36,
1322,Expense,7/3/2013,1128,0,Utilities,HST - Input tax,12.14,
1322,Expense,7/3/2013,1128,0,,,105.50,105.5
1323,Expense,4/3/2013,1129,0,Bell,Cash on Hand,,466.69
1323,Expense,4/3/2013,1129,0,Bell,Telephone,413.00,
1323,Expense,4/3/2013,1129,0,Bell,HST - Input tax,53.69,
1323,Expense,4/3/2013,1129,0,,,466.69,466.69
1324,Expense,1/3/2013,1130,0,Consulting,Cash on Hand,,20.61
1324,Expense,1/3/2013,1130,0,Consulting,IT Services,18.24,
1324,Expense,1/3/2013,1130,0,Consulting,HST - Input tax,2.37,
1324,Expense,1/3/2013,1130,0,,,20.61,20.61
1325,Expense,6/3/2013,1131,0,Utilities,Cash on Hand,,79.09
1325,Expense,6/3/2013,1131,0,Utilities,Utilities,69.99,
1325,Expense,6/3/2013,1131,0,Utilities,HST - Input tax,9.10,
1325,Expense,6/3/2013,1131,0,,,79.09,79.09

正如您在所需的输出中看到的，我需要一些新东西：

  • 如果下一行是，则复制上一行的第一、第三、第五列NULL
  • 如果前一行的第 5 列是NULL则打印N/A则
  • 将第二列中的所有条目更改为Expense（来自General Journal）
  • 第 8 列（借方）是现有值减13%
  • 插入新行并计算 HST 税 (13%)

到目前为止我做了什么？我搜索了整个 StackExchange 并提出了以下内容（从这篇文章:)

    awk '{
       split($0,D,/[^[:space:]]*/);
       s = "";
       for(i=1;i<=NF;i++){ 
            if($i~/NoData/){ $i =  last[i]; } 
            last[i]=$i ; 
            s = s  sprintf("%s%s",D[i],$i) 
       }  
       print s
 }' file

答案1

这awk脚本尝试实施您描述的操作：

BEGIN   { OFS = FS = "," }

NR == 1 { name = "N/A" }

NR > 1 {
    # use values from previous row if missing
    if ($1 == "") $1 = trans
    if ($3 == "") $3 = date
    if ($5 == "") $5 = name

    $2 = "Expense"

    $9 = 0.13 * $8
    $8 -= $9

    # set values that may be used by the next row
    trans = $1
    date  = $3
    name  = ($5 == "" ? "N/A" : $5)
}

{ print }

在您提供的示例数据上运行此命令：

$ awk -f script.awk file.csv
Trans #,Type,Date,Num,Name,Memo,Account,Debit,Credit
1321,Expense,1/4/2013,1127,N/A,Consulting,Cash on Hand,0,0
1321,Expense,1/4/2013,,N/A,,IT Services,17.9307,2.6793
1321,Expense,1/4/2013,,N/A,,,17.9307,2.6793
1321,Expense,1/4/2013,,N/A,,,0,0
1322,Expense,7/3/2013,1128,N/A,Utilities,Cash on Hand,0,0
1322,Expense,7/3/2013,,N/A,,Utilities,91.785,13.715
1322,Expense,7/3/2013,,N/A,,,91.785,13.715
1322,Expense,7/3/2013,,N/A,,,0,0
1323,Expense,4/3/2013,1129,N/A,Bell,Cash on Hand,0,0
1323,Expense,4/3/2013,,N/A,,Telephone,406.02,60.6697
1323,Expense,4/3/2013,,N/A,,,406.02,60.6697
1323,Expense,4/3/2013,,N/A,,,0,0
1324,Expense,1/3/2013,1130,N/A,Consulting,Cash on Hand,0,0
1324,Expense,1/3/2013,,N/A,,IT Services,17.9307,2.6793
1324,Expense,1/3/2013,,N/A,,,17.9307,2.6793
1324,Expense,1/3/2013,,N/A,,,0,0
1325,Expense,6/3/2013,1131,N/A,Utilities,Cash on Hand,0,0
1325,Expense,6/3/2013,,N/A,,Utilities,68.8083,10.2817
1325,Expense,6/3/2013,,N/A,,,68.8083,10.2817
1325,Expense,6/3/2013,,N/A,,,0,0

这与您的预期输出不符，但由于您的预期输出一开始就不符合规范，因此我没有尝试对其进行过多解释。

相关内容