Skip to content

Commit f9314b6

Browse files
mgrojomgrojo
committed
CSV Import: give option to use system locale conventions
This will allow to import numbers in CSV which use ',' as decimal separator and '.' as thousands separator, when the system locale is Spanish (Spain), for example. Additionally this commit detects the full range supported by SQLite for INTEGER and REAL numbers, that is, 64 bit integers are not converted to REAL and double precision reals are not converted to TEXT. See issues #1347 and #2140.
1 parent 4a2179b commit f9314b6

2 files changed

Lines changed: 53 additions & 13 deletions

File tree

‎src/ImportCsvDialog.cpp‎

Lines changed: 33 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -51,20 +51,23 @@ ImportCsvDialog::ImportCsvDialog(const std::vector<QString>& filenames, DBBrowse
5151
ui->checkboxHeader->blockSignals(true);
5252
ui->checkBoxTrimFields->blockSignals(true);
5353
ui->checkBoxSeparateTables->blockSignals(true);
54+
ui->checkLocalConventions->blockSignals(true);
5455
ui->comboSeparator->blockSignals(true);
5556
ui->comboQuote->blockSignals(true);
5657
ui->comboEncoding->blockSignals(true);
5758

5859
ui->checkboxHeader->setChecked(Settings::getValue("importcsv", "firstrowheader").toBool());
5960
ui->checkBoxTrimFields->setChecked(Settings::getValue("importcsv", "trimfields").toBool());
6061
ui->checkBoxSeparateTables->setChecked(Settings::getValue("importcsv", "separatetables").toBool());
62+
ui->checkLocalConventions->setChecked(Settings::getValue("importcsv", "localconventions").toBool());
6163
setSeparatorChar(Settings::getValue("importcsv", "separator").toChar());
6264
setQuoteChar(Settings::getValue("importcsv", "quotecharacter").toChar());
6365
setEncoding(Settings::getValue("importcsv", "encoding").toString());
6466

6567
ui->checkboxHeader->blockSignals(false);
6668
ui->checkBoxTrimFields->blockSignals(false);
6769
ui->checkBoxSeparateTables->blockSignals(false);
70+
ui->checkLocalConventions->blockSignals(false);
6871
ui->comboSeparator->blockSignals(false);
6972
ui->comboQuote->blockSignals(false);
7073
ui->comboEncoding->blockSignals(false);
@@ -169,6 +172,7 @@ void ImportCsvDialog::accept()
169172
Settings::setValue("importcsv", "quotecharacter", currentQuoteChar());
170173
Settings::setValue("importcsv", "trimfields", ui->checkBoxTrimFields->isChecked());
171174
Settings::setValue("importcsv", "separatetables", ui->checkBoxSeparateTables->isChecked());
175+
Settings::setValue("importcsv", "localconventions", ui->checkLocalConventions->isChecked());
172176
Settings::setValue("importcsv", "encoding", currentEncoding());
173177

174178
// Get all the selected files and start the import
@@ -428,23 +432,24 @@ sqlb::FieldVector ImportCsvDialog::generateFieldList(const QString& filename) co
428432
if(old_type != "TEXT")
429433
{
430434
QString content = QString::fromUtf8(rowData.fields[i].data, static_cast<int>(rowData.fields[i].data_length));
435+
const QLocale &locale = ui->checkLocalConventions->isChecked() ? QLocale::system() : QLocale::c();
431436

432-
// Check if the content can be converted to an integer or to float
433-
bool convert_to_int, convert_to_float;
434-
content.toInt(&convert_to_int);
435-
content.toFloat(&convert_to_float);
437+
// Check if the content can be converted to an integer or to real
438+
bool convert_to_integer, convert_to_real;
439+
locale.toLongLong(content, &convert_to_integer);
440+
locale.toDouble(content, &convert_to_real);
436441

437442
// Set new data type. If we don't find any better data type, we fall back to the TEXT data type
438443
std::string new_type = "TEXT";
439-
if(old_type == "INTEGER" && !convert_to_int && convert_to_float) // So far it's integer, but now it's only convertible to float
444+
if(old_type == "INTEGER" && !convert_to_integer && convert_to_real) // So far it's integer, but now it's only convertible to float
440445
new_type = "REAL";
441-
else if(old_type.empty() && convert_to_int) // No type yet, but this bit is convertible to integer
446+
else if(old_type.empty() && convert_to_integer) // No type yet, but this bit is convertible to integer
442447
new_type = "INTEGER";
443-
else if(old_type.empty() && convert_to_float) // No type yet and only convertible to float (less 'difficult' than integer)
448+
else if(old_type.empty() && convert_to_real) // No type yet and only convertible to float (less 'difficult' than integer)
444449
new_type = "REAL";
445-
else if(old_type == "REAL" && convert_to_float) // It was float so far and still is
450+
else if(old_type == "REAL" && convert_to_real) // It was float so far and still is
446451
new_type = "INTEGER";
447-
else if(old_type == "INTEGER" && convert_to_int) // It was integer so far and still is
452+
else if(old_type == "INTEGER" && convert_to_integer) // It was integer so far and still is
448453
new_type = "INTEGER";
449454

450455
fieldList.at(i).setType(new_type);
@@ -647,8 +652,23 @@ bool ImportCsvDialog::importCsv(const QString& fileName, const QString& name)
647652
} else if(!importToExistingTable && rowData.fields[i].data_length == 0) {
648653
// No need to bind NULL values here as that is the default bound value in SQLite
649654
} else {
650-
// This is a non-empty value, or we want to insert the empty string. Just add it to the statement
651-
sqlite3_bind_text(stmt, static_cast<int>(i)+1, rowData.fields[i].data, static_cast<int>(rowData.fields[i].data_length), SQLITE_STATIC);
655+
// This is a non-empty value, or we want to insert the empty string. Just add it to the statement.
656+
// Find the correct data type taken into account the locale.
657+
QString content = QString::fromUtf8(rowData.fields[i].data, static_cast<int>(rowData.fields[i].data_length));
658+
const QLocale &locale = ui->checkLocalConventions->isChecked() ? QLocale::system() : QLocale::c();
659+
bool convert_ok;
660+
sqlite_int64 int64_value = locale.toLongLong(content, &convert_ok);
661+
if(convert_ok)
662+
sqlite3_bind_int64(stmt, static_cast<int>(i)+1, int64_value);
663+
else {
664+
double value = locale.toDouble(content, &convert_ok);
665+
if(convert_ok)
666+
sqlite3_bind_double(stmt, static_cast<int>(i)+1, value);
667+
else
668+
// Set new data type. If we don't find any better data type, we fall back to the TEXT data type
669+
670+
sqlite3_bind_text(stmt, static_cast<int>(i)+1, rowData.fields[i].data, static_cast<int>(rowData.fields[i].data_length), SQLITE_STATIC);
671+
}
652672
}
653673
}
654674

@@ -822,6 +842,8 @@ void ImportCsvDialog::toggleAdvancedSection(bool show)
822842
{
823843
ui->labelNoTypeDetection->setVisible(show);
824844
ui->checkNoTypeDetection->setVisible(show);
845+
ui->labelLocalConventions->setVisible(show);
846+
ui->checkLocalConventions->setVisible(show);
825847
ui->labelFailOnMissing->setVisible(show);
826848
ui->checkFailOnMissing->setVisible(show);
827849
ui->labelIgnoreDefaults->setVisible(show);

‎src/ImportCsvDialog.ui‎

Lines changed: 20 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -299,14 +299,14 @@
299299
</property>
300300
</widget>
301301
</item>
302-
<item row="9" column="1">
302+
<item row="10" column="1">
303303
<widget class="QCheckBox" name="checkIgnoreDefaults">
304304
<property name="toolTip">
305305
<string>When importing an empty value from the CSV file into an existing table with a default value for this column, that default value is inserted. Activate this option to insert an empty value instead.</string>
306306
</property>
307307
</widget>
308308
</item>
309-
<item row="9" column="0">
309+
<item row="10" column="0">
310310
<widget class="QLabel" name="labelIgnoreDefaults">
311311
<property name="text">
312312
<string>Ignore default &amp;values</string>
@@ -350,6 +350,23 @@
350350
</property>
351351
</widget>
352352
</item>
353+
<item row="9" column="0">
354+
<widget class="QLabel" name="labelLocalConventions">
355+
<property name="text">
356+
<string>Use local number conventions</string>
357+
</property>
358+
<property name="buddy">
359+
<cstring>checkLocalConventions</cstring>
360+
</property>
361+
</widget>
362+
</item>
363+
<item row="9" column="1">
364+
<widget class="QCheckBox" name="checkLocalConventions">
365+
<property name="toolTip">
366+
<string>Use decimal and thousands separators according to the system locale.</string>
367+
</property>
368+
</widget>
369+
</item>
353370
<item row="12" column="1">
354371
<widget class="QComboBox" name="comboOnConflictStrategy">
355372
<property name="toolTip">
@@ -492,6 +509,7 @@
492509
<tabstop>buttonAdvanced</tabstop>
493510
<tabstop>checkIgnoreDefaults</tabstop>
494511
<tabstop>checkNoTypeDetection</tabstop>
512+
<tabstop>checkLocalConventions</tabstop>
495513
<tabstop>checkFailOnMissing</tabstop>
496514
<tabstop>comboOnConflictStrategy</tabstop>
497515
<tabstop>filePicker</tabstop>

0 commit comments

Comments
 (0)