SQL Server 创建有问题的 CSV 文件
当使用 SQL Server（2005、2008）“将结果另存为...”选项时，我确实得到了 CSV，但 SQL Server 没有执行我认为正确的 CSV 创建操作。
具体来说，如果我的数据包含逗号，则这些字段不会用双引号引起来。我现在正在查看的特定数据集不包含带双引号的字段，但如果包含，我也不确定 SQL Server 是否会正确执行这些操作。
每周我都必须对远程服务器上的数据库运行查询，该查询会生成大约 36k 行，并且我必须以 CSV 格式将其返回给我办公室的某个人。到目前为止，我一直在复制数据，然后将其粘贴到 Excel 中（通过远程桌面）。但是从远程剪贴板粘贴 36k 行需要相当长的时间。当发生这种情况时，Excel 会给出各种“等待 OLE 操作完成”消息框。
我更愿意只运行查询，将结果以 CSV 格式保存在远程服务器上，然后复制该文件。
从长远来看，我将编写一个流程来自动为我完成此操作，但我在 SQL Server 上已经遇到这个问题很多年了，但直到现在，痛苦程度还没有高到足以让我找到解决方案。
When using the SQL Server (2005, 2008) "Save Results As..." option, I do get a CSV, but SQL Server isn't doing what I would consider proper CSV creation.
Specifically, if my data contains commas, those fields aren't being wrapped in double quotes. The specific data set that I'm looking at right now doesn't contain fields with double quotes, but if it did, I'm not sure SQL Server would do those correctly, either.
Every week I have to run a query on a database on a remote server, that generates about 36k rows, and I have to get it back to someone in my office in CSV format. Up to now, I've been copying the data, then pasting it into Excel (via Remote Desktop). But pasting 36k rows from a remote clipboard is taking quite a while. Excel gives all sorts of "Waiting for OLE operation to complete" messageboxes as this happens.
I would prefer to just run the query, save the results as a CSV on the remote server itself, then copy down the file.
Long term I will write a process to do this automatically for me, but I've had this problem with SQL Server for many years now, but the pain level hasn't been high enough for me to find a solution until now.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
选择菜单项“工具”>“选项和选项对话框出现。在“选项”对话框中导航到“查询结果”>“查询结果” SQL服务器> “结果到文本”。选择“输出格式”项“自定义分隔符”。在“自定义分隔符”字段中指定分隔符。单击“确定”保存。
打开查询窗口并选择菜单项 Query > “结果到”和“结果到文件”或“结果到文本”。运行您的查询，该查询将使用您指定的分隔符输出。
对于您的自动化过程，请查看 SQL Server BCP 用于从 SQL Server 输出数据的命令行实用程序。通过命令行开关，您可以完全控制分隔符和文本限定符。使用 BCP 非常简单，我建议即使长期流程的其他元素未到位也使用它。
我有一个调用 BCP 的自动化流程，该流程使用存储过程来收集数据，这些数据被压缩并通过 FTP 传输到客户端每晚为基础。我的进程在由 SQL Server 计算机上的计划任务启动的批处理文件中运行。
Select menu item Tools > Options and the Options dialog appears. On the Options dialog navigate to Query Results > SQL Server > 'Results to Text'. Select for 'Output format' item 'Custom delimiter'. Specify your delimiter in the 'Custom delimiter' field. Click OK to save.
Open a query window and select menu item Query > 'Results To' and either 'Results to File' or 'Results to Text'. Run your query which will be output with your specified delimiter.
For your automated process though take a look at the SQL Server BCP command line utility for outputting your data from SQL Server. Through the command line switches you have complete control over the delimiter and text qualifier. Using BCP is quite easy and I suggest using it even if other elements of your long term process aren't in place
I have an automated process that calls BCP which uses a Stored Procedure to collect data which is zipped and FTPed to a client on a nightly basis. My process is run in a batch file kicked off by a Scheduled Task on the SQL Server machine.
抱歉，我现在不在 Windows 环境中 - 您是在谈论 SQL Management Studio 中的查询管理器应用程序吗？
完成您想要的操作的最佳方法是使用 SQL Server 提供的数据导入/导出工具（抱歉，现在无法检查名称）。
Sorry, I'm not at a windows box right now - are you talking about the query manager app in SQL Management Studio?
The best way to do what you want would be to use the data import/export tool you get with SQL Server (sorry, can't check the name right now).
这听起来像是 SSMS 错误，而不是 sql 服务器错误。我认为乔的回答会解决你的问题。
如果您有可用的集成服务，我恳请您看一下，您可能可以立即创建一个包来完成此操作。
That sounds like a SSMS bug rather than a sql server bug. I think Joe's answer will fix your problem.
If you have Integration Services available to you, i'd implore you to look at that, you can probably knock up a package to do it in no time at all.
我已经使用内部制作的 VBS 脚本将结果集导出为 CSV 文件已有相当一段时间了。
这是一个链接： exec_script.zip
这是一个示例用法：
I've been using an in-house made VBS script to export result-sets as CSV files for quite some time now.
Here is a link: exec_script.zip
Here is a sample usage: