-1

Good day! I have a .CSV file that has 9 columns. I need to parse its contents into a new file, perform some small calculations and create a new row in the process. I think this is best illustrated by showing the existing .CSV and the desired output:

Existing .CSV Contents (9 columns)

$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
,,,,,,,,

Desired Output

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

As you can see in the desired output I need a few new things:

  • Duplicate 1st, 3rd, 5th columns from previous row if next row is NULL
  • If 5th column in previous row is NULL then print N/A instead
  • Change all entries in 2nd column to Expense (from General Journal)
  • The 8th column (Debit) is the existing value minus 13%
  • Insert a new row and calculate the HST tax (13%)

What have I done so far? I've search all throughout StackExchange and came up with the following (from this post:)

    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
    your "desired" output looks arbitrary and does not fit your conditions. Here are only some redlines: - no N/A values; there are duplicates of Utilities,Utilities; - percent calculations are also doubtful. Conclusion: the current "desired" output is not representative – RomanPerekhrest Feb 28 '18 at 21:01
1

This awk script attempts to implement the operations that you describe:

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 }

Running this on the example data that you have supplied:

$ 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

This does not correspond to your expected output, but since your expected output was not in accordance with the specifications in the first place, I didn't try to interpret that too much.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.