读取EXCEL数据到SQL的实例

最新推荐文章于 2024-10-10 15:21:14 发布

gavin2007

最新推荐文章于 2024-10-10 15:21:14 发布

阅读量1k

点赞数

分类专栏： IT学堂文章标签： excel sql 数据库 sql server table 脚本

本文链接：https://blog.csdn.net/gavin2007/article/details/6907103

版权

IT学堂专栏收录该内容

0 篇文章 0 订阅

订阅专栏

读取EXCEL数据到SQL的方法很多，但是今天这个问题困扰了我很长时间，最后终于解决了，现在总结一下。

有一EXCEL文件，格式如下：

‍

需要通过SQL脚本将其读入数据库中，代码如下：

DECLARE @SqlString NVARCHAR(4000);

SELECT @SqlString='openrowset(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;HDR=YES;DATABASE='+@path+@fname+''','+@sheetname+'$)'

EXEC('select * into bpl_from from '+@SqlString)

参数说明：

@path   nvarchar(1000), --文件存放目录
@fname   nvarchar(250), --文件名
@sheetname   varchar(250)='' --sheet页名

这个时候会执行失败，错误消息如下：

消息 7308，级别 16，状态 1，第 1 行
因为 OLE DB 访问接口 'Microsoft.Jet.OLEDB.4.0'配置为在单线程单元模式下运行，所以该访问接口无法用于分布式查询

于是上网查找解决办法，多数给出了如下代码：

--启用Ad Hoc Distributed Queries：
　　　　exec sp_configure 'show advanced options',1
　　　　reconfigure
　　　　exec sp_configure 'Ad Hoc Distributed Queries',1
　　　　reconfigure

　　　　--使用完成后，关闭Ad Hoc Distributed Queries：

　　　　exec sp_configure 'Ad Hoc Distributed Queries',0
　　　　reconfigure

　　　　exec sp_configure 'show advanced options',0
　　　　reconfigure

按照这种方法运行到reconfigure 语句进行安装时，错误又来了：不支持对系统目录进行即席更新。

SQL SERVER从2005开始，与2000的区别就很大了，虽说优点不少，但是使用上还是有很多的不便了。最后查找资料，reconfigure需要加上 WITH OVERRIDE才可以正常执行。问题解决。

这个问题解决了，可是第一个问题还是会出现，怎么办？原来是该EXCEL文件不在SQL SERVER 服务器上的原因。后将该EXCEL文件放到服务器上，再执行SQL脚本，结果发现，问题依然存在，我都无语了。。。

但是困难再大也不能阻止我解决问题的决心。于是继续查找资料，这次很费了一番苦心，终于找到了答案。

原来是在64位的SQL Engine中已经不提供jet.oledb.4.0的驱动了，解决办法是把连接字符串的Microsoft.jet.Oledb.4.0 更改为 Microsoft.ACE.OLEDB.12.0，前提是先要下载安装一个ACE.Oledb.12.0 for X64位的驱动

下载链接：http://www.microsoft.com/downloads/zh-cn/details.aspx?displaylang=zh-cn&FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d

又经过一番折腾，心想这次总可以解决问题了吧。呵呵，高兴太早了，这个问题是解决了，可是又出现了新的问题：

消息 468，级别 16，状态 9，过程 p_ExcelToSQL，第 57 行
无法解决 equal to 运算中 "Chinese_PRC_CI_AS" 和 "SQL_Latin1_General_CP1_CI_AS" 之间的排序规则冲突

查看出问题的代码：

INSERT INTO #tmp_FromExcel(BarCode,ItemCode,SizeSite,Num)
SELECT A.BarCode,B.ItemCode,B.SizeSite,A.Num
FROM #ExcelTbl A
INNER JOIN edp_barcode B ON (A.BarCode = B.BarCode);

仔细分析，是排序规则出了问题。检查发现，我的数据库的排序规则是Chinese_PRC_CI_AS，由于使用了临时表#tmp_FromExcel和#ExcelTbl，而临时表是存在系统数据库tempdb中的，系统数据库tempdb的排序规则正是SQL_Latin1_General_CP1_CI_AS，找到了问题所在。既然是排序规则的冲突问题，那修改数据库的排序规则不就得啦？且慢，要知道，系统数据库的排序规则是无法修改的。那改我的数据库？由于不太清楚数据库的排序规则有什么作用，又担心修改了会对数据库造成其他影响，心想还是算了吧，想想其他的解决办法。

既然这样，我不用这种带#号的临时表是不是可以呢？抱着试一试的想法，我对SQL脚本做了部分修改，将所有带#号的临时表，全部用@table这样的表参数代替，比如：

CREATE TABLE #tmpTable(
BarCode CHAR(13),
ItemCode CHAR(16),
-SizeSite VARCHAR(7),
-Num INT
);

替换为：

DECLARE @tmp_FromExcel TABLE(
BarCode CHAR(13),
ItemCode CHAR(16),
SizeSite VARCHAR(7),
Num INT
);

修改后执行，问题解决！