cancel
Showing results for 
Search instead for 
Did you mean: 

Is there a way to export a DB table to an excel sheet?

Frequent Contributor

Is there a way to export a DB table to an excel sheet?

Hi,

 

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.

 

Thank you

Abhi 

 

8 REPLIES 8
Community Hero

Re: Is there a way to export a DB table to an excel sheet?

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.


Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Extensions available
Frequent Contributor

Re: Is there a way to export a DB table to an excel sheet?

Please Click on the link. This may help DB Table to Excel

 

Regards

Valla

http://vallatestcomplete.wordpress.com

With Regards
Vallalarasu Pandiyan | LinkedIn |Vallalarasu.p@gmail.com
Frequent Contributor

Re: Is there a way to export a DB table to an excel sheet?

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.

 

Thank you

Abhi 

Community Hero

Re: Is there a way to export a DB table to an excel sheet?

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?


Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Extensions available
Frequent Contributor

Re: Is there a way to export a DB table to an excel sheet?

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.

 

Thank you

Abhi

Community Hero

Re: Is there a way to export a DB table to an excel sheet?

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.


Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Extensions available
Contributor

Re: Is there a way to export a DB table to an excel sheet?

use batch and export to csv very simple.and easy to use

sqlcmd command lines like connections strings db name and -i input sql file (you can specify the query which output you want to export)
Community Hero

Re: Is there a way to export a DB table to an excel sheet?

Hi,

 

A variation of what was suggested by @anupamchampati:

https://www.sqlservercentral.com/Forums/912903/exporting-ADO-recordset-straight-into-CSV-or-Excel (.CopyFromRecordset() method).

 

 

Regards,
Alex
[Community Expert Group]
____
[Community Expert Group] members are not employed by SmartBear Software but
are just volunteers who have some experience with the tools by SmartBear Software
and a desire to help others. Postings made by [Community Expert Group] members
may differ from the official policies of SmartBear Software and should be treated
as the own private opinion of their authors and under no circumstances as an
official answer from SmartBear Software.
[Community Expert Group] signature is used with permission by SmartBear Software.
http://smartbear.com/forums/f83/t86934/community-experts/
================================
New Here?
Join us and watch the welcome video:
Watch the new Interview
Top Kudoed Authors