Click here to Skip to main content
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.... Pinmemberpsycik12-Aug-10 17:51 
GeneralRe: Stuck with linq.... Pinmemberrodrigo diniz13-Aug-10 1:50 
GeneralNew Tag PinmemberDizZ1-Jul-10 5:12 
NewsNew version PinmemberZote8-May-09 4:26 
GeneralRe: New version PinmemberJim Jewett31-Jul-09 6:39 
GeneralSource not available PinmemberBrett Swift6-Mar-09 2:21 
GeneralRe: Source not available Pinmemberrodrigo diniz6-Mar-09 4:33 
GeneralWorks - OFX version differences Pinmembernicza6-May-08 19:39 
GeneralDoes not work Pinmemberjimmers@codeproject14-May-07 16:54 
GeneralRe: Does not work Pinmemberrodrigo diniz1-Jun-07 2:26 
GeneralRe: Does not work PinsussDerek5-Dec-07 4:21 
GeneralRe: Does not work PinsussDerek5-Dec-07 4:21 
GeneralRe: Does not work PinmemberLeonardo Melo Santos15-Aug-08 17:03 
GeneralRe: Does not work PinmemberLeonardo Melo Santos15-Aug-08 17:08 
Generalworks like a charm! PinmemberUsarian9-Sep-06 18: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 | Mobile
Web02 | 2.8.141029.1 | Last Updated 28 Jul 2010
Article Copyright 2006 by rodrigo diniz
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid