Get OFX data into a database

Question

1.44/5 (2 votes)

See more:

My bank has moved to the OFX file format along with the csv.
I have downloaded the specifications for the OFX and the DTD's and am currently reading thu them. Searched the net and here for anything that could possibly get me closer to parsing the data into a database.
I haven't worked with xml very much.
What I am trying to figure out is how to get the data from the files into a database so I can create reports aginst the data later.

Do I need to parse it into some form of data set then save to a database ?

Any pointers or further reading would be greatly appreciated.
Thank you for your time.

Posted 28-May-12 18:32pm

ledtech3

Updated 3-Aug-22 7:58am

Add a Solution

Comments

Jim Jos 29-May-12 2:16am

Using the DTD you will get the fieldnames and datatypes which will enable you to create a staging table (intermediary) from where you could do proper loading into the tables.. Loading a csv into staging table is easy (using ssis etc)

ledtech3 29-May-12 9:17am

Thanks for the information. I'll start looking into that now.

Kornfeld Eliyahu Peter 28-Jan-18 8:10am

AS OFX is actually an XML you may use this: https://msdn.microsoft.com/en-us/library/fs0z9zxd(v=vs.110).aspx
It can help to load it to a DataTable and later store it to SQL...

Richard Deeming 30-Jan-18 13:07pm

2012! :)

ledtech3 30-Jan-18 13:18pm

Well at least people are still trying to help :)

Richard Deeming 30-Jan-18 13:21pm

Are you still trying to solve it after all this time? :D

ledtech3 30-Jan-18 13:25pm

No, I went a different direction then they changed again so the whole project became pointless after several days of research.

Kornfeld Eliyahu Peter 30-Jan-18 13:21pm

It got an update 3 days ago and jumped to the top...

Richard Deeming 30-Jan-18 13:22pm

I guess a spam answer got deleted. :)

Kornfeld Eliyahu Peter 30-Jan-18 13:34pm

I have a bad habit not to check dates, but to go for all on the opening page of QA...

Richard Deeming 30-Jan-18 13:36pm

I've definitely been there! 😄

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Arthur Rawn · Answer 1 · 2022-08-03T03:14:00

Don't know if anybody has been able to do this yet but this is a function I wrote to get my OFX data into a program from my bank

DISCLAIMER:
It is based on TDBank in Ontario Canada
You pass it a filename ... full path, Returns a 2D Array
Code can probably be fixed up but here is a starting point for you ... written in VS 2022 VB.NET

VB

