private ParsedBankAccount BuildBankAccount(DataTable dataTable, HeaderColumns headerColumns, string fileName)
{
var bankAccount = new ParsedBankAccount {
Name = fileName,
Transactions = new List <ParsedBankTransaction>()
};
foreach (DataRow row in dataTable.Rows)
{
try {
var transaction = new ParsedBankTransaction();
transaction.Description = row[headerColumns.Description].ToString();
transaction.Date = ParseDate(row[headerColumns.Date].ToString());
if (headerColumns.Amount != null)
{
transaction.Amount = ParseDecimal(row[headerColumns.Amount].ToString());
transaction.IsCredit = transaction.Amount >= 0;
transaction.Amount = Math.Abs(transaction.Amount);
}
if (headerColumns.Credit != null && headerColumns.Debit != null)
{
if (!String.IsNullOrEmpty(row[headerColumns.Credit].ToString()))
{
transaction.Amount = ParseDecimal(row[headerColumns.Credit].ToString());
transaction.IsCredit = true;
}
if (!String.IsNullOrEmpty(row[headerColumns.Debit].ToString()))
{
transaction.Amount = ParseDecimal(row[headerColumns.Debit].ToString());
transaction.IsCredit = false;
transaction.Amount = Math.Abs(transaction.Amount);
}
}
if (headerColumns.Balance != null)
{
transaction.Balance = ParseDecimal(row[headerColumns.Balance].ToString());
}
bankAccount.Transactions.Add(transaction);
} catch (Exception ex) {
Log.Warn("failed to parse row \n {0}", ex);
}
}
bankAccount.NumOfTransactions = bankAccount.Transactions.Count();
if (bankAccount.NumOfTransactions > 0)
{
bankAccount.DateFrom = bankAccount.Transactions.Min(x => x.Date);
bankAccount.DateTo = bankAccount.Transactions.Max(x => x.Date);
bankAccount.Balance = bankAccount.Transactions.OrderByDescending(x => x.Date)
.First()
.Balance;
}
Log.Debug(bankAccount);
return(bankAccount);
}
private HeaderColumns FindHeaderRow(DataTable dataTable)
{
var headerColumns = new HeaderColumns();
foreach (DataColumn col in dataTable.Columns)
{
string cell = col.ToString();
if (!String.IsNullOrEmpty(cell))
{
var cellLower = cell.ToLowerInvariant()
.Trim();
if (cellLower.Contains("date"))
{
if (headerColumns.Date == null)
{
headerColumns.Date = col;
}
else
{
Log.Debug("more then one date columns found");
}
continue;
}
if (cellLower.Contains("credit") || (cellLower.Contains(" in") && !cellLower.Contains("amount")))
{
if (headerColumns.Credit == null)
{
headerColumns.Credit = col;
}
else
{
Log.Debug("more then one credit columns found");
}
continue;
}
if (cellLower.Contains("debit") || (cellLower.Contains(" out") && !cellLower.Contains("amount")))
{
if (headerColumns.Debit == null)
{
headerColumns.Debit = col;
}
else
{
Log.Debug("more then one credit columns found");
}
continue;
}
if (cellLower.Contains("balance"))
{
if (headerColumns.Balance == null)
{
headerColumns.Balance = col;
}
else
{
Log.Debug("more then one balance columns found");
}
continue;
}
if ((cellLower.Contains("amount") || cellLower.Contains("value") || cellLower == "money") && !cellLower.Contains("balance") && !cellLower.Contains("debit") && !cellLower.Contains("credit"))
{
if (headerColumns.Amount == null)
{
headerColumns.Amount = col;
}
else
{
Log.Debug("more then one amount columns found");
}
continue;
}
if (cellLower.Contains("description") || cellLower.Contains("text") || cellLower.Contains("narrative") || cellLower.Contains("memo"))
{
if (headerColumns.Description == null)
{
headerColumns.Description = col;
}
else
{
Log.Debug("more then one description columns found");
}
continue;
}
}
}
return(headerColumns);
}
