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 <[email protected]> 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 <[email protected]> > Sent: Monday, January 02, 2023 5:18 PM > To: '[email protected]' <[email protected]> > Cc: '[email protected]' <[email protected]> > 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: [email protected] <mailto:[email protected]> > To: "[email protected] <mailto:[email protected]>" > <[email protected] <mailto:[email protected]>> > Subject: [GNC] Date format for QIF file > Message-ID: <[email protected] > <mailto:[email protected]>> > 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 [email protected] 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.
