问题描述
我有一个Excel工作表 .xlsx 扩展名，具有约500,000行和16列.我想将其导入运行在 windows server 2008 R2 下的sql服务器数据库中.因此，我在 SQL Server Management Studio 2008 R2 中尝试了以下步骤，但是它们不起作用:-
I have an excel sheet .xlsx extension with around 500,000 row and 16 columns. and i want to import it inside our sql server database which runs under windows server 2008 R2. so i tried the following steps inside my SQL Server Management studio 2008 R2, but they did not work:-
-
我创建了一个新的数据库，该数据库具有相同的16列+我添加了一个新的ID列，并将其设置为主键.
i created a new database which have the same 16 columns + i add a new ID column and i set it as the Primary key.
然后右键单击数据库>任务>>导入数据
then i right click on the database > Tasks >> Import Data
我选择excel工作表2007 >>浏览文件>>单击下一步>>我收到此错误:-
i select excel sheet 2007 >> browse for the file >> click next >> i got this error:-
------------------------------其他信息:
------------------------------ ADDITIONAL INFORMATION:
"Microsoft.ACE.OLEDB.12.0"提供程序未在本地注册 机器. (系统数据)
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)
------------------------------按钮:
------------------------------ BUTTONS:
- 现在位于此链接正确答案提到以下内容:-
- now inside this link the correct answer mentioned the following:-
所以我运行导入/导出向导"(64位)，但是在数据源下拉列表中我找不到excel表，如下所示:-
so i run the Import/Export Wizard (64-bit) , but inside the data source drop-down i can not find excel sheet as follow:-
- 最后一步，我尝试运行此命令:-
但我收到此错误:-
所以有人可以将我的.xlsx文件导入sql server 2008 r2数据库中吗?
so can anyone adivce how i can import my .xlsx file inside my sql server 2008 r2 database ?
最后一点，现在我正在尝试在SQl服务器中进行导入，但没有安装excel表格或Office ..所以这可能是问题吗?
Final note, now the SQl server i am trying to do the import inside it , does not have excel sheet or office installed.. so could this be the problem ?
推荐答案
转到Microsoft的网站并下载 Microsoft Access数据库引擎2010可再发行.安装该文件，然后将以下设置导入注册表.
Go to Microsoft's website and download Microsoft Access Database Engine 2010 Redistributable. Install that and import the following settings into the registry.
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\Providers\Microsoft.ACE.OLEDB.12.0]
"AllowInProcess"=dword:00000001
"DynamicParameters"=dword:00000001
"DisallowAdhocAccess"=dword:00000000
我相信这应该可以让您导入电子表格.
I believe that should allow you to import your spreadsheet.
这篇关于无法将我的.xlsx导出到我的Sql Server 2008数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！