Skip to content

CSV import into a chosen schema, match columns by name, and locale number display #4251

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
deepsidhu1313 wants to merge 3 commits into
base: master
Choose a base branch
from
Open

CSV import into a chosen schema, match columns by name, and locale number display #4251

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
3 commits
Select commit Hold shift + click to select a range
f87ff95
Allow choosing the schema when importing a CSV file
claude Oct 31, 2026
be074e3
Add an option to match CSV columns to table columns by name
claude Oct 31, 2026
c4f65a7
Add an option to show numbers in the system number format
claude Oct 31, 2026
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
130 changes: 116 additions & 14 deletions src/ImportCsvDialog.cpp
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -59,6 +59,16 @@ ImportCsvDialog::ImportCsvDialog(const std::vector<QString>& filenames, DBBrowse
ui->editName->setText(file.baseName());
}

// List the schemata to import into. The main schema is the default and comes first. The temp schema is always offered, even though SQLite
// only reports it once it has been used. The attached schemata follow in the same order as everywhere else.
ui->comboSchema->addItem("main");
ui->comboSchema->addItem("temp");
for(const auto& schema : pdb->schemata)
{
if(schema.first != "main" && schema.first != "temp")
ui->comboSchema->addItem(QString::fromStdString(schema.first));
}

// Create a list of all available encodings and create an auto completion list from them
encodingCompleter = new QCompleter(toStringList(QTextCodec::availableCodecs()), this);
encodingCompleter->setCaseSensitivity(Qt::CaseInsensitive);
Expand Down Expand Up @@ -92,6 +102,14 @@ ImportCsvDialog::ImportCsvDialog(const std::vector<QString>& filenames, DBBrowse
ui->comboEncoding->blockSignals(false);
ui->checkReplaceNonAlnum->blockSignals(false);

// The names in the first line are what the columns are matched by
auto enableMatchColumnsByName = [this](bool header) {
ui->labelMatchColumnsByName->setEnabled(header);
ui->checkMatchColumnsByName->setEnabled(header);
};
connect(ui->checkboxHeader, &QCheckBox::toggled, this, enableMatchColumnsByName);
enableMatchColumnsByName(ui->checkboxHeader->isChecked());

// Prepare and show interface depending on how many files are selected
if (csvFilenames.size() > 1)
{
Expand Down Expand Up @@ -141,6 +159,34 @@ void rollback(
}
pdb->revertToSavepoint(savepointName);
}

// Find the column of the table to import each column of the CSV file into by comparing the names, ignoring the case. If a name is found
// more than once in the file, only the first of the columns is imported. The result has one entry for each column of the CSV file and
// the entry is a null pointer for the columns which are not imported because there is no column to import them into.
std::vector<const sqlb::Field*> matchFieldsByName(const sqlb::FieldVector& csvFields, const sqlb::Table& table)
{
std::vector<const sqlb::Field*> result;
std::vector<bool> used(table.fields.size(), false);

for(const sqlb::Field& csvField : csvFields)
{
const sqlb::Field* match = nullptr;
for(size_t i=0;i<table.fields.size();i++)
{
// Values cannot be inserted into generated columns
if(!used[i] && !table.fields[i].generated() &&
QString::compare(QString::fromStdString(csvField.name()), QString::fromStdString(table.fields[i].name()), Qt::CaseInsensitive) == 0)
{
used[i] = true;
match = &table.fields[i];
break;
}
}
result.push_back(match);
}

return result;
}
}

class CSVImportProgress : public CSVProgress
Expand Down Expand Up @@ -529,23 +575,45 @@ bool ImportCsvDialog::importCsv(const QString& fileName, const QString& name)
if(fieldList.size() == 0)
return true;

// The table is looked up and created in the selected schema
const sqlb::ObjectIdentifier tableId(ui->comboSchema->currentText().toStdString(), tableName.toStdString());
const QString tableDisplayName = QString::fromStdString(tableId.toDisplayString());

// Are we importing into an existing table?
bool importToExistingTable = false;
const sqlb::TablePtr tbl = pdb->getTableByName(sqlb::ObjectIdentifier("main", tableName.toStdString()));
const sqlb::TablePtr tbl = pdb->getTableByName(tableId);

