Início / unix / Perguntas / 427301
Accepted
AfroJoe
Asked: 2018-03-01 12:35:42 +0800 CST

Transforme .CSV existente comparando a linha anterior com cálculos atuais e secundários

  • 772

Bom dia! Eu tenho um arquivo .CSV com 9 colunas. Preciso analisar seu conteúdo em um novo arquivo, realizar alguns pequenos cálculos e criar uma nova linha no processo. Acho que isso é melhor ilustrado mostrando o .CSV existente e a saída desejada:

Conteúdo .CSV existente (9 colunas)

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

Saída Desejada

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

Como você pode ver na saída desejada, preciso de algumas coisas novas:

  • Duplicar 1ª, 3ª e 5ª colunas da linha anterior se a próxima linha forNULL
  • Se a 5ª coluna na linha anterior for NULL, imprima N/Aem vez disso
  • Altere todas as entradas na 2ª coluna para Expense(de General Journal)
  • A 8ª coluna (Débito) é o valor existente menos 13%
  • Insira uma nova linha e calcule o imposto HST (13%)

O que eu fiz até agora? Pesquisei em todo o StackExchange e encontrei o seguinte ( deste 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
awk csv

1 respostas

  1. Best Answer

    Este awkscript tenta implementar as operações que você descreve:

    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 }

    Executando isso nos dados de exemplo que você forneceu:

    $ 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

    Isso não corresponde à saída esperada, mas como a saída esperada não estava de acordo com as especificações em primeiro lugar, não tentei interpretar muito isso.

    • 1

relate perguntas