Skip to content

Commit

Permalink
exportcsv: allow to select more than 1 table at a time to export
Browse files Browse the repository at this point in the history
  • Loading branch information
@rp-
rp- committed Nov 23, 2014
1 parent e814fa2 commit 1ce3379
Show file tree
Hide file tree
Showing 4 changed files with 180 additions and 97 deletions.
236 changes: 155 additions & 81 deletions src/ExportCsvDialog.cpp
View file
Original file line number Diff line number Diff line change
Expand Up @@ -33,18 +33,26 @@ ExportCsvDialog::ExportCsvDialog(DBBrowserDB* db, QWidget* parent, const QString
// Get list of tables to export
objectMap objects = pdb->getBrowsableObjects();
for(objectMap::ConstIterator i=objects.begin();i!=objects.end();++i)
ui->comboTable->addItem(QIcon(QString(":icons/%1").arg(i.value().gettype())), i.value().getname());
{
ui->listTables->addItem(new QListWidgetItem(QIcon(QString(":icons/%1").arg(i.value().gettype())), i.value().getname()));
}

// Sort list of tables and select the table specified in the selection parameter or alternatively the first one
ui->comboTable->model()->sort(0);
ui->listTables->model()->sort(0);
if(selection.isEmpty())
ui->comboTable->setCurrentIndex(0);
{
ui->listTables->setCurrentItem(ui->listTables->item(0));
}
else
ui->comboTable->setCurrentIndex(ui->comboTable->findText(selection));
{
QList<QListWidgetItem*> items = ui->listTables->findItems(selection, Qt::MatchExactly);
ui->listTables->setCurrentItem(items.at(0));
}
} else {
// Hide table combo box
ui->labelTable->setVisible(false);
ui->comboTable->setVisible(false);
ui->listTables->setVisible(false);
resize(minimumSize());
}
}

Expand All @@ -53,103 +61,169 @@ ExportCsvDialog::~ExportCsvDialog()
delete ui;
}

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 newlineChar = "\r\n";

// 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.contains(quoteChar) || content.contains(newlineChar))
stream << quoteChar << content.replace(quoteChar, quotequoteChar) << quoteChar;
else
stream << content;
if(i != columns - 1)
stream << sepChar;
}
stream << newlineChar;
}

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.contains(quoteChar) || content.contains(sepChar) || content.contains('\n'))
stream << quoteChar << content.replace(quoteChar, quotequoteChar) << quoteChar;
else
stream << content;
if(i != columns - 1)
stream << sepChar;
}
stream << newlineChar;
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));
}
}

void ExportCsvDialog::accept()
{
// Get filename
QString fileName = QFileDialog::getSaveFileName(
if(!m_sQuery.isEmpty())
{
// called from sqlexecute query tab
QString sFilename = QFileDialog::getSaveFileName(
this,
tr("Choose a filename to export data"),
PreferencesDialog::getSettingsValue("db", "defaultlocation").toString(),
tr("Text files(*.csv *.txt)"));
if(sFilename.isEmpty())
{
close();
return;
}

// Only if the user hasn't clicked the cancel button
if(fileName.size() > 0)
exportQuery(m_sQuery, sFilename);
}
else
{
// save settings
QSettings settings(QApplication::organizationName(), QApplication::organizationName());
settings.beginGroup("exportcsv");
settings.setValue("firstrowheader", ui->checkHeader->isChecked());
settings.setValue("separator", currentSeparatorChar());
settings.setValue("quotecharacter", currentQuoteChar());
settings.endGroup();

// Create select statement when exporting an entire table
if(m_sQuery.isEmpty())
// called from the File export menu
QList<QListWidgetItem*> selectedItems = ui->listTables->selectedItems();

if(selectedItems.isEmpty())
{
m_sQuery = QString("SELECT * from `%1`;").arg(ui->comboTable->currentText());
QMessageBox::warning(this, QApplication::applicationName(),
tr("Please select at least 1 table."));
return;
}

// Prepare the quote and separating characters
QChar quoteChar = currentQuoteChar();
QString quotequoteChar = QString(quoteChar) + quoteChar;
QChar sepChar = currentSeparatorChar();
QString newlineChar = "\r\n";

// Open file
QFile file(fileName);
if(file.open(QIODevice::WriteOnly))
// Get filename
QStringList filenames;
if(selectedItems.size() == 1)
{
// Open text stream to the file
QTextStream stream(&file);
QString fileName = QFileDialog::getSaveFileName(
this,
tr("Choose a filename to export data"),
PreferencesDialog::getSettingsValue("db", "defaultlocation").toString(),
tr("Text files(*.csv *.txt)"));
if(fileName.isEmpty())
{
close();
return;
}

QByteArray utf8Query = m_sQuery.toUtf8();
sqlite3_stmt *stmt;
filenames << fileName;
}
else
{
// ask for folder
QString csvfolder = QFileDialog::getExistingDirectory(
this,
tr("Choose a directory"),
PreferencesDialog::getSettingsValue("db", "defaultlocation").toString(),
QFileDialog::ShowDirsOnly | QFileDialog::DontResolveSymlinks);

int status = sqlite3_prepare_v2(pdb->_db, utf8Query.data(), utf8Query.size(), &stmt, NULL);
if(SQLITE_OK == status)
if(csvfolder.isEmpty())
{
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.contains(quoteChar) || content.contains(newlineChar))
stream << quoteChar << content.replace(quoteChar, quotequoteChar) << quoteChar;
else
stream << content;
if(i != columns - 1)
stream << sepChar;
}
stream << newlineChar;
}
close();
return;
}

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.contains(quoteChar) || content.contains(sepChar) || content.contains('\n'))
stream << quoteChar << content.replace(quoteChar, quotequoteChar) << quoteChar;
else
stream << content;
if(i != columns - 1)
stream << sepChar;
}
stream << newlineChar;
if(counter % 1000 == 0)
qApp->processEvents();
counter++;
}
for(QList<QListWidgetItem*>::iterator it = selectedItems.begin(); it != selectedItems.end(); ++it)
{
filenames << QDir(csvfolder).filePath((*it)->text() + ".csv");
}
sqlite3_finalize(stmt);
}

