YNAB4 CSV Conversion Script

ynab-convert.sh

#!/usr/bin/env bash
set -e -u -o pipefail

fileType="$1"
file="$2"

case "$fileType" in
    deutsche-bank)
        # CSV Columns:
        # 1 = Booking date
        # 2 = Value date
        # 3 = Transaction Type
        # 4 = Beneficiary / Originator
        # 5 = Payment Details
        # 6 = IBAN
        # 7 = BIC
        # 8 = Customer Reference
        # 9 = Mandate Reference
        # 10 = Creditor ID
        # 11 = Compensation amount
        # 12 = Original Amount
        # 13 = Ultimate creditor
        # 14 = Number of transactions
        # 15 = Number of cheques
        # 16 = Debit
        # 17 = Credit
        # 18 = Currency
        awk -F';' 'NR > 4 { \
          if (NR == 5) { print "Date,Payee,Category,Memo,Outflow,Inflow"; } \
          else { sub("-", "", $16); print $1",\""$4"\",,\""$5"\",\""$16"\",\""$17"\""; } \
        }' "$file"
        ;;
    paypal)
        # CSV Columns:
        # 1 = Date
        # 2 = Time
        # 3 = Time zone
        # 4 = Name
        # 5 = Type
        # 6 = Status
        # 7 = Currency
        # 8 = Gross
        # 9 = Fee
        # 10 = Net
        # 11 = From email address
        # 12 = To email address
        # 13 = Transaction id
        # 14 = Counterparty status
        # 15 = Address status
        # 16 = Item title
        # 17 = Item id
        # 18 = Shipping and handling amount
        # 19 = Insurance amount
        # 20 = Sales tax
        # 21 = Option 1 name
        # 22 = Option 1 value
        # 23 = Option 2 name
        # 24 = Option 2 value
        # 25 = Auction site
        # 26 = Buyer id
        # 27 = Item url
        # 28 = Closing date
        # 29 = Escrow id
        # 30 = Invoice id
        # 31 = Reference transaction id
        # 32 = Invoice number
        # 33 = Custom number
        # 34 = Receipt id
        # 35 = Balance
        # 36 = Address line 1
        # 37 = Address line 2
        # 38 = Town/city
        # 39 = State/provice/region/country/territory
        # 40 = Postal code
        # 41 = Country
        # 42 = Contact phone number
        awk -F$'\t' 'BEGIN { outflow=""; inflow="" } { \
          if (NR == 1) { print "Date,Payee,Category,Memo,Outflow,Inflow"; next } \
          if ($10 ~ /^"-/) { outflow=$10; inflow=""; sub("-","",outflow) } \
          else { outflow=""; inflow=$10 } \
          { print $1 "," $4 ",\"\",\"\"," outflow "," inflow; } \
        }' "$file"
        ;;
    *)
        printf "Unrecognised type '%s'" "$fileType"
        exit 1
esac