将Excel数据更新至SQL Server遇OLE DB错误，求解决方案

我有一份LibreOffice Calc文件，包含以下列及样本数据：

F_PRODUCT OLD_F_COMP_ID F_C_NUMBER NEW_F_COMP_ID
00231149 017-002-00-2 C_4457 017-002-01-X
ADRENALINE SPRAY 017-002-00-2 C_4457 017-002-01-X

我的SQL Server表结构（Testupdate）如下：

Create Table Testupdate(
    ID INT,
    F_PRODUCT NVARCHAR(50),
    F_C_NUMBER NVARCHAR(40),
    F_COMP_ID NVARCHAR(100)
)

表内样本数据：

F_PRODUCT F_C_NUMBER F_COMP_ID
00231149 C_4457 017-002-00-2
ADRENALINE SPRAY C_4457 017-002-00-2

我需要将TestUpdate表中的F_COMP_ID列更新为Calc文件中的NEW_F_COMP_ID值，预期更新逻辑如下：

update TestUpdate 
set F_COMP_ID=excel.NEW_F_COMP_ID 
where TestUpdate.F_COMP_ID=excel.OLD_F_COMP_ID 
and TestUpdate.F_C_NUMBER=excel.F_C_NUMBER

我尝试执行以下语句：

UPDATE TestUpdate 
SET TestUpdate.F_COMP_ID= ExcelTable.NEW_F_COMP_ID 
FROM TestUpdate 
INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testdata.xls;', 
    'SELECT F_PRODUCT,OLD_F_COMP_ID, F_C_NUMBER,NEW_F_COMP_ID FROM [Sheet1$]') AS ExcelTable 
ON TestUpdate.F_PRODUCT = ExcelTable.F_PRODUCT 
WHERE (TestUpdate.F_C_NUMBER = ExcelTable.F_C_NUMBER AND TestUpdate.F_COMP_ID = testexcel.OLD_F_COMP_ID )

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE

执行时出现错误：

Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

由于文件包含5000+条记录，手动更新不现实，求解决该错误的方法或其他可行更新方案。

这个问题我碰到过很多次，核心原因是Microsoft.Jet.OLEDB.4.0是32位驱动，和64位SQL Server不兼容，另外你原语句里还有个小别名错误。给你几个靠谱的解决思路：

方案1：替换为64位兼容的ACE驱动 #

如果想继续用OPENROWSET的方式，先把驱动换成支持64位的Microsoft.ACE.OLEDB.12.0，同时修正语句里的别名错误：

1. 先下载安装64位的Access Database Engine 2010 Redistributable（如果你的系统是64位），注意如果已经装了32位Office，可能需要选择“仅安装运行时”模式避免冲突。
2. 修改你的SQL语句：

-- 先启用必要的配置
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

-- 启用ACE驱动的InProcess模式（解决权限/实例创建问题）
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1;
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1;

-- 执行更新（修正了别名错误，同时用ACE驱动）
UPDATE TestUpdate 
SET TestUpdate.F_COMP_ID= ExcelTable.NEW_F_COMP_ID 
FROM TestUpdate 
INNER JOIN OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
    'Excel 12.0 Xml;HDR=YES;Database=D:\testdata.xlsx;', -- 建议把Calc存为xlsx格式，兼容性更好
    'SELECT F_PRODUCT,OLD_F_COMP_ID, F_C_NUMBER,NEW_F_COMP_ID FROM [Sheet1$]') AS ExcelTable 
ON TestUpdate.F_PRODUCT = ExcelTable.F_PRODUCT 
AND TestUpdate.F_C_NUMBER = ExcelTable.F_C_NUMBER 
AND TestUpdate.F_COMP_ID = ExcelTable.OLD_F_COMP_ID; -- 这里原语句写成了testexcel.OLD_F_COMP_ID，别名错了

方案2：CSV导入+临时表更新（强烈推荐） #

对于5000条记录，这种方法更稳妥，完全避开OLE DB驱动的坑：

1. 把LibreOffice Calc文件导出为CSV：打开文件，选择文件>另存为，保存类型选CSV（逗号分隔），编码选UTF-8或者SQL Server支持的格式，记得勾选“导出列标题”。
2. 用SSMS导入CSV到临时表：
   • 打开SQL Server Management Studio，右键你的数据库，选择任务>导入数据。
   • 数据源选平面文件源，指向你的CSV文件，配置分隔符为逗号，勾选“第一行有列名称”，预览确认数据没问题。
   • 目标选你的SQL Server数据库，把数据导入到一个临时表（比如Temp_Excel_Updates，或者用#Temp_Updates临时表）。
3. 执行更新语句：

UPDATE tu
SET tu.F_COMP_ID = td.NEW_F_COMP_ID
FROM TestUpdate tu
INNER JOIN Temp_Excel_Updates td
    ON tu.F_COMP_ID = td.OLD_F_COMP_ID
    AND tu.F_C_NUMBER = td.F_C_NUMBER;

这个方法的好处是导入过程中可以直观检查数据格式，不容易出错，而且不需要依赖任何OLE DB驱动，稳定性拉满。

方案3：切换到32位SQL Server实例 #

如果你的环境允许，也可以安装一个32位的SQL Server实例，然后在32位实例上运行原有的Jet驱动语句，不过这个方案不如前两个实用，除非你本来就有32位实例可用。

内容的提问来源于stack exchange，提问作者Ram