将 SQL 查询数据导出到 Excel

2021-12-02 00:00:00 excel sql export-to-excel sql-server

我有一个查询返回一个非常大的数据集.我无法像往常一样将其复制并粘贴到 Excel 中.我一直在研究如何直接导出到 Excel 工作表.我在运行 Microsoft Server 2003 的服务器上运行 SQL SERVER 2008.我正在尝试使用 Microsoft.Jet.OLEDB.4.0 数据提供程序和 Excel 2007.我拼凑了一小段代码，看起来像这样已经在例子中看到了.

I have a query that returns a very large data set. I cannot copy and paste it into Excel which I usually do. I have been doing some research on how to export directly to an Excel sheet. I am running SQL SERVER 2008 on a server running Microsoft Server 2003. I am trying to use the Microsoft.Jet.OLEDB.4.0 data provider and Excel 2007. I've pieced together a small piece of code that looks like this from what I've seen in examples.

INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:WorkingBook1.xlsx;Extended Properties=EXCEL 12.0;HDR=YES')
SELECT productid, price FROM dbo.product

但是这不起作用，我收到一条错误消息说

However this is not working, I am getting an error message saying

关键字 'SELECT' 附近的语法不正确".

"Incorrect syntax near the keyword 'SELECT'".

有没有人对如何做到这一点或可能有更好的方法有任何想法?

Does anyone have any ideas about how to do this or possibly a better approach?

推荐答案

我不知道这是否是您要找的，但您可以像这样将结果导出到 Excel:

I don't know if this is what you're looking for, but you can export the results to Excel like this:

在结果窗格中，单击左上角的单元格以突出显示所有记录，然后右键单击左上角的单元格并单击结果另存为".导出选项之一是 CSV.

In the results pane, click the top-left cell to highlight all the records, and then right-click the top-left cell and click "Save Results As". One of the export options is CSV.

你也可以试一试:

INSERT INTO OPENROWSET 
   ('Microsoft.Jet.OLEDB.4.0', 
   'Excel 8.0;Database=c:Test.xls;','SELECT productid, price FROM dbo.product')

最后，您可以考虑使用 SSIS(替代 DTS)进行数据导出.这是教程的链接:

Lastly, you can look into using SSIS (replaced DTS) for data exports. Here is a link to a tutorial:

http://www.accelebrate.com/sql_training/ssis_2008_tutorial.htm

== 更新 #1 ==

要将结果保存为带有列标题的 CSV 文件，可以按照以下步骤操作:

To save the result as CSV file with column headers, one can follow the steps shown below:

  1. 转到工具->选项
  2. 查询结果->SQL Server->结果到网格
  3. 选中复制或保存结果时包括列标题"
  4. 点击确定.
  5. 请注意，新设置不会影响任何现有的查询"选项卡 - 您需要打开新选项卡和/或重新启动 SSMS.

相关文章