How to import Excel Data to SQL Server

Import and append

The example SQL statement used in this article demonstrates the "Create Table" query. This query imports the Excel data into the new
SQL Server table. As shown in the code example, when you continue to reference the source and target objects, you can use insert
The into... select... from syntax converts these statements into append queries.

Use DTS

You can use the SQL Server Data Transmission Service (DTS) Import Wizard to import Excel Data to the SQL Server table. In step-by-step wizard and select
When you create an Excel source table, remember that the EXCEL object name appended with the dollar sign ($) represents the worksheet (for example, sheet1 $), and 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/) how to combine SQL server linked servers and distributed queries 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.