How to import Excel data into SQL Server
Suitable
This task content
summary
Technical description requirements
Import and additional use of DTS Using the Link Server Using Distributed Queries Using ADO and SQLOLEDB Using ADO and Jet Provider Troubleshooting Reference
SUMMARY This article is gradually demonstrated how to use different ways to import data from the Microsoft Excel worksheet to the Microsoft SQL Server database.
Back to top
Technical Description This article uses the following tools to import Excel data:
SQL Server Data Transfer Service (DTS) SQL Server Link Server SQL Server Distributed Query ActiveX Data Objects (ADO) and Microsoft Ole DB Provider for SQL Server ADO and Microsoft Ole DB Provider for Jet 4.0
Back to top
Require the following list lists the recommended hardware, software, network architecture, and service packs required:
Microsoft SQL Server 7.0 or Microsoft SQL Server 2000 Available Instances Microsoft Visual Basic 6.0 (for ADO for Visual Basic) This article is assumed to be familiar with the following topics:
Data Transfer Service Link Server and Distributed Query Visual Basic ADO Development
Back to top
Example
The example SQL statement used by the introduction and adding this article demonstrates the "Create Table" query. The query imports Excel data into a new SQL Server table by using the Select ... Into ... from syntax. As shown in these code examples, when continues to reference source objects and target objects, you can convert these statements into additional queries by using INSERT INTO ... SELECT ... FROM.
Back to top
Use the DTS to use the SQL Server Data Transfer Service (DTS) Import Wizard to import Excel data into the SQL Server table. When you gradually execute the wizard and select the Excel source table, you have to remember the additional US dollar symbol ($) of the Excel object name representative worksheet (for example, sheet1 $), without the US dollar symbol, represents the range specified by Excel.
Back to top
Using the Link Server To simplify the query, you can configure the Excel workbook as a link server in SQL Server. For additional information, click the article number below to see the article in the Microsoft Knowledge Base:
306397 How to combine Server and distributed query for SQL Server Use Excel The following code The Customers worksheet data on Excel link server "Excellink" is imported into the SQL Server table named XLIMPORT1:
SELECT * INTO XLIMPORT1 from Excellink ... [Customers $]
You can also use OpenQuery to perform queries on the source data by using OpenQuery in the following manner:
Select * INTO XLIMPORT2 from OpenQuery (Excellink,
'Select * from [Customers $]')
Back to top
Using a Distributed Query If you do not want to configure a permanent connection to an Excel workbook to a link server, you can import data to import data for a specific purpose by using the OpenDataSource or OpenRowSet function. The following code examples can also import the Excel Customers worksheet data into 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 * FROMER $]')
Back to top
Using ADO and SQLOLEDB When connecting to SQL Server by using Microsoft Ole DB for SQL Server (SQLOLEDB), you can use
"Distributed Query" syntax in "Distributed Query" is used to import Excel data into SQL Server.
The following Visual Basic 6.0 code sample requires an item reference to the ActiveX data object (ADO). This code example 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 =
"Initial Catalog =
'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 the ADO and SQLOLEDB Provider to connect to the target import from Excel to SQL using the examples in the ADO and Jet Provider. You can also connect to the Excel source using Ole DB Provider for Jet 4.0.
The Jet data engine can reference external databases in SQL statements by using special syntax with 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 creates to the target SQL Server using the third format The database's ODBC connection. You can use an ODBC Data Source Name (DSN) or DSN-LESS connection string:
DSN:
[odbc; dsn =
DSN-Less:
[odbc; driver = {SQL Server}; server =
UID =
The following Visual Basic 6.0 code sample requires an item reference to ADO. This code example demonstrates how to use Jet 4.0 Provider to import Excel data into SQL Server through the 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 =
"UID =
Debug.print strsql
Cn.execute strsql, lngrecsaff, adexecutenorecords
Debug.print "Records Affected:" & lngRecsaff
Cn.close
SET CN = Nothing
You can also import Excel data into other Microsoft Access Database, Index Order Access Method (ISAM) ("Desktop") database or ODBC database by using this syntax (Jet Provider support).
Back to top
Troubleshooting
Remember the EXCEL object name of additional US dollar symbol ($) represents a worksheet (for example: sheet1 $), and the normal object name represents the range specified by Excel. In some environments, especially when replacing the SELECT query assigning Excel source data, the column in the target SQL Server table is rejected according to the alphabetical order. For additional information about this problem exists in the Jet Provider, click the article number below to see the article in the Microsoft Knowledge Base: 299484 PRB: When using Adox Retrieve the column, column is arranged in Jet Provider determines that an Excel column contains mixed text and numerical data, Jet Provider selects the "Majority" data type and returns the mismatched value in null. For additional information about how to solve this problem, click the article number below to see the article in the Microsoft Knowledge Base: 194124 PRB: EXCEL return value when using Dao OpenRecordset
Back to top
Refer to how to use Excel as additional information as a data source, click the article number below to view the article in the Microsoft Knowledge Base:
257819 How to use ADO in Visual Basic or VBA to handle Excel data about how to transfer data to additional information, click the article number below to view the article in the Microsoft Knowledge Base:
295646 How to use ADO to transfer data from ADO data source to Excel
247412 INFO: Method for transferring data from Visual Basic to Excel
246335 How to use the "Automation" function to transfer data from the ADO recordset to Excel
319951 How to transfer data to Excel via SQL Server Data Transfer Service
306125 How to import data from Microsoft SQL Server into Microsoft Excel
Back to top
The information in this article applies to:
Microsoft Excel 2000 Microsoft SQL Server 2000 (All Editions) Microsoft SQL Server 2000 64 Bit (All Editions) Microsoft SQL Server 7.0 Microsoft Excel 2002 Microsoft Excel 97 for Windows
Recent Updated: 2004-5-31 (2.1) Keywords: KbhowTomaster Kbjet KB321686 KBAUDDEVELOPER
