也談SQL Server表與Excel、Access資料互導

        最近看到很多朋友在論壇上問SQL Server表與Excel、Access資料互導的問題，問題很簡單，也很早就有人專門寫文章討論過這個問題，但看了那些文章，也沒幾個人講得很明白，都是些很籠統的格式，估計初學者會被那些答案弄得稀裡糊塗，更別說能學到新的東西。

        基於這個原因，下面我將詳細的講解互導的過程，當然，常規的在SQL Server管理器中得用嚮導互導的過程我就不多講了，下面講的都是直接用T-SQL語句來實現的。

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

        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

        如果你直接引用這個樣本進行查詢，那麼肯定是通不過的。關鍵在於語句中的兩個地方需要修改，一處在於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的資料互導講解完了，你明白了嗎？而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;


//注意中文欄位名左右兩邊不能有空格

                引用請註明出處：

                        cnblogs(bonny.wong) 2005.1.29