[GNC] Date format for QIF file
ml at tgr66.me
ml at tgr66.me
Mon Jan 2 18:59:18 EST 2023
Thanks Kalpesh—
I’m on a Mac and Quicken doesn’t have a QIF export, only CSV.
I’m writing a NodeJS app/lib to convert csv exports to QIF. The dates in the csv are consistently M/D/YYYY.
I can export non investment accounts to QMTF which is a QIF but certain fields (like tags) aren’t included so I’ll be working that one too.
This migration might take most of this year to accomplish :-)
> On Jan 2, 2023, at 16:51, Kalpesh Patel <kalpesh.patel at usa.net> wrote:
>
> I over looked format described by Wiki at https://en.wikipedia.org/wiki/Quicken_Interchange_Format <https://en.wikipedia.org/wiki/Quicken_Interchange_Format> and it denoted that in date field leading zeroes on month and day can be skipped and year can be either 4 digits or 2 digits or '6 (=2006).
>
> From: Kalpesh Patel <kalpesh.patel at usa.net>
> Sent: Monday, January 02, 2023 5:18 PM
> To: 'ml at tgr66.me' <ml at tgr66.me>
> Cc: 'gnucash-user at gnucash.org' <gnucash-user at gnucash.org>
> Subject: RE: Date format for QIF file
>
> Tim –
>
> This might be a bit late (I had to hunt down old Quicken QIF migration files that I did back in 2020 to check), nonetheless I figure I respond in case if there is any value.
>
> If you are exporting out data in QIF format from Quicken (Windows 2017 version but this is likely to be true for any Quicken version), the format of the date field is all over the place and it will definitely get barfed by GNC QIF importer.
>
> Bunch of *NIX shell sed commands worked like a charm to normalize them into consistent form of MM/dd/yyyy so that GNC would be happy (here is script that did the dirty work – modified to include for year 2021 and 2022):
>
>
>
> #!/bin/bash
> # first parameter to the script is the file name that needs to be normalized and will be modified in place.
>
> iam=${1}
>
> if [ -z ${iam} ] ; then
> echo “Please pass in the file name as the first parameter.”
> exit 255
> fi
>
> if [ -e ${iam} ] ; then
> echo “${iam} does not exist. Please provide a file that already exist.”
> exit 254
> fi
>
> cat $(iam} > ${iam}.bak
>
>
> #-##cat $iam | while read a ; do
> #-##
> #-##case $a in
> #-## *\'[0-2][0-9]*)
> #-## echo $a |sed -e "s+'+/20+g" ;;
> #-## *\'[3-9][0-9]*)
> #-## echo $a |sed -e "s+'+/19+g" ;;
> #-## *\'\ [0-9]*)
> #-## echo $a |sed -e "s+' +/200+g" ;;
> #-## */[9][0-9]*)
> #-## echo $a |sed -e "s+/9+/199+g" ;;
> #-## */[8][0-9]*)
> #-## echo $a |sed -e "s+/8+/198+g" ;;
> #-## */[7][0-9]*)
> #-## echo $a |sed -e "s+/7+/197+g" ;;
> #-## *)
> #-## echo $a ;;
> #-## esac
> #-##
> #-##done >> ${iam}_mod
>
> set -x
>
> sed -i.tmp -e "s+' 0+/2000+g" ${iam}
> sed -i.tmp -e "s+' 1+/2001+g" ${iam}
> sed -i.tmp -e "s+' 2+/2002+g" ${iam}
> sed -i.tmp -e "s+' 3+/2003+g" ${iam}
> sed -i.tmp -e "s+' 4+/2004+g" ${iam}
> sed -i.tmp -e "s+' 5+/2005+g" ${iam}
> sed -i.tmp -e "s+' 6+/2006+g" ${iam}
> sed -i.tmp -e "s+' 7+/2007+g" ${iam}
> sed -i.tmp -e "s+' 8+/2008+g" ${iam}
> sed -i.tmp -e "s+' 9+/2009+g" ${iam}
> sed -i.tmp -e "s+'10+/2010+g" ${iam}
> sed -i.tmp -e "s+'11+/2011+g" ${iam}
> sed -i.tmp -e "s+'12+/2012+g" ${iam}
> sed -i.tmp -e "s+'13+/2013+g" ${iam}
> sed -i.tmp -e "s+'14+/2014+g" ${iam}
> sed -i.tmp -e "s+'15+/2015+g" ${iam}
> sed -i.tmp -e "s+'16+/2016+g" ${iam}
> sed -i.tmp -e "s+'17+/2017+g" ${iam}
> sed -i.tmp -e "s+'18+/2018+g" ${iam}
> sed -i.tmp -e "s+'19+/2019+g" ${iam}
> sed -i.tmp -e "s+'20+/2020+g" ${iam}
> sed -i.tmp -e "s+'21+/2021+g" ${iam}
> sed -i.tmp -e "s+'22+/2022+g" ${iam}
>
> sed -i.tmp -e "s+/81+/1981+g" ${iam}
> sed -i.tmp -e "s+/82+/1982+g" ${iam}
> sed -i.tmp -e "s+/83+/1983+g" ${iam}
> sed -i.tmp -e "s+/84+/1984+g" ${iam}
> sed -i.tmp -e "s+/85+/1985+g" ${iam}
> sed -i.tmp -e "s+/86+/1986+g" ${iam}
> sed -i.tmp -e "s+/87+/1987+g" ${iam}
> sed -i.tmp -e "s+/88+/1988+g" ${iam}
> sed -i.tmp -e "s+/89+/1989+g" ${iam}
> sed -i.tmp -e "s+/90+/1990+g" ${iam}
> sed -i.tmp -e "s+/91+/1991+g" ${iam}
> sed -i.tmp -e "s+/92+/1992+g" ${iam}
> sed -i.tmp -e "s+/93+/1993+g" ${iam}
> sed -i.tmp -e "s+/94+/1994+g" ${iam}
> sed -i.tmp -e "s+/95+/1995+g" ${iam}
> sed -i.tmp -e "s+/96+/1996+g" ${iam}
> sed -i.tmp -e "s+/97+/1997+g" ${iam}
> sed -i.tmp -e "s+/98+/1998+g" ${iam}
> sed -i.tmp -e "s+/99+/1999+g" ${iam}.
>
> # End of Script
>
>
>
> For what it is worth, there is an actual documentation that Intuit (predecessor owner and original publisher of the QIF file format) published it in 1997 at http://www.intuit.com/quicken/technical-support/quicken/old-faqs/dosfaqs/60006.html <http://www.intuit.com/quicken/technical-support/quicken/old-faqs/dosfaqs/60006.html> which no longer exists but cached copy by Google can be found at https://www.w3.org/2000/10/swap/pim/qif-doc/QIF-doc.htm#:~:text=A%3A%20The%20Quicken%20interchange%20format,that%20supports%20the%20QIF%20format <https://www.w3.org/2000/10/swap/pim/qif-doc/QIF-doc.htm#:~:text=A%3A%20The%20Quicken%20interchange%20format,that%20supports%20the%20QIF%20format>. In the documentation you will notice that there is no definition of what the format of the date should be so depending on the transaction date, Intuit decided to implement it two different ways for date field:
>
> - 2000 and after years, the format is “D3/ 1' 8” (March one two thousand eight – they were space filled to make it two digits for day and year but not for month and a forward tick was used to mark that it was year 2000 and afterwards)
> - before year 2000, the format is “D3/25/97” (March twenty five nineteen nighty seven – the month day and year were all simply broken up by a slash).
>
> The rollover of the format happens for example from D12/23/99 to D3/24' 0 when y2k hit. GNC did not mind spaces in any field and it simply ignored them so the script above made minimal change to be able to import the modified QIF file into GNC.
>
> When prompted by GNC during QIF import, you can tell it that it is in M/D/Y format and it will go through fine.
>
>
>
>
>
>
>
> ------------------------------
> Message: 3
> Date: Thu, 29 Dec 2022 12:33:50 -0600
> From: ml at tgr66.me <mailto:ml at tgr66.me>
> To: "gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org>" <gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org>>
> Subject: [GNC] Date format for QIF file
> Message-ID: <87B9505E-1FB3-4FEC-8DF8-BD49B939702F at tgr66.me <mailto:87B9505E-1FB3-4FEC-8DF8-BD49B939702F at tgr66.me>>
> Content-Type: text/plain; charset=utf-8
>
> I?m developing QIF files to import my old investments. I get asked every time to confirm the date format (the entire import process is a bit long imo). The QIF specification says I ought to be able to enter a date using `dd month year`; for example: 29 December 2022. However, this results in an error in GnuCash.
>
> Is there a format I can use in the files that GnuCash will accurately autodetect the correct format?
>
> Thanks.
>
> Tim
>
> ------------------------------
More information about the gnucash-user
mailing list