Class to Transform an OFX (Microsoft Money) File into a DataSet

• Download source files - 1.03 KB

Introduction

This is my first article to CodeProject, so please be patient. I will tell you why I developed this class.

I wanted to have better control of my money, but I thought most financial programs were too complicated. So I decided to do one on my own. The problem in having my own financial program is that I don't have the patience and the memory to type my expenses one by one.

Then I saw my bank had an interesting service that exported all my expenses to an OFX file. I decided to download the file and see if I could do something with that. I opened the file in Notepad, and surprise!! The file was not binary format and it was almost a perfect XML.

What my class does is, it reads the file and makes the content a perfect XML, loads it on a DataSet and returns. Then you can manipulate the DataSet anyway you want.

Using the new code (LinqToXml)

XElement doc = ImportOfx.toXElement(pathToOfx);
//queryiny the XElement
var imps = (from c in doc.Descendants("STMTTRN")
            where c.Element("TRNTYPE").Value == "DEBIT"
            select new tb_import
            {
                amount = decimal.Parse(c.Element("TRNAMT").Value.Replace("-", ""),
                                       NumberFormatInfo.InvariantInfo),
                data = DateTime.ParseExact(c.Element("DTPOSTED").Value, 
                                           "yyyyMMdd", null),
                description = c.Element("MEMO").Value,
                id_account = id_account
            });

Points of interest

These are the columns inside the DataSet:

• TRNTYPE - Type of transaction: DEBIT or CREDIT
• DTPOSTED - Date of the transaction, formatted YYYYMMDDHHMMSS
• TRNAMT - Amount (negative when it is a DEBIT)
• FITID - Transaction ID CHECKNUM - Number of the check or transaction ID
• MEMO - Small description of the transaction; very useful when you use your debit card

History

• Version 2.0 - Some minor adjustments and uses .NET Framework 2.0
• Version 3.0 - Uses Framework 3.5 and LinqToXml to return an XElement