public async Task <IEnumerable <AccountTransaction> > Import(string fileLocation, AccountTransactionColumnMap map) { var transactions = new List <AccountTransaction>(); IEnumerable <AccountTransaction> kiwibankTransactions = await TransactionsImporter.Import(ImportHelper.KiwiBankLocation, ImportHelper.KiwiBankMap); transactions.AddRange(kiwibankTransactions.Select(transaction => { transaction.AccountId = 1; return(transaction); }).ToList()); IEnumerable <AccountTransaction> purpleTransactions = await TransactionsImporter.Import(ImportHelper.PurpleVisaBankLocation, ImportHelper.PurpleVisaBankMap); transactions.AddRange(purpleTransactions.Select(transaction => { transaction.AccountId = 2; return(transaction); }).ToList()); IEnumerable <AccountTransaction> amexTransactions = await TransactionsImporter.Import(ImportHelper.AmexBankLocation, ImportHelper.AmexBankMap); transactions.AddRange(amexTransactions.Select(transaction => { transaction.AccountId = 3; return(transaction); }).ToList()); return(transactions); }
private static IEnumerable <AccountTransaction> ParseTransactionFromExcelWorkSheet(ExcelRange cells, AccountTransactionColumnMap map) { var accountTransactions = new List <AccountTransaction>(); bool isTransactionRowValid = true; for (int row = 2; isTransactionRowValid; row++) { if (cells[row, map.DateColumnIndex + 1].Value != null) // If row does not have a date, it is invalid. This will likely indicate the end transactions. { accountTransactions.Add(ParseTransactionFromExcelWorksheetRow(cells, row, map)); } else { isTransactionRowValid = false; } } return(accountTransactions); }
private static async Task <List <AccountTransaction> > ImportTransactionsFromExcel(FileInfo file, AccountTransactionColumnMap map) { ExcelPackage.LicenseContext = LicenseContext.NonCommercial; return(await Task.Factory.StartNew(() => { var transactions = new List <AccountTransaction>(); using var package = new ExcelPackage(file); ExcelWorksheet worksheet = package.Workbook.Worksheets.First(); //Requirement, the transactions are on the first worksheet of the excel if (worksheet != null) { transactions.AddRange(ParseTransactionFromExcelWorkSheet(worksheet.Cells, map)); } return transactions; })); }
private async Task <List <AccountTransaction> > ImportTransactionsFromCsv(string file, AccountTransactionColumnMap map) { return(await Task.Factory.StartNew(() => { using var reader = new StreamReader(file); using var csv = new CsvReader(reader, CultureInfo.InvariantCulture); var transactions = new List <AccountTransaction>(); if (!map.IncludeFirstRow) { csv.Read(); csv.ReadHeader(); } while (csv.Read()) { var transaction = new AccountTransaction { Payee = csv.GetField <string>(map.PayeeColumnIndex) }; string dateAsString = csv.GetField <string>(map.DateColumnIndex); SetTransactionDate(transaction, dateAsString); string amountAsString = csv.GetField <string>(map.AmountColumnIndex); SetTransactionAmount(transaction, amountAsString, map.IsMinusOutflow); transactions.Add(transaction); } return transactions; })); }
public async Task <IEnumerable <AccountTransaction> > Import(string fileLocation, AccountTransactionColumnMap map) { var file = new FileInfo(fileLocation); var transactions = new List <AccountTransaction>(); if (file.Extension.Equals(".csv", StringComparison.InvariantCultureIgnoreCase)) { transactions.AddRange(await ImportTransactionsFromCsv(fileLocation, map)); } if (file.Extension.Equals(".xlsx", StringComparison.InvariantCultureIgnoreCase)) { transactions.AddRange(await ImportTransactionsFromExcel(file, map)); } return(transactions); }
private static AccountTransaction ParseTransactionFromExcelWorksheetRow(ExcelRange cells, int row, AccountTransactionColumnMap map) { var transaction = new AccountTransaction { Date = cells[row, map.DateColumnIndex + 1].GetValue <DateTime>(), Payee = cells[row, map.PayeeColumnIndex + 1].GetValue <string>() }; string amountAsString = cells[row, map.AmountColumnIndex + 1].GetValue <string>(); SetTransactionAmount(transaction, amountAsString, map.IsMinusOutflow); return(transaction); }