QApplication::restoreOverrideCursor();
qApp->processEvents();
// 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(selectedItems.at(i)->text());

// Done writing the file
file.close();
QMessageBox::information(this, QApplication::applicationName(), tr("Export completed."));
QDialog::accept();
} else {
QMessageBox::warning(this, QApplication::applicationName(), tr("Could not open output file."));
exportQuery(sQuery, filenames.at(i));
}
}

// save settings
QSettings settings(QApplication::organizationName(), QApplication::organizationName());
settings.beginGroup("exportcsv");
settings.setValue("firstrowheader", ui->checkHeader->isChecked());
settings.setValue("separator", currentSeparatorChar());
settings.setValue("quotecharacter", currentQuoteChar());
settings.endGroup();


QMessageBox::information(this, QApplication::applicationName(), tr("Export completed."));
QDialog::accept();
}

void ExportCsvDialog::showCustomCharEdits()
Expand Down
13 changes: 8 additions & 5 deletions src/ExportCsvDialog.h
View file
Original file line number Diff line number Diff line change
Expand Up @@ -22,16 +22,19 @@ private slots:
void showCustomCharEdits();

private:
Ui::ExportCsvDialog* ui;
DBBrowserDB* pdb;

QString m_sQuery;

void setQuoteChar(const QChar& c);
char currentQuoteChar() const;

void setSeparatorChar(const QChar& c);
char currentSeparatorChar() const;

bool exportQuery(const QString& sQuery, const QString& sFilename);

private:
Ui::ExportCsvDialog* ui;
DBBrowserDB* pdb;

QString m_sQuery;
};

#endif
26 changes: 16 additions & 10 deletions src/ExportCsvDialog.ui
View file
Original file line number Diff line number Diff line change
Expand Up @@ -6,28 +6,35 @@
<rect>
<x>0</x>
<y>0</y>
<width>331</width>
<height>145</height>
<width>540</width>
<height>300</height>
</rect>
</property>
<property name="windowTitle">
<string>Export data as CSV</string>
</property>
<layout class="QVBoxLayout" name="verticalLayout">
<item>
<layout class="QGridLayout" name="gridLayout_2">
<item row="0" column="0">
<layout class="QFormLayout" name="formLayout">
<item row="0" column="0">
<widget class="QLabel" name="labelTable">
<property name="text">
<string>&amp;Table</string>
<string>&amp;Table(s)</string>
</property>
<property name="buddy">
<cstring>comboTable</cstring>
<cstring>listTables</cstring>
</property>
</widget>
</item>
<item row="0" column="1">
<widget class="QComboBox" name="comboTable"/>
<widget class="QListWidget" name="listTables">
<property name="selectionMode">
<enum>QAbstractItemView::MultiSelection</enum>
</property>
<property name="sortingEnabled">
<bool>false</bool>
</property>
</widget>
</item>
<item row="1" column="0">
<widget class="QLabel" name="labelHeader">
Expand Down Expand Up @@ -172,7 +179,7 @@
</item>
</layout>
</item>
<item>
<item row="1" column="0">
<widget class="QDialogButtonBox" name="buttonBox">
<property name="orientation">
<enum>Qt::Horizontal</enum>
Expand All @@ -185,13 +192,12 @@
</layout>
</widget>
<tabstops>
<tabstop>comboTable</tabstop>
<tabstop>listTables</tabstop>
<tabstop>checkHeader</tabstop>
<tabstop>comboFieldSeparator</tabstop>
<tabstop>editCustomSeparator</tabstop>
<tabstop>comboQuoteCharacter</tabstop>
<tabstop>editCustomQuote</tabstop>
<tabstop>buttonBox</tabstop>
</tabstops>
<resources/>
<connections>
Expand Down
2 changes: 1 addition & 1 deletion src/MainWindow.ui
View file
Original file line number Diff line number Diff line change
Expand Up @@ -1256,7 +1256,7 @@
</action>
<action name="fileExportCSVAction">
<property name="text">
<string>Table as CSV file...</string>
<string>Table(s) as CSV file...</string>
</property>
<property name="toolTip">
<string>Export a database table as a comma separated text file.</string>
Expand Down

0 comments on commit 1ce3379

Please sign in to comment.