How to import Excel Data to SQL Server
Applicable
Content of this task
- Summary
-
- Technical description
- Requirements
- Example
-
- Import and append
- Use DTS
- Use linked servers
- Use Distributed Query
- Use ADO and sqloledb
- Use ADO and jet provider
- Troubleshooting
- Reference
Summary
This document step by step demonstrates how to import data from a Microsoft Excel worksheet to a Microsoft SQL Server database in different ways.
Back to Top
Technical description
In this example, use the following tools to import Excel Data:
- SQL Server Data Transmission Service (DTS)
- SQL Server connection Server
- SQL Server Distributed Query
- ActiveX Data Object (ADO) and Microsoft ole db provider for SQL Server
- ADO and Microsoft ole db provider for Jet 4.0
Back to Top
Requirements
The following list lists the recommended hardware, software, network architecture, and required service packages:
- Available instances of Microsoft SQL Server 7.0 or Microsoft SQL Server 2000
- Microsoft Visual Basic 6.0 (for ADO examples using Visual Basic)
This section assumes that you are familiar with the following topics:
- Data Transmission Service
- Link server and Distributed Query
- Ado development in Visual Basic
Back to Top
Example import and append
The example SQL statement used in this article demonstrates the "Create Table" query. This query imports Excel data into a new SQL Server table by using the Select... into... from syntax. As shown in the code example, when you continue to reference the source and target objects, you can use the insert into... select... from syntax to convert these statements into append queries.
Back to Top
Use DTS
You can use the SQL Server Data Transmission Service (DTS) Import Wizard to import Excel Data to the SQL Server table. When you step through the wizard and select an Excel source table, remember that the EXCEL object name with the dollar sign ($) appended represents the worksheet (for example, sheet1 $ ), the normal object name without the dollar sign represents the range specified by Excel.
Back to Top
Use linked servers
To simplify the query, you can configure the Excel Workbook as a link server in SQL Server. For other information, click the following article number to view the article in the Microsoft Knowledge Base:
306397 how to use excel in combination with SQL Server linked servers and distributed queries
Run the following code to import the MERs worksheet data on the Excel link server "excellink" to a new SQL Server table named xlimport1:
SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]
You can also use openquery to query source data in the following way:
SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK, 'SELECT * FROM [Customers$]')
Back to Top
Use Distributed Query
If you do not want to configure a persistent connection to an Excel Workbook as a linked server, you can use the OpenDataSource or OpenRowSet function to import data for a specific purpose. The following code example can also import the Excel MERs worksheet data to a new SQL Server table:
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$]')
Back to Top
Use ADO and sqloledb
When Microsoft ole db for SQL Server (sqloledb) is used to connect to SQL server in the ADO application, you can use the same "Distributed Query" syntax as the "Use Distributed Query" section to import Excel Data to SQL Server.
The following Visual Basic 6.0 code example requires that you add a project reference to ActiveX Data Object (ADO. This sample code also demonstrates how to use OpenDataSource and OpenRowset on the sqloledb connection.
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
Back to Top
Use ADO and jet provider
The example in the previous section uses ADO and sqloledb provider to connect to the target for importing data from Excel to SQL. You can also use ole db provider for Jet 4.0 to connect to the Excel source.
The jet data engine can reference external databases in SQL statements by using special syntaxes in three different formats:
- [Full path to Microsoft Access database]. [Table Name]
- [Isam name;Isam connection string]. [Table Name]
- [ODBC;ODBC connection string]. [Table Name]
This section uses the third format to create an ODBC connection to the target SQL Server database. You can use the ODBC Data Source Name (DSN) or DSN-less to connect to the string:
DSN: [odbc;DSN=<DSN name>;UID=<user>;PWD=<password>]DSN-less: [odbc;Driver={SQL Server};Server=<server>;Database=<database>; UID=<user>;PWD=<password>]
The following Visual Basic 6.0 code example requires that you add a project reference to ADO. This sample code demonstrates how to use the JET 4.0 provider to import Excel Data to SQL Server through an ADO connection.
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
You can also use this syntax (supported by jet provider) to import Excel data to other Microsoft Access databases, index sequential access methods (isam) ("desktop") databases, or ODBC databases.
Back to Top
Troubleshooting
Back to Top reference
For more information about how to use Excel as a data source, click the following article number to view the article in the Microsoft Knowledge Base:
257819 how to use ADO in Visual Basic or VBA to process Excel Data
For more information about how to transfer data to excel, click the following article number to view the article in the Microsoft Knowledge Base:
295646 how to use ADO to transmit data from the ADO data source to excel
247412 info: how to transfer data from Visual Basic to excel
246335 how to use the "Automation" function to transfer data from the ADO record set to excel
319951 how to transfer data to excel through SQL Server Data Transmission Service
306125 how to import data from Microsoft SQL server to Microsoft Excel
The information in this article is applicable:
- In Microsoft Excel 2000
- Microsoft SQL Server 2000 (all editions)
- Microsoft SQL Server 2000 64-bit (all editions)
- Microsoft SQL Server 7.0
- In Microsoft Excel 2002
- Microsoft Excel 97 for Windows
Latest updates: |
(2.1) |
Keywords: |
Kbhowtomaster kbjet kb321686 kbauddeveloper |