Click here to Skip to main content
11,437,002 members (61,475 online)
Click here to Skip to main content

Class to transform an OFX (Microsoft Money) file into a DataSet

, 28 Jul 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
A small class that transforms the contens of an OFX file into a DataSet.

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.

Visit my homepage to see more interesting stuff: http://www.rodiniz.com.

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

License

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

Share

About the Author

rodrigo diniz
Web Developer
Brazil Brazil
I am Web Developer since 1999 , and I am a
Microsoft Certified Professional since 2005.
I work with asp.net and c# since 2003.
Extender Samples .

Comments and Discussions

 
GeneralStuck with linq.... Pin
psycik12-Aug-10 18:51
memberpsycik12-Aug-10 18:51 
GeneralRe: Stuck with linq.... Pin
rodrigo diniz13-Aug-10 2:50
memberrodrigo diniz13-Aug-10 2:50 
GeneralNew Tag Pin
DizZ1-Jul-10 6:12
memberDizZ1-Jul-10 6:12 
NewsNew version Pin
Zote8-May-09 5:26
memberZote8-May-09 5:26 
GeneralRe: New version Pin
Jim Jewett31-Jul-09 7:39
memberJim Jewett31-Jul-09 7:39 
GeneralSource not available Pin
Brett Swift6-Mar-09 3:21
memberBrett Swift6-Mar-09 3:21 
GeneralRe: Source not available Pin
rodrigo diniz6-Mar-09 5:33
memberrodrigo diniz6-Mar-09 5:33 
GeneralWorks - OFX version differences Pin
nicza6-May-08 20:39
membernicza6-May-08 20:39 
GeneralDoes not work Pin
jimmers@codeproject14-May-07 17:54
memberjimmers@codeproject14-May-07 17:54 
GeneralRe: Does not work Pin
rodrigo diniz1-Jun-07 3:26
memberrodrigo diniz1-Jun-07 3:26 
GeneralRe: Does not work Pin
Derek5-Dec-07 5:21
sussDerek5-Dec-07 5:21 
GeneralRe: Does not work Pin
Derek5-Dec-07 5:21
sussDerek5-Dec-07 5:21 
GeneralRe: Does not work Pin
Leonardo Melo Santos15-Aug-08 18:03
memberLeonardo Melo Santos15-Aug-08 18:03 
GeneralRe: Does not work Pin
Leonardo Melo Santos15-Aug-08 18:08
memberLeonardo Melo Santos15-Aug-08 18:08 
Generalworks like a charm! Pin
Usarian9-Sep-06 19:59
memberUsarian9-Sep-06 19:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150428.2 | Last Updated 28 Jul 2010
Article Copyright 2006 by rodrigo diniz
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid