会发现要么纯数字的数据导过去之后变成了NULL,

作者: www.9159.com  发布:2019-11-20

在Excel中,我们时常会碰到这样的字段(最常见的就是电话号码),即有纯数字的(如没有带区号的电话号码),又有数字和其它字符混合 (如“区号-电

     今天给客户导入数据,发现有一列出现很多为null的数据。在使用Sql Server数据导入的时候,数据类型默认为了float,怎么都不能改变其类型。只要求助万能的网络,于是

--数据库中不存在需要导入的表

--使用sql语句打开访问EXECL文件

话号码”)的数据,在导入SQLServer过程中,会发现要么纯数字的数据导过去之后变成了NULL,要么就是数字和其它字符混合的数据导过去之后变成

用google搜到一篇:

SELECT * INTO tab_PurchasePriceTemp FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0', 'EXCEL 5.0;HDR=YES;IMEX=1; DATABASE=D:A.xls',Sheet2$)

--SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource' 的访问
--使用下列语句授权访问  1 打开  0关闭
EXEC  sp_configure  'show advanced options',1
GO
RECONFIGURE
GO    
EXEC  sp_configure 'Ad Hoc Distributed Queries',1  
GO
RECONFIGURE
GO

了NULL。

         在Excel中,我们时常会碰到这样的字段(最常见的就是电话号码),即有纯数字的(如没有带区号的电话号码),又有数字和其它字符混合 (如“区号-电话号码”)的数据,在导入SQLServer过程中,会发现要么纯数字的数据导过去之后变成了NULL,要么就是数字和其它字符混合的数据导过去之后变成了NULL。

--数据库中已存在需要导入的表

--2007 Office system 驱动程序:数据连接组件 下载地址:
--Microsoft.ACE.OLEDB.12.0  ACE 引擎访问 Excel 97-2007;  Excel 12.0
--Microsoft.Jet.OLEDB.4.0  Jet 引擎访问 Excel 97-2003 ;  Excel 8.0
--Excel文件必须是在本地

    我首先想到的就是将这个字段的所有数据在Excel中设置为文本格式,刚才说了本来就是希望导入SQLServer时成为字符型,但结果令人失望,不起

为什么有些是纯数字的数据导过去之后变成了NULL,有些却是数字和其它字符混合的数据导过去之后变成了NULL,原来是在将Excel数据导入

INSERT INTO tab_PurchasePriceTemp
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0', 'EXCEL 5.0;HDR=YES;IMEX=1; DATABASE=D:A.xls',Sheet2$)

SELECT  * FROM  OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=Yes;IMEX=1;Data Source=c:222.xls')...[Sheet1$]
 
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=c:111.xlsx',[Sheet1$])    

作用。

SQLServer过程中,SQLServer会做出判断,是采用float型还是nvarchar型来接受数据,测试发现(没有科学依据),SQLServer采用哪一型取决于将要导入

--出现  SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset错误   解决方案

    最终网上搜索到了答案:混合数据类型列的强制解析——IMEX=1
使用 IMEX=1 选参之后,只要取样数据里是混合数据类型的列,一律强制解析为 nvarchar/ntext 文本。当然,IMEX=1 对单一数据类型列的解析是不影

的数据中本身具有哪一型的记录数比例多,如10笔数据,有4笔没有带区号的电话号码,6笔是带区号的电话号码,那么转到SQLServer就会选择

--启用Ad Hoc Distributed Queries:

响的。

nvarchar型,结果就是4笔没有带区号的电话号码导过去之后全成了NULL,反之亦然。不管怎么样,我们最终都希望SQLServer是采用nvarchar来接受

exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure   

SELECT * INTO Table08
FROM OpenDataSource
('Microsoft.Jet.OLEDB.4.0','Data Source="E:/1.xls";Extended properties="Excel 5.0;HDR=Yes;IMEX=1;"')...[Sheet1$]

数据,毕意我们要导入的数据中有数字和其它字符混合的数据,用float型来接受是不可能的,这样只要我们解决了将纯数字的数据转换成字符型并让

--出现   链接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.Jet.OLEDB.4.0" 报错。 错误 解决方案

    注:
    1.这条语句是在SQLServer查询分析器中执行,并且要选择好数据库,否则会把要导入的数据往别的数据库中导了。
    2.Table08是数据导入后在SQLServer中的表名,属于新建,所以请确认在导入数据前数据库中没有该表名,否则会提示已存在同一表名。
    3.Data Source,不要连在一起写,中间有一空格。
    4.E:/1.xls,为Excel所在的绝对路径和数据库名。
    5.Excel 5.0,根据不同的Excel版本写5.0或8.0或其它。

SQLServer接受就可以了。

导入的Excel格式不正确,格式为.xls

    6.Microsoft.Jet.OLEDB.4.0,根据不同的office版本写4.0或12.0或其它。
    7.IMEX=1,是转换成文本输入的意思,非常重要,如果没有,就跟你直接导入效果一样。
    8.Sheet1是表名,千万别看到语句中有$就在表名后加上$,因为$是语句要加的,别画蛇添足。

    我首先想到的就是将这个字段的所有数据在Excel中设置为文本格式,刚才说了本来就是希望导入SQLServer时成为字符型,但结果令人失望,不起作用。

--关闭Ad Hoc Distributed Queries:
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

上述语句执行后,可能会报出以下错误:

    最终网上搜索到了答案:混合数据类型列的强制解析——IMEX=1
使用 IMEX=1 选参之后,只要取样数据里是混合数据类型的列,一律强制解析为 nvarchar/ntext 文本。当然,IMEX=1 对单一数据类型列的解析是不影响的。

 

SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource' 的访问,因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'Ad Hoc Distributed Queries'。有关启用 'Ad Hoc Distributed Queries' 的详细信息,请参阅 SQL Server 联机丛书中的 "外围应用配置器"。

IMEX是用来告诉驱动程序使用Excel文件的模式,其值有0、1、2三种,分别代表导出、导入、混合模式。当我们设置IMEX=1时将强制混合数据转换为文本,但仅仅这种设置并不可靠,IMEX=1只确保在某列前8行数据至少有一个是文本项的时候才起作用,它只是把查找前8行数据中数据类型占优选择的行为作了略微的改变。例如某列前8行数据全为纯数字,那么它仍然以数字类型作为该列的数据类型,随后行里的含有文本的数据仍然变空。
另一个改进的措施是IMEX=1与注册表值TypeGuessRows配合使用,TypeGuessRows 值决定了ISAM 驱动程序从前几条数据采样确定数据类型,默认为“8”。可以通过修改“HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesExcel”下的该注册表值来更改采样行数。但是这种改进还是没有根本上解决问题,即使我们把IMEX设为“1”, TypeGuessRows设得再大,例如1000,假设数据表有1001行,某列前1000行全为纯数字,该列的第1001行又是一个文本,ISAM驱动的这种机制还是让这列的数据变成空。

1.开启Ad Hoc Distributed Queries组件,在sql查询编辑器中执行如下语句:
 exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

select * INTO Table08 from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=E:1.xls;IMEX=1;',[sheet1$])

如需关闭则参数1改为0

注:
    1.这条语句是在SQLServer查询分析器中执行,并且要选择好数据库,否则会把要导入的数据往别的数据库中导了。
    2.Table08是数据导入后在SQLServer中的表名,属于新建,所以请确认在导入数据前数据库中没有该表名,否则会提示已存在同一表名。
    3.Data Source,不要连在一起写,中间有一空格。
    4.E:1.xls,为Excel所在的绝对路径和数据库名。
    5.Excel 5.0,根据不同的Excel版本写5.0或8.0或其它。
    6.IMEX=1,是转换成文本输入的意思,非常重要,如果没有,就跟你直接导入效果一样。
    7.Sheet1是表名,千万别看到语句中有$就在表名后加上$,因为$是语句要加的,别画蛇添足。

如果出现了错误:
    SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource' 的访问,因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'Ad Hoc Distributed Queries'。有关启用 'Ad Hoc Distributed Queries' 的详细信息,请参阅 SQL Server 联机丛书中的 "外围应用配置器"。
查询相关资料,找到解决方法:

    启用Ad Hoc Distributed Queries:
exec sp_configure 'show advanced options',1
www.9159.com,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

还有一种比较麻烦的问题就是excel的科学计数法。网上找到了解决办法,

经常碰到的情况是在表格里面输入手机号码、身份证号等等比较长的数字串时,Excel却自作聪明的将其以科学计数的方式来显示,更麻烦的是,本想设置以文本显示,但设置后,还是一成不变。而且即便以文本方式复制,却将科学计数中的E 也复制过去。想要正常显示,则需要重新双击这个单元格,或请按F2后再请按回车键,才能以真面目示人。实在麻烦!

Excel里面如何一次性取消所有科学计数显示方式?

一、一个比较方便的临时解决办法是用分列功能。

以Excel2007操作:
1、选择想要转换的单元格,设置属性为文本格式(此步骤可忽略,不过建议操作一次)
2、用Excel选择一列数字(好像只能是选择一列),选择数据--分列--下一步--下一步--选择文本--完成。
操作后,基本上已经达到目的,不过如果您有些数字串是以0开头的,可能会被Excel自以为是的去掉了,这种情况需要自己手动重新添加了,至于如何更高效率的添加完毕,可用EditPlus等功能超强的文本编辑器实现。
二、还有一个更快的,设置单元格属性类型为0。比如已在单元A1:A100输入了号码,请按以下步骤做:选择单元A1:A100》单击鼠标右键,设置单元格式》选择自定义,在类型中输入0即可,轻松搞定,呵呵,当然这种方式同样会丢失以0开头的数字串!

以上均是我搜索到的相关的结果,但其实用了最后一个去掉科学计数法的方法,我发现这个才是最简单而有效的方法,这样处理过后,数字都当字符串处理了,也就是说,根本就不需要最上边的修改注册表,混合数据类型列的强制解析之类的操作。但是为什么全部选中修改成文本格式,不起作用呢??这个恐怕就是微软的设计了,不能说是问题,但肯定操作不方便。通过选择数据--分列--下一步--下一步--选择文本--完成,这样的步骤才能得到我们想要的结果。这样处理过后,不管是什么数字都是文本,也不会出现科学技术法,而且处理过后,可以直接用sql导入,也不需要写sql语句。

 

      我使用第二个方法解决了问题。感谢!

本文由9159.com发布于www.9159.com,转载请注明出处:会发现要么纯数字的数据导过去之后变成了NULL,

关键词: