Skip to content

Improve RFC 4180 compliance of CSV import/export and add a quoted-as-text option #4246

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

Improve RFC 4180 compliance of CSV import/export and add a quoted-as-text option #4246

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
99c654e
Improve RFC 4180 compliance of the CSV import and export
claude Oct 30, 2026
c478131
Add an option to import quoted CSV values as text
claude Oct 30, 2026
bf8d023
Use UTF-8 for the text streams in the CSV import tests
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
23 changes: 6 additions & 17 deletions src/ExportDataDialog.cpp
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -6,6 +6,7 @@
#include "FileDialog.h"
#include "IconCache.h"
#include "Data.h"
#include "csvparser.h"

#include <QSaveFile>
#include <QTextStream>
Expand Down Expand Up @@ -101,12 +102,9 @@ bool ExportDataDialog::exportQueryCsv(const std::string& sQuery, const QString&
{
// Prepare the quote and separating characters
QChar quoteChar = currentQuoteChar();
QString quotequoteChar = QString(quoteChar) + quoteChar;
QChar sepChar = currentSeparatorChar();
QString newlineStr = currentNewLineString();

// Chars that require escaping
std::string special_chars = newlineStr.toStdString() + sepChar.toLatin1() + quoteChar.toLatin1();
bool writeError = false;
// Open file
QSaveFile file(sFilename);
Expand All @@ -126,11 +124,7 @@ bool ExportDataDialog::exportQueryCsv(const std::string& sQuery, const QString&
int columns = sqlite3_column_count(stmt);
for (int i = 0; i < columns; ++i)
{
QString content = QString::fromUtf8(sqlite3_column_name(stmt, i));
if(content.toStdString().find_first_of(special_chars) != std::string::npos)
stream << quoteChar << content.replace(quoteChar, quotequoteChar) << quoteChar;
else
stream << content;
stream << quoteCsvField(QString::fromUtf8(sqlite3_column_name(stmt, i)), sepChar, quoteChar, newlineStr);
if(i != columns - 1)
// Only output the separator value if sepChar isn't 0,
// as that's used to indicate no separator character
Expand All @@ -155,16 +149,11 @@ bool ExportDataDialog::exportQueryCsv(const std::string& sQuery, const QString&
// Convert to base64 if the data is binary. This case doesn't need quotes.
if(!isTextOnly(blob))
stream << blob.toBase64();
// If no quote char is set but the content contains a line break, we enforce some quote characters. This probably isn't entirely correct
// but still better than having the line breaks unquoted and effectively outputting a garbage file.
else if(quoteChar.isNull() && content.contains(newlineStr))
stream << '"' << content.replace('"', "\"\"") << '"';
// If the content needs to be quoted, quote it. But only if a quote char has been specified
else if(!quoteChar.isNull() && content.toStdString().find_first_of(special_chars) != std::string::npos)
stream << quoteChar << content.replace(quoteChar, quotequoteChar) << quoteChar;
// If it doesn't need to be quoted, don't quote it
// Otherwise quote the content if necessary. If no quote char is set but the content contains a line break, some quote
// characters are enforced. This probably isn't entirely correct but still better than having the line breaks unquoted
// and effectively outputting a garbage file.
else
stream << content;
stream << quoteCsvField(content, sepChar, quoteChar, newlineStr);

if(i != columns - 1)
// Only output the separator value if sepChar isn't 0,
Expand Down
11 changes: 10 additions & 1 deletion src/ImportCsvDialog.cpp
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -79,6 +79,7 @@ ImportCsvDialog::ImportCsvDialog(const std::vector<QString>& filenames, DBBrowse
ui->checkBoxSeparateTables->setChecked(Settings::getValue("importcsv", "separatetables").toBool());
ui->checkLocalConventions->setChecked(Settings::getValue("importcsv", "localconventions").toBool());
ui->checkReplaceNonAlnum->setChecked(Settings::getValue("importcsv", "replacenonalnum").toBool());
ui->checkQuotedAsText->setChecked(Settings::getValue("importcsv", "quotedastext").toBool());
setSeparatorChar(getSettingsChar("importcsv", "separator"));
setQuoteChar(getSettingsChar("importcsv", "quotecharacter"));
setEncoding(Settings::getValue("importcsv", "encoding").toString());
Expand Down Expand Up @@ -198,6 +199,7 @@ void ImportCsvDialog::accept()
Settings::setValue("importcsv", "localconventions", ui->checkLocalConventions->isChecked());
Settings::setValue("importcsv", "encoding", currentEncoding());
Settings::setValue("importcsv", "replacenonalnum", ui->checkReplaceNonAlnum->isChecked());
Settings::setValue("importcsv", "quotedastext", ui->checkQuotedAsText->isChecked());

// Get all the selected files and start the import
if (ui->filePickerBlock->isVisible())
Expand Down Expand Up @@ -470,6 +472,10 @@ sqlb::FieldVector ImportCsvDialog::generateFieldList(const QString& filename) co
locale.toLongLong(content, &convert_to_integer);
locale.toDouble(content, &convert_to_real);

// If the user wants quoted values to be text, don't treat them as numbers
if(ui->checkQuotedAsText->isChecked() && rowData.fields[i].quoted)
convert_to_integer = convert_to_real = false;

// Set new data type. If we don't find any better data type, we fall back to the TEXT data type
std::string new_type = "TEXT";
if(old_type == "INTEGER" && !convert_to_integer && convert_to_real) // So far it's integer, but now it's only convertible to float
Expand Down Expand Up @@ -588,6 +594,7 @@ bool ImportCsvDialog::importCsv(const QString& fileName, const QString& name)
std::vector<bool> failOnMissingFieldList;
bool ignoreDefaults = ui->checkIgnoreDefaults->isChecked();
bool failOnMissing = ui->checkFailOnMissing->isChecked();
bool quotedAsText = ui->checkQuotedAsText->isChecked();
if(!importToExistingTable)
{
if(!pdb->createTable(sqlb::ObjectIdentifier("main", tableName.toStdString()), fieldList))
Expand Down Expand Up @@ -696,7 +703,7 @@ bool ImportCsvDialog::importCsv(const QString& fileName, const QString& name)
} else {
// This is a non-empty value, or we want to insert the empty string. Just add it to the statement.
bool convert_ok = false;
if(ui->checkLocalConventions->isChecked()) {
if(ui->checkLocalConventions->isChecked() && !(quotedAsText && rowData.fields[i].quoted)) {
// Find the correct data type taking into account the locale.
QString content = QString::fromUtf8(rowData.fields[i].data, static_cast<int>(rowData.fields[i].data_length));
sqlite_int64 int64_value = QLocale::system().toLongLong(content, &convert_ok);
Expand Down Expand Up @@ -897,6 +904,8 @@ void ImportCsvDialog::toggleAdvancedSection(bool show)
ui->comboOnConflictStrategy->setVisible(show);
ui->labelReplaceNonAlnum->setVisible(show);
ui->checkReplaceNonAlnum->setVisible(show);
ui->labelQuotedAsText->setVisible(show);
ui->checkQuotedAsText->setVisible(show);
}

char32_t ImportCsvDialog::toUtf8(const QString& s) const
Expand Down
18 changes: 18 additions & 0 deletions src/ImportCsvDialog.ui
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -416,6 +416,23 @@
</property>
</widget>
</item>
<item row="14" column="1">
<widget class="QCheckBox" name="checkQuotedAsText">
<property name="toolTip">
<string>Import values enclosed in quote characters as text, even if they look like numbers, e.g. "007". When creating a new table, columns containing such values get the TEXT data type.</string>
</property>
</widget>
</item>
<item row="14" column="0">
<widget class="QLabel" name="labelQuotedAsText">
<property name="text">
<string>Treat quoted values as text</string>
</property>
<property name="buddy">
<cstring>checkQuotedAsText</cstring>
</property>
</widget>
</item>
</layout>
</item>
<item>
Expand Down Expand Up @@ -528,6 +545,7 @@
<tabstop>checkNoTypeDetection</tabstop>
<tabstop>checkLocalConventions</tabstop>
<tabstop>checkFailOnMissing</tabstop>
<tabstop>checkQuotedAsText</tabstop>
<tabstop>comboOnConflictStrategy</tabstop>
<tabstop>filePicker</tabstop>
<tabstop>toggleSelected</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 @@ -200,6 +200,8 @@ QVariant Settings::getDefaultValue(const std::string& group, const std::string&
return "UTF-8";
if(name == "localconventions")
return false;
if(name == "quotedastext")
return false;
}

// exportsql group?
Expand Down
35 changes: 34 additions & 1 deletion src/csvparser.cpp
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -58,6 +58,7 @@ inline void increaseRowSize(CSVRow& r)
r.fields[i].buffer = nullptr;
r.fields[i].buffer_length = 0;
r.fields[i].buffer_max_length = 0;
r.fields[i].quoted = false;
}
}
}
Expand Down Expand Up @@ -96,6 +97,9 @@ inline CSVField* addColumn(CSVRow& r, CSVField* field, bool trim)
// Increase field list size if it is too small
increaseRowSize(r);

// The next field is not known to be quoted until we find its opening quote character
r.fields[r.num_fields].quoted = false;

// Return pointer to the next field
return &r.fields[r.num_fields];
}
Expand All @@ -110,6 +114,7 @@ inline CSVField* addRow(CSVParser::csvRowFunction f, CSVRow& r, size_t& rowCount

// Reset the field list by setting the field count to 0. No need to deconstruct anything else.
r.num_fields = 0;
r.fields->quoted = false;

// Increase row count by one, as we're now starting to parse the next row
rowCount++;
Expand Down Expand Up @@ -188,6 +193,7 @@ CSVParser::ParserResult CSVParser::parse(csvRowFunction insertFunction, QTextStr
m_iNumExtraBytesQuoteChar))
{
state = StateInQuote;
field->quoted = true;
it += m_iNumExtraBytesQuoteChar;
}
else
Expand Down Expand Up @@ -278,6 +284,7 @@ CSVParser::ParserResult CSVParser::parse(csvRowFunction insertFunction, QTextStr
// look ahead to check for linefeed
if(!look_ahead(stream, sBuffer, &it, &sBufferEnd, '\n'))
{
state = StateNormal;
addColumn(record, field, m_bTrimFields);

if(!(field = addRow(insertFunction, record, parsedRows)))
Expand All @@ -304,7 +311,8 @@ CSVParser::ParserResult CSVParser::parse(csvRowFunction insertFunction, QTextStr
}
}

if(record.num_fields || record.fields->buffer_length)
// Also parse a last field which is empty but quoted. In this case there is no content in the buffers but we are still in the end quote state.
if(record.num_fields || record.fields->buffer_length || state == StateEndQuote)
{
addColumn(record, field, m_bTrimFields);

Expand Down Expand Up @@ -357,3 +365,28 @@ bool CSVParser::look_ahead(QTextStream& stream, QByteArray& sBuffer, const char*
}
return true;
}

QString quoteCsvField(const QString& content, QChar separator, QChar quote, const QString& newline)
{
// Everything which could be taken for a line break needs to be quoted, not only the configured line break. A lone CR or LF is a
// line break for many CSV readers, including the parser in this file, even when the records are separated by something else.
const bool containsLineBreak = content.contains(QChar('\r')) || content.contains(QChar('\n')) || (!newline.isEmpty() && content.contains(newline));

if(quote.isNull())
{
// Quoting has been disabled but there is no way to write a line break without it
if(!containsLineBreak)
return content;

QString escaped = content;
return QChar('"') + escaped.replace(QChar('"'), QString("\"\"")) + QChar('"');
}

if(containsLineBreak || content.contains(quote) || (!separator.isNull() && content.contains(separator)))
{
QString escaped = content;
return quote + escaped.replace(quote, QString(2, quote)) + quote;
}

return content;
}
19 changes: 19 additions & 0 deletions src/csvparser.h
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -5,6 +5,8 @@
#include <cstdint>
#include <cstddef>

#include <QString>

class QByteArray;
class QTextStream;

Expand Down Expand Up @@ -36,6 +38,8 @@ struct CSVField
// These variables point to the parsed contents of the field and can be directly passed to SQLite functions
char* data; // Pointer to the start of the field contents
uint64_t data_length; // Length of the field contents

bool quoted; // True if the field was enclosed in quote characters in the file
};

/*
Expand Down Expand Up @@ -99,4 +103,19 @@ class CSVParser
const char* expected, int extraBytes);
};

/*!
* \brief Prepares a value for being written to a CSV file.
*
* The value is enclosed in quote characters if it contains the separator, the quote character or a line break. Quote characters inside
* the value are escaped by doubling them.
*
* \param content The value to write.
* \param separator The field separator. A null character means that there is no field separator.
* \param quote The quote character. A null character means that quoting has been disabled. Values containing line breaks are quoted
* anyway in this case, using a double quote, because the file would be unusable otherwise.
* \param newline The line break that is written after each record.
* \return The value ready to be written to the file.
*/
QString quoteCsvField(const QString& content, QChar separator, QChar quote, const QString& newline);

#endif
69 changes: 69 additions & 0 deletions src/tests/CMakeLists.txt
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -123,3 +123,72 @@ set(TESTCACHE_HDR
add_executable(test-cache ${TESTCACHE_HDR} ${TESTCACHE_SRC})
target_link_libraries(test-cache ${QT_MAJOR}::Test)
add_test(NAME test-cache COMMAND $<TARGET_FILE:test-cache>)

# test import csv dialog

set(TESTIMPORTCSVDIALOG_SRC
TestImportCsvDialog.cpp
../ImportCsvDialog.cpp
../csvparser.cpp
../sqlitedb.cpp
../sqlitetablemodel.cpp
../RowLoader.cpp
../CipherDialog.cpp
../CipherSettings.cpp
../CondFormat.cpp
../Data.cpp
../Settings.cpp
../sql/sqlitetypes.cpp
../sql/Query.cpp
../sql/ObjectIdentifier.cpp
../sql/parser/ParserDriver.cpp
../sql/parser/sqlite3_lexer.cpp
../sql/parser/sqlite3_parser.cpp
)

set(TESTIMPORTCSVDIALOG_HDR
TestImportCsvDialog.h
../ImportCsvDialog.h
../csvparser.h
../sqlitedb.h
../sqlitetablemodel.h
../RowLoader.h
../RowCache.h
../CipherDialog.h
../CipherSettings.h
../CondFormat.h
../Data.h
../Settings.h
../sql/sqlitetypes.h
../sql/Query.h
../sql/ObjectIdentifier.h
../sql/parser/ParserDriver.h
../sql/parser/sqlite3_lexer.h
../sql/parser/sqlite3_location.h
../sql/parser/sqlite3_parser.hpp
)

add_executable(test-import-csv-dialog ${TESTIMPORTCSVDIALOG_HDR} ${TESTIMPORTCSVDIALOG_SRC} ../ImportCsvDialog.ui ../CipherDialog.ui)
target_include_directories(test-import-csv-dialog SYSTEM PRIVATE ${CMAKE_SOURCE_DIR}/libs/json)
if(QT_MAJOR STREQUAL "Qt6")
set_target_properties(test-import-csv-dialog PROPERTIES AUTOUIC_OPTIONS "--connections=string")
endif()
target_link_libraries(test-import-csv-dialog ${QT_MAJOR}::Test ${QT_MAJOR}::Widgets ${QT_MAJOR}::Concurrent ${LIBSQLITE_NAME} ${QT5_COMPAT})
add_test(NAME test-import-csv-dialog COMMAND $<TARGET_FILE:test-import-csv-dialog>)
if(WIN32)
set(_icd_extra_paths "")
foreach(_p IN LISTS CMAKE_PREFIX_PATH)
list(APPEND _icd_extra_paths "${_p}" "${_p}/bin")
# OpenSSL 3 Universal installer layout: bin/x86, bin/x64
if(CMAKE_SIZEOF_VOID_P EQUAL 8)
list(APPEND _icd_extra_paths "${_p}/bin/x64")
else()
list(APPEND _icd_extra_paths "${_p}/bin/x86")
endif()
endforeach()
# SQLite and SQLCipher are built as DLLs in the CMake prefix by the Windows CI jobs.
string(REPLACE ";" "\;" TESTIMPORTCSVDIALOG_PATH "${_icd_extra_paths};$ENV{PATH}")
set_tests_properties(test-import-csv-dialog PROPERTIES ENVIRONMENT "QT_QPA_PLATFORM=offscreen;PATH=${TESTIMPORTCSVDIALOG_PATH}")
else()
set_tests_properties(test-import-csv-dialog PROPERTIES ENVIRONMENT "QT_QPA_PLATFORM=offscreen")
endif()
Loading
Loading