sql server與excel、access資料互導

1、SQL Server匯出為Excel：

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

　　2、Excel匯入SQL Server表：

　　在SQL Server中，有定義一個OpenDateSource函數，用於引用那些不經常訪問的 OLE DB 資料來源，而我們的資料互導操作，就是建立在這個函數之上。

　　首先看一個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

　　註：--在password=；的後面，加個 HDR=NO 的選項， 表示第1行是資料， 預設為YES， 表示第1行是欄位名

　　如果你直接引用這個樣本進行查詢，那麼肯定是通不過的。關鍵在於語句中的兩個地方需要修改，一處在於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進行顯示類型轉換，否則資料類型會不如預期。

　　特別注意!!!：1)如果是從資料庫中匯出的exel表，例如從jobs表匯出的exel檔案mytest.xls工作表預設是jobs上面例子中的[sheet1$] 應改為[jobs$]

　　2)如果出現“伺服器： 訊息 7399，層級 16，狀態 1，行 1

　　OLE DB 提供者 'MICROSOFT.JET.OLEDB.4.0' 報錯。提供者未給出有關錯誤的任何資訊。”

　　上面這個錯誤是因為你的EXECL 檔案被開啟著，關掉那個EXCEL檔案再試試.

　　3)被匯入的exel表第一行要有各列的列名如

　　id name age

　　1 tomclus 35

　　。。。

　　如果沒有列名僅僅

　　1 tomclus 35

　　。。。

　　可能會出錯

　　如果上面的例子中沒有制定所有列，或select*，都會出錯，如列不完全，或資料類型布匹

　　SQL Server與Excel的資料互導講解完了，你明白了嗎?而Access和Excel的基本一樣，只是要去掉Extended properties聲明。

　　=======================

　　Delphi樣本(導出為excel表)：

　　ADOQ1.Close；

　　ADOQ1.SQL.Clear；

　　sqltrs ：=

　　'INSERT INTO CTable (Name1，Sex，ID)'+

　　' SELECT'+

　　' 姓名，性別，社會安全號碼'+

　　' FROM [excel 8.0；database=' + XlsName + '].[sheet1$]'；

　　ADOQ1.Parameters.Clear；

　　ADOQ1.ParamCheck：=false；

　　ADOQ1.SQL.Text ：= sqltrs；

　　ADOQ1.Execsql；

　　//中文欄位兩邊不能有空格

　　另附：(下面的部分內容沒有親自實踐)

　　熟 悉SQL SERVER 2000的資料庫管理員都知道，其DTS可以進行資料的匯入匯出，其實，我們也可以使用Transact-SQL語句進行匯入匯出操作。在 Transact-SQL語句中，我們主要使用OpenDataSource函數、OPENROWSET 函數，關於函數的詳細說明，請參考SQL線上說明。利用下述方法，可以十分容易地實現SQL SERVER、ACCESS、EXCEL資料轉換，詳細說明如下：

　　一、SQL SERVER 和ACCESS的資料匯入匯出

　　常規的資料匯入匯出：

　　使用DTS嚮導遷移你的Access資料到SQL Server，你可以使用這些步驟：

　　○1在SQL SERVER企業管理器中的Tools(工具)菜單上，選擇Data Transformation

　　○2Services(資料轉換服務)，然後選擇 czdImport Data(匯入資料)。

　　○3在Choose a Data Source(選擇資料來源)對話方塊中選擇Microsoft Access as the Source，然後鍵入你的.mdb資料庫(.mdb副檔名)的檔案名稱或通過瀏覽尋找該檔案。

　　○4在Choose a Destination(選擇目標)對話方塊中，選擇Microsoft OLE　DB Prov ider for SQL　Server，選擇資料庫伺服器，然後單擊必要的驗證方式。

　　○5在Specify Table Copy(指定表格複製)或Query(查詢)對話方塊中，單擊Copy tables(複製表格)。

　　○6在Select Source Tables(選擇源表格)對話方塊中，單擊Select All(全部選定)。下一步，完成。

　　Transact-SQL語句進行匯入匯出：

　　1.在SQL SERVER裡查詢access資料：

　　SELECT *

　　FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0'，

　　'Data Source="c：\DB.mdb"；User ID=Admin；Password=')...表名

　　2.將access匯入SQL server

　　在SQL SERVER 裡運行：

　　SELECT *

　　INTO newtable

　　FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0'，

　　'Data Source="c：\DB.mdb"；User ID=Admin；Password=' )...表名

　　3.將SQL SERVER表裡的資料插入到Access表中

　　在SQL SERVER 裡運行：

　　insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0'，

　　'Data Source=" c：\DB.mdb"；User ID=Admin；Password=')...表名

　　(列名1，列名2)

　　select 列名1，列名2 from sql表

　　執行個體：

　　insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0'，

　　'C：\db.mdb'；'admin'；''， Test)

　　select id，name from Test

　　INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0'， 'c：\trade.mdb'； 'admin'； ''， 表名)

　　SELECT *

　　FROM sqltablename

　　二、SQL SERVER 和EXCEL的資料匯入匯出

　　1、在SQL SERVER裡查詢Excel資料：

　　SELECT *

　　FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0'，

　　'Data Source="c：\book1.xls"；User ID=Admin；Password=；Extended properties=Excel 5.0')...[Sheet1$]

　　下面是個查詢的樣本，它通過用於 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

　　2、將Excel的資料匯入SQL server ：

　　SELECT * into newtable

　　FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0'，

　　'Data Source="c：\book1.xls"；User ID=Admin；Password=；Extended properties=Excel 5.0')...[Sheet1$]

　　執行個體：

　　SELECT * into newtable

　　FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0'，

　　'Data Source="c：\Finance\account.xls"；User ID=Admin；Password=；Extended properties=Excel 5.0')...xactions

　　3、將SQL SERVER中查詢到的資料導成一個Excel檔案

　　T-SQL代碼：

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

　　參數：S 是SQL伺服器名；U是使用者；P是密碼

　　說明：還可以匯出文字檔等多種格式

　　執行個體：EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c：\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'

　　EXEC master..xp_cmdshell 'bcp "SELECT au_fname， au_lname FROM pubs..authors ORDER BY au_lname" queryout C：\ authors.xls -c -Sservername -Usa -Ppassword'

　　在VB6中應用ADO匯出EXCEL檔案代碼：

　　Dim cn As New ADODB.Connection

　　cn.open "Driver={SQL Server}；Server=WEBSVR；DataBase=WebMis；UID=sa；WD=123；"

　　cn.execute "master..xp_cmdshell 'bcp "SELECT col1， col2 FROM 庫名.dbo.表名" queryout E：\DT.xls -c -Sservername -Usa -Ppassword'"

　　4、在SQL SERVER裡往Excel插入資料：

　　insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0'，

　　'Data Source="c：\Temp.xls"；User ID=Admin；Password=；Extended properties=Excel 5.0')...table1 (A1，A2，A3) values (1，2，3)

　　T-SQL代碼：

　　INSERT INTO

　　OPENDATASOURCE('Microsoft.JET.OLEDB.4.0'，

　　'Extended Properties=Excel 8.0；Data source=C：\training\inventur.xls')...[Filiale1$]

　　(bestand， produkt) VALUES (20， 'Test')

　　總結：利用以上語句，我們可以方便地將SQL SERVER、ACCESS和EXCEL試算表軟體中的資料進行轉換，為我們提供了極大方便!

　　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”關鍵字

　　關於SQL Server與Excel、Access資料互導問題的補充：

　　1、將excel中的資料匯入sql中時，數字變為科學計數法的解決辦法：

　　如：

　　excel中的資料為：8630890

　　匯入sql後變為：8.63089e+006

　　註：sql中該欄位資料類型為nvchar。

　　可以參考下面的方法轉換已經匯入的資料，但因精度問題導致的資料不準確不能被處理，另外，excel資料中，如果有前置的0，那麼匯入後的資料由於是float數字，所以會丟失前置0。

　　declare @a float

　　set @a=8.63089e+006

　　select cast(@a as decimal(38))

　　--結果：8630890

　　結合自己的執行個體，給大家一段代碼：

　　file1=request("file")

　　sql="insert into student(studyid，yourname，yourpass，yourclass，courseid) SELECT cast(學號 as decimal(18))，姓名，cast(密碼 as decimal(18))，班級，cast(選課班號 as decimal(18)) FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0'，'Data Source="&file1&"；User ID=Admin；Password=；Extended properties=Excel 8.0')...[sheet1$]"

　　conn.Execute sql

　　2、如何得到EXCEL的表名(asp中)：

　　set app=server.CreateObject("Excel.Application")

　　app.Workbooks.Open(""&file1&"")

　　for i =1 to app.worksheets.count

　　response.write app.worksheets(i).name

　　next

　　測試的時候，不知什麼原因，時好時不好的，有待進一步解決!