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 |
|
jeff06
Posting Yak Master
166 Posts |
 Posted - 2008-08-30 : 22:48:05
|
| I save my query result as .csv file (say sqlserver.csv)from sql server management studio 2005.Usally When I double clicked to open a .csv file(e.g. saved as .csv from excel), the content appears in grid format with one cell one value. But for sqlsever.csv. All contents of a row resides in first column of a row. (all value with commas in one cell). Also, I have no problem to read .csv from excel into sas but I can not read .csv from sql server correctly.Can someone explain this to me that is .csv from sql server different from .csv from excel?Thank. Jeff |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-08-31 : 00:39:07
|
what does it look like if you open it in notepad? are the columns comma-delimited?
elsasoft.org |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-31 : 06:11:17
|
| do you mean opening csv in excel? you're getting all in one column? |
|
|
|
jpesenti
Starting Member
1 Post |
Posted - 2008-10-03 : 10:06:25
|
| Management Studio defaults the .CSV file to UNICODE and prefixed the whole thing with byte containing 0xFF. This seems to confuse Excel, which expects this type of file to contain tab delimited data.There are 3 solutions:1 When you use "save the results as...", click the dropdown arrow on the Save button, select "Save With Encoding" and change the Encoding from Unicode to ANSI.2 If you haved saved the file as unicode, start Excel, click Open and select the file. The data import dialog will open and you can change the delimiter from tab to comma. 3 Open the Unicode csv file in Notepad, and save it with another file name, changing the encoding to ANSI.(I'm using EXCEL 2003, by the way) |
|
|
|
lepeniotis
Yak Posting Veteran
75 Posts |
Posted - 2008-10-03 : 10:41:19
|
| I don't know if that helps but try to see if you have any commas inside a collumn record. This will confuse it and will present it as a different collumn despite the fact that it has to be in the same collumn. Sometimes it happens to have commas in a collumn record and when you want to insert the csv into excel or other data destination you get errors or different(wrong) persentation of the actual data.I hope that helps.MSc Advanced Computing Science MSc Database ProfessionalSheffield Hallam UniversityMCP (70-229, 70-228)Industrial IT Engineer |
|
|
|
mandm
Posting Yak Master
120 Posts |
Posted - 2008-10-03 : 13:19:53
|
| Also make sure that Management studio is configured properly for delimited output by choosing Tools/Options/Query Results/Results to Text. There are 3 options there for comma, space, and tab delimited output. |
|
|
|
Fromper
Starting Member
33 Posts |
Posted - 2009-05-07 : 14:21:45
|
| Thank you, jpesenti. I was having the same problem, and this corrected it. |
|
|
|
dpoirier
Starting Member
9 Posts |
Posted - 2009-10-14 : 23:10:45
|
| If anyone is interested, I created an SSMS 2005/2008 Addin that allows saving SQL query results as an XLS file, with header and value formats intact.http://www.SsmsXlsExport.com/Cheers,David |
|
|
|
|
|
|
|
|
csv file from sql server management studio 2005