Export sqlserver Data To Text and Excel

M4RZB4Ni

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?

sneubert

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_

@M4RZB4Ni
To generate an Excel File (.xls, .xlsx) see https://wiki.qt.io/Handling_Microsoft_Excel_file_format and/or https://github.com/VSRonin/QtXlsxWriter (Thanks to @VRonin ;))

VRonin

@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();
}

M4RZB4Ni

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

sneubert

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.