通過異類查詢(OpenDataSource)把外部資料(Excel,TXT)匯入到SqlServer

標籤：匯入檔案資料   sqlserver   excel   txt   執行個體   

無論用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 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內的資料格式是統一的文本格式，這裡只簡單給出案例圖，不做詳細說明。
img-blog3
對於txt格式檔案，本人所用格式如下。
img-blog4
//標示部分為所用的標題；
ok，下面是關鍵，我們需要獨自未txt格式的匯入配置Schema.ini！如果匯入不成功或者出錯，大都是這一步驟錯了
（ps：此設定檔要和匯入源放在同一個檔案夾下，另外，檔案夾目錄最好別太長或者包含有空格之類的，這些自己注意！）
其中Schema.ini裡規範格式如下。
img-blog5
這裡需要標明的是：域值內，第一行表示資料來源檔案名稱；第二行至第六行是必要的設定和說明，每個人按需修改；第七行以下必須按照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#txt

ok，大概代碼就是這樣，如果有什麼不懂或者有不同意見的，可用留言討論！

通過異類查詢(OpenDataSource)把外部資料(Excel,TXT)匯入到SqlServer