Quicken QFX file import to SQL Server

  • sgmunson

    SSC Guru

    Points: 110433

    Someone must be doing this successfully somewhere... it wasn't more than a couple of years ago that I ran across a company that had written code to sync up Quick Books (multi-user version only) data with SQL Server. All I need is a way to parse the "not quite SGML" that a QFX file contains.

    Anyone? Pretty please with sugar on top?

    Steve

    (aka smunson)

    :):):)

    Steve
    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

  • sgmunson

    SSC Guru

    Points: 110433

    BUMP...

    No one has ever imported a Quicken QFX file into SQL Server? I did manage to find a database program for MAC OS X that will import such files into it, but then I'd need a MAC computer, which isn't a practical solution. There doesn't appear to be a Windows version of the program. Any and all assistance will be most appreciated...

    Steve

    (aka smunson)

    :):):)

    Steve
    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88053

    AFAIK - the QFX format is proprietary and banks actually have to pay Intuit for the specs. Not sure you are going to find anything available that is free.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • sgmunson

    SSC Guru

    Points: 110433

    Ok, I've found SX.exe, which can at least output well-formed XML. Unfortunately, trying to import it's resultant XML is still a problem, as while all the tags are now closed, they contain unnecessary levels and multiple content types (both text and numbers), and that is apparently not ok with SQL Server. Thus I'm wondering if anyone has any solution to "flatten" the XML, such that it retains only the nodes that contain values. Perhaps then I can at least get it into Excel, if not SQL Server...

    I've seen at least two examples of XSL code that was written to flatten a specific example of XML, but I have no idea how you actually get that transformation to take place. Can someone help out with that? Thanks in advance for any / all assistance.

    Steve

    (aka smunson)

    :):):)

    Steve
    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

  • dtcalif-297369

    SSC Enthusiast

    Points: 167

    I am in the process of creating a simple routine which will parse the .QFX files that I download from my bank (Bank of America). I have the entire .QFX specification at home (there are multiple revisions) and can send you that later. Note that the specification allows for extensibility and the banks all seem to make their own tweaks to the format. The routine I make will not be sophisticated and will probably be written in VB.NET or maybe even VBA, since I will be using Excel to display the resultant financial data.

  • sgmunson

    SSC Guru

    Points: 110433

    Fantastic! I'll look forward to seeing what you have. Thanks!

    Steve

    (aka smunson)

    :-):-):-)

    dtcalif (8/12/2009)


    I am in the process of creating a simple routine which will parse the .QFX files that I download from my bank (Bank of America). I have the entire .QFX specification at home (there are multiple revisions) and can send you that later. Note that the specification allows for extensibility and the banks all seem to make their own tweaks to the format. The routine I make will not be sophisticated and will probably be written in VB.NET or maybe even VBA, since I will be using Excel to display the resultant financial data.

    Steve
    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

  • dtcalif-297369

    SSC Enthusiast

    Points: 167

    Everything you ever wanted to know about .OFX (QFX) files is available at:

    http://www.ofx.net/DownloadPage/Downloads.aspx

    Bank of America apparently uses version 1.0.2 for their files.

    Here's some very simple, but effective VBA code for the import of Bank of America .QFX files into SQL Server.

    ADDENDUM: SORRY, I CANNOT POST THE CODE AS WRITTEN TO SQLSERVERCENTRAL. I GUESS YOU CAN'T PLACE CERTAIN CHARACTERS SUCH AS A GREATER-THAN SIGN IN A POST. THE CASE STATEMENTS ARE LOOKING FOR LT AND GT SIGNS AND SOME OFX FIELD IDENTIFIERS. I'VE REPLACED LESS-THAN WITH {LT} AND GREATER-THAN WITH {GT}.

    ' This code module is used to process QFX files for import

    Sub Import_QFX_Files(fileToProcess)

    'MsgBox "Here's where you process files!"

    Const fsoForWriting = 2

    Const fsoForAppend = 8

    Const fsoForReading = 1

    '******************************************************

    Dim ServerName As String

    Dim mksConnectionString As String

    ServerName = "PHENOM\SQLEXPRESS"

    mksConnectionString = "Provider=SQLOLEDB;Data Source=" & ServerName & ";Initial Catalog=QIF;User ID=sa; Password=; Trusted_Connection=yes"

    Dim cn As New ADODB.Connection

    Dim rs As New ADODB.Recordset

    Dim sSql As String

    Dim strA As String

    Dim fso As New Scripting.FileSystemObject

    Dim txtFile As Scripting.TextStream

    Dim C, N, P, A1, A2, A3, L, S, E As String

    Dim S_M As String

    Dim D As Date

    Dim T As Currency

    Dim A_S1 As Currency

    Dim A_S2 As Currency

    Dim A2_flag, A3_flag, S_flag, A_S_flag As Boolean

    '--------------------

    Dim ORG As String

    Dim FID As String

    Dim BANKID As String

    Dim ACCTID As String

    Dim ACCTTYPE As String

    Dim TRANTYPE As String

    Dim DTPOSTED As Date

    Dim DTUSER As Date

    Dim TRNAMT As Currency

    Dim FITID As String

    Dim NAME As String

    Dim MEMO As String

    'ResetVariables 'sub

    T = 0

    C = ""

    N = ""

    P = ""

    A1 = ""

    A2 = ""

    A3 = ""

    L = ""

    S = ""

    E = ""

    A_S1 = 0

    S_M = ""

    A_S2 = 0

    '------------

    ORG = ""

    FID = ""

    BANKID = ""

    ACCTID = ""

    ACCTTYPE = ""

    TRANTYPE = ""

    DTPOSTED = "12/31/2040"

    DTUSER = "12/31/2040"

    TRNAMT = 0

    FITID = ""

    NAME = ""

    MEMO = ""

    'reset the flags used

    A2_flag = 0

    A3_flag = 0

    S_flag = 0

    A_S_flag = 0

    With cn

    .ConnectionString = mksConnectionString

    .Open

    End With

    ' text file operations...

    'Open the text file

    Dim objTextStream

    Set objTextStream = fso.OpenTextFile(fileToProcess, fsoForReading, True)

    'Stop

    'Display the contents of the text file

    Do Until objTextStream.AtEndOfStream

    nextline = objTextStream.ReadLine

    'MsgBox nextline

    If Left(nextline, 10) = "{LT}/STMTTRN{GT}" Then

    'MsgBox "End of transaction"

    'Insert a record into the database

    sSql = "INSERT into QFX (ORG, FID,BANKID, ACCTID, ACCTTYPE," & _

    "TRNTYPE, DTPOSTED, DTUSER, TRNAMT, FITID,NAME, MEMO)" & _

    "VALUES ('" & ORG & "','" & FID & "','" & BANKID & "','" & ACCTID & "','" & ACCTTYPE & "','" & TRANTYPE & "','" & DTPOSTED & _

    "','" & DTUSER & "','" & TRNAMT & "','" & FITID & "','" & NAME & "','" & MEMO & "')"

    cn.Execute (sSql)

    ' reset the transaction flags and the variables

    TRANTYPE = ""

    DTPOSTED = "12/31/2040"

    DTUSER = "12/31/2040"

    TRNAMT = 0

    FITID = ""

    NAME = ""

    MEMO = ""

    End If

    If Left(nextline, 5) = "{LT}ORG{GT}" Then

    ORG = Mid(nextline, 6, Len(nextline) - 1)

    ElseIf Left(nextline, 5) = "{LT}FID{GT}" Then

    FID = Mid(nextline, 6, Len(nextline) - 1)

    End If

    Select Case Left(nextline, 6)

    Case "{LT}STMTT"

    'Stop

    Case "{LT}BANKI"

    BANKID = Mid(nextline, 9, Len(nextline) - 1)

    Case "{LT}ACCTI"

    ACCTID = Mid(nextline, 9, Len(nextline) - 1)

    Case "{LT}ACCTT"

    ACCTTYPE = Mid(nextline, 11, Len(nextline) - 1)

    Case "{LT}TRNTY"

    TRANTYPE = Mid(nextline, 10, Len(nextline) - 1)

    Case "{LT}DTPOS"

    DTPOSTED = CDate(Mid(nextline, 15, 2) & "/" & Mid(nextline, 17, 2) & "/" & Mid(nextline, 11, 4))

    Case "{LT}DTUSE"

    DTUSER = CDate(Mid(nextline, 13, 2) & "/" & Mid(nextline, 15, 2) & "/" & Mid(nextline, 9, 4))

    Case "{LT}TRNAM"

    If Mid(nextline, 9, 1) = "-" Then

    TRNAMT = CCur(Mid(nextline, 10, Len(nextline) - 1)) * -1

    Else

    TRNAMT = CCur(Mid(nextline, 10, Len(nextline) - 1))

    End If

    Case "{LT}FITID"

    FITID = Mid(nextline, 8, Len(nextline) - 1)

    Case "{LT}NAME{GT}"

    NAME = PQ(Mid(nextline, 7, Len(nextline) - 1))

    Case "{LT}MEMO{GT}"

    MEMO = PQ(Mid(nextline, 7, Len(nextline) - 1))

    Loop

    MsgBox "Import is complete!", vbOKOnly, "QFX File Import"

    '******************************************************

    'Close the file and clean up

    objTextStream.Close

    Set objTextStream = Nothing

    cn.Close

    Set cn = Nothing

    Set fso = Nothing

    End Sub

    Sub ResetVariables()

    'D = Null

    T = 0

    C = ""

    N = ""

    P = ""

    A1 = ""

    A2 = ""

    A3 = ""

    L = ""

    S = ""

    E = ""

    A_S1 = 0

    S_M = ""

    A_S2 = 0

    'reset the flags used

    A2_flag = 0

    A3_flag = 0

    S_flag = 0

    A_S_flag = 0

    End Sub

    Sub DeleteAllQFXData()

    Dim ServerName As String

    Dim mksConnectionString As String

    ServerName = "PHENOM\SQLEXPRESS"

    ' or this one...

    'Const mksConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=M:\Programming\QIF\QIF.mdb"

    'Server=myServerName\theInstanceName;Database=myDataBase;Trusted_Connection=True;

    mksConnectionString = "Provider=SQLOLEDB;Data Source=" & ServerName & ";Initial Catalog=QIF;User ID=sa; Password=; Trusted_Connection=yes"

    Dim cn As New ADODB.Connection

    cn.ConnectionString = mksConnectionString

    cn.Open

    sSql = "Delete QFX"

    cn.Execute (sSql)

    cn.Close

    Set cn = Nothing

    End Sub

  • dtcalif-297369

    SSC Enthusiast

    Points: 167

    dtcalif (8/17/2009)


    Everything you ever wanted to know about .OFX (QFX) files is available at:

    http://www.ofx.net/DownloadPage/Downloads.aspx

    Bank of America apparently uses version 1.0.2 for their files.

    Here's some very simple, but effective VBA code for the import of Bank of America .QFX files into SQL Server.

    ' This code module is used to process QFX files for import

    Sub Import_QFX_Files(fileToProcess)

    'MsgBox "Here's where you process files!"

    Const fsoForWriting = 2

    Const fsoForAppend = 8

    Const fsoForReading = 1

    '******************************************************

    Dim ServerName As String

    Dim mksConnectionString As String

    ServerName = "PHENOM\SQLEXPRESS"

    mksConnectionString = "Provider=SQLOLEDB;Data Source=" & ServerName & ";Initial Catalog=QIF;User ID=sa; Password=; Trusted_Connection=yes"

    'Stop

    Dim cn As New ADODB.Connection

    Dim rs As New ADODB.Recordset

    Dim sSql As String

    Dim fso As New Scripting.FileSystemObject

    Dim txtFile As Scripting.TextStream

    '--------------------

    Dim ORG As String

    Dim FID As String

    Dim BANKID As String

    Dim ACCTID As String

    Dim ACCTTYPE As String

    Dim TRANTYPE As String

    Dim DTPOSTED As Date

    Dim DTUSER As Date

    Dim TRNAMT As Currency

    Dim FITID As String

    Dim NAME As String

    Dim MEMO As String

    'ResetVariables

    ORG = ""

    FID = ""

    BANKID = ""

    ACCTID = ""

    ACCTTYPE = ""

    TRANTYPE = ""

    DTPOSTED = "12/31/2040"

    DTUSER = "12/31/2040"

    TRNAMT = 0

    FITID = ""

    NAME = ""

    MEMO = ""

    With cn

    .ConnectionString = mksConnectionString

    .Open

    End With

    ' text file operations...

    'Open the text file

    Dim objTextStream

    Set objTextStream = fso.OpenTextFile(fileToProcess, fsoForReading, True)

    'Stop

    'Display the contents of the text file

    Do Until objTextStream.AtEndOfStream

    nextline = objTextStream.ReadLine

    'MsgBox nextline

    If Left(nextline, 10) = "" Then

    'MsgBox "End of transaction"

    'Insert a record into the database

    sSql = "INSERT into QFX (ORG, FID,BANKID, ACCTID, ACCTTYPE," & _

    "TRNTYPE, DTPOSTED, DTUSER, TRNAMT, FITID,NAME, MEMO)" & _

    "VALUES ('" & ORG & "','" & FID & "','" & BANKID & "','" & ACCTID & "','" & ACCTTYPE & "','" & TRANTYPE & "','" & DTPOSTED & _

    "','" & DTUSER & "','" & TRNAMT & "','" & FITID & "','" & NAME & "','" & MEMO & "')"

    cn.Execute (sSql)

    ' reset the transaction flags and the variables

    TRANTYPE = ""

    DTPOSTED = "12/31/2040"

    DTUSER = "12/31/2040"

    TRNAMT = 0

    FITID = ""

    NAME = ""

    MEMO = ""

    End If

    If Left(nextline, 5) = "" Then

    ORG = Mid(nextline, 6, Len(nextline) - 1)

    ElseIf Left(nextline, 5) = "" Then

    FID = Mid(nextline, 6, Len(nextline) - 1)

    End If

    Select Case Left(nextline, 6)

    Case "<STMTT"

    'Stop

    Case "<BANKI"

    BANKID = Mid(nextline, 9, Len(nextline) - 1)

    Case "<ACCTI"

    ACCTID = Mid(nextline, 9, Len(nextline) - 1)

    Case "<ACCTT"

    ACCTTYPE = Mid(nextline, 11, Len(nextline) - 1)

    Case "<TRNTY"

    TRANTYPE = Mid(nextline, 10, Len(nextline) - 1)

    Case "<DTPOS"

    DTPOSTED = CDate(Mid(nextline, 15, 2) & "/" & Mid(nextline, 17, 2) & "/" & Mid(nextline, 11, 4))

    Case "<DTUSE"

    DTUSER = CDate(Mid(nextline, 13, 2) & "/" & Mid(nextline, 15, 2) & "/" & Mid(nextline, 9, 4))

    Case "<TRNAM"

    If Mid(nextline, 9, 1) = "-" Then

    TRNAMT = CCur(Mid(nextline, 10, Len(nextline) - 1)) * -1

    Else

    TRNAMT = CCur(Mid(nextline, 10, Len(nextline) - 1))

    End If

    Case "<FITID"

    FITID = Mid(nextline, 8, Len(nextline) - 1)

    Case ""

    NAME = PQ(Mid(nextline, 7, Len(nextline) - 1))

    Case ""

    MEMO = PQ(Mid(nextline, 7, Len(nextline) - 1))

    End Select

    Loop

    MsgBox "Import is complete!", vbOKOnly, "QIF File Import"

    '******************************************************

    'Close the file and clean up

    objTextStream.Close

    Set objTextStream = Nothing

    cn.Close

    Set cn = Nothing

    Set fso = Nothing

    End Sub

  • support 88105

    SSC Rookie

    Points: 39

    try OFX2CSV http://ofx2csv.propersoft.net/[/url] to convert OFX/QFX/QIF/QBO/OFX files to CSV format.

  • sgmunson

    SSC Guru

    Points: 110433

    Thanks - I'll check it out...

    Steve

    (aka sgmunson)

    :-):-):-)

    Steve
    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

  • nepaluz

    Newbie

    Points: 5

    try iCreateOFX Basic from:

    http://icreateofx.co.uk/basic.php

    You can convert any CSV or QIF to OFX including converting PayPal CSV to OFX.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply