CSV to gnucash via QIF with the help of awk

A simple task, i have a database with income information that is logged from a cash register. I have a number of columns that say the daily total, products sold, cash amount, credit card amounts etc. For accounting reasons i want to import this directly into GNU cash and avoid all manual entry. In the past i have use CSV/TXT formats but these cannot handle the split automatically.

In GNUCash i want to put the daily total in income, and transfer as a split to the cash account and the Accounts Receivable account to handle the money owed by the credit card company. CSV/TXT import can’t handle split but QIF can.

My example sql :-

select DATE(DATE), NETSALE_value,PO_1_value,CASH_IN_D_value+CHEQUE_IN_D_value,CHG1_IN_D_value from fin_report_Z1 where DATE>"2017-04-5" AND DATE<"2018-04-06" INTO OUTFILE '/tmp/blah.txt' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

This will produce a file in /tmp called blah.txt with a number of columns. the ones i care about here are NETSALES, PaidOut, Cash+Cheque total, and Credit card total. I want to send the Cash+Cheque total to the cash account and the credit card total to the A/R account, also i want to handle any paid out by transferring to a liabilities account. Next come AWK

BEGIN {
 FS=","
 print "!Account"
 print "NIncome:Sales:UK:Shop"
 print "D"
 print "TOth L"
 print "^"
 print "!Type:Oth L"
 }
 {
 print "D"$1
 print "T-"$2
 print "PShop End Of Day"
 print "SAssets:Accounts Receivable:FDMS Incomming"
 print "ECredit Card"
 print "$-"$5
 print "SCash"
 print "ECash"
 print "$-"$4
 if ($3!=0) {
     print "SLiabilities:PaidOut"
     print "EPaid Out"
     print "$-"$3
 }
 print "^"
 }

And to invoke the entire thing:-

!/bin/sh
 rm /tmp/blah.txt
 mysql -D stock -u username -p --batch < /root/exportsales.sql awk -f /root/exportsales.awk /tmp/blah.txt > 201718.qif

it will prompt your your mysql password then dump the finished qif in the current folder. Importing in to GNU cash is just using the qif importer, ensure the date format is correct then ensure account names match your layout and it works!