<pre>    Public Function OFXImport(ByVal strMoneyFile As String) As String(,)
        'First off this is based on a sample from my TDBank ofx file in Canada
        'The first 41 lines were just a bunch of data that I really don't need at
        'this time

        Dim intRowsRead As Integer
        Dim intArryCol As Integer
        Dim aryMSMData(,) As String
        Using myMSMReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(strMoneyFile)
            myMSMReader.TextFieldType = FileIO.FieldType.Delimited
            myMSMReader.SetDelimiters(">")
            Dim currentRow As String()
            Dim intField As Integer
            Dim strTranType As String
            Dim blnDebit As Boolean = False
            'Dim 
            intField = 0
            intArryCol = 0
            intRowsRead = 0
            'Read the first 41 lines of useless data
            For intRowsRead = 1 To 41
                currentRow = myMSMReader.ReadFields()
            Next
            intRowsRead = 0
            While Not myMSMReader.EndOfData
                Try
                    Dim currentField As String
                    'Dim intCounter As Integer
                    currentRow = myMSMReader.ReadFields()
                    For Each currentField In currentRow
                        'Now we are going to check what field we are on
                        'and assign the array location to save the data
                        If currentField = "<STMTTRN" Or currentField = "</STMTTRN" Then
                            If currentField = "</STMTTRN" Then
                                'we have a complete transaction
                                'increase the rows read by 1
                                intRowsRead += 1
                            Else
                                'Redefine the array for the number of
                                'transactions we have
                                ReDim Preserve aryMSMData(4, intRowsRead)
                            End If
                            'This is the beginning and end of transaction
                            'statement, we can exit the loop and read
                            'the next field
                            Exit For
                        ElseIf currentField = "</BANKTRANLIST" Then
                            'This is the end of the transactions
                            'when can exit and return the array
                            Exit While
                        Else
                            If intField = 0 Then
                                intField = 1
                                'Now assign array location for the data
                                'array defintion is
                                '0 = Date
                                '1 = memo
                                '2 = debit
                                '3 = credit
                                '4 = transaction ID
                                If currentField = "<TRNTYPE" Then
                                    strTranType = "DC"
                                ElseIf currentField = "<FITID" Then
                                    intArryCol = 4
                                    strTranType = "POST"
                                ElseIf currentField = "<NAME" Then
                                    intArryCol = 1
                                    strTranType = "POST"
                                ElseIf currentField = "<DTPOSTED" Then
                                    intArryCol = 0
                                    strTranType = "POST"
                                ElseIf currentField = "<TRNAMT" Then
                                    If blnDebit = True Then
                                        intArryCol = 2
                                    Else
                                        intArryCol = 3
                                    End If
                                    strTranType = "POST"
                                End If
                            Else
                                intField = 0
                                'checking to see if we got a good field or the
                                'transaction type ... if our string returned
                                ' POST then we can post, if not we are assigning
                                'array location based on DEBIT / CREDIT
                                If strTranType = "POST" Then
                                    If intArryCol = 0 Then
                                        'if it is the date we just want the
                                        'first 8 as it is a huge time stamp
                                        aryMSMData(intArryCol, intRowsRead) = currentField.Substring(0, 8)
                                    ElseIf intArryCol = 2 Then
                                        'Now I only want Positive numbers because
                                        'I am separating out DEBIT / CREDIT into
                                        'there own rows so I am stripping the "-"
                                        'off the number
                                        aryMSMData(intArryCol, intRowsRead) = currentField.Substring(1, (currentField.Length - 1))
                                    Else
                                        aryMSMData(intArryCol, intRowsRead) = currentField
                                    End If
                                Else
                                    If currentField = "DEBIT" Then
                                        blnDebit = True
                                    Else
                                        blnDebit = False
                                    End If
                                End If
                            End If
                        End If
                    Next
                Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                    MsgBox("Line " & ex.Message & "is not valid and will be skipped.")
                End Try
            End While
        End Using
        Return aryMSMData
    End Function

Arthur Rawn · Answer 2 · 2022-08-03T07:58:00

OK ... so I found out that there are various standards of OFX Files out there ...
Here is my V2 Version of OFX importer.

My bank Uses V1 and My Credit Card V2

VB

