How to import Excel Data to SQL Server (from msdn)

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

• Remember that the EXCEL object name appended with the dollar sign ($) represents the worksheet (for example, sheet1 $), and the normal object name represents the range specified by Excel.
• In some environments, especially when the table name replaces the SELECT query assignment Excel source data, the columns in the target SQL Server table are rearranged alphabetically. For other information about this issue in Jet provider, click the following article number to view the article in the Microsoft Knowledge Base:

  299484 PRB: Use ADOX to retrieve access table columns in alphabetical order

• When jet provider determines that an Excel column contains mixed text and numeric data, jet provider selects the "majority" data type and returns unmatched values as null. For more information about how to solve this problem, click the following article number to view the article in the Microsoft Knowledge Base:

  194124 PRB: the Excel return value is null when Dao openrecordset is used.

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