Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
deanfp
Starting Member
26 Posts |
 Posted - 2012-02-03 : 04:52:31
|
HiWe have a customer SQL table with 90,000 rows. I am trying to export the file to Excel so that I can use it for a mailshot. I need to do some filtering so currently all the data is needed.It's the age old problem where it reaches 65,000 rows then stops. Ecen when trying to export it to Excel 2007 format it's still the same.Any idea on how I can bypass that issue?Thanks! |
|
|
revdnrdy
Posting Yak Master
220 Posts |
Posted - 2012-02-06 : 15:14:04
|
quote:
Originally posted by deanfp
HiWe have a customer SQL table with 90,000 rows. I am trying to export the file to Excel so that I can use it for a mailshot. I need to do some filtering so currently all the data is needed.It's the age old problem where it reaches 65,000 rows then stops. Ecen when trying to export it to Excel 2007 format it's still the same.Any idea on how I can bypass that issue?
This is my idea..I am not sure you can get around the 65k limitation so here is how I have had to do it..Write two smaller queries. One which operates on the first 45k rows and the other which operates on the last 45k rows. Each query output goes into a separate sheet on the excel file. I think you can use BCP (Bulk Copy Output) for this but not 100% certain.Once you have the data in the two sheets you can write a small excel macro to filter before you send out an email blast.just an idea.r&r |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-06 : 15:16:54
|
| you can use OPENROWSET and as revdnrdy pointed out you can add a row number column and then split dataset into two based on it sending them to different sheets------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Using SQL Management Studio for Excel export