how toconvert sqlite3 database file to .csv file in qt code ?

sksh

i create sqlite3 database in qt but now i want to convert that file in to .csv file using qt code

SGaist

Hi,

The most straight forward is:

Open your output file using QFile
Run a select * query on your database table
Iterate the result
-- Make a QStringList containing the output of each field
-- Use join on that list using comma as separator
-- Write the result in the file
Close the file

Done.

[edit: Made more clear what should be done in the loop SGaist]

sksh

Thank you sir for your useful replay.
But it is possible using Sqlite3 query or any other method because it take to much time to generate .csv file. Is any other way to convert it fast. I have huge amount of entry in my table approx 20000 rows and each row have 30 column.

VRonin

@sksh said in how toconvert sqlite3 database file to .csv file in qt code ?:

it take to much time to generate .csv file

Can you show us your code?

JonB

@sksh
Is it the actual time to output the CSV file, or is it just the time to iterate over 20,000 rows of 30 columns? You could probably optimize @SGaist's algorithm to output to file as you go along rather than build in-memory string lists + join, but that may be marginal. Do give us some idea of your "it take to much time", seconds/minutes/what?

sksh

@JonB it is take more then 30 second for only 1000 entry

Christian Ehrlicher

You should show us your code - I don't think reading some lines and writing them out into a file will take that long when properly coded...

sksh

Thank you for your replay

I seen my code and i found that it is not stop read some data constant.so please have any solution for it?
my code as below:

int s;
Application conn;

QSqlQuery* query= new QSqlQuery(conn.mydb);

query->prepare(" select * from teb1;");
QVector<QStringList> lst;

  while (query->exec())
  {
        QSqlRecord record = query->record();
        qDebug()<<"record"<<record;
        QStringList tmp;
        for(int i=0;record.value(i)=="\0";i++)//record.count()
        {
            tmp<<record.value(i).toString();
        }
        lst.append(tmp);
        qDebug()<<"tmp"<<s++<<"data"<<tmp;
  }
  foreach(const QStringList &var,lst)
  {
      qDebug()<<"sadsfsfsd"<<var;
  }

SGaist

Several things:

No need to allocate query on the heap (furthermore, you don't delete it so you have a memory leak)
Why put the exec call in a while loop ? It's the next call you need to do that for.
Why build that huge QStringList ? Just dump your table line by line directly into the file. That will save time and memory.

sksh

@SGaist Thanks sir
can you share me a sample code for this?

VRonin

QString escapedCSV(QString unexc)
{
    if (!unexc.contains(QLatin1Char(',')))
        return unexc;
    return '\"' + unexc.replace(QLatin1Char('\"'), QStringLiteral("\"\"")) + '\"';
}

void queryToCsv()
{
    QSqlQuery query(conn.mydb);
    query.prepare(" select * from teb1;");
    QFile csvFile ("output.csv");
    if (!csvFile.open(QFile::WriteOnly | QFile::Text)){
        qDebug("failed to open csv file");
        return;
    }
    if (!query.exec()){
        qDebug("failed to run query");
        return;
    }
    QTextStream outStream(&csvFile);
    outStream.setCodec("UTF-8");
    while (query.next()){
        const QSqlRecord record = query.record();
        for (int i=0, recCount = record.count() ; i<recCount ; ++i){
            if (i>0)
                outStream << ','
            outStream << escapedCSV(record.value(i).toString());
        }
        outStream << '\n';
    }
}

sksh

@VRonin
Thank You sir it is work best for me .

Also thanks to other expert for help me.

mrjj

@sksh
super.
Please set as solved.