Hi, thanks for replying. Sorry I missed the notification.
This is the the full code for the console app. .Net Core v8.0, Windows 11.
using Aspose.Finance.Ofx;
using Aspose.Finance.Ofx.CreditCard;
using Aspose.Finance.Ofx.Signon;
using CsvHelper;
using System.Globalization;
using System.Numerics;
using System.Text;
if (args != null && args.Length > 0)
{
string csvFilePath = args[0];
string qfxFilePath = "Transactions.qfx";
Console.WriteLine($"Converting {csvFilePath} to {qfxFilePath}");
GenerateQfx(ReadCsv(csvFilePath), qfxFilePath);
// Fix the file to just be in UTF8 instead of UTF8 with BOM
// var fileContents = File.ReadAllText(qfxFilePath);
// File.WriteAllText(qfxFilePath, fileContents, new UTF8Encoding(false));
}
else
{
Console.WriteLine("Usage: CsvToQfx <csv file>");
}
static List<CsvTransaction> ReadCsv(string csvFilePath)
{
List<CsvTransaction> transactions = new List<CsvTransaction>();
using (var reader = new StreamReader(csvFilePath))
{
using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
{
var records = new List<CsvTransaction>();
csv.Read(); // Read the first row (header row)
csv.ReadHeader(); // Parse the header row so fields can be accessed by name
while (csv.Read())
{
var transaction = new CsvTransaction()
{
Date = csv.GetField<string>("Date"),
Description = csv.GetField<string>("Description"),
OriginalDescription = csv.GetField<string>("Original Description"),
Category = csv.GetField<string>("Category"),
Amount = csv.GetField<decimal>("Amount"),
Status = csv.GetField<string>("Status")
};
records.Add(transaction);
}
Console.WriteLine($"Read {records.Count} transactions from {csvFilePath}");
return records;
}
}
}
static void GenerateQfx(List<CsvTransaction> transactions, string qfxFilePath)
{
var doc = new OfxResponseDocument();
doc.SignonResponseMessageSetV1 = new SignonResponseMessageSetV1();
var signonResponse = new SignonResponse();
doc.SignonResponseMessageSetV1.SignonResponse = signonResponse;
signonResponse.Status = new Status();
signonResponse.Status.Code = "0";
signonResponse.Status.Severity = SeverityEnum.INFO;
signonResponse.Status.Message = "SUCCESS";
signonResponse.Language = LanguageEnum.ENG;
signonResponse.ServerDate = DateTime.Today.ToString("yyyyMMddHHmmss") + "[0:GMT]";
//signonResponse.ProfileUpdateDate = DateTime.Today.ToString("yyyyMMdd");
var fi = new FinancialInstitution();
fi.Organization = "XYZ";
fi.FinancialInstitutionId = "67811";
signonResponse.FinancialInstitution = fi;
doc.CreditcardResponseMessageSetV1 = new CreditcardResponseMessageSetV1();
var stmtResponse = new CreditCardStatementTransactionResponse();
doc.CreditcardResponseMessageSetV1.CreditCardStatementTransactionResponses.Add(stmtResponse);
stmtResponse.TransactionUniqueId = "0";
stmtResponse.Status = new Status();
stmtResponse.Status.Code = "0";
stmtResponse.Status.Severity = SeverityEnum.INFO;
stmtResponse.CreditCardStatementResponse = new CreditCardStatementResponse();
stmtResponse.CreditCardStatementResponse.Currency = CurrencyEnum.USD;
stmtResponse.CreditCardStatementResponse.CreditCardAccountFrom = new CreditCardAccount();
stmtResponse.CreditCardStatementResponse.CreditCardAccountFrom.AccountId = "4995";
stmtResponse.CreditCardStatementResponse.BankTransactionList = new BankTransactionList();
stmtResponse.CreditCardStatementResponse.BankTransactionList.StartDate = DateTime.Parse(transactions.OrderBy(x => x.Date).First().Date).ToString("yyyyMMddHHmmss") + "[0:GMT]";
stmtResponse.CreditCardStatementResponse.BankTransactionList.EndDate = DateTime.Parse(transactions.OrderByDescending(x => x.Date).First().Date).ToString("yyyyMMddHHmmss") + "[0:GMT]";
var i = 1;
foreach (var transaction in transactions.Where(x => x.Status != "Declined")) {
var tran = new StatementTransaction();
tran.TransactionType = transaction.Amount < 0 ? TransactionEnum.CREDIT : TransactionEnum.DEBIT;
tran.PostedDate = DateTime.Parse(transaction.Date).ToString("yyyyMMddHHmmss") + "[0:GMT]";
tran.TransactionAmount = (transaction.Amount * -1).ToString("0.00", CultureInfo.InvariantCulture);
if(transaction.Description.Length > 32) {
tran.Name = transaction.Description.Substring(0, 32).Trim();
}
else
{
tran.Name = transaction.Description;
}
tran.FinancialInstitutionTransactionId = DateTime.Today.ToString("yyyyMMdd") + i.ToString().PadLeft(3, '0');
stmtResponse.CreditCardStatementResponse.BankTransactionList.StatementTransactions.Add(tran);
i++;
}
var ledger = new LedgerBalance();
ledger.BalanceAmount = transactions.Sum(x => x.Amount).ToString(CultureInfo.InvariantCulture);
ledger.DateAsOf = DateTime.Today.ToString("yyyyMMddHHmmss") + "[0:GMT]";
stmtResponse.CreditCardStatementResponse.LedgerBalance = ledger;
var avail = new AvailableBalance();
avail.BalanceAmount = "20000.00";
avail.DateAsOf = DateTime.Today.ToString("yyyyMMddHHmmss") + "[0:GMT]";
stmtResponse.CreditCardStatementResponse.AvailableBalance = avail;
Console.WriteLine($"Generated {transactions.Count} transactions in {qfxFilePath}");
doc.Save(qfxFilePath, new SaveOptions() { SaveFormat = SaveFormat.SGML });
}
This is the object that the CSV gets loaded into.
internal class CsvTransaction
{
public string? Date { get; set; }
public string? Description { get; set; }
public string? OriginalDescription { get; set; }
public string? Category { get; set; }
public decimal Amount { get; set; }
public string? Status { get; set; }
}
With lines 18, 19 commented out
bom.jpg (34.6 KB)
Uncommenting 18, 19:
utf8.jpg (27.9 KB)
Sample CSV:
Date,Description,Original Description,Category,Amount,Status
2024-09-28,“Test”,“Test”,Category Pending,11.05,Pending
Sample BOM output:
Transactions.zip (614 Bytes)