December 28, 2008 at 7:34 pm
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 29, 2008 at 1:40 pm
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 29, 2008 at 3:32 pm
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.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 2, 2009 at 2:31 pm
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 12, 2009 at 2:52 pm
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.
August 12, 2009 at 8:07 pm
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 17, 2009 at 12:42 am
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
August 17, 2009 at 1:08 am
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
April 20, 2010 at 9:26 pm
April 21, 2010 at 8:11 pm
Thanks - I'll check it out...
Steve
(aka sgmunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 28, 2010 at 12:20 pm
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 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply