Excel導入SQL Server表的方法

大家知道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:Financeaccount.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:ook2.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:ook2.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: rade.mdb'; 'admin'; '', 表名)

SELECT *

FROM sqltablename

二、SQL SERVER 和EXCEL的數據導入導出

1、在SQL SERVER裡查詢Excel數據:

SELECT *

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

'Data Source="c:ook1.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:Financeaccount.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:ook1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

實例:

SELECT * into newtable

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

'Data Source="c:Financeaccount.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: emp1.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: raininginventur.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"&q