// The column of the existing table to import each column of the CSV file into. By default the columns are used in the order they are in. When
// matching by name, a column of the file which has no match is not imported and is null here.
std::vector<const sqlb::Field*> targetFields;
const bool matchColumnsByName = ui->checkMatchColumnsByName->isChecked() && ui->checkboxHeader->isChecked();

if(tbl)
{
if(tbl->fields.size() != fieldList.size())
if(matchColumnsByName)
{
targetFields = matchFieldsByName(fieldList, *tbl);
} else {
for(size_t i=0;i<tbl->fields.size() && i<fieldList.size();i++)
targetFields.push_back(&tbl->fields[i]);
}

if(matchColumnsByName && std::none_of(targetFields.begin(), targetFields.end(), [](const sqlb::Field* f) { return f != nullptr; }))
{
QMessageBox::warning(this, QApplication::applicationName(),
tr("There is already a table named '%1' and an import into an existing table is only possible if the number of columns match.").arg(tableName));
tr("There is already a table named '%1' but none of the column names in the CSV file matches one of its columns.").arg(tableDisplayName));
return true;
} else if(!matchColumnsByName && tbl->fields.size() != fieldList.size()) {
QMessageBox::warning(this, QApplication::applicationName(),
tr("There is already a table named '%1' and an import into an existing table is only possible if the number of columns match.").arg(tableDisplayName));
return true;
} else {
// Only ask whether to import into any table if the 'Yes to All' button has not been clicked (yet) (empty string is included).
// Only ask whether to import into the existing table if the 'Yes' button has not been clicked (yet) for that table.
if(!dontAskForExistingTableAgain.contains("") && !dontAskForExistingTableAgain.contains(tableName))
if(!dontAskForExistingTableAgain.contains("") && !dontAskForExistingTableAgain.contains(tableDisplayName))
{
int answer = QMessageBox::question(this, QApplication::applicationName(),
tr("There is already a table named '%1'. Do you want to import the data into it?").arg(tableName),
tr("There is already a table named '%1'. Do you want to import the data into it?").arg(tableDisplayName),
QMessageBox::Yes | QMessageBox::No | QMessageBox::YesAll | QMessageBox::Cancel, QMessageBox::No);

// Stop now if the No button has been clicked
Expand All @@ -559,7 +627,7 @@ bool ImportCsvDialog::importCsv(const QString& fileName, const QString& name)

// If the 'Yes' button has been clicked, save the answer for that table for later
case QMessageBox::Yes:
dontAskForExistingTableAgain.append(tableName);
dontAskForExistingTableAgain.append(tableDisplayName);
break;
// If the 'Yes to All' button has been clicked, save the answer for any future table name. An empty string is used in that case.
case QMessageBox::YesAll:
Expand Down Expand Up @@ -590,24 +658,33 @@ bool ImportCsvDialog::importCsv(const QString& fileName, const QString& name)
bool failOnMissing = ui->checkFailOnMissing->isChecked();
if(!importToExistingTable)
{
if(!pdb->createTable(sqlb::ObjectIdentifier("main", tableName.toStdString()), fieldList))
if(!pdb->createTable(tableId, fieldList))
{
rollback(this, pdb, nullptr, restorepointName, 0, tr("Creating the table failed: %1").arg(pdb->lastError()));
return false;
}

// If we're creating the table in this import session, don't ask the user if it's okay to import more data into it. It seems
// safe to just assume that's what they want.
dontAskForExistingTableAgain.append(tableName);
dontAskForExistingTableAgain.append(tableDisplayName);
} else {
// Importing into an existing table. So find out something about it's structure.

// Prepare the values for each table column that are to be inserted if the field in the CSV file is empty. Depending on the data type
// and the constraints of a field, we need to handle this case differently.
if(tbl)
{
for(const sqlb::Field& f : tbl->fields)
for(const sqlb::Field* target : targetFields)
{
// Columns of the file which are not imported never get a value
if(!target)
{
failOnMissingFieldList.push_back(false);
nullValues.push_back(QByteArray());
continue;
}
const sqlb::Field& f = *target;

// For determining the value for empty fields we follow a set of rules

// Normally we don't have to fail the import when importing an empty field. This last value of the vector
Expand Down Expand Up @@ -646,11 +723,30 @@ bool ImportCsvDialog::importCsv(const QString& fileName, const QString& name)
}

// Prepare the INSERT statement. The prepared statement can then be reused for each row to insert
std::string sQuery = "INSERT " + currentOnConflictStrategy() + " INTO " + sqlb::escapeIdentifier(tableName.toStdString()) + " VALUES(";
for(size_t i=1;i<=fieldList.size();i++)
sQuery += "?" + std::to_string(i) + ",";
sQuery.pop_back(); // Remove last comma
sQuery.append(")");
std::string sQuery = "INSERT " + currentOnConflictStrategy() + " INTO " + tableId.toString();
if(importToExistingTable && matchColumnsByName)
{
// Name the columns to insert into. The parameters are numbered by the position of the column in the file, so the values need not be
// bound in the order of the table. There is no parameter for the columns of the file which are not imported.
std::string columns, values;
for(size_t i=0;i<targetFields.size();i++)
{
if(targetFields[i])
{
columns += sqlb::escapeIdentifier(targetFields[i]->name()) + ",";
values += "?" + std::to_string(i + 1) + ",";
}
}
columns.pop_back(); // Remove last comma
values.pop_back();
sQuery += " (" + columns + ") VALUES(" + values + ")";
} else {
sQuery += " VALUES(";
for(size_t i=1;i<=fieldList.size();i++)
sQuery += "?" + std::to_string(i) + ",";
sQuery.pop_back(); // Remove last comma
sQuery.append(")");
}
sqlite3_stmt* stmt;
auto pDb = pdb->get(tr("importing CSV"));
if(sqlite3_prepare_v2(pDb.get(), sQuery.c_str(), static_cast<int>(sQuery.size()), &stmt, nullptr) != SQLITE_OK)
Expand Down Expand Up @@ -678,6 +774,10 @@ bool ImportCsvDialog::importCsv(const QString& fileName, const QString& name)
// Bind all values
for(size_t i=0;i<rowData.num_fields;i++)
{
// Skip the columns of the file which have no column to be imported into
if(importToExistingTable && i < targetFields.size() && !targetFields[i])
continue;

// Empty values need special treatment
// When importing into an existing table where we could find out something about its table definition
if(importToExistingTable && rowData.fields[i].data_length == 0 && nullValues.size() > i)
Expand Down Expand Up @@ -895,6 +995,8 @@ void ImportCsvDialog::toggleAdvancedSection(bool show)
ui->checkIgnoreDefaults->setVisible(show);
ui->labelOnConflictStrategy->setVisible(show);
ui->comboOnConflictStrategy->setVisible(show);
ui->labelMatchColumnsByName->setVisible(show);
ui->checkMatchColumnsByName->setVisible(show);
ui->labelReplaceNonAlnum->setVisible(show);
ui->checkReplaceNonAlnum->setVisible(show);
}
Expand Down
42 changes: 41 additions & 1 deletion src/ImportCsvDialog.ui
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -30,7 +30,28 @@
</widget>
</item>
<item row="0" column="1">
<widget class="QLineEdit" name="editName"/>
<layout class="QHBoxLayout" name="horizontalLayoutName">
<item>
<widget class="QLineEdit" name="editName"/>
</item>
<item>
<widget class="QLabel" name="labelSchema">
<property name="text">
<string>Schema</string>
</property>
<property name="buddy">
<cstring>comboSchema</cstring>
</property>
</widget>
</item>
<item>
<widget class="QComboBox" name="comboSchema">
<property name="toolTip">
<string>The schema in which the table is created or, if it exists already, looked up. Choose 'temp' for a temporary table or the name of an attached database.</string>
</property>
</widget>
</item>
</layout>
</item>
<item row="1" column="0">
<widget class="QLabel" name="labelHeader">
Expand Down Expand Up @@ -399,6 +420,23 @@
</property>
</widget>
</item>
<item row="15" column="1">
<widget class="QCheckBox" name="checkMatchColumnsByName">
<property name="toolTip">
<string>When importing into an existing table, import each column of the file into the table column with the same name instead of relying on the order of the columns. The names are compared ignoring the case. Columns of the file which have no match are skipped and columns of the table which are not in the file get their default value. This requires the column names to be in the first line.</string>
</property>
</widget>
</item>
<item row="15" column="0">
<widget class="QLabel" name="labelMatchColumnsByName">
<property name="text">
<string>Match columns by name</string>
</property>
<property name="buddy">
<cstring>checkMatchColumnsByName</cstring>
</property>
</widget>
</item>
<item row="13" column="1">
<widget class="QCheckBox" name="checkReplaceNonAlnum">
<property name="toolTip">
Expand Down Expand Up @@ -514,6 +552,7 @@
</widget>
<tabstops>
<tabstop>editName</tabstop>
<tabstop>comboSchema</tabstop>
<tabstop>checkboxHeader</tabstop>
<tabstop>comboSeparator</tabstop>
<tabstop>editCustomSeparator</tabstop>
Expand All @@ -529,6 +568,7 @@
<tabstop>checkLocalConventions</tabstop>
<tabstop>checkFailOnMissing</tabstop>
<tabstop>comboOnConflictStrategy</tabstop>
<tabstop>checkMatchColumnsByName</tabstop>
<tabstop>filePicker</tabstop>
<tabstop>toggleSelected</tabstop>
<tabstop>matchSimilar</tabstop>
Expand Down
2 changes: 2 additions & 0 deletions src/PreferencesDialog.cpp
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -123,6 +123,7 @@ void PreferencesDialog::loadSettings()
ui->spinCompleteThreshold->setValue(Settings::getValue("databrowser", "complete_threshold").toInt());
ui->checkShowImagesInline->setChecked(Settings::getValue("databrowser", "image_preview").toBool());
ui->checkCellWordWrap->setChecked(Settings::getValue("databrowser", "cell_word_wrap").toBool());
ui->checkLocaleNumbers->setChecked(Settings::getValue("databrowser", "locale_numbers").toBool());
ui->txtNull->setText(Settings::getValue("databrowser", "null_text").toString());
ui->txtBlob->setText(Settings::getValue("databrowser", "blob_text").toString());
ui->editFilterEscape->setText(Settings::getValue("databrowser", "filter_escape").toString());
Expand Down Expand Up @@ -209,6 +210,7 @@ void PreferencesDialog::saveSettings(bool accept)
Settings::setValue("databrowser", "fontsize", ui->spinDataBrowserFontSize->value());
Settings::setValue("databrowser", "image_preview", ui->checkShowImagesInline->isChecked());
Settings::setValue("databrowser", "cell_word_wrap", ui->checkCellWordWrap->isChecked());
Settings::setValue("databrowser", "locale_numbers", ui->checkLocaleNumbers->isChecked());
saveColorSetting(ui->fr_null_fg, "null_fg");
saveColorSetting(ui->fr_null_bg, "null_bg");
saveColorSetting(ui->fr_reg_fg, "reg_fg");
Expand Down
18 changes: 18 additions & 0 deletions src/PreferencesDialog.ui
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -1343,6 +1343,23 @@ Can be set to 0 for disabling completion.</string>
</property>
</widget>
</item>
<item row="4" column="0">
<widget class="QLabel" name="labelLocaleNumbers">
<property name="text">
<string>Show numbers in the system number format</string>
</property>
<property name="buddy">
<cstring>checkLocaleNumbers</cstring>
</property>
</widget>
</item>
<item row="4" column="1">
<widget class="QCheckBox" name="checkLocaleNumbers">
<property name="toolTip">
<string>Enable this option to show the numbers in the cells of columns with a numeric type (INTEGER, REAL or NUMERIC) with the digit grouping and decimal separator of the system locale, e.g. 1.234,5 instead of 1234.5. Only the display changes. The stored values and the values which are edited, copied or exported stay the same.</string>
</property>
</widget>
</item>
</layout>
</widget>
</item>
Expand Down Expand Up @@ -2071,6 +2088,7 @@ Can be set to 0 for disabling completion.</string>
<tabstop>spinCompleteThreshold</tabstop>
<tabstop>checkShowImagesInline</tabstop>
<tabstop>checkCellWordWrap</tabstop>
<tabstop>checkLocaleNumbers</tabstop>
<tabstop>spinFilterDelay</tabstop>
<tabstop>editFilterEscape</tabstop>
<tabstop>treeSyntaxHighlighting</tabstop>
Expand Down
2 changes: 2 additions & 0 deletions src/Settings.cpp
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -319,6 +319,8 @@ QVariant Settings::getDefaultValue(const std::string& group, const std::string&
return 1000;
if(name == "image_preview")
return false;
if(name == "locale_numbers")
return false;
if(name == "cell_word_wrap")
return true;
if(name == "indent_compact")
Expand Down
Loading
Loading