Knowledge Base
HOW TO: Import Data into SQL Server from ExcelPSS ID Number: 321686
Article Last Modified on 5/8/2003
--------------------------------------------------------------------------------
The information in this article applies to:
Microsoft Excel 2000
Microsoft SQL Server 2000 (all editions)
Microsoft SQL Server 7.0
Microsoft Excel 2002
Microsoft Excel 97 for Windows
--------------------------------------------------------------------------------
This article was previously published under Q321686
IN THIS TASK
SUMMARY
Description of the Technique
Requirements
Samples
Import vs. Append
Use DTS
Use a Linked Server
Use Distributed Queries
Use ADO and SQLOLEDB
Use ADO and the Jet Provider
Troubleshooting
REFERENCES
SUMMARY
This step-by-step article demonstrates how to import data from Microsoft Excel worksheets into Microsoft SQL Server databases by using a variety of methods.
back to the top
Description of the Technique
The samples in this article import Excel data by using:
SQL Server Data Transformation Services (DTS)
SQL Server linked servers
SQL Server distributed queries
ActiveX Data Objects (ADO) and the Microsoft OLE DB Provider for SQL Server
ADO and the Microsoft OLE DB Provider for Jet 4.0
back to the top
Requirements
The following list outlines the recommended hardware, software, network infrastructure, and service packs that are required:
Available instance of Microsoft SQL Server 7.0 or Microsoft SQL Server 2000
Microsoft Visual Basic 6.0 for the ADO samples that use Visual Basic
Portions of this article assume that you are familiar with the following topics:
Data Transformation Services
Linked servers and distributed queries
ADO development in Visual Basic
back to the top
Samples
Import vs. Append
The sample SQL statements that are used in this article demonstrate Create Table queries that import Excel data into a new SQL Server table by using the SELECT...INTO...FROM syntax. You can convert these statements to Append queries by using the INSERT INTO...SELECT...FROM syntax while you continue to reference the source and destination objects as shown in these code samples.
back to the top
Use DTS
You can use the SQL Server Data Transformation Services (DTS) Import Wizard to import Excel data into SQL Server tables. When you are stepping through the wizard and selecting the Excel source tables, remember that Excel object names that are appended with a dollar sign ($) represent worksheets (for example, Sheet1$), and that plain object names without the dollar sign represent Excel named ranges.
back to the top
Use a Linked Server
To simplify queries, you can configure an Excel workbook as a linked server in SQL Server. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
306397 HOWTO: Use Excel with SQL Server Linked Servers and Distributed Queries
The following code imports the data from the Customers worksheet on the Excel linked server "EXCELLINK" into a new SQL Server table named XLImport1:
SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]
You can also execute the query against the source in a passthrough manner by using OPENQUERY as follows:
SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,
'SELECT * FROM [Customers$]')
back to the top
Use Distributed Queries
If you do not want to configure a persistent connection to the Excel workbook as a linked server, you can import data for a specific purpose by using the OPENDATASOURCE or the OPENROWSET function. The following code samples also import the data from the Excel Customers worksheet into new SQL Server tables:
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 the top
Use ADO and SQLOLEDB
When you are connected to SQL Server in an ADO application by using Microsoft OLE DB for SQL Server (SQLOLEDB), you can use the same "distributed query" syntax from the Using Distributed Queries section to import Excel data into SQL Server.
The following Visual Basic 6.0 code sample requires that you add a project reference to ActiveX Data Objects (ADO). This code sample also demonstrates how to use OPENDATASOURCE and OPENROWSET over an 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 the top
Use ADO and the Jet Provider
The sample in the preceding section uses ADO with the SQLOLEDB Provider to connect to the destination of your Excel-to-SQL import. You can also use the OLE DB Provider for Jet 4.0 to connect to the Excel source.
The Jet database engine can reference external databases in SQL statements by using a special syntax that has 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 make an ODBC connection to the destination SQL Server database. You can use an ODBC Data Source Name (DSN) or a DSN-less connection 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 sample requires that you add a project reference to ADO. This code sample demonstrates how to import Excel data to SQL Server over an ADO connection by using the Jet 4.0 Provider.
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, which the Jet Provider supports, to import Excel data into other Microsoft Access databases, indexed sequential access method (ISAM) ("desktop") databases, or ODBC databases.
back to the top
Troubleshooting
Remember that Excel object names that are appended with a dollar sign ($) represent worksheets (for example, Sheet1$) and that plain object names represent Excel named ranges.
In some circumstances, especially when you designate the Excel source data by using the table name instead of a SELECT query, the columns in the destination SQL Server table are rearranged in alphabetical order.For additional information about this problem with the Jet Provider, click the article number below to view the article in the Microsoft Knowledge Base:
299484 PRB: Columns Are Sorted Alphabetically When You Use ADOX to Retrieve Columns of Access Table
When the Jet Provider determines that an Excel column contains mixed text and numeric data, the Jet Provider selects the "majority" data type and returns non-matching values as NULLs.For additional information about how to work around this problem, click the article number below to view the article in the Microsoft Knowledge Base:
194124 PRB: Excel Values Returned as NULL Using DAO OpenRecordset
back to the top
REFERENCES
For additional information about how to use Excel as a data source, click the article number below to view the article in the Microsoft Knowledge Base:
257819 HOWTO: Use ADO with Excel Data from Visual Basic or VBA
For additional information about how to transfer data into Excel, click the article numbers below to view the articles in the Microsoft Knowledge Base:
295646 HOWTO: Transfer Data from ADO Data Source to Excel with ADO
247412 INFO: Methods for Transferring Data to Excel from Visual Basic
246335 HOWTO: Transfer Data from an ADO Recordset to Excel with Automation
319951 HOW TO: Transfer Data to Excel by Using SQL Server Data Transformation Services
306125 HOW TO: Import Data from SQL Server into Microsoft Excel
back to the top
Additional query words: excel sql ado dts sqloledb jet
Keywords: kbhowto kbHOWTOmaster kbJET KB321686
Technology: kbAudDeveloper kbExcel2000 kbExcel2000Search kbExcel2002 kbExcel2002Search kbExcel97Search kbExcelSearch kbExcelWinSearch kbSQLServ2000 kbSQLServ2000Search kbSQLServ700 kbSQLServSearch kbZNotKeyword3
--------------------------------------------------------------------------------
Send feedback to Microsoft
© 2004 Microsoft Corporation. All rights reserved.