1. 在注册表中按照以下方法设置 Excel 的文本长度。如果不设置, 会导致列中超过255个字符的记录被截断;
如果是:Jet引擎.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel
然后找到 TypeGuessRows这个项.默认为8的.修改为0.
2. 在SQL Server中启用:
--使用前,启用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
3. 将Excel 中的文件插入到 SQL Server 中
--直接查询Excel
SELECT * into TempTable FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0', 'Excel 8.0;HDR=YES;DATABASE=d:\1.xls ',sheet1$)
--HDR=YES 有两个值:YES/NO,表示第一行是否字段名,默认是YES,第一行是字段名
--IMEX=1 解决数字与字符混合时,识别不正常的情况.
值得注意的是:
1. 记住:如果你在Excel中是 sheet1 , 在上面的语句中还是得写 sheet1$
2. 如果你直接复制某段代码过去执行不了, 很可能是中间多了空格什么的, 有些网站复制过来会多些空格。
3. 还是在本地连接执行查询xls吧, 外部连接查询xls可能会有错误。
4. 如果你没有关闭Excel就来查询xls, 会报以下错误:
Msg 7399, Level 16, State 1, Line 1
链接服务器 "(null)" 的 OLE DB 访问接口 "MICROSOFT.JET.OLEDB.4.0" 报错。提供程序未给出有关错误的任何信息。
Msg 7303, Level 16, State 1, Line 1
无法初始化链接服务器 "(null)" 的 OLE DB 访问接口 "MICROSOFT.JET.OLEDB.4.0" 的数据源对象。
4. 虽然采取了这么多措施, 但还是要防止出现截断, 如果数据量少于1000条,手工检查一下全部数据(看一下头和尾就好)。如果数据量大,至少要查看LEN大于255的所有记录。
5. 将临时表的的字段去掉回车,换行,Tab键, 左右空格。
UPDATE Table1 SET ICD=LTRIM(RTRIM(replace(replace(replace(CONVERT(VARCHAR(max),ICD),char(10),''),char(13),''),CHAR(9),'')))
6. 更新数据表。手工查检, 如果数据量不超过1000条, 应该每一条都测试;
7. 主要的几点容易出错的地方:
- 在初次测试时, 应该测试所有的较短的单条数据, 对于较长的数据, 测试最尾一条和中间一条Code即可
- 有些数据需要后补0, 如: 995 ==> 995.0
- 有些数据需要前补0, 如: 78.11 ==> 078.11
- 有些数据中间有空格, 如:7945 V77.0, 其实应该是两条数据, 应该用逗号让两者分开。
- 必需测试每一条数据。