iT邦幫忙

DAY 2
5

我努力當個報表達人:一招半式闖江湖的經驗分享系列 第 2

指令型轉檔與不懂程式的轉檔方式

透過簡短的指令或程式開發人員開發的轉檔系統來完成轉檔作業
如果想要把EXCEL、文字檔等檔案透過指令的方式來轉出或轉入到SQL SERVER裡頭,透過下列的幾個指令,就可以完成。
開啟SQL SERVER Management Studio後依照下列的步驟
Step1:
#查詢是否已經啟用 xp_cmdshell
EXEC sp_configure 'show advanced option', '1';
GO
RECONFIGURE
GO

Step2:
EXEC sp_configure 'xp_cmdshell', '1' RECONFIGURE WITH OVERRIDE
GO

Step3:
#資料匯出excel
EXEC master..xp_cmdshell 'bcp eip.dbo.SYS_MAIL out c:\test.xls -c -q -S"Star\SQLEXPRESS" -U"sa" -P"pass"'

Step4:
#把資料從excel匯入到SQL SERVER
EXEC master..xp_cmdshell 'bcp eip.dbo.SYS_MAIL1 in c:\test.xls -c -q -S"Star\SQLEXPRESS" -U"sa" -P"pass"'

Step5:
#透過openrowset抓取文字檔資料到SQLSERVER
INSERT INTO T(e1)
SELECT * FROM OPENROWSET(
BULK 'c:\item1.txt',
SINGLE_BLOB) AS x
這裡用到的bcp是sql server中用大量檔案之間資料交換用的指令,它的詳細參數用法可以參考微軟官網http://technet.microsoft.com/zh-tw/library/ms162802.aspx,但通常轉檔作業我們會作一個視窗型的操作介面,來給使用者直接依照指定的格式,就可以輕鬆完成轉檔作業,如此也可以減輕程式開發人員的loading,視窗型作法如下

而它的程式說明重點整理如下
‘將使用者所上傳的EXCEL報表抓取出來
Dim ExcelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("../Upload_Excel/") + FileUpload1.FileName & ";Extended Properties=""Excel 8.0;HDR=NO;IMEX=1"""
……….
'抓取活頁簿
ExcelCmd.CommandText = "Select * from [Sheet1$]" '抓取Excel資料的SQL指令
ExcelCmd.CommandType = CommandType.Text
ExcelCmd.Connection = ExcelCn

'設定資料庫Connection連接
cn.ConnectionString = ConfigurationManager.ConnectionStrings("EIPConnectionString").ConnectionString
cn.Open()

‘透過bulkcopy來完成資料複製
Dim BulkCopy As New SqlBulkCopy(cn) '宣告SqlBulkCopy物件
BulkCopy.DestinationTableName = "PrizeData" '定義要匯入的資料庫Table
BulkCopy.WriteToServer(ExcelDr) '寫入資料

如此使用者就算不會寫程式也不懂指令,但透過寫好的轉檔工具,一樣可以快速完成轉檔功能,來實現快速資料轉換的效果出來,而通常這樣的轉檔作業,通常會是每個系統轉換的前置作業,如果能夠把這樣的瑣事給簡易化,相信後續對其他作業的執行也會更加順利。


上一篇
不同資料庫類型的資料交換
下一篇
大檔案的資料轉移方式
系列文
我努力當個報表達人:一招半式闖江湖的經驗分享27

我要留言

立即登入留言