I have a dbTable having >400K rows and want to export it to an excel sheet.
What I'm doing right now is reading one value at a time and copying it to the spreadsheet.
Is there a way to copy all the data with just one command? Any help would be appreciated.
Not native to TestComplete, no. There are some SQL commands that you can execute to do such, requiring certain drivers to be present on your SQL server. But generally, if you're reading the values in via script and then exporting out also via script, that's the best option you have at the moment.
Thank you Valla, I was actually trying ot find a way to copy all the rows faster. This solution also goes row by row, so it would be significantly slow while copying around 400k rows to an excel.
Appreciate you taking time looking at this.
Let's approach from perhaps a different direction. What's the purpose for the output to XLS? What are you intending to do with it?
So I'm trying to run a SQL query on production data which would flag some transactions, export them in a spreadhsheet and save it in a location. A notification goes out to someone to review these faulty transactions.
I can still do all of this but not so quick. When the program reads the query results row by row and copies to the excel, it takes 2 minutes/1000 rows. I'm trying to make it <10 sec.
Yeah, in script code, I don't think you're going to get to that <10 sec requirement. You'll need to execute some sort of SQL server side command to dump the table off into an Excel file. Some sort of SSIS job or mssql command line I think is what you're going to end up needing to do.
A variation of what was suggested by @anupamchampati:
https://www.sqlservercentral.com/Forums/912903/exporting-ADO-recordset-straight-into-CSV-or-Excel (.CopyFromRecordset() method).