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); }