從Excel匯入sql server

使用sql server 2005。

在sql server中，有一個OpenDateSource函式，用於引用那些不經常訪問的DLE DB資料來源，Excel與sql server資料匯入匯出就是建立在這個函式之上。

sql的查詢語句語法：SELECT 列名稱 FROM 表名稱。

插入語句語法：INSERT INTO 表名稱 VALUES (值1, 值2,....)。

在開始查詢excel之前，要先開啟對元件'Ad Hoc Distributed Queries' 的訪問，系統管理員可以通過使用 sp_configure 啟用 'Ad Hoc Distributed Queries'。開啟方法如下:
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

好了，準備工作就這麼多，下面開始excel向sql server的資料匯入。

首先看一個T-SQL幫助中的示例，描述如下：
--下面是個查詢的示例，它通過用於 Jet 的 OLE DB 提供程式查詢 Excel 電子表格。

SELECT * 
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

如 果你直接引用這個示例進行查詢，那麼肯定是通不過的。關鍵在於語句中的兩個地方需要修改，一處在於Data Source處，雙引號內為Excel表格的實際存放位置，要修改為你想查詢的Excel表實際完整路徑；二為最後的...xactions，其實這裡代 表的是要進行的某些動作，下面會講，這裡修改成用中括號包圍的Excel表中工作表名字（加上一個$）就可以了，如[Sheet1$]。當然，還可以將 Excel 5.0改為Excel 8.0，因為5.0是以前的老版本了。

下面是例項說明： 
     /*1、插入Excel中的資料到現存的sql資料庫表中（假設C盤有excel表book2.xls，book2.xls中有個工作表sheet1，sheet1中有兩列id和FName；而同時sql資料庫中也有一個表test）：*/
insert into test SELECT id,FName
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\book2.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')...[sheet1$]
--如果用select * ，則列的次序會亂，資料內容也會亂，無法插入成功，所以指定列名
－－－－－－－－－－－－－－－－－－－－－－－
     /*2、插入excel表中資料到sql資料庫並新建一個sql表（excel的定義和內容同上）：*/
select convert(int,id)as id,FName into test7
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\book2.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')...[sheet1$]
--在select 列中最好用convert進行顯示型別轉換，否則資料型別會不如預期。

sql server 向excel匯入資料

要用T-SQL語句直接匯出至Excel工作薄，就不得不用借用SQL Server管理器的一個擴充套件儲存過程：xp_cmdshell，此過程的作用為“以作業系統命令列直譯器的方式執行給定的命令字串，並以文字行方式返回任何輸出。”下面為定義示例：

EXEC master..xp_cmdshell 'bcp 庫名.dbo.表名out c:\Book3.xls -c -q -S"servername" -U"sa" -P""'

--引數：S 是SQL伺服器名；U是使用者名稱；P是密碼,沒有就空著

--說明：其實用這個過程匯出的格式實質上就是文字格式的，不信的話在匯出的Excel表中改動一下再儲存看看。

實際例子與說明如下：
/*如果要將表整個匯出至Excel的話*/
EXEC master..xp_cmdshell 'bcp northwind.dbo.orders out c:\Book1.xls -c -q -S"(local)" -U"sa" -P""'

--注意句中的northwind.dbo.orders，為資料庫名＋擁有者＋表名

--直接匯出用“out”關健字

-------------------------------------------
/*如果要利用查詢來匯出部分欄位至Excel的話*/
EXEC master..xp_cmdshell 'bcp "SELECT orderid,cutomerid,freight FROM northwind..orders ORDER BY orderid" queryout C:\ Book2.xls -c -S"(local)" -U"sa" -P""'

--這裡在bcp後面加了一個查詢語句，並用雙引號括起來

--利用查詢要用“queryout”關鍵字

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-625447/，如需轉載，請註明出處，否則將追究法律責任。