如何将Excel电子表格导入SQL Server 2008R2数据库？ [英] How do I import an Excel spreadsheet into SQL Server 2008R2 database?

问题描述

我在Excel工作表中有大量的联系信息，我希望将其转换为数据库中的表，以便我可以在数据库中维护它们。

I have got huge list of contact information in an Excel sheet that I would like to turn into table in the database so that I can maintain them in the database.

我尝试从SQL Server 2008 R2导入导入/导出平面文件，但它没有用。谁能告诉我如何成功将电子表格导入数据库中的表？

I tried following the import/export flat file import from the SQL Server 2008 R2, but it did not work. Can anyone tell me how do I successfully import the spreadsheet into a table in the database?

谢谢

推荐答案

有一篇微软知识库文章了解所有可能的方法。

There is a microsoft knowledge base article that lays out all the ways this is possible.

http： //support.microsoft.com/kb/321686

我认为使用OPENROWSET或OPENDATASOURCE将是最简单的方法，没有向导。 （请参阅分布式查询）

I think using OPENROWSET or OPENDATASOURCE will be the easiest way, without the wizard. (see Distributed Queries)

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

请参阅OPENROWSET文档，页面下方有示例。

See OPENROWSET documentation, with examples lower down the page.

http://msdn.microsoft.com/en-us/library/ms190312.aspx

手动

右键单击数据库名称/转到任务然后选择导入数据，作为源选择之前创建的excel文件并在下一页上选择它的路径选择sql服务器作为目的地

Right click on the database name/go to task and then select import data, as a source select an excel file that you created before and choose it's path on the next page select sql server as destination

这篇关于如何将Excel电子表格导入SQL Server 2008R2数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！