how toconvert sqlite3 database file to .csv file in qt code ?
-
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]
-
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. -
@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? -
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...
-
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; }
-
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 thenext
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.
- No need to allocate
-
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'; } }