Excel导入数据库脚本,Excel数据导入Sql

在Excel中,大家常常会碰到那样的字段(最普遍的正是电话号码),即有纯数字的(如未有带区号的电话号码),又有数字和其余字符混合 (如“区号-电

     今天给顾客导入数据,发掘成一列现身过多为null的多寡。在使用Sql Server数据导入的时候,数据类型默以为了float,怎么都不能够校勘其品种。只需求助万能的网络,于是

--数据库中荒诞不经需求导入的表

复制代码 代码如下: exec sp_configure 'show advanced options',1 reconfigure exec sp_configure 'Ad Hoc Distributed Queries',1 reconfigure 复制代码 代码如下: SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=E:HaierWebMyWebDocabc.xls;Extended Properties=Excel 8.0')...Sheet1$ 第风姿浪漫行作为表头。 其他行业成后生可畏种数据类型,且无法转变,假若有文件,有数字, 按现身多的一项呈现,其他为Null 工作者新闻 F2 NULL 姓名 664754 张三 664783 李四 Null 处应该是 工作者编号 。 前两行是表头。 对于这种样式,应该把 数字前多输四个' 强行调换为文本格式。火速填入形似内容 选中八个单元格后,输入字符,然后按Ctrl Enter组合键,就能够在当选的各类单元格中填入上述同样的字符。 化解方法: 复制代码 代码如下: SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=E:HaierWebMyWebDocabc.xls;Extended Properties="Excel 8.0;HD安德拉=YES;IMEX=1;"')...Sheet1$ HDCR-V=Yes , 是说第一列充作表头。 IMEX=1 , 是说把表内容混合读取。 Jet 居然扶助那样好些个据库, 连Html table 也支撑。

话号码”)的多少,在导入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$)

了NULL。

         在Excel中,大家平常会碰到这么的字段(最广泛的正是电话号码),即有纯数字的(如未有带区号的电话号码),又有数字和别的字符混合 (如“区号-电话号码”)的数目,在导入SQLServer进度中,会意识依旧纯数字的数据导过去之后成为了NULL,要么正是数字和其余字符混合的数据导过去过后成为了NULL。

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

    作者首先想到的正是将这几个字段的兼具数据在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$)

作用。

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
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接受一列数字(好像只好是选拔一列卡塔 尔(英语:State of Qatar),选取数据--分列--下一步--下一步--选用文本--完结。
操作后,基本三月经高达指标,不过倘若你有个别数字串是以0开首的,或然会被Excel趾高气扬的去掉了,这种景象供给团结手动重新扩展加了,至于怎么更加高成效的增进完工,可用EditPlus等效果超强的公文编辑器达成。
二、还恐怕有二个越来越快的,设置单元格属性类型为0。举个例子已在单元A1:A100输入了号码,请按以下步骤做:选用单元A1:A100》单击鼠标右键,设置单元格式》选取自定义,在项目中输入0就能够,轻巧解决,呵呵,当然这种方法雷同会抛弃以0伊始的数字串!

以上均是本身找找到的相干的结果,但其实用了最后三个去掉科学计数法的办法,小编开掘那么些才是最简单易行而使得的主意,那样管理过后,数字都当字符串管理了,也正是说,根本就没有必要最上面包车型地铁改良注册表,混合数据类型列的强制剖判之类的操作。可是怎么全体中选纠正成文本格式,不起成效呢??那几个大概正是微软的设计了,不可能算得难题,但不得不承认操作不便利。通过甄选数据--分列--下一步--下一步--选择文本--完成,那样的步骤工夫获取大家想要的结果。那样处理过后,不管是什么样数字都以文件,也不会现出科学本领法,并且管理过后,能够一向用sql导入,也没有要求写sql语句。

 

      我动用第贰个法子消除了问题。感激!

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:Excel导入数据库脚本,Excel数据导入Sql

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。