[GNC] Date format for QIF file

Mon Jan 2 17:51:03 EST 2023

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.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/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: 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

------------------------------