Migrate transaction data from QuickBooks Online to GnuCash

qbo2gc.awk

#!/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 (norec == "") {
	    if ($8 == "R" || $8 == "C") rec = "c"
	    else rec = "n"
	}
	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
    }
}

$5 != "" {
    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 [-v norec=x] 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. (NOTE: A key thing
#	to watch for here are commas in the credit/debit fields. If you use anything other than
#	comma as your delimiter, then you can use a text editor to search the output file for any
#	commas.)
#	
#	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 (comma-separated) 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. On the subsequent screen, make sure all transactions matched cleanly (green). Fix any
#	unmatched ones (red).
#	
# OPTIONS
#	-v norec=x
#		By default, the reconciliation flag from QBO is imported (but see NOTES
#		below). Setting the variable norec to any non-null string will cause the script to
#		ignore reconciliation values.
#
# NOTES
#	Currently, it is unclear how to mark an imported transaction as reconciled. If the imported
#	reconciliation field is "y", then account matching fails and must be done manually.
#	Therefore, any QBO transaction marked reconciled (R) or cleared (C) is imported as cleared
#	(c) only. See OPTIONS for how to ignore reconciliation entirely.
#	
#	awk is a Linux command. Windows users should be able to access it via the Windows Subsystem
#	for Linux (WSL), although this script has not been tested with WSL.
#
# AUTHOR
#	Tom Olin (tomolin.net)
#	Thanks to Vincent Dawans for debugging help!
#;--