In Management Studio there are options to send your query results to text, grid, or file. I would like to create a .csv file. But when I pick "results to file" it creates a .rpt file. If I then save the file as .csv it's not a csv file, it's binary.

How can I save the results of my query as a true .csv file?

Pick "results to grid" then then right-click on the grid and select "Save Results As..." This will save a CSV.

Thank you.

Actually, it's still not a csv file. Still binary for some reason. I'm really confused as to why. If you try and open it with VI, for example you get a warning that it's binary. And lookint at it - it looks like a binary file. So, what is going on?

I can't speak for VI, but these are CSV files and they open fine in Notepad, EditPlus and Excel on multiple machines where I have tested it.

Does VI know how to handle Unicode files? I don't know if this is the problem, but it's worth a look...

Ok, thanks. I'll look into it.

Pick "results to grid" then then right-click on the grid and select "Save Results As..." This will save a CSV.

Actually, there is a problem with that if some values contain commas - the resulting CSV is not properly escaped. The RPT file is actually quite nice as it contains fixed-width columns. If you have Excel, a relatively easy way of converting the result to CSV is to open the RPT file in Excel. This will bring up the text import wizard and Excel would do a pretty good job at guessing the columns. Go through the wizard and then save the results as CSV.

• Proposed as answer by xschen2000 10 hours 34 minutes ago

HI KSTUS,

It's been good if you create a package that will take data from table and dumped into csv file.

 

Amit

Please mark as answer if helpful

http://fascinatingsql.wordpress.com/

Why don't you use Database->Tasks->Export Data wizard and enter the source as your query and target as the csv file. I prefer this way of getting any data into the files than saving the query results.

• Proposed as answer by V Karthik Friday, July 12, 2013 5:39 AM

Using Excel to handle the RPT file as fixed-width column does the job to convert text to columns. I just need to manually check the column header in the import wizard.
This way is helpful when I had to export from SQL query output to a huge file which is too big to copy from SQL query grid and paste to Excel.