From 8cebce567664a52fbead78bc24097f3095313d54 Mon Sep 17 00:00:00 2001 From: John Wiegley Date: Fri, 8 Aug 2008 19:21:55 -0400 Subject: Added code for the csv2ledger parser that I use personally. --- .gitignore | 1 + contrib/CSVReader.cs | 165 ++++++++++++++++++++++++++++++++++++++++++++ contrib/Makefile | 4 ++ contrib/ParseCcStmt.cs | 184 +++++++++++++++++++++++++++++++++++++++++++++++++ 4 files changed, 354 insertions(+) create mode 100644 contrib/CSVReader.cs create mode 100644 contrib/Makefile create mode 100644 contrib/ParseCcStmt.cs diff --git a/.gitignore b/.gitignore index b8131651..4e6eb177 100644 --- a/.gitignore +++ b/.gitignore @@ -3,6 +3,7 @@ *.cp *.dSYM *.elc +*.exe *.fn *.ky *.la diff --git a/contrib/CSVReader.cs b/contrib/CSVReader.cs new file mode 100644 index 00000000..a22eab06 --- /dev/null +++ b/contrib/CSVReader.cs @@ -0,0 +1,165 @@ +// This code is in the public domain. I can't remember where I found it on the Web, but it +// didn't come with any license. + +using System; +using System.Collections; +using System.IO; +using System.Text; + +namespace CSVReader { + + /// + /// A data-reader style interface for reading CSV files. + /// + public class CSVReader : IDisposable { + + #region Private variables + + private Stream stream; + private StreamReader reader; + + #endregion + + /// + /// Create a new reader for the given stream. + /// + /// The stream to read the CSV from. + public CSVReader(Stream s) : this(s, null) { } + + /// + /// Create a new reader for the given stream and encoding. + /// + /// The stream to read the CSV from. + /// The encoding used. + public CSVReader(Stream s, Encoding enc) { + + this.stream = s; + if (!s.CanRead) { + throw new CSVReaderException("Could not read the given CSV stream!"); + } + reader = (enc != null) ? new StreamReader(s, enc) : new StreamReader(s); + } + + /// + /// Creates a new reader for the given text file path. + /// + /// The name of the file to be read. + public CSVReader(string filename) : this(filename, null) { } + + /// + /// Creates a new reader for the given text file path and encoding. + /// + /// The name of the file to be read. + /// The encoding used. + public CSVReader(string filename, Encoding enc) + : this(new FileStream(filename, FileMode.Open), enc) { } + + /// + /// Returns the fields for the next row of CSV data (or null if at eof) + /// + /// A string array of fields or null if at the end of file. + public string[] GetCSVLine() { + + string data = reader.ReadLine(); + if (data == null) return null; + if (data.Length == 0) return new string[0]; + + ArrayList result = new ArrayList(); + + ParseCSVFields(result, data); + + return (string[])result.ToArray(typeof(string)); + } + + // Parses the CSV fields and pushes the fields into the result arraylist + private void ParseCSVFields(ArrayList result, string data) { + + int pos = -1; + while (pos < data.Length) + result.Add(ParseCSVField(data, ref pos)); + } + + // Parses the field at the given position of the data, modified pos to match + // the first unparsed position and returns the parsed field + private string ParseCSVField(string data, ref int startSeparatorPosition) { + + if (startSeparatorPosition == data.Length-1) { + startSeparatorPosition++; + // The last field is empty + return ""; + } + + int fromPos = startSeparatorPosition + 1; + + // Determine if this is a quoted field + if (data[fromPos] == '"') { + // If we're at the end of the string, let's consider this a field that + // only contains the quote + if (fromPos == data.Length-1) { + fromPos++; + return "\""; + } + + // Otherwise, return a string of appropriate length with double quotes collapsed + // Note that FSQ returns data.Length if no single quote was found + int nextSingleQuote = FindSingleQuote(data, fromPos+1); + startSeparatorPosition = nextSingleQuote+1; + return data.Substring(fromPos+1, nextSingleQuote-fromPos-1).Replace("\"\"", "\""); + } + + // The field ends in the next comma or EOL + int nextComma = data.IndexOf(',', fromPos); + if (nextComma == -1) { + startSeparatorPosition = data.Length; + return data.Substring(fromPos); + } + else { + startSeparatorPosition = nextComma; + return data.Substring(fromPos, nextComma-fromPos); + } + } + + // Returns the index of the next single quote mark in the string + // (starting from startFrom) + private int FindSingleQuote(string data, int startFrom) { + + int i = startFrom-1; + while (++i < data.Length) + if (data[i] == '"') { + // If this is a double quote, bypass the chars + if (i < data.Length-1 && data[i+1] == '"') { + i++; + continue; + } + else + return i; + } + // If no quote found, return the end value of i (data.Length) + return i; + } + + /// + /// Disposes the CSVReader. The underlying stream is closed. + /// + public void Dispose() { + // Closing the reader closes the underlying stream, too + if (reader != null) reader.Close(); + else if (stream != null) + stream.Close(); // In case we failed before the reader was constructed + GC.SuppressFinalize(this); + } + } + + + /// + /// Exception class for CSVReader exceptions. + /// + public class CSVReaderException : ApplicationException { + + /// + /// Constructs a new exception object with the given message. + /// + /// The exception message. + public CSVReaderException(string message) : base(message) { } + } +} diff --git a/contrib/Makefile b/contrib/Makefile new file mode 100644 index 00000000..6e4d367a --- /dev/null +++ b/contrib/Makefile @@ -0,0 +1,4 @@ +all: ParseCcStmt.exe + +ParseCcStmt.exe: ParseCcStmt.cs CSVReader.cs + gmcs -out:ParseCcStmt.exe ParseCcStmt.cs CSVReader.cs diff --git a/contrib/ParseCcStmt.cs b/contrib/ParseCcStmt.cs new file mode 100644 index 00000000..f6b2f20b --- /dev/null +++ b/contrib/ParseCcStmt.cs @@ -0,0 +1,184 @@ +/* + * Copyright (c) 2003-2008, John Wiegley. All rights reserved. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions are + * met: + * + * - Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * + * - Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in the + * documentation and/or other materials provided with the distribution. + * + * - Neither the name of New Artisans LLC nor the names of its + * contributors may be used to endorse or promote products derived from + * this software without specific prior written permission. + * + * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS + * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT + * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR + * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT + * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, + * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT + * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, + * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY + * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT + * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE + * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + */ + +using System; +using System.Collections; +using System.Collections.Generic; +using System.IO; +using System.Text; +using System.Text.RegularExpressions; + +using CSVReader; + +/** + * @file ParseCcStmt.cs + * + * @brief Provides a .NET way to turn a CSV report into Ledger entries. + * + * I use this code for converting the statements from my own credit card + * issuer. I realize it's strange for this to be in C#, but I wrote it + * during a phase of C# contracting. The code is solid enough now -- + * and the Mono project is portable enough -- that I haven't seen the + * need to rewrite it into another language like Python. + */ + +namespace JohnWiegley +{ + public class Transaction + { + public DateTime Date; + public DateTime PostedDate; + public string Code; + public string Payee; + public Decimal Amount; + } + + public interface IStatementConverter + { + List ConvertRecords(Stream s); + } + + public class ConvertGoldMasterCardStatement : IStatementConverter + { + public List ConvertRecords(Stream s) + { + List xacts = new List(); + + using (CSVReader.CSVReader csv = new CSVReader.CSVReader(s)) { + string[] fields; + while ((fields = csv.GetCSVLine()) != null) { + if (fields[0] == "TRANSACTION DATE") + continue; + + Transaction xact = new Transaction(); + + xact.Date = DateTime.ParseExact(fields[0], "mm/dd/yy", null); + xact.PostedDate = DateTime.ParseExact(fields[1], "mm/dd/yy", null); + xact.Payee = fields[2].Trim(); + xact.Code = fields[3].Trim(); + xact.Amount = Convert.ToDecimal(fields[4].Trim()); + + if (xact.Code.Length == 0) + xact.Code = null; + + xacts.Add(xact); + } + } + return xacts; + } + } + + public class ConvertMastercardStatement : IStatementConverter + { + public List ConvertRecords(Stream s) + { + List xacts = new List(); + + using (CSVReader.CSVReader csv = new CSVReader.CSVReader(s)) { + string[] fields; + while ((fields = csv.GetCSVLine()) != null) { + Transaction xact = new Transaction(); + + xact.Date = DateTime.ParseExact(fields[0], "m/dd/yyyy", null); + xact.Payee = fields[2].Trim(); + xact.Code = fields[3].Trim(); + xact.Amount = - Convert.ToDecimal(fields[4].Trim()); + + if (xact.Code.Length == 0) + xact.Code = null; + + xacts.Add(xact); + } + } + return xacts; + } + } + + public class PrintTransactions + { + public string DefaultAccount(Transaction xact) { + if (Regex.IsMatch(xact.Payee, "IGA")) + return "Expenses:Food"; + return "Expenses:Food"; + } + + public void Print(string AccountName, string PayAccountName, + List xacts) + { + foreach (Transaction xact in xacts) { + if (xact.Amount < 0) { + Console.WriteLine("{0} * {1}{2}", xact.Date.ToString("yyyy/mm/dd"), + xact.Code != null ? "(" + xact.Code + ") " : "", + xact.Payee); + Console.WriteLine(" {0,-36}{1,12}", AccountName, + "$" + (- xact.Amount).ToString()); + Console.WriteLine(" {0}", PayAccountName); + } else { + Console.WriteLine("{0} {1}{2}", xact.Date.ToString("yyyy/mm/dd"), + xact.Code != null ? "(" + xact.Code + ") " : "", + xact.Payee); + Console.WriteLine(" {0,-36}{1,12}", DefaultAccount(xact), + "$" + xact.Amount.ToString()); + Console.WriteLine(" * {0}", AccountName); + } + Console.WriteLine(); + } + } + } + + public class ParseCcStmt + { + public static int Main(string[] args) + { + StreamReader reader = new StreamReader(args[0]); + string firstLine = reader.ReadLine(); + + string CardAccount = args[1]; + string BankAccount = args[2]; + + IStatementConverter converter; + + if (firstLine.StartsWith("TRANSACTION DATE")) { + converter = new ConvertGoldMasterCardStatement(); + } else { + converter = new ConvertMastercardStatement(); + } + + reader = new StreamReader(args[0]); + List xacts = converter.ConvertRecords(reader.BaseStream); + + PrintTransactions printer = new PrintTransactions(); + printer.Print(CardAccount, BankAccount, xacts); + + return 0; + } + } +} -- cgit v1.2.3