From 2c204cd94e2d30aee136fbb0f14b64d5cc29b266 Mon Sep 17 00:00:00 2001 From: Calvin Morrison Date: Sun, 25 Jan 2026 15:23:07 -0500 Subject: CSV importer --- CashFlo.pro | 6 +- cashflow.cpp | 14 ++ cashflow.h | 1 + cashflow.ui | 9 ++ importdialog.cpp | 461 +++++++++++++++++++++++++++++++++++++++++++++++++++++++ importdialog.h | 58 +++++++ 6 files changed, 547 insertions(+), 2 deletions(-) create mode 100644 importdialog.cpp create mode 100644 importdialog.h diff --git a/CashFlo.pro b/CashFlo.pro index d739ab1..5701b37 100644 --- a/CashFlo.pro +++ b/CashFlo.pro @@ -13,14 +13,16 @@ SOURCES += \ cashflow.cpp \ database.cpp \ settingsdialog.cpp \ - multiselectcombobox.cpp + multiselectcombobox.cpp \ + importdialog.cpp HEADERS += \ cashflow.h \ database.h \ transaction.h \ settingsdialog.h \ - multiselectcombobox.h + multiselectcombobox.h \ + importdialog.h FORMS += \ cashflow.ui \ diff --git a/cashflow.cpp b/cashflow.cpp index 6df01a9..2af4319 100644 --- a/cashflow.cpp +++ b/cashflow.cpp @@ -2,6 +2,7 @@ #include "ui_cashflow.h" #include "settingsdialog.h" #include "multiselectcombobox.h" +#include "importdialog.h" #include #include #include @@ -188,6 +189,7 @@ void CashFlow::setupConnections() { connect(ui->actionNew, &QAction::triggered, this, &CashFlow::onNewFile); connect(ui->actionOpen, &QAction::triggered, this, &CashFlow::onOpenFile); connect(ui->actionSaveAs, &QAction::triggered, this, &CashFlow::onSaveAs); + connect(ui->actionImportCSV, &QAction::triggered, this, &CashFlow::onImportCSV); connect(ui->actionExportCSV, &QAction::triggered, this, &CashFlow::onExportCSV); connect(ui->actionQuit, &QAction::triggered, this, &CashFlow::onQuit); @@ -2031,6 +2033,18 @@ void CashFlow::onExportCSV() { QMessageBox::information(this, "Success", QString("Exported %1 rows to CSV.").arg(rowCount)); } +void CashFlow::onImportCSV() { + ImportDialog dialog(database, this); + if (dialog.exec() == QDialog::Accepted) { + QList imported = dialog.getImportedTransactions(); + for (const Transaction &t : imported) { + database->addTransaction(t); + } + refreshView(); + QMessageBox::information(this, "Success", QString("Imported %1 transactions.").arg(imported.size())); + } +} + void CashFlow::onQuit() { QApplication::quit(); } diff --git a/cashflow.h b/cashflow.h index cdf9a84..700a58c 100644 --- a/cashflow.h +++ b/cashflow.h @@ -53,6 +53,7 @@ private slots: void onExpandAll(); void onJumpToToday(); void onTransactionTableDoubleClicked(int row, int column); + void onImportCSV(); private: enum PeriodType { diff --git a/cashflow.ui b/cashflow.ui index 3bed492..40779c7 100644 --- a/cashflow.ui +++ b/cashflow.ui @@ -731,6 +731,7 @@ + @@ -793,6 +794,14 @@ Ctrl+E + + + Import CSV... + + + Ctrl+I + + diff --git a/importdialog.cpp b/importdialog.cpp new file mode 100644 index 0000000..962bca5 --- /dev/null +++ b/importdialog.cpp @@ -0,0 +1,461 @@ +#include "importdialog.h" +#include +#include +#include +#include +#include +#include +#include +#include +#include +#include +#include + +ImportDialog::ImportDialog(Database *db, QWidget *parent) + : QDialog(parent) + , database(db) +{ + setWindowTitle("Import CSV"); + resize(900, 600); + + QVBoxLayout *mainLayout = new QVBoxLayout(this); + + // File selection + QHBoxLayout *fileLayout = new QHBoxLayout(); + QLabel *fileLabel = new QLabel("CSV File:"); + browseBtn = new QPushButton("Browse..."); + connect(browseBtn, &QPushButton::clicked, this, &ImportDialog::onBrowseFile); + fileLayout->addWidget(fileLabel); + fileLayout->addWidget(browseBtn); + fileLayout->addStretch(); + mainLayout->addLayout(fileLayout); + + // Column mapping section + QGroupBox *mappingBox = new QGroupBox("Column Mapping"); + QFormLayout *mappingLayout = new QFormLayout(); + + dateColumnCombo = new QComboBox(); + amountColumnCombo = new QComboBox(); + withdrawalColumnCombo = new QComboBox(); + depositColumnCombo = new QComboBox(); + descriptionColumnCombo = new QComboBox(); + + mappingLayout->addRow("Date Column:", dateColumnCombo); + mappingLayout->addRow("Amount Column (or use Withdrawal/Deposit):", amountColumnCombo); + mappingLayout->addRow("Withdrawal Column:", withdrawalColumnCombo); + mappingLayout->addRow("Deposit Column:", depositColumnCombo); + mappingLayout->addRow("Description Column:", descriptionColumnCombo); + + mappingBox->setLayout(mappingLayout); + mainLayout->addWidget(mappingBox); + + // Default values section + QGroupBox *defaultsBox = new QGroupBox("Default Values"); + QFormLayout *defaultsLayout = new QFormLayout(); + + accountCombo = new QComboBox(); + accountCombo->setEditable(true); + categoryCombo = new QComboBox(); + categoryCombo->setEditable(true); + + // Populate from database + QSet accounts; + QSet categories; + for (const Transaction &t : database->getAllTransactions()) { + if (!t.account.isEmpty()) accounts.insert(t.account); + if (!t.category.isEmpty()) categories.insert(t.category); + } + accountCombo->addItems(accounts.values()); + categoryCombo->addItems(categories.values()); + + defaultsLayout->addRow("Account:", accountCombo); + defaultsLayout->addRow("Category:", categoryCombo); + + defaultsBox->setLayout(defaultsLayout); + mainLayout->addWidget(defaultsBox); + + // Preview table + QLabel *previewLabel = new QLabel("Preview (check rows to import):"); + mainLayout->addWidget(previewLabel); + + previewTable = new QTableWidget(); + previewTable->setSelectionBehavior(QAbstractItemView::SelectRows); + mainLayout->addWidget(previewTable); + + // Selection buttons + QHBoxLayout *selectionLayout = new QHBoxLayout(); + selectAllBtn = new QPushButton("Select All"); + deselectAllBtn = new QPushButton("Deselect All"); + connect(selectAllBtn, &QPushButton::clicked, this, &ImportDialog::onSelectAll); + connect(deselectAllBtn, &QPushButton::clicked, this, &ImportDialog::onDeselectAll); + selectionLayout->addWidget(selectAllBtn); + selectionLayout->addWidget(deselectAllBtn); + selectionLayout->addStretch(); + mainLayout->addLayout(selectionLayout); + + // Dialog buttons + QHBoxLayout *buttonLayout = new QHBoxLayout(); + importBtn = new QPushButton("Import"); + importBtn->setEnabled(false); + QPushButton *cancelBtn = new QPushButton("Cancel"); + connect(importBtn, &QPushButton::clicked, this, &ImportDialog::onImport); + connect(cancelBtn, &QPushButton::clicked, this, &QDialog::reject); + buttonLayout->addStretch(); + buttonLayout->addWidget(importBtn); + buttonLayout->addWidget(cancelBtn); + mainLayout->addLayout(buttonLayout); + + // Connect column mapping changes + connect(dateColumnCombo, QOverload::of(&QComboBox::currentIndexChanged), + this, &ImportDialog::onColumnMappingChanged); + connect(amountColumnCombo, QOverload::of(&QComboBox::currentIndexChanged), + this, &ImportDialog::onColumnMappingChanged); + connect(withdrawalColumnCombo, QOverload::of(&QComboBox::currentIndexChanged), + this, &ImportDialog::onColumnMappingChanged); + connect(depositColumnCombo, QOverload::of(&QComboBox::currentIndexChanged), + this, &ImportDialog::onColumnMappingChanged); + connect(descriptionColumnCombo, QOverload::of(&QComboBox::currentIndexChanged), + this, &ImportDialog::onColumnMappingChanged); +} + +ImportDialog::~ImportDialog() +{ +} + +void ImportDialog::onBrowseFile() +{ + QString fileName = QFileDialog::getOpenFileName( + this, + "Select CSV File", + QDir::homePath(), + "CSV Files (*.csv);;All Files (*)" + ); + + if (!fileName.isEmpty()) { + loadCsvFile(fileName); + } +} + +void ImportDialog::loadCsvFile(const QString &filePath) +{ + QFile file(filePath); + if (!file.open(QIODevice::ReadOnly | QIODevice::Text)) { + QMessageBox::critical(this, "Error", "Failed to open file: " + file.errorString()); + return; + } + + QTextStream in(&file); + csvRows.clear(); + csvHeaders.clear(); + + // Read header + if (!in.atEnd()) { + QString line = in.readLine(); + csvHeaders = line.split(','); + // Trim whitespace and quotes + for (QString &header : csvHeaders) { + header = header.trimmed(); + if (header.startsWith('"') && header.endsWith('"')) { + header = header.mid(1, header.length() - 2); + } + } + } + + // Read data rows + while (!in.atEnd()) { + QString line = in.readLine(); + QStringList row = line.split(','); + // Trim whitespace and quotes + for (QString &cell : row) { + cell = cell.trimmed(); + if (cell.startsWith('"') && cell.endsWith('"')) { + cell = cell.mid(1, cell.length() - 2); + } + } + if (row.size() == csvHeaders.size()) { + csvRows.append(row); + } + } + + file.close(); + + // Populate column mapping combos + dateColumnCombo->clear(); + amountColumnCombo->clear(); + withdrawalColumnCombo->clear(); + depositColumnCombo->clear(); + descriptionColumnCombo->clear(); + + dateColumnCombo->addItem("(None)", -1); + amountColumnCombo->addItem("(None)", -1); + withdrawalColumnCombo->addItem("(None)", -1); + depositColumnCombo->addItem("(None)", -1); + descriptionColumnCombo->addItem("(None)", -1); + + for (int i = 0; i < csvHeaders.size(); i++) { + dateColumnCombo->addItem(csvHeaders[i], i); + amountColumnCombo->addItem(csvHeaders[i], i); + withdrawalColumnCombo->addItem(csvHeaders[i], i); + depositColumnCombo->addItem(csvHeaders[i], i); + descriptionColumnCombo->addItem(csvHeaders[i], i); + } + + // Try to auto-detect columns + for (int i = 0; i < csvHeaders.size(); i++) { + QString header = csvHeaders[i].toLower(); + if (header.contains("date") || header.contains("transaction date")) { + dateColumnCombo->setCurrentIndex(i + 1); + } else if (header.contains("withdrawal") || header.contains("debit") || header.contains("withdrawals")) { + withdrawalColumnCombo->setCurrentIndex(i + 1); + } else if (header.contains("deposit") || header.contains("credit") || header.contains("deposits")) { + depositColumnCombo->setCurrentIndex(i + 1); + } else if (header.contains("amount")) { + amountColumnCombo->setCurrentIndex(i + 1); + } else if (header.contains("description") || header.contains("memo") || header.contains("payee")) { + descriptionColumnCombo->setCurrentIndex(i + 1); + } + } + + updatePreview(); + importBtn->setEnabled(true); +} + +void ImportDialog::updatePreview() +{ + if (csvRows.isEmpty()) { + return; + } + + // Setup preview table + previewTable->clear(); + previewTable->setColumnCount(csvHeaders.size() + 4); // +4 for checkbox, parsed date, amount, description + previewTable->setRowCount(csvRows.size()); + + QStringList headers; + headers << "Import" << "Parsed Date" << "Parsed Amount" << "Parsed Description"; + headers.append(csvHeaders); + previewTable->setHorizontalHeaderLabels(headers); + + int dateCol = dateColumnCombo->currentData().toInt(); + int amountCol = amountColumnCombo->currentData().toInt(); + int withdrawalCol = withdrawalColumnCombo->currentData().toInt(); + int depositCol = depositColumnCombo->currentData().toInt(); + int descCol = descriptionColumnCombo->currentData().toInt(); + + for (int row = 0; row < csvRows.size(); row++) { + // Checkbox + QCheckBox *checkbox = new QCheckBox(); + checkbox->setChecked(true); + QWidget *checkboxWidget = new QWidget(); + QHBoxLayout *layout = new QHBoxLayout(checkboxWidget); + layout->addWidget(checkbox); + layout->setAlignment(Qt::AlignCenter); + layout->setContentsMargins(0, 0, 0, 0); + previewTable->setCellWidget(row, 0, checkboxWidget); + + // Parse and show interpreted values + QString parsedDate = ""; + QString parsedAmount = ""; + QString parsedDesc = ""; + + if (dateCol >= 0 && dateCol < csvRows[row].size()) { + QDate date = QDate::fromString(csvRows[row][dateCol], "yyyy-MM-dd"); + if (!date.isValid()) { + date = QDate::fromString(csvRows[row][dateCol], "MM/dd/yyyy"); + } + if (!date.isValid()) { + date = QDate::fromString(csvRows[row][dateCol], "M/d/yyyy"); + } + if (date.isValid()) { + parsedDate = date.toString("yyyy-MM-dd"); + } + } + + // Use withdrawal/deposit if either is selected, otherwise use amount + if (withdrawalCol >= 0 || depositCol >= 0) { + // Parse withdrawal and deposit columns + double withdrawal = 0.0; + double deposit = 0.0; + + if (withdrawalCol >= 0 && withdrawalCol < csvRows[row].size()) { + QString withdrawalStr = csvRows[row][withdrawalCol]; + withdrawalStr.replace("$", "").replace(",", "").replace(" ", ""); + if (!withdrawalStr.isEmpty()) { + bool ok; + withdrawal = withdrawalStr.toDouble(&ok); + if (!ok) withdrawal = 0.0; + } + } + + if (depositCol >= 0 && depositCol < csvRows[row].size()) { + QString depositStr = csvRows[row][depositCol]; + depositStr.replace("$", "").replace(",", "").replace(" ", ""); + if (!depositStr.isEmpty()) { + bool ok; + deposit = depositStr.toDouble(&ok); + if (!ok) deposit = 0.0; + } + } + + // Calculate net amount (deposit is positive, withdrawal is negative) + double amount = deposit - withdrawal; + parsedAmount = QString::number(amount, 'f', 2); + } else if (amountCol >= 0 && amountCol < csvRows[row].size()) { + // Parse single amount column + QString amountStr = csvRows[row][amountCol]; + amountStr.replace("$", "").replace(",", "").replace(" ", ""); + bool ok; + double amount = amountStr.toDouble(&ok); + if (ok) { + parsedAmount = QString::number(amount, 'f', 2); + } + } + + if (descCol >= 0 && descCol < csvRows[row].size()) { + parsedDesc = csvRows[row][descCol]; + } + + previewTable->setItem(row, 1, new QTableWidgetItem(parsedDate)); + previewTable->setItem(row, 2, new QTableWidgetItem(parsedAmount)); + previewTable->setItem(row, 3, new QTableWidgetItem(parsedDesc)); + + // Original CSV columns + for (int col = 0; col < csvRows[row].size(); col++) { + previewTable->setItem(row, col + 4, new QTableWidgetItem(csvRows[row][col])); + } + } + + previewTable->resizeColumnsToContents(); +} + +void ImportDialog::onColumnMappingChanged() +{ + updatePreview(); +} + +void ImportDialog::onImport() +{ + importedTransactions.clear(); + + int dateCol = dateColumnCombo->currentData().toInt(); + int amountCol = amountColumnCombo->currentData().toInt(); + int withdrawalCol = withdrawalColumnCombo->currentData().toInt(); + int depositCol = depositColumnCombo->currentData().toInt(); + int descCol = descriptionColumnCombo->currentData().toInt(); + + if (dateCol < 0) { + QMessageBox::warning(this, "Missing Mapping", "Date column is required."); + return; + } + + // Check if either amount OR withdrawal/deposit columns are specified + if (amountCol < 0 && withdrawalCol < 0 && depositCol < 0) { + QMessageBox::warning(this, "Missing Mapping", "Either Amount column or Withdrawal/Deposit columns are required."); + return; + } + + QString defaultAccount = accountCombo->currentText(); + QString defaultCategory = categoryCombo->currentText(); + + int importCount = 0; + for (int row = 0; row < previewTable->rowCount(); row++) { + QWidget *widget = previewTable->cellWidget(row, 0); + QCheckBox *checkbox = widget->findChild(); + + if (checkbox && checkbox->isChecked()) { + Transaction t; + t.id = -1; + + // Parse date + QString dateStr = csvRows[row][dateCol]; + t.date = QDate::fromString(dateStr, "yyyy-MM-dd"); + if (!t.date.isValid()) { + t.date = QDate::fromString(dateStr, "MM/dd/yyyy"); + } + if (!t.date.isValid()) { + t.date = QDate::fromString(dateStr, "M/d/yyyy"); + } + + // Parse amount - use withdrawal/deposit if either is selected, otherwise use amount + if (withdrawalCol >= 0 || depositCol >= 0) { + double withdrawal = 0.0; + double deposit = 0.0; + + if (withdrawalCol >= 0 && withdrawalCol < csvRows[row].size()) { + QString withdrawalStr = csvRows[row][withdrawalCol]; + withdrawalStr.replace("$", "").replace(",", "").replace(" ", ""); + if (!withdrawalStr.isEmpty()) { + withdrawal = withdrawalStr.toDouble(); + } + } + + if (depositCol >= 0 && depositCol < csvRows[row].size()) { + QString depositStr = csvRows[row][depositCol]; + depositStr.replace("$", "").replace(",", "").replace(" ", ""); + if (!depositStr.isEmpty()) { + deposit = depositStr.toDouble(); + } + } + + t.amount = deposit - withdrawal; + } else if (amountCol >= 0 && amountCol < csvRows[row].size()) { + QString amountStr = csvRows[row][amountCol]; + amountStr.replace("$", "").replace(",", "").replace(" ", ""); + t.amount = amountStr.toDouble(); + } + + // Parse description + if (descCol >= 0 && descCol < csvRows[row].size()) { + t.description = csvRows[row][descCol]; + } + + t.account = defaultAccount; + t.category = defaultCategory; + t.type = TransactionType::Actual; + t.recurringId = -1; + t.reconciled = false; + t.sortOrder = 0; + + if (t.date.isValid()) { + importedTransactions.append(t); + importCount++; + } + } + } + + if (importCount == 0) { + QMessageBox::information(this, "No Rows Selected", "No valid rows were selected for import."); + return; + } + + QMessageBox::information(this, "Import Complete", + QString("Successfully imported %1 transactions.").arg(importCount)); + accept(); +} + +void ImportDialog::onSelectAll() +{ + for (int row = 0; row < previewTable->rowCount(); row++) { + QWidget *widget = previewTable->cellWidget(row, 0); + QCheckBox *checkbox = widget->findChild(); + if (checkbox) { + checkbox->setChecked(true); + } + } +} + +void ImportDialog::onDeselectAll() +{ + for (int row = 0; row < previewTable->rowCount(); row++) { + QWidget *widget = previewTable->cellWidget(row, 0); + QCheckBox *checkbox = widget->findChild(); + if (checkbox) { + checkbox->setChecked(false); + } + } +} + +QList ImportDialog::getImportedTransactions() const +{ + return importedTransactions; +} diff --git a/importdialog.h b/importdialog.h new file mode 100644 index 0000000..68ae7ae --- /dev/null +++ b/importdialog.h @@ -0,0 +1,58 @@ +#ifndef IMPORTDIALOG_H +#define IMPORTDIALOG_H + +#include +#include +#include +#include +#include +#include +#include +#include +#include +#include "transaction.h" +#include "database.h" + +class ImportDialog : public QDialog +{ + Q_OBJECT + +public: + explicit ImportDialog(Database *db, QWidget *parent = nullptr); + ~ImportDialog(); + + QList getImportedTransactions() const; + +private slots: + void onBrowseFile(); + void onColumnMappingChanged(); + void onImport(); + void onSelectAll(); + void onDeselectAll(); + +private: + Database *database; + QTableWidget *previewTable; + QPushButton *browseBtn; + QPushButton *importBtn; + QPushButton *selectAllBtn; + QPushButton *deselectAllBtn; + + QComboBox *dateColumnCombo; + QComboBox *amountColumnCombo; + QComboBox *withdrawalColumnCombo; + QComboBox *depositColumnCombo; + QComboBox *descriptionColumnCombo; + QComboBox *accountCombo; + QComboBox *categoryCombo; + + QStringList csvHeaders; + QList csvRows; + QList importedTransactions; + + void loadCsvFile(const QString &filePath); + void updatePreview(); + Transaction parseRow(int rowIndex); +}; + +#endif // IMPORTDIALOG_H -- cgit v1.2.3