Export sqlserver Data To Text and Excel



  • hello
    i have a sqlserver db and i want to write a query or code
    to export mydb data to a TEXT FILE AND A EXCEL FILE
    what should i do?



  • Hi M4RZB4Ni,

    basically you have to get all tables with (http://doc.qt.io/qt-5/qsqldatabase.html#tables), and for each table fetch a QSqlRecord (http://doc.qt.io/qt-5/qsqldatabase.html#record) to get column names and types. Now you can iterate with maybe SELECT * FROM ...over each table entry an print the result to a file. On how to produce a valid EXCEL file you have to consult MSDN.

    Maybe it would be enough to call a managment console for your sqlserver. The Major sql products normally offer tools for export a database.





  • @the_ said:

    (Thanks to @VRonin ;))

    I'm not the author of the library, I can take no credit for it, I'm just making sure the code compiles with recent Qt.

    I don't think the library is needed here anyway, something like this should do the job:

    QFile data("output.csv");
    if (data.open(QFile::WriteOnly)) {
        QTextStream outTxt(&data);
    	QSqlQuery query;
    	bool firstLine=true;
    	query.prepare("SELECT * FROM MyTable");
    	if(query.exec()){
    		while (query.next()) {
    			const QSqlRecord recrd= query.record();
    			if(firstLine){
    				for(int i=0;i<recrd.count();++i)
    					outTxt << recrd.field(i) << ','; //Headers
    			}
    			firstLine=false;
    			outTxt << "\r\n";
    			for(int i=0;i<recrd.count();++i)
    				outTxt << recrd.value(i).toString() << ',';
    		}
    	}
    	data.close();
    }
    


  • @sneubert
    can i use QsqlQuery and Write Direct to File?



  • You can use QSqlQuery to run the select statements and loop the resultset with next() like in VRonin´s example. If you comma seperate the columns like in VRonin´s example you can open this file in excel.

    One more option, if your on windows is to use ODBC Excel Driver to generate the Excel file. With this you can use QSqlQuery to create tables and insert rows.


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.