本文循序漸進(jìn)地演示如何用不同的方法將數(shù)據(jù)從 Microsoft Excel 工作表導(dǎo)入到 Microsoft SQL Server 數(shù)據(jù)庫。
技術(shù)說明
本文中的示例使用以下工具導(dǎo)入 Excel 數(shù)據(jù)：
| • |
SQL Server 數(shù)據(jù)傳輸服務(wù) (DTS) |
| • |
SQL Server 鏈接服務(wù)器 |
| • |
SQL Server 分布式查詢 |
| • |
ActiveX 數(shù)據(jù)對(duì)象 (ADO) 和 Microsoft OLE DB Provider for SQL Server |
| • |
ADO 和 Microsoft OLE DB Provider for Jet 4.0 |
要求
下面的列表列出了推薦使用的硬件、軟件、網(wǎng)絡(luò)架構(gòu)以及所需的服務(wù)包：
| • |
Microsoft SQL Server 7.0 或 Microsoft SQL Server 2000 的可用實(shí)例 |
| • |
Microsoft Visual Basic 6.0（針對(duì)使用 Visual Basic 的 ADO 示例） |
本文的部分內(nèi)容假定您熟悉下列主題：
| • |
數(shù)據(jù)傳輸服務(wù) |
| • |
鏈接服務(wù)器和分布式查詢 |
| • |
Visual Basic 中的 ADO 開發(fā) |
示例
導(dǎo)入與追加
本文使用的示例 SQL 語句演示了“創(chuàng)建表”查詢。該查詢通過使用 SELECT...INTO...FROM 語法將 Excel 數(shù)據(jù)導(dǎo)入新的 SQL Server 表。如這些代碼示例所示，在繼續(xù)引用源對(duì)象和目標(biāo)對(duì)象時(shí)，可以通過使用 INSERT INTO...SELECT...FROM 語法將這些語句轉(zhuǎn)換成追加查詢。
使用 DTS
可以使用 SQL Server 數(shù)據(jù)傳輸服務(wù) (DTS) 導(dǎo)入向?qū)?Excel 數(shù)據(jù)導(dǎo)入 SQL Server 表。在逐步執(zhí)行向?qū)Р⑦x擇 Excel 源表時(shí)，要記住附加美元符號(hào) ($) 的 Excel 對(duì)象名稱代表工作表（例如，Sheet1$），而沒有美元符號(hào)的普通對(duì)象名稱代表 Excel 指定的范圍。
使用鏈接服務(wù)器
要簡(jiǎn)化查詢，可以將 Excel 工作簿配置為 SQL Server 中的鏈接服務(wù)器。 有關(guān)其他信息，請(qǐng)單擊下面的文章編號(hào)，以查看 Microsoft 知識(shí)庫中相應(yīng)的文章：
306397 (http://support.microsoft.com/kb/306397/) 如何結(jié)合 SQL Server 鏈接的服務(wù)器和分布式查詢使用 Excel
下列代碼將 Excel 鏈接服務(wù)器“EXCELLINK”上的 Customers 工作表數(shù)據(jù)導(dǎo)入新的名為 XLImport1 的 SQL Server 表：
SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]
SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,
‘SELECT * FROM [Customers$]‘)
使用分布式查詢
如果不想將對(duì) Excel 工作簿的永久連接配置為鏈接服務(wù)器，可以通過使用 OPENDATASOURCE 或 OPENROWSET 函數(shù)為特定目的導(dǎo)入數(shù)據(jù)。下列代碼示例也能將 Excel Customers 工作表數(shù)據(jù)導(dǎo)入新的 SQL Server 表：
SELECT * INTO XLImport3 FROM OPENDATASOURCE(‘Microsoft.Jet.OLEDB.4.0‘,
‘Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0‘)...[Customers$]
SELECT * INTO XLImport4 FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0‘,
‘Excel 8.0;Database=C:\test\xltest.xls‘, [Customers$])
SELECT * INTO XLImport5 FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0‘,
‘Excel 8.0;Database=C:\test\xltest.xls‘, ‘SELECT * FROM [Customers$]‘)
使用 ADO 和 SQLOLEDB
當(dāng)通過使用 Microsoft OLE DB for SQL Server (SQLOLEDB) 在 ADO 應(yīng)用程序中連接到 SQL Server 時(shí)，可以使用與
“使用分布式查詢” 一節(jié)中相同的“分布式查詢”語法將 Excel 數(shù)據(jù)導(dǎo)入 SQL Server。
下列 Visual Basic 6.0 代碼示例要求添加對(duì) ActiveX 數(shù)據(jù)對(duì)象 (ADO) 的項(xiàng)目引用。此代碼示例還演示了如何在 SQLOLEDB 連接上使用 OPENDATASOURCE 和 OPENROWSET。
Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;Data Source=<server>;" & _
"Initial Catalog=<database>;User ID=<user>;Password=<password>"
‘Import by using OPENDATASOURCE.
strSQL = "SELECT * INTO XLImport6 FROM " & _
"OPENDATASOURCE(‘Microsoft.Jet.OLEDB.4.0‘, " & _
"‘Data Source=C:\test\xltest.xls;" & _
"Extended Properties=Excel 8.0‘)...[Customers$]"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff
‘Import by using OPENROWSET and object name.
strSQL = "SELECT * INTO XLImport7 FROM " & _
"OPENROWSET(‘Microsoft.Jet.OLEDB.4.0‘, " & _
"‘Excel 8.0;Database=C:\test\xltest.xls‘, " & _
"[Customers$])"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff
‘Import by using OPENROWSET and SELECT query.
strSQL = "SELECT * INTO XLImport8 FROM " & _
"OPENROWSET(‘Microsoft.Jet.OLEDB.4.0‘, " & _
"‘Excel 8.0;Database=C:\test\xltest.xls‘, " & _
"‘SELECT * FROM [Customers$]‘)"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff
cn.Close
Set cn = Nothing
使用 ADO 和 Jet Provider
上一節(jié)中的示例使用 ADO 和 SQLOLEDB Provider 連接到從 Excel 到 SQL 導(dǎo)入的目標(biāo)。也可以使用 OLE DB Provider for Jet 4.0 來連接到 Excel 源。
Jet 數(shù)據(jù)引擎可以通過使用具有三種不同格式的特殊語法來在 SQL 語句中引用外部數(shù)據(jù)庫：
| • |
[Full path to Microsoft Access database].[Table Name] |
| • |
[ISAM Name;ISAM Connection String].[Table Name] |
| • |
[ODBC;ODBC Connection String].[Table Name] |
本節(jié)使用第三種格式創(chuàng)建到目標(biāo) SQL Server 數(shù)據(jù)庫的 ODBC 連接。可以使用 ODBC 數(shù)據(jù)源名稱 (DSN) 或者 DSN-less 連接字符串：
DSN:
[odbc;DSN=<DSN name>;UID=<user>;PWD=<password>]
DSN-less:
[odbc;Driver={SQL Server};Server=<server>;Database=<database>;
UID=<user>;PWD=<password>]
Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\test\xltestt.xls;" & _
"Extended Properties=Excel 8.0"
‘Import by using Jet Provider.
strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
"Server=<server>;Database=<database>;" & _
"UID=<user>;PWD=<password>].XLImport9 " & _
"FROM [Customers$]"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff
cn.Close
Set cn = Nothing
疑難解答
| • |
記住附加美元符號(hào) ($) 的 Excel 對(duì)象名稱代表工作表（例如：Sheet1$），而普通對(duì)象名稱代表 Excel 指定的范圍。 |
| • |
在某些環(huán)境中，特別是用表名稱取代 SELECT 查詢指派 EXCEL 源數(shù)據(jù)時(shí)，目標(biāo) SQL Server 表中的列會(huì)按照字母順序重排。有關(guān) Jet Provider 中存在的這一問題的其他信息，請(qǐng)單擊下面的文章編號(hào)，以查看 Microsoft 知識(shí)庫中相應(yīng)的文章：
299484 (http://support.microsoft.com/kb/299484/) PRB：使用 ADOX 檢索 Access 表的列時(shí)，列按字母順序排列 |
| • |
當(dāng) Jet Provider 確定一個(gè) Excel 列包含了混合文本和數(shù)值數(shù)據(jù)時(shí)，Jet Provider 會(huì)選擇“majority”數(shù)據(jù)類型并將不匹配的值以 NULL 形式返回。有關(guān)如何解決這個(gè)問題的其他信息，請(qǐng)單擊下面的文章編號(hào)，以查看 Microsoft 知識(shí)庫中相應(yīng)的文章：
194124 (http://support.microsoft.com/kb/194124/) PRB：使用 DAO OpenRecordset 時(shí) Excel 返回值為 NULL |
有關(guān)如何將 Excel 用作數(shù)據(jù)源的其他信息，請(qǐng)單擊下面的文章編號(hào)，以查看 Microsoft 知識(shí)庫中相應(yīng)的文章：
257819 (http://support.microsoft.com/kb/257819/) 如何在 Visual Basic 或 VBA 中使用 ADO 來處理 Excel 數(shù)據(jù)
有關(guān)如何將數(shù)據(jù)傳輸?shù)?Excel 中的其他信息，請(qǐng)單擊下面的文章編號(hào)，以查看 Microsoft 知識(shí)庫中相應(yīng)的文章：
295646 (http://support.microsoft.com/kb/295646/) 如何使用 ADO 將數(shù)據(jù)從 ADO 數(shù)據(jù)源傳輸?shù)?Excel
247412 (http://support.microsoft.com/kb/247412/) INFO：將數(shù)據(jù)從 Visual Basic 傳輸?shù)?Excel 的方法
246335 (http://support.microsoft.com/kb/246335/) 如何使用“自動(dòng)化”功能將數(shù)據(jù)從 ADO 記錄集傳輸?shù)?Excel
319951 (http://support.microsoft.com/kb/319951/) 如何通過 SQL Server 數(shù)據(jù)傳輸服務(wù)向 Excel 傳送數(shù)據(jù)
306125 (http://support.microsoft.com/kb/306125/) 如何將數(shù)據(jù)從 Microsoft SQL Server 導(dǎo)入 Microsoft Excel
