I over looked format described by Wiki at 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.pa...@usa.net> Sent: Monday, January 02, 2023 5:18 PM To: 'm...@tgr66.me' <m...@tgr66.me> Cc: 'gnucash-user@gnucash.org' <gnucash-user@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/60 006.html> http://www.intuit.com/quicken/technical-support/quicken/old-faqs/dosfaqs/600 06.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%2 0Quicken%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: m...@tgr66.me <mailto:m...@tgr66.me> To: "gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org> " <gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org> > Subject: [GNC] Date format for QIF file Message-ID: <87b9505e-1fb3-4fec-8df8-bd49b9397...@tgr66.me <mailto:87b9505e-1fb3-4fec-8df8-bd49b9397...@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 ------------------------------ _______________________________________________ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: https://lists.gnucash.org/mailman/listinfo/gnucash-user ----- Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.