<pre>    Public Function OFXImportV2(ByVal strMoneyFile As String) As String(,)
        'OK .... so there is difference's in OFX Files based on standard used
        'Some have then ending </> field definitions and some don't
        'I noticed this with differences between my accounts
        'My Bank OFX files were Version 1.02 did not end the transactions with </>
        'Credit Card OFX files were version 2.xx and did

        Dim intRowsRead As Integer
        Dim intArryCol As Integer
        Dim aryOFXData(,) As String
        Using myMSMReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(strMoneyFile)
            myMSMReader.TextFieldType = FileIO.FieldType.Delimited
            myMSMReader.SetDelimiters(">")
            Dim currentRow As String()
            Dim intField As Integer
            Dim strTranType As String
            Dim blnInATransaction As Boolean = False
            Dim blnDebit As Boolean = False
            Dim currentField As String
            intField = 0
            intArryCol = 0
            intRowsRead = 0
            'Start Reading the File
            While Not myMSMReader.EndOfData
                Try
                    currentRow = myMSMReader.ReadFields()
                    For Each currentField In currentRow
                        If blnInATransaction = False Then
                            If currentField = "<STMTTRN" Then
                                'Start of a New transaction
                                blnInATransaction = True
                                ReDim Preserve aryOFXData(4, intRowsRead)
                            End If
                        Else
                            If intField = 0 Then
                                If currentField = "<TRNTYPE" Then
                                    strTranType = "DC"
                                    intField = 1
                                ElseIf currentField = "<FITID" Then
                                    intArryCol = 4
                                    intField = 1
                                    strTranType = "POST"
                                ElseIf currentField = "<NAME" Then
                                    intField = 1
                                    intArryCol = 1
                                    strTranType = "POST"
                                ElseIf currentField = "<DTPOSTED" Then
                                    intField = 1
                                    intArryCol = 0
                                    strTranType = "POST"
                                ElseIf currentField = "<TRNAMT" Then
                                    intField = 1
                                    If blnDebit = True Then
                                        intArryCol = 2
                                    Else
                                        intArryCol = 3
                                    End If
                                    strTranType = "POST"
                                ElseIf currentField = "</STMTTRN" Then
                                    '
                                    '=================================
                                    'we have a complete transaction
                                    'increase the rows read by 1
                                    '=================================
                                    '
                                    blnInATransaction = False
                                    intRowsRead += 1
                                Else
                                    'I don't care about this type of field
                                    'could be a alternate memo field
                                    'user ID like which card the transaction on
                                    'a shared account
                                    'you can add code here to handle if you like
                                End If
                            Else
                                intField = 0
                                If currentField = "</STMTTRN" Then
                                    '
                                    '=================================
                                    'we have a complete transaction
                                    'increase the rows read by 1
                                    '=================================
                                    '
                                    blnInATransaction = False
                                    intRowsRead += 1
                                    '
                                    '=================================
                                    'checking to see if we got a good field or the
                                    'transaction type ... if our string returned
                                    ' POST then we can post, if not we are assigning
                                    'array location based on DEBIT / CREDIT
                                    '=================================
                                    '
                                ElseIf strTranType = "POST" Then
                                    '
                                    '=================================
                                    'OK so this is where version of OFX Change
                                    'V 1 there in no </> ending
                                    'V2 there is ... we need to strip it.
                                    '=================================
                                    '
                                    Dim intCurrentLetter As Integer = 0
                                    Dim strTransaction As String
                                    Dim strCurrentCharacter As String
                                    '
                                    '=================================
                                    'read the transaction letter by letter
                                    'because they add the "</> at the end
                                    '=================================
                                    '
                                    For intCurrentLetter = 0 To currentField.Length - 1
                                        strCurrentCharacter = currentField.Substring(intCurrentLetter, 1)
                                        If intCurrentLetter = 0 Then
                                            strTransaction = strCurrentCharacter
                                        Else
                                            If strCurrentCharacter = "<" Then
                                                'reached end of transaction type
                                                'exit loop
                                                Exit For
                                            Else
                                                strTransaction = strTransaction & strCurrentCharacter
                                            End If
                                        End If
                                    Next
                                    '
                                    '=================================
                                    'Now that we got the data
                                    'Place it in the array
                                    '=================================
                                    '
                                    If intArryCol = 0 Then
                                        '
                                        '=================================
                                        'if it is the date we just want the
                                        'first 8 as it is a huge time stamp
                                        'first 8 are YYYYMMDD
                                        '=================================
                                        '
                                        aryOFXData(intArryCol, intRowsRead) = strTransaction.Substring(0, 8)
                                    ElseIf intArryCol = 2 Then
                                        '
                                        '=================================
                                        'Now I only want Positive numbers because
                                        'I am separating out DEBIT / CREDIT into
                                        'there own rows so I am stripping the "-"
                                        'off the number
                                        '=================================
                                        '
                                        aryOFXData(intArryCol, intRowsRead) = strTransaction.Substring(1, (strTransaction.Length - 1))
                                    Else
                                        aryOFXData(intArryCol, intRowsRead) = strTransaction
                                    End If
                                Else
                                    If strTranType = "DC" Then
                                        Dim intCurrentLetter As Integer = 0
                                        Dim strTransaction As String
                                        Dim strCurrentCharacter As String
                                        '
                                        '=================================
                                        'read the transaction letter by letter
                                        'because they add the "</> at the end
                                        '=================================
                                        '
                                        For intCurrentLetter = 0 To currentField.Length - 1
                                            strCurrentCharacter = currentField.Substring(intCurrentLetter, 1)
                                            If intCurrentLetter = 0 Then
                                                strTransaction = strCurrentCharacter
                                            Else
                                                If strCurrentCharacter = "<" Then
                                                    'reached end of transaction type
                                                    'exit loop
                                                    Exit For
                                                Else
                                                    strTransaction = strTransaction & strCurrentCharacter
                                                End If
                                            End If
                                        Next
                                        If strTransaction = "DEBIT" Then
                                            blnDebit = True
                                        Else
                                            blnDebit = False
                                        End If
                                    End If
                                End If
                            End If
                        End If
                    Next
                Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                    MsgBox("Line " & ex.Message & "is not valid and will be skipped.")
                End Try
            End While
        End Using
        Return aryOFXData
    End Function

Get OFX data into a database

2 solutions

Solution 2

Solution 3

Add your solution here

Preview 0