May 29, 2008 at 9:51 pm
When copying a resultset from sql management studio to an excel spreadsheet, I sometimes get less records. Eg. copying 2259 results to a new blank excel sheet and only 2110 records show.
The same happens whether the query results are in text or grid format. I've also tried text format with tab delimited and dragging the results to excel as recommended in a sqlservercentral article (same problem) http://www.sqlservercentral.com/columnists/yShlafman/thebestkeptsecretaboutsqlqueryanalyzer.asp
I've searched google and can only find someone else who had the problem in 2006, but there was no response to their issue:
http://charliem.wordpress.com/2006/02/24/copy-and-past-sql-server-2005-to-excel/
Anyone know why this is happening?
May 30, 2008 at 3:30 am
Might be a GUI bug - I'm not sure.
Try saving the result to a CSV file, and open/import that in Excel.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
May 30, 2008 at 7:28 am
Have you tried a export with RESULTS to FILE, then EXECUTE and in the File Open Dialog-> Save as type to ALL Files and in the File Name give "XXX.xls"
Then try to open the File and check if you still get the same results.
Or goto TOOLS--> Options and under the Query Results change the options for Query REsults.
Maninder
www.dbanation.com
May 30, 2008 at 7:41 am
Hi,
it happens with me as well. I tried 2-3 times and it worked. But the best way is to save it as CSV file and then save it to XSL file.
Make sure when it comes to date field then you need to change column format to text before you paste it or save it.
thanks,
vijay
May 30, 2008 at 7:59 am
Well, if you're passing data to Excel on a regular basis, you could also add a linked server pointing to the Excel workbook, or use SSIS/DTS to copy data to Excel.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
June 9, 2008 at 4:16 am
Any specific reason you are copying and pasting from SSMS to Excel?
I tend to agree, exporting works equally as nice, plus you get headers for free
~PD
June 9, 2008 at 9:48 am
pduplessis (6/9/2008)
Any specific reason you are copying and pasting from SSMS to Excel?I tend to agree, exporting works equally as nice, plus you get headers for free
~PD
There's a setting in Management Studio that makes it include column headers when you copy-and-paste the result-set.
I copy-and-paste ad hoc queries to Excel pretty much daily, and I haven't had the problem from the original post. Is it possible you need to update Management Studio?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 10, 2008 at 12:09 am
Hey,
I would be really interested to know what that setting is...
I know that if you set results to text and copy and paste to excel it keeps your formats. But right click save as doesnt give me headers? Any suggestions
~PD
June 10, 2008 at 12:59 am
Go to Tools/Options, the settings are under Query Results/SQL Server/Results to Grid.
An illustration is attached.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
June 10, 2008 at 1:29 am
Awesome!!!!
:):):):)
I have a team of guys who are seriously smiling this morning because of this posting.
Thanks everyone
June 10, 2008 at 1:37 am
No problem. Just remember SSC! 🙂
However, if moving data between SQL Server and Excel is a regular requirement, you should also look into SSIS, linked servers, or perhaps even the analytical functionalities available in Excel 2007.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
August 20, 2015 at 8:32 am
It happens because some of your fields do have quotes, double quotes, carriage return and possibly other symbols. Data needs to be cleaned if possible.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply