void ImportCsvDialog::updatePreview()
{
// Show/hide custom quote/separator input fields
ui->editCustomQuote->setVisible(ui->comboQuote->currentIndex() == ui->comboQuote->count()-1);
ui->editCustomSeparator->setVisible(ui->comboSeparator->currentIndex() == ui->comboSeparator->count()-1);
ui->editCustomEncoding->setVisible(ui->comboEncoding->currentIndex() == ui->comboEncoding->count()-1);
// Get preview data
QFile file(csvFilename);
file.open(QIODevice::ReadOnly);
CSVParser csv(ui->checkBoxTrimFields->isChecked(), currentSeparatorChar(), currentQuoteChar());
QTextStream tstream(&file);
tstream.setCodec(currentEncoding().toUtf8());
csv.parse(tstream, 20);
file.close();
// Reset preview widget
ui->tablePreview->clear();
ui->tablePreview->setColumnCount(csv.columns());
// Exit if there are no lines to preview at all
if(csv.columns() == 0)
return;
// Use first row as header if necessary
CSVParser::TCSVResult::const_iterator itBegin = csv.csv().begin();
if(ui->checkboxHeader->isChecked())
{
ui->tablePreview->setHorizontalHeaderLabels(*itBegin);
++itBegin;
}
// Fill data section
ui->tablePreview->setRowCount(std::distance(itBegin, csv.csv().end()));
for(CSVParser::TCSVResult::const_iterator ct = itBegin;
ct != csv.csv().end();
++ct)
{
for(QStringList::const_iterator it = ct->begin(); it != ct->end(); ++it)
{
int rowNum = std::distance(itBegin, ct);
if(it == ct->begin())
{
ui->tablePreview->setVerticalHeaderItem(
rowNum,
new QTableWidgetItem(QString::number(rowNum + 1)));
}
ui->tablePreview->setItem(
rowNum,
std::distance(ct->begin(), it),
new QTableWidgetItem(*it));
}
}
}
bool ExportCsvDialog::exportQuery(const QString& sQuery, const QString& sFilename)
{
// 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();
// Open file
QFile file(sFilename);
if(file.open(QIODevice::WriteOnly))
{
// Open text stream to the file
QTextStream stream(&file);
QByteArray utf8Query = sQuery.toUtf8();
sqlite3_stmt *stmt;
int status = sqlite3_prepare_v2(pdb->_db, utf8Query.data(), utf8Query.size(), &stmt, NULL);
if(SQLITE_OK == status)
{
if(ui->checkHeader->isChecked())
{
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;
if(i != columns - 1)
stream << sepChar;
}
stream << newlineStr;
}
QApplication::setOverrideCursor(Qt::WaitCursor);
int columns = sqlite3_column_count(stmt);
size_t counter = 0;
while(sqlite3_step(stmt) == SQLITE_ROW)
{
for (int i = 0; i < columns; ++i)
{
QString content = QString::fromUtf8(
(const char*)sqlite3_column_blob(stmt, i),
sqlite3_column_bytes(stmt, i));
if(content.toStdString().find_first_of(special_chars) != std::string::npos)
stream << quoteChar << content.replace(quoteChar, quotequoteChar) << quoteChar;
else
stream << content;
if(i != columns - 1)
stream << sepChar;
}
stream << newlineStr;
if(counter % 1000 == 0)
qApp->processEvents();
counter++;
}
}
sqlite3_finalize(stmt);
QApplication::restoreOverrideCursor();
qApp->processEvents();
// Done writing the file
file.close();
} else {
QMessageBox::warning(this, QApplication::applicationName(),
tr("Could not open output file: %1").arg(sFilename));
return false;
}
return true;
}
void ImportCsvDialog::accept()
{
// save settings
QSettings settings(QApplication::organizationName(), QApplication::organizationName());
settings.beginGroup("importcsv");
settings.setValue("firstrowheader", ui->checkboxHeader->isChecked());
settings.setValue("separator", currentSeparatorChar());
settings.setValue("quotecharacter", currentQuoteChar());
settings.setValue("trimfields", ui->checkBoxTrimFields->isChecked());
settings.setValue("encoding", currentEncoding());
settings.endGroup();
// Parse all csv data
QFile file(csvFilename);
file.open(QIODevice::ReadOnly);
CSVParser csv(ui->checkBoxTrimFields->isChecked(), currentSeparatorChar(), currentQuoteChar());
csv.setCSVProgress(new CSVImportProgress(file.size()));
QTextStream tstream(&file);
tstream.setCodec(currentEncoding().toUtf8());
csv.parse(tstream);
file.close();
if(csv.csv().size() == 0)
return;
// Generate field names. These are either taken from the first CSV row or are generated in the format of "fieldXY" depending on the user input
sqlb::FieldVector fieldList;
CSVParser::TCSVResult::const_iterator itBegin = csv.csv().begin();
if(ui->checkboxHeader->isChecked())
{
++itBegin;
for(QStringList::const_iterator it = csv.csv().at(0).begin();
it != csv.csv().at(0).end();
++it)
{
// Remove invalid characters
QString thisfield = *it;
thisfield.replace("`", "");
thisfield.replace(" ", "");
thisfield.replace('"', "");
thisfield.replace("'","");
thisfield.replace(",","");
thisfield.replace(";","");
// Avoid empty field names
if(thisfield.isEmpty())
thisfield = QString("field%1").arg(std::distance(csv.csv().at(0).begin(), it) + 1);
fieldList.push_back(sqlb::FieldPtr(new sqlb::Field(thisfield, "")));
}
} else {
for(size_t i=0; i < csv.columns(); ++i)
fieldList.push_back(sqlb::FieldPtr(new sqlb::Field(QString("field%1").arg(i+1), "")));
}
// Show progress dialog
QProgressDialog progress(tr("Inserting data..."), tr("Cancel"), 0, csv.csv().size());
progress.setWindowModality(Qt::ApplicationModal);
progress.show();
// Are we importing into an existing table?
bool importToExistingTable = false;
objectMap objects = pdb->getBrowsableObjects();
for(objectMap::ConstIterator i=objects.begin();i!=objects.end();++i)
{
if(i.value().gettype() == "table" && i.value().getname() == ui->editName->text())
{
if((size_t)i.value().table.fields().size() != csv.columns())
{
QMessageBox::warning(this, QApplication::applicationName(),
tr("There is already a table of that name and an import into an existing table is only possible if the number of columns match."));
return;
} else {
if(QMessageBox::question(this, QApplication::applicationName(), tr("There is already a table of that name. Do you want to import the data into it?"), QMessageBox::Yes, QMessageBox::No) == QMessageBox::Yes)
{
importToExistingTable = true;
break;
} else {
return;
}
}
}
}
// Create a savepoint, so we can rollback in case of any errors during importing
// db needs to be saved or an error will occur
QString restorepointName = QString("CSVIMPORT_%1").arg(QDateTime::currentMSecsSinceEpoch());
if(!pdb->setSavepoint(restorepointName))
return rollback(this, pdb, progress, restorepointName, 0, tr("Creating restore point failed: %1").arg(pdb->lastErrorMessage));
// Create table
if(!importToExistingTable)
{
if(!pdb->createTable(ui->editName->text(), fieldList))
return rollback(this, pdb, progress, restorepointName, 0, tr("Creating the table failed: %1").arg(pdb->lastErrorMessage));
}
// now lets import all data, one row at a time
for(CSVParser::TCSVResult::const_iterator it = itBegin;
it != csv.csv().end();
++it)
{
QString sql = QString("INSERT INTO `%1` VALUES(").arg(ui->editName->text());
QStringList insertlist;
for(QStringList::const_iterator jt = it->begin(); jt != it->end(); ++jt)
{
// need to mprintf here
char* formSQL = sqlite3_mprintf("%Q", (const char*)jt->toUtf8());
insertlist << formSQL;
if(formSQL)
sqlite3_free(formSQL);
}
// add missing fields with empty values
for(unsigned int i = insertlist.size(); i < csv.columns(); ++i)
{
qWarning() << "ImportCSV" << tr("Missing field for record %1").arg(std::distance(itBegin, it) + 1);
insertlist << "NULL";
}
sql.append(insertlist.join(QChar(',')));
sql.append(");");
if(!pdb->executeSQL(sql, false, false))
return rollback(this, pdb, progress, restorepointName, std::distance(itBegin, it) + 1, tr("Inserting row failed: %1").arg(pdb->lastErrorMessage));
// Update progress bar and check if cancel button was clicked
unsigned int prog = std::distance(csv.csv().begin(), it);
if(prog % 100 == 0)
progress.setValue(prog);
if(progress.wasCanceled())
return rollback(this, pdb, progress, restorepointName, std::distance(itBegin, it) + 1, "");
}
QApplication::restoreOverrideCursor(); // restore original cursor
QDialog::accept();
}
void ExportCsvDialog::accept()
{
if(!m_sQuery.isEmpty())
{
// called from sqlexecute query tab
QString sFilename = FileDialog::getSaveFileName(
this,
tr("Choose a filename to export data"),
tr("Text files(*.csv *.txt)"));
if(sFilename.isEmpty())
{
close();
return;
}
exportQuery(m_sQuery, sFilename);
}
else
{
// called from the File export menu
QList<QListWidgetItem*> selectedItems = ui->listTables->selectedItems();
if(selectedItems.isEmpty())
{
QMessageBox::warning(this, QApplication::applicationName(),
tr("Please select at least 1 table."));
return;
}
// Get filename
QStringList filenames;
if(selectedItems.size() == 1)
{
QString fileName = FileDialog::getSaveFileName(
this,
tr("Choose a filename to export data"),
tr("Text files(*.csv *.txt)"),
selectedItems.at(0)->text() + ".csv");
if(fileName.isEmpty())
{
close();
return;
}
filenames << fileName;
}
else
{
// ask for folder
QString csvfolder = FileDialog::getExistingDirectory(
this,
tr("Choose a directory"),
QFileDialog::ShowDirsOnly | QFileDialog::DontResolveSymlinks);
if(csvfolder.isEmpty())
{
close();
return;
}
for(QList<QListWidgetItem*>::iterator it = selectedItems.begin(); it != selectedItems.end(); ++it)
{
filenames << QDir(csvfolder).filePath((*it)->text() + ".csv");
}
}
// Only if the user hasn't clicked the cancel button
for(int i = 0; i < selectedItems.size(); ++i)
{
// if we are called from execute sql tab, query is already set
// and we only export 1 select
QString sQuery = QString("SELECT * FROM %1;").arg(sqlb::escapeIdentifier(selectedItems.at(i)->text()));
exportQuery(sQuery, filenames.at(i));
}
}
// Save the dialog preferences for future use
PreferencesDialog::setSettingsValue("exportcsv", "firstrowheader", ui->checkHeader->isChecked(), false);
PreferencesDialog::setSettingsValue("exportcsv", "separator", currentSeparatorChar(), false);
PreferencesDialog::setSettingsValue("exportcsv", "quotecharacter", currentQuoteChar(), false);
PreferencesDialog::setSettingsValue("exportcsv", "newlinecharacters", currentNewLineString(), false);
// Notify the user the export has completed
QMessageBox::information(this, QApplication::applicationName(), tr("Export completed."));
QDialog::accept();
}
