也談SQL Server表與Excel、Access數(shù)據(jù)互導（使用T

悟靜 2012-04-30

展開全文

今天，那個新來的MM又來問我如何使用T-SQL語句進行 SQL Server表與Excel、Access數(shù)據(jù)互導的問題了，看來她是吃定我了，，，什么時候是個盡頭啊，，，，，555555555，，，，，
         不過也好，正好寫些東西，免得以后忘記了，或許對大家還有些幫助，，，，呵呵，，，只能自我安慰一下了。。

1、SQL Server導出為Excel：
        要用T-SQL語句直接導出至Excel工作薄，就必須借用SQL Server管理器的一個擴展存儲過程：“xp_cmdshell”，此過程的作用為“以操作系統(tǒng)命令行解釋器的方式執(zhí)行給定的命令字符串，并以文本行方式返回任何輸出?！毕旅鏋槎x示例：
EXEC master..xp_cmdshell 'bcp 庫名.dbo.表名out c:\Book3.xls -c -q -S"servername" -U"sa" -P""'

參數(shù)：S 是SQL服務(wù)器名；U是用戶名；P是密碼,沒有就空著

說明：其實用這個過程導出的格式實質(zhì)上就是文本格式的，不信的話在導出的Excel表中改動一下再保存看看。

    實際例子與說明如下：
A，將表整個導出至Excel：

注：
××northwind.dbo.orders，為數(shù)據(jù)庫名＋擁有者＋表名

××直接導出用“out”關(guān)健字

-------------------------------------------
B，利用查詢來導出部分字段至Excel：

注：
××在bcp后面加了一個查詢語句，并用雙引號括起來

××利用查詢要用“queryout”關(guān)鍵字

2、Excel導入SQL Server表：
        在SQL Server中，有定義一個OpenDateSource函數(shù)，用于引用那些不經(jīng)常訪問的 OLE DB 數(shù)據(jù)源，而我們的數(shù)據(jù)互導操作，就是建立在這個函數(shù)之上。

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

SELECT *
xactions

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

實例說明如下：

A、插入Excel中的資料到現(xiàn)存的sql數(shù)據(jù)庫表中（假設(shè)C盤有excel表book1.xls，book1.xls中有個工作表sheet1，sheet1中有兩列id和Name；而同時sql數(shù)據(jù)庫中也有一個表A）：
insert into A SELECT id,Name
[sheet1$]
注意：
不要用select * ，否則列的次序會亂，資料內(nèi)容也會亂，無法插入成功，慎重??！易錯。

B、插入excel表中資料到sql數(shù)據(jù)庫并新建一個sql表（excel的假設(shè)同上）：
select convert(int,id)as id,Name into A
[sheet1$]
注意：
在select 列中最好用convert進行顯示類型轉(zhuǎn)換，否則資料類型可能會不如預期。

3，補充：以上是 SQL Server與Excel的數(shù)據(jù)互導，而Access和Excel的基本一樣，只是要去掉Extended properties聲明，一樣easy。

下面補充一個Delphi的例子：（導出為excel表）：
        ADOQ1.Close;
        ADOQ1.SQL.Clear;
       QSTR := 'INSERT INTO CTable (Name1,Sex,ID) SELECT Name,Sex,IDNo FROM [excel 9.0;database=' + XlsName + '].[sheet1$]'；
       ADOQ1.Parameters.Clear;
       ADOQ1.ParamCheck:=FALSE;
       ADOQ1.SQL.Text := QSTR;
       ADOQ1.Execsql;