通过异类查询(OpenDataSource)把外部数据(Excel,TXT)导入到SqlSe-Mysql Tutorial-php.cn

通过异类查询(OpenDataSource)把外部数据(Excel,TXT)导入到SqlSe

WBOY

Release： 2016-06-07 16:10:32

Original

1947 people have browsed it

无论用sqlcmd或者通过C#逻辑处理，或者还是直接在SqlServer里执行操作，个人总结大概不过可归总为这两种方式： 1，SSIS； 2，OLEDB驱动引擎。 SSIS实在也好用，现在这里先不去讨论。其中大多数人用的都是第二种方法，下面，本人也以第二种方法在SqlServer里导

无论用sqlcmd或者通过C#逻辑处理，或者还是直接在SqlServer里执行操作，个人总结大概不过可归总为这两种方式：
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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_ReadXLSSource]
@trgPath varchar(255),
@trgTable varchar(255),
@xlsFile varchar(255),
@xlsSheet varchar(255),
@xlsFields varchar(8000)
AS
declare @mySql varchar(8000)
declare @myIntermediate varchar(8000)
declare @Firstfield varchar(100)
declare @FirstfieldValue varchar(100)

--Lookup lastfield
set @Firstfield = dbo.fnGetFirstField(@xlsFields)
set @FirstfieldValue = RTRIM(LTRIM(SUBSTRING(@Firstfield,2,CHARINDEX(&#39;]&#39;,@Firstfield,0)-2)))
--PRINT @FirstfieldValue

--Drop table if exists
set @myIntermediate = &#39;IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N&#39;&#39;[dbo].[&#39; + @trgTable + &#39;]&#39;&#39;) AND type in (N&#39;&#39;U&#39;&#39;))&#39;
set @mySql = @myIntermediate + &#39; DROP TABLE &#39; + @trgTable
exec(@mySql)
--PRINT @mySql

--Load Table
set @mySql = &#39;SELECT &#39; + @xlsFields + &#39; INTO &#39; + @trgTable + &#39; FROM OPENROWSET(&#39;&#39;Microsoft.ACE.OLEDB.12.0&#39;&#39;,  
&#39;&#39;Excel 12.0;DATABASE=&#39; + @trgPath + &#39;\&#39; + @xlsFile + &#39;;IMEX=1&#39;&#39;,  &#39;&#39;Select * from &#39; + @xlsSheet + &#39;&#39;&#39;)&#39;
+ &#39;WHERE NOT &#39; + @Firstfield + &#39; IS NULL&#39;
--PRINT @mySql
exec(@mySql)

Copy after login

存储过程：sp_ReadSource

USE [WOOX_CQM]
GO
/****** Object:  StoredProcedure [dbo].[sp_ReadSource]    Script Date: 11/21/2014 2:54:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_ReadSource]
@trgPath varchar(255),
@trgTable varchar(255),
@trgFields varchar(8000)
AS
declare @mySql varchar(8000)
declare @myIntermediate varchar(8000)
declare @Firstfield varchar(100)
declare @FirstfieldValue varchar(100)

--Lookup lastfield
set @Firstfield = dbo.fnGetFirstField(@trgFields)
set @FirstfieldValue = RTRIM(LTRIM(SUBSTRING(@Firstfield,2,CHARINDEX(&#39;]&#39;,@Firstfield,0)-2)))
--PRINT @FirstfieldValue

--Drop table if exists
set @myIntermediate = &#39;IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N&#39;&#39;[dbo].[&#39; + @trgTable + &#39;]&#39;&#39;) AND type in (N&#39;&#39;U&#39;&#39;))&#39;
set @mySql = @myIntermediate + &#39; DROP TABLE &#39; + @trgTable
exec(@mySql)
--PRINT @mySql

--Load Table
set @mySql = &#39;SELECT &#39; + @trgFields + &#39; INTO &#39; + @trgTable + &#39; &#39; +
&#39;FROM OpenDataSource (&#39;&#39;Microsoft.ACE.OLEDB.12.0&#39;&#39;,
&#39;&#39;Data Source="&#39; + @trgPath + &#39;";Extended properties=Text&#39;&#39;)...&#39; 
+ @trgTable + &#39;#txt &#39; 
+ &#39;WHERE NOT &#39; + @Firstfield + &#39; IS NULL AND &#39; + @Firstfield + &#39; <> &#39; + &#39;&#39;&#39;&#39; + @FirstfieldValue + &#39;&#39;&#39;&#39;
--PRINT @mySql
exec(@mySql)

Copy after login

三，文件准备,导入源及Schema.ini配置文件

对于excel文件来说,相对txt格式，实在简单很多，关键不过以下这段代码

TRUNCATE TABLE WOOX_INFORMATICA..VBPA;
INSERT INTO WOOX_INFORMATICA..VBPA select * FROM OPENROWSET(&#39;Microsoft.ACE.OLEDB.12.0&#39;, &#39;Excel 12.0;Database=C:\InitialData\db_data.xlsx;HDR=YES&#39;, &#39;SELECT * FROM [VBPA$]&#39;)
--(适用于office2007及以上版本，2003用Jet - "Microsoft.Jet.OLEDB.4.0")

Copy after login

其它需要注意的不过就是保证excel内的数据格式是统一的文本格式，这里只简单给出事例图，不做详细说明。

对于txt格式文件，本人所用格式如下。

vcr9vt21xMXk1sPOxLz+U2NoZW1hLmluaaOhyOe5+7W8yOuyu7PJuaa78tXfs/a07aOstPO2vMrH1eLSu7K91ui07cHLPGJyPgqjqHBzo7q0y8Xk1sPOxLz+0qq6zbW8yOvUtLfF1NrNrNK7uPbOxLz+vNDPwqOswe3N4qOszsS8/rzQxL/CvNfuusOx8Myrs6S78tXfsPy6rNPQv9UmIzI2Njg0O9auwOC1xKOs1eLQqdfUvLrXotLio6GjqTxicj4KxuTW0FNjaGVtYS5pbmnA77nmt7YmIzI2Njg0O8q9yOfPwqGjPHByZSBjbGFzcz0="brush:sql;">[SAP_TVSTZ.txt] FORMAT=Delimited(|) ColNameHeader=True MaxScanRows=0 CharacterSet=Unicode TextDelimiter=` Col1=SKIP1 TEXT Col2=SKIP2 TEXT COL3=VSBED TEXT COL4=LADGR char(100) COL5=WERKS TEXT COL6=LGORT TEXT COL7=VSTEL TEXT

这里需要标明的是：域值内，第一行表示数据源文件名；第二行至第六行是必要的设置和说明，每个人按需修改；第七行以下必须按照Col从1索引开始递增，右边是显示的列名及格式。具体有兴趣的同学可以自己试着操作下，看下差异。

四，执行及检查

执行代码如下

USE WOOX_CQM

EXECUTE sp_ActivateDistributedQueries
----------------------------- SAP Tables -----------------------------
DECLARE @LoadPath varchar(2000)
SET @LoadPath = &#39;C:\InitialData\SAPdata&#39;

--import Excel--
EXECUTE sp_ReadXLSSource 
@LoadPath, 
&#39;SAP_TCURF&#39;, &#39;SAP_TCURF.xls&#39;, &#39;[Sheet1$]&#39;,
&#39;[KURST],[FCURR],[TCURR],[GDATU], [FFACT],[TFACT],[FromDate],[ToDate]&#39;

--import txt--
EXECUTE sp_ReadSource 
@LoadPath, 
&#39;SAP_TVSTZ&#39;, 
&#39;[VSBED],[LADGR],[WERKS],[LGORT],[VSTEL]&#39;

Copy after login

可用以下代码先行测试导入数据是否有差距，同学们记得更改本人所用的hardcored。

select * 
FROM OpenDataSource (&#39;Microsoft.ACE.OLEDB.12.0&#39;,
&#39;Data Source="C:\InitialData\SAPdata";Extended properties=Text&#39;)...SAP_TW06S#txt

Copy after login

ok，大概代码就是这样，如果有什么不懂或者有不同意见的，可用留言讨论！