How to import data from Excel to SQL Server

This document step by step demonstrates how to import data from a Microsoft Excel worksheet to a Microsoft SQL Server database in different ways. Technical description

In this example, use the following tools to import Excel Data:

• SQL Server Data Transmission Service (DTS)
• Microsoft SQL Server 2005 integration services (SSIS)
• 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

Requirements

The following list lists the recommended hardware, software, network architecture, and required Service Packs:

• Available instances of Microsoft SQL Server 7.0, Microsoft SQL Server 2000, or Microsoft SQL Server 2005
• 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

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.

Use DTS or SSIS

You can use the "SQL server data transformation services (DTS) Import wizard" or "SQL Server Import and Export 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.

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 (http://support.microsoft.com/kb/306397/EN-US/) How to: Combine SQL server linked server and distributed query using Excel

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$]')

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$]')

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

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.