Geek asks and answers

SQL Server creates problematic CSV files

When using SQL Server (2005, 2008) "Save Results As ..." option, I get CSV, but SQL Server does not do what I consider to be the correct CSV creation.

In particular, if my data contains commas, these fields are not wrapped in double quotes. The specific dataset that I am looking at right now does not contain double-quoted fields, but if so, I’m not sure if SQL Server will do it correctly either.

Every week I have to run a query in a database on a remote server that generates about 36 thousand rows, and I need to return it to someone in my office in CSV format. So far I have copied the data and then pasted it into Excel (via Remote Desktop). But pasting 36k lines from a remote clipboard takes a lot of time. Excel provides all kinds of "Waiting for OLE operation to complete" messages when this happens.

I would rather just run the query, save the results as CSV on the most remote server, and then copy the file.

In the long run, I will write a process to do this automatically for me, but I have had this problem with SQL Server for many years, but the pain level has not been high enough for me to find a solution so far.

+3
source share
4 answers

Choose Tools> Options, and the Options dialog box appears. In the Options dialog box, select Query Results> SQL Server> Text Results. Select Custom Separator for Output Format. Specify a separator in the Custom Separator field. Click "OK" to save.

Open a query window and select Query> 'Results To' from the menu and either "Results to File" or "Results for Text." Run a query that will be displayed with the specified delimiter.

SQL Server BCP SQL Server. . BCP , , .

, BCP, , FTP . , SQL Server.

+1

, Windows - SQL Management Studio?

, , - / , SQL Server (, ).

+1

This seems like an SSMS error, not a sql server error. I think Joe's answer will solve your problem.

If you have Integration Services for you, I beg you to take a look at this, you can probably bring down a package to do this as soon as possible.

+1
source

I use the built-in VBS script to export result sets as CSV files for quite some time.

Here is the link: exec_script.zip

Here is a usage example:

c:>cscript //nologo exec_script.vbs /s:MyServer /d:MyDb /q:"exec MyStoredProc" /r:MyFile.csv /header
+1
source

More articles:


All Articles