主页 / user-230070

AfroJoe's questions

Martin Hope
AfroJoe
Asked: 2018-03-01 12:35:42 +0800 CST
  • -1

再会！我有一个包含 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

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

  • 如果下一行是，则从上一行复制第 1、3、5 列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
awk csv