通过异类查询(OpenDataSource - OLEDB引擎驱动)把外部数据(Excel,TXT)导入到SqlServer及job代理执行出错的处理
来源:互联网 发布:rds数据库 编辑:程序博客网 时间:2024/06/06 09:10
无论用sqlcmd或者通过C#逻辑处理,或者还是直接在SqlServer里执行操作,个人总结大概不过可归总为这两种方式:
1,SSIS;
2,OLEDB驱动引擎。
SSIS实在也好用,现在这里先不去讨论。其中大多数人用的都是第二种方法,下面,本人也以第二种方法在SqlServer里导入excel和txt格式文件的具体实例来说明怎样在项目里灵活通过OLEDB导入数据!
1,SSIS;
2,OLEDB驱动引擎。
SSIS实在也好用,现在这里先不去讨论。其中大多数人用的都是第二种方法,下面,本人也以第二种方法在SqlServer里导入excel和txt格式文件的具体实例来说明怎样在项目里灵活通过OLEDB导入数据!
一,必要的环境说明
本实例用sql语句在SqlServer里面完成对数据的导入操作。在本实例开始之前,可能需要先安装AccessDatabase引擎包
(本人机器64位,安装的是32位的office组件,所以需要另外安装office驱动引擎包(此引擎包作用在于使得office系统文件与office应用程序之间进行数据传输) - 此步骤中需要先卸载32位office组件,否则会提示驱动安装不成功)
本人所用引擎包下载地址如下:
http://www.microsoft.com/zh-cn/download/details.aspx?id=13255
安装完成后,具体配置可以这里配,建议如非必要,默认就好。
二,代码准备 - 建数据库及存储过程
建数据库这里不多说,建立与导入数据相匹配的数据库而已。而为了项目拓展及方便使用管理,需要创建存储过程,目的是为了批量导入外部数据。
因为本例以excel和txt分别来做实例说明,所以,需要新建两个存储过程 - "sp_ReadXLSSource"和"sp_ReadSource"。代码分别如下:
存储过程:sp_ReadXLSSource
USE [CUSTOMS]GO/****** Object: StoredProcedure [dbo].[sp_ReadXLSSource] Script Date: 11/21/2014 3:01:39 PM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[sp_ReadXLSSource]@trgPath varchar(255),@trgTable varchar(255),@xlsFile varchar(255),@xlsSheet varchar(255),@xlsFields varchar(8000)ASdeclare @mySql varchar(8000)declare @myIntermediate varchar(8000)declare @Firstfield varchar(100)declare @FirstfieldValue varchar(100)--Lookup lastfieldset @Firstfield = dbo.fnGetFirstField(@xlsFields)set @FirstfieldValue = RTRIM(LTRIM(SUBSTRING(@Firstfield,2,CHARINDEX(']',@Firstfield,0)-2)))--PRINT @FirstfieldValue--Drop table if existsset @myIntermediate = 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @trgTable + ']'') AND type in (N''U''))'set @mySql = @myIntermediate + ' DROP TABLE ' + @trgTableexec(@mySql)--PRINT @mySql--Load Tableset @mySql = 'SELECT ' + @xlsFields + ' INTO ' + @trgTable + ' FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;DATABASE=' + @trgPath + '\' + @xlsFile + ';IMEX=1'', ''Select * from ' + @xlsSheet + ''')'+ 'WHERE NOT ' + @Firstfield + ' IS NULL'--PRINT @mySqlexec(@mySql)存储过程:sp_ReadSource
USE [WOOX_CQM]GO/****** Object: StoredProcedure [dbo].[sp_ReadSource] Script Date: 11/21/2014 2:54:33 PM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[sp_ReadSource]@trgPath varchar(255),@trgTable varchar(255),@trgFields varchar(8000)ASdeclare @mySql varchar(8000)declare @myIntermediate varchar(8000)declare @Firstfield varchar(100)declare @FirstfieldValue varchar(100)--Lookup lastfieldset @Firstfield = dbo.fnGetFirstField(@trgFields)set @FirstfieldValue = RTRIM(LTRIM(SUBSTRING(@Firstfield,2,CHARINDEX(']',@Firstfield,0)-2)))--PRINT @FirstfieldValue--Drop table if existsset @myIntermediate = 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @trgTable + ']'') AND type in (N''U''))'set @mySql = @myIntermediate + ' DROP TABLE ' + @trgTableexec(@mySql)--PRINT @mySql--Load Tableset @mySql = 'SELECT ' + @trgFields + ' INTO ' + @trgTable + ' ' +'FROM OpenDataSource (''Microsoft.ACE.OLEDB.12.0'',''Data Source="' + @trgPath + '";Extended properties=Text'')...' + @trgTable + '#txt ' + 'WHERE NOT ' + @Firstfield + ' IS NULL AND ' + @Firstfield + ' <> ' + '''' + @FirstfieldValue + ''''--PRINT @mySqlexec(@mySql)
三,文件准备,导入源及Schema.ini配置文件
对于excel文件来说,相对txt格式,实在简单很多,关键不过以下这段代码TRUNCATE TABLE WOOX_INFORMATICA..VBPA;INSERT INTO WOOX_INFORMATICA..VBPA select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\InitialData\db_data.xlsx;HDR=YES', 'SELECT * FROM [VBPA$]')--(适用于office2007及以上版本,2003用Jet - "Microsoft.Jet.OLEDB.4.0")其它需要注意的不过就是保证excel内的数据格式是统一的文本格式,这里只简单给出事例图,不做详细说明。
对于txt格式文件,本人所用格式如下。
//标示部分为标题;
ok,下面是关键,我们需要配置导入txt格式数据的配置文件Schema.ini!如果导入不成功或者出错,大都是这一步骤错了
(ps:此配置文件要和导入源放在同一个文件夹下,另外,文件夹目录最好别太长或者包含有空格之类的,这些自己注意!)
其中Schema.ini里规范格式如下。
[SAP_TVSTZ.txt]FORMAT=Delimited(|)ColNameHeader=TrueMaxScanRows=0CharacterSet=UnicodeTextDelimiter=`Col1=SKIP1 TEXTCol2=SKIP2 TEXTCOL3=VSBED TEXTCOL4=LADGR char(100)COL5=WERKS TEXTCOL6=LGORT TEXTCOL7=VSTEL TEXT
这里需要标明的是:域值内,第一行表示数据源文件名;第二行至第六行是必要的设置和说明,每个人按需修改;第七行以下必须按照Col从1索引开始递增,右边是显示的列名及格式。具体有兴趣的同学可以自己试着操作下,看下差异。
四,执行及检查
执行代码如下USE WOOX_CQMEXECUTE sp_ActivateDistributedQueries----------------------------- SAP Tables -----------------------------DECLARE @LoadPath varchar(2000)SET @LoadPath = 'C:\InitialData\SAPdata'--import Excel--EXECUTE sp_ReadXLSSource @LoadPath, 'SAP_TCURF', 'SAP_TCURF.xls', '[Sheet1$]','[KURST],[FCURR],[TCURR],[GDATU], [FFACT],[TFACT],[FromDate],[ToDate]'--import txt--EXECUTE sp_ReadSource @LoadPath, 'SAP_TVSTZ', '[VSBED],[LADGR],[WERKS],[LGORT],[VSTEL]'可用以下代码先行测试导入数据是否有差距,同学们记得更改本人所用的hardcored。
select * FROM OpenDataSource ('Microsoft.ACE.OLEDB.12.0','Data Source="C:\InitialData\SAPdata";Extended properties=Text')...SAP_TW06S#txtok,大概代码就是这样,如果有什么不懂或者有不同意见的,可用留言讨论!
五,通过SQL Server Agent Job 计划执行的出错处理
在用Job代理计划执行的时候,提示出错此时的解决办法是打开Windows Services ,找到对应的服务实例,然后右键打开属性,选择登录选项,把登录用户更改为本地机器账号,保存重启,然后就可以的了。
0 0
- 通过异类查询(OpenDataSource - OLEDB引擎驱动)把外部数据(Excel,TXT)导入到SqlServer及job代理执行出错的处理
- 把excel表格数据导入到SqlServer
- Excel导入外部数据的引擎
- sql批量导入外部数据excel到sqlserver
- 少量数据通过Excel表格数据导入server SqlServer查询
- 把Excel中的数据导入到Sqlserver中
- Excel的数据导入到sqlserver数据库
- excel导入数据到sqlserver的问题
- 通过vbs脚本从excel导入数据到SqlServer
- 使用C#把Excel格式文件的数据导入到sqlserver中
- 如何把sqlserver数据库中表的数据导入到excel中
- 使用OpenDataSource从Excel导入数据到SQL时报错
- C#把txt的数据导入excel,用逗号分隔
- SQL SERVER使用OpenRowset,、OpenDataSource函数导入、导出数据到Excel 的几种方法(整理)
- SQL SERVER使用OpenRowset,、OpenDataSource函数导入、导出数据到Excel 的几种方法(整理)
- 通过excel把SQLSERVER2000数据导入到SQLITE中
- SQLServer外部数据导入--Excel版
- 把txt文件的数据导入到mysql中2
- EF6增改删等常用基类
- ubuntu 网络仿真 段错误的一种可能的处理方法
- FindBugs 恶意代码(may expose internal representation),序列化(defines non-transient non-serializable )错误解决方法
- struts 搭建成功
- 关于 Linux C 中的头文件引用可能出现的问题解答
- 通过异类查询(OpenDataSource - OLEDB引擎驱动)把外部数据(Excel,TXT)导入到SqlServer及job代理执行出错的处理
- Unity3D的几种坐标系
- 一般图最大匹配问题-带花树开花算法
- netsh初步--删除无线网与设置共享无线网
- PHP集成百度Ueditor 1.4.3
- HighCharts常用方法总结
- C/C++中判断某一文件或目录是否存在
- Android编译系统参考手册
- Quartz2.2.1+spring4.0.6出现的异常