Excel数据导入Sql Server出现Null

Excel文件:

序号 姓名 内部电话 住址
1 小李 1234 ……
2 小王 5678 ……
3 小张 2345(国内长途) ……
…… …… …… ……

如上结构的Excel文件,用SQL Server的“导入数据”功能来将其导入SQL数据库中。
结果发现在“内部电话”列中,所有带有文字的电话号,被导入后字段值全部为NULL

SQL数据表:

序号 姓名 内部电话 住址
1 小李 1234 ……
2 小王 5678 ……
3 小张 NULL ……
…… …… …… ……


通过在SQL中设置(在导入导出中,目的方选择新建表),发现数据库默认将“内部电话”列识别为float型,因为其新建表对应字段就是float类型的。
看来是SQL认为包含文字的那些电话号码无法转换成数字,所以是无效的数据,从而采用NULL来代替。

通过查询微软网站,发现MS大意如是说:SQL在导入Excel混合数据列的时候,由于数据类型不唯一,导致SQL无法确定数据类型(看来SQL也有犯糊涂的时候)。SQL的应对之道就是统计该数据列的前8行数据中出现最多的类型,并以此类型做为默认类型。而在我的Excel文件中,“内部电话”列的前8行中的确要数纯数字格式的电话号码最多,所以SQL就把这列认为是float型的(为什么不是int型?不解)。至于此列其它格式的数据,SQL的办法是——直接扔了(汗一个。。)

MS原文:

……Excel 不会像关系数据库那样为 ADO 提供有关其数据的详细架构信息。因此,驱动程序必须至少扫描几行现有数据,才能有根据地猜测各列的数据类型。“要扫描的行数”的默认值为八 (8) 行。可以指定从一 (1) 行到十六 (16) 行的整数值,或指定零 (0),扫描所有现有行。这可通过向连接字符串添加可选的  MaxScanRows= 设置,或在 DSN 配置对话框中更改 要扫描的行数设置来完成。

但是,由于 ODBC 驱动程序中存在一个错误,所以目前指定“要扫描的行数”(MaxScanRows) 设置不起作用。换句话说,Excel ODBC 驱动程序(MDAC 2.1 和更高版本)始终扫描指定数据源中的前 8 行,以确定各列的数据类型。

……
使用这两种 OLE DB 提供程序时都应考虑的问题
混用数据类型时应注意的事项
如上文所述,ADO 必须猜测 Excel 工作表或范围中各列的数据类型。(这不受 Excel 单元格格式设置的影响。)如果同一列中既有数字值,也有文本值,会出现严重的问题。Jet 和 ODBC 提供程序将返回占多数的类型的数据,但对于占少数的数据类型,则会返回 NULL(空)值。如果该列中两种类型数据的数量相等,提供程序将优先选择数字型数据,放弃文本型数据。

例如:
在被扫描的八 (8) 行中,如果该列包含五 (5) 个数字值和三 (3) 个文本值,则提供程序将返回五 (5) 个数字和三 (3) 个空值。
在被扫描的八 (8) 行中,如果该列包含三 (3) 个数字值和五 (5) 个文本值,则提供程序将返回三 (3) 个空值和五 (5) 个文本值。
在被扫描的八 (8) 行中,如果该列包含四 (4) 个数字值和四 (4) 个文本值,则提供程序将返回四 (4) 个数字和四 (4) 个空值。
因此,如果列中包含不同类型的值,唯一的解决方法是将该列中的数字值存储为文本,然后在需要时使用 Visual Basic VAL 函数或同等功能的函数将其还原为数字。

作为解决只读数据问题的一种替代方法,可在连接字符串的“扩展属性”部分中使用“IMEX=1”这一设置来启用导入模式。这可强制执行 ImportMixedTypes=Text 注册表设置。但在此模式下,执行更新操作时可能会出现意外的结果。

原文地址:257819: 如何在 Visual Basic 或 VBA 中使用 ADO 来处理 Excel 数据 

看到了没有?你想在前8行里多放几行文本的企图也是徒劳的。数字格式的数据一样会被扔掉,尽管数字可以毫无问题的转成文本。

DSN设置那里根本就没给我们提供加“IMEX=1”的地方,这个字符串是给ADO用的,如果你要编程读取Excel中的数据就用得着它了。不过MS说用这样的连接字符串更新时可能会有问题,这点要稍加注意。我们这里只是数据导入,根本不用考虑这一项。

而在Excel中将有问题的单元格单元格格式设为文本这一办法,事实证明,不起作用。
倒是在每个数字前加单引号可以正常导入,可问题是行数太多,不可能一行行手动加单引号。
用VBA或公式加吧,又无法判断每个单元格是数字类型还是文本类型,导致该列所有单元格都被加了引号。
而且这种方式导入SQL数据库后,还得用查询分析器写SQL语句把引号去了。
解决方法:
最简单的一个办法——把Excel文件另存为“文本文件(制表符分隔)”,再用SQL来导入,一切OK了
为什么?因为txt文件里的内容只有文本属性,不像Excel中还有数字、货币、日期等一帮捣乱的类型。
一个注意的地方是SQL导入时将列长度设为200(具体对我自己的文件),它默认为50.开始导入时应将excel文件关闭,注意是excel文件

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

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

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

注:
    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 
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语句。


  • 2
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值