无法将我的.xlsx导出到我的Sql Server 2008数据库 [英] Unable to export my .xlsx to my Sql server 2008 database
问题描述
我有一个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:-
标题:SQL Server导入和导出向导
操作无法完成.
TITLE: SQL Server Import and Export Wizard
The operation could not be completed.
------------------------------其他信息:
------------------------------ 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:-
您可能遇到的问题是正在导入/导出向导 作为32位版本发布.这可能是由于您的权利 单击数据库，然后单击导入，因为SSMS是 32位程序将启动32位进程.明确尝试 通过单击运行导入/导出向导(64位) 开始->程序文件-> Microsoft SQL Server 2012->导入和导出 数据(64位)以从64位数据源导入数据.
The problem you are likely having is the Import/Export Wizard is being launched as the 32-bit version. This is probably due to you right clicking on a database and clicking on import and since SSMS is a 32-bit program it will launch 32-bit processes. Try explicitly running the Import/Export Wizard (64-bit) by clicking on Start->Program Files->Microsoft SQL Server 2012->Import and Export Data (64-bit) to import your data from a 64-bit datasource.
所以我运行导入/导出向导"(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:-
- 最后一步，我尝试运行此命令:-
插入[SalesDataDemo].[dbo].[SalesData]选择* FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0'，'Excel 12.0; Database = c:*****.xlsx; HDR = YES'，'SELECT * FROM [Sheet1 $]')
INSERT INTO [SalesDataDemo].[dbo].[SalesData] select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c:*****.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')
但我收到此错误:-
消息7403，级别16，状态1，第1行OLE DB提供程序 "Microsoft.ACE.OLEDB.12.0"尚未注册.
Msg 7403, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.
所以有人可以将我的.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 ?
推荐答案
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数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
