Saving Query results as CSV file in SQL Server 2005
-
Hello,
I outputted a query results in Grid and saved as CSV (comma-separated) file. When I open this file in Excel, all my fields are in the same column (instead of column per value) and yes, they are comma-ceparated. I do know that if have a text (txt) file containing comma-separated values than Excel will open this file as one field. But my query output does have CSV extension and values in the file are comma-separated. I tried to check Query options, nothing relevant there. Am I missing something? The same query results saved as CSV from QA 2000 open fine with Excel, each value in its column.
---------------------------
New info added: created 2 outputs with the same query, one in QA 2000 and one in MS 2005, saved both as CVS file and read first 30 characters each using VBscript which will show a character value and its ASCII value. The result started with A41221...for both outputs. While reading the output from 2000 would show characters A with its ASCII value 65 then 4 with its ASCII value 52 etc, when you are trying to read values saved as CSV from Management Studio, the first character will be y with tilda (ASCII 255) then Greek b (ASCII Value 254) then the real output A41221 but each character will be followed by nothing like they are unicode values.
Regards,Yelena Varsha
-
It's probably creating the CSV file in unicode format. SQL 2000 Reporting Services has the same issue.
--------------------
Colt 45 - the original point and click interface
-
Try to open the file with Notepad.
Probably you'll see why Excell does not see commas in it.
_____________
Code for TallyGenerator -
There is no difference in Notepad between 2 outputs, same as in Wordpad.
The output from 2005 is in Unicode, this is correct. The only(?) problem that the database fields that are outputted are not unicode, they are varchar. Also if I click on the results window grid and copy the output and paste it into the opened Excel worksheet, it pastes fine, each value in its own columns. It is only if I Save As CSV from the results window I am getting a unicode file.
Phil - do you know if it is "By Design"?
Regards,Yelena Varsha
-
Not to sure about 2005, but in SQL 2000 RS it was by design. We had to include an extra device info parameter to output ANSI characters.
--------------------
Colt 45 - the original point and click interface -
Dunno if this helps but if you save the output of a GRID in QA, it doesn't get saved with commas between columns... it get's saved with tabs which is why it works so well with Excel.
You can change the delimiter of the output of the TEXT or FILE output mode under {Tools}{Options}{Results} in QA.
If you intend to use the file in Excel, why not use TAB delimited TEXT mode (similar to the GRID but makes headers available) and be done with the whole problem?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff,
If I save the output of a GRID as CSV it saves it WITH commas because it is CSV. And if I save them as "ALL Files" it saves it with commas. Both are unicode CSV. I do know several workarounds: copy from the grid and past into Excel or import Unicode file into Excel by opening Excel first and navigating to the file and specifying a dlimeter in Excel.
What I am talking about is: if the Microsoft product has an option (the only option) to output as CSV and Microsoft OS File associating treats CSV as Excel file then Excel as also Microsoft product has to open it correctly. Or Query Analyzer has to output it correctly or post the warning "Hey, I will call it a CSV, but you can not open it with Excel 2000 unless involving extra steps"
Also, I wish I can find where to specify the delimeter when saving from Grid. So far i did not find one. It is certainly not in Tools->Options
Regards,Yelena Varsha
-
I think you are using File/Open to open the file. Have you tried using Data/Import External Data/Import Data (This brings up an popup window for you to choose the delimiter)?
-
Also, I wish I can find where to specify the delimeter when saving from Grid. So far i did not find one. It is certainly not in Tools->Options
You can only do so when the results window is in the text mode.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
fpoon,
I am getting a popup window with the Wizard even if I do in Excel File->Open and navigating to the file. Everything opens in one cell if I double-click on the csv file to open it. But both options are bad if you have to email the results to the managers. You have to convert into the proper format before you send it. But there are the implications: not all fields in Excel file get data in the first 8 rows. And when you try to import from Excel file using DTS or the third-party tools the fields that don't have data in the first 8 rows and then have integer data in other rows these fields don't get imported because Excel determines data type by the first 8 rows. The Microsoft workaround is to save a file as CSV first. This is the known "by design" problem with Excel provider. So now instead getting query results, saving as csv and then importing if needed into another database I will need to save as csv, convert to Excel, send, then convert back then import (just one possible scenario)
Regards,Yelena Varsha
-
Hi,
try using data\text to column. this should do the trick in Excel.
Cheers, Jan
-
Hi Jan,
I tested it and it brings up the same wizard as if I would just open Excel first and then navigate to the file using File->Open menu. Thanks for the advise though, it is good to know. The question still exists why if the comma-separated CSV file is in Unicode format and it still has Excel icon in Windows and it is still comma-separated it can not be opened properly by Excel?
Regards,Yelena Varsha
-
There is a solution...
I had the same problem since I switched from SQL Server 2000 Query Analyzer to SQL Server 2005 Management Studio. They both have the same basic look, and let you output results to either a file, text, or grid. I was frustrated, I read y'all's thread here and started poking around. Text and Grid results have a couple of options under Tools, Options. That threw me off the trail. I expected to find it there.
The file encoding option in Management Studio is all but hidden now. And that would be fine, why should you have to worry about it? In SQL Server 2000 Query Analyzer the file encoding option was as obvious as it is in Notepad, but you didn't need to ever change it because it was correctly set to ANSI, perfect for creating CSV files that can be opened with Excel. So hiding it was fine, *except* why set the default to UNICODE?! Now you have to change it every time from UNICODE to ANSI to get a CSV file tht Excel can correctly open.
Solution
- Choose any of the results options. (With Text or Grid you set the file name *after* you run the query. With File you set the file name *before*.)
- Enter the file name (this enables the Save button)
- Click the tiny little drop box pointer located on right side the Save button itself. (I didn't even see this until I said to myself "it's gotta be here somewhere")
- Select Save with Encoding...
- Change UNICODE to ANSI
- Click OK
That's it. This is in the Books Online btw, which I found *afterwards*. Search for "Managing Files with Encoding".
I hope this helps you.
-
Tracy,
You ARE THE BEST!
It sure works. The dropdown is really small, it is a part of the SAVE button if I would to describe it. I have never noticed it. I also would never search by encoding keyword in BOL because for me ANSI means no encoding at all (which is a questionable statement, it is an encoding).
I suspected this option should be somewhere.
THANKS!
Regards,Yelena Varsha
-
I just experienced the same issue. I found this thread and it works like I expected now. Thanks a lot for sharing this information Tracy
Mike
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply