#!/usr/bin/awk -f 
# Documentation is at the end of this file.

BEGIN {
    FS = "\t"
    tid = 1
    getline
    printf "Date,Transaction ID,Number,Description,Notes,Commodity/Currency,"
    printf "Void Reason,Action,Memo,Full Account Name,-Account Name,-Amount With Sym,"
    printf "Amount Num.,-Value With Sym,Value Num.,Reconcile,Reconcile Date,Rate/Price\n"
}

function dotrans() {
    date = $1
    while ($5 != "") {
        if ($6 != "") {
            raw = $6 + 0
            num = -raw
            sym = sprintf("-$%s", raw)
        }
        else if ($7 != "") {
            raw = $7 + 0
            num = raw
            sym = sprintf("$%s", raw)
        }
        else {
            print
            print "INVALID SPLIT - NEITHER CREDIT NOR DEBIT VALUE"
            exit
        }
        if ($8 == "R") rec = "y"
        else if ($8 == "C") rec = "c"
        else rec = "n"
        printf "%s,%d,%s,%s,,CURRENCY::USD,,,%s,%s,%s,%s,%s,%s,%s,%s,,1\n", \
            date, tid, $2, name, $4, $5, $5, sym, num, sym, num, rec
        getline
    }
}

length($5) > 0 {
    date = $1
    # If field 3 (name) is empty, give it something. Otherwise, the transaction will be combined
    # with the previous one, corrupting both.
    name = ($3 == "") ? "UNNAMED" : $3
    dotrans()
    tid++
}

#;++
# TITLE
#       qbo2gc.awk 1 $Date::
#
# NAME
#       qbo2gc.awk
#
# SUMMARY
#       convert QuickBooks Online journal data to CSV for import to GnuCash
#
# SYNOPSIS
#       qbo2gc.awk FILE
#
# DESCRIPTION
#       This script converts journal data from QuickBooks Online (QBO) to a format suitable for
#       importation into GnuCash.
#       
#       FILE is a CSV file generated from the QBO journal data. Here is the procedure for creating
#       FILE:
#       
#       1. In QBO, configure a journal report with only the following columns in the specified
#       order:
#       
#               Date, Num, Name, Memo/Description, Account, Credit, Debit, Clr
#       
#       2. Remove header and footer fields (or manually remove them from the output file).
#       
#       3. Run the report and export it to Excel.
#       
#       4. Open the Excel file (I used LibreOffice for this). Remove the header and footer, if
#       present. Remove the first column which is blank except for the Total line at the
#       bottom. Remove that Total line as well. Leave all other blank lines between transactions as
#       they are used as delimiters.
#       
#       5. IMPORTANT! Examine the data for any malformed transactions and either fix or delete any
#       that you find. Things to look for:
#       
#               * any transaction split with nothing in the Account column
#               * any transaction split with nothing in both Credit and Debit
#       
#       6. IMPORTANT! Pick a field separator to use for FILE. This script assumes TAB but you can
#       use something else. Whatever you pick, find and replace/delete all instances of it in the
#       spreadsheet, making sure not to corrupt any meaningful data. If you end up using something
#       other than TAB, modify the FS line in the BEGIN block above to match it.
#       
#       7. Save/export the file as CSV using the selected field separator. In LibreOffice, this is
#       done with "Save As...", setting the file type to CSV, and selecting "Edit filter settings".
#       
#       This script generates CSV data, including a header line, to standard output. Save it to a
#       file.
#       
#       To import the transactions into GnuCash:
#       
#       1. Select File> Import> Import Transactions from CSV...
#       
#       2. Load the GnuCash Export Format settings.
#       
#       3. Set the Date Format to m-d-y.
#       
#       4. Be sure to skip the header line if not set by default.
#       
#       5. On the next screen, make sure all incoming accounts (ID) are matched with a corresponding
#       GnuCash account. Create new ones as needed.
#       
#       6. If you get to the next screen without issue, then cross your fingers and proceed. Good
#       luck!
#       
#       Here are some personal observations on the process of transferring data from QBO to GC.
#       
#       Once I got this script working and data issues resolved, I was able to import data into GC
#       with one major issue. My data spanned 2 years. The second (current) year imported cleanly,
#       but transactions from the first (previous) year required manual account matching for
#       splits. Changing the accounting period in GC had no effect. I have no explanation for this.
#       
#       Also consider transferring your account tree from QBO to GC instead of recreating it
#       manually. I did that manually and don't recall details, but it was relatively
#       painless. Another script might simplify the process.
#
# AUTHOR
#       Tom Olin
#;--