SQL Server Helper - Tips and Tricks - Query and Import an Excel File

Home

Articles

SQL Server 2012

SQL Server 2014

SQL Server 2016

FAQ

Practice Test

Tip of the Day : SQL Server Database Design - Twitter Tweets

Home > Tips and Tricks > Query and Import an Excel File

Part 3: Importing an Excel Worksheet

Based on the first two parts of this article, Querying an Excel Worksheet and Querying an Excel Worksheet Without Header and With Mixed Column Data Type, you can easily read an Excel file using the OPENROWSET function.

The OPENROWSET function includes all connection information necessary to access remote data from an OLE DB data source, and in this case from an Excel file data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad-hoc method of connecting, accessing and updating remove data using OLE DB. The OPENROWSET function can be referenced in the FROM clause of a query as though it is a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE or DELETE statement, as will be shown later, subject to the capabilities of the OLE DB provider.

To reiterate how it is done, given the following sample Excel worksheet:

  |     A     |     B    |               C             |         D        |   E   |     F      |
------------------------------------------------------------------------------------------------
1 | FirstName | LastName | Address                     | City             | State | ZIP        |
2 | Mickey    | Mouse    | Walt Disney World           | Lake Buena Vista | FL    | 32830      |
3 | Donald    | Duck     | Walt Disney World           | Lake Buena Vista | FL    | 32830      |
4 | George    | Bush     | 1600 Pennsylvania Avenue NW | Washington       | DC    | 20500-0003 |
5 | George    | Clooney  | 151 El Camino Drive         | Beverly Hills    | CA    | 90212-2704 |
6 | Tom       | Cruise   | 9830 Wilshire Boulevard     | Beverly Hills    | CA    | 90212-1804 |

This Excel worksheet can easily be read using the following SELECT statement together with the OPENROWSET function:

SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\Source\Addresses.xls;IMEX=1',
                'SELECT * FROM [Sheet1$]')

The output of this SELECT statement is as follows:

FirstName  LastName   Address                        City                 State  ZIP        
---------- ---------- ------------------------------ -------------------- ------ ---------- 
Mickey     Mouse      Walt Disney World              Lake Buena Vista     FL     32830
Donald     Duck       Walt Disney World              Lake Buena Vista     FL     32830
George     Bush       1600 Pennsylvania Avenue NW    Washington           DC     20500-0003
George     Clooney    151 El Camino Drive            Beverly Hills        CA     90212-2704
Tom        Cruise     9830 Wilshire Boulevard        Beverly Hills        CA     90212-1804

To import this Excel worksheet to a SQL Server table without using DTS, since you are able to read the data using the SELECT statement, you can simply add the INTO clause to insert the records into a new SQL Server table.

SELECT * 
INTO [dbo].[Addresses_Temp]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\Source\Addresses.xls;IMEX=1',
                'SELECT * FROM [Sheet1$]')

This will create a SQL table called [dbo].[Addresses_Temp] that will contain the records from the Excel worksheet. The column names of the table will be the first record of the worksheet.

Importing an Excel Worksheet With Correct Data Types

By default, the data type of string/text values from an Excel worksheet is translated to NVARCHAR(255). This may not be the data type you want for your columns and this may not be length you want. There are two ways to override the data type generated by the OLE DB provider when it creates the table.

The first way is to specify the data types you want for each column in the SELECT statement using the CAST function:

SELECT CAST([FirstName] AS VARCHAR(20)) AS [FirstName],
       CAST([LastName] AS VARCHAR(20)) AS [LastName],
       CAST([Address] AS VARCHAR(50)) AS [Address],
       CAST([City] AS VARCHAR(30)) AS [City],
       CAST([State] AS VARCHAR(2)) AS [State],
       CAST([ZIP] AS VARCHAR(10)) AS [ZIP]
INTO [dbo].[Addresses_Temp]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\Source\Addresses.xls;IMEX=1',
                'SELECT * FROM [Sheet1$]')

The data types of each column will now be based on the data type specified in the CAST function and not based on the default NVARCHAR(255).

The second way is to first create the destination table, then use the INSERT statement together with the SELECT statement to import the data from the Excel worksheet:

CREATE TABLE [dbo].[Addresses_Temp] (
    [FirstName]   VARCHAR(20),
    [LastName]    VARCHAR(20),
    [Address]     VARCHAR(50),
    [City]        VARCHAR(30),
    [State]       VARCHAR(2),
    [ZIP]         VARCHAR(10)
)
GO

INSERT INTO [dbo].[Address_Temp] ( [FirstName], [LastName], [Address], [City], [State], [ZIP] )
SELECT [FirstName], [LastName], [Address], [City], [State], [ZIP]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\Source\Addresses.xls;IMEX=1',
                'SELECT * FROM [Sheet1$]')

Part 1: Querying an Excel Worksheet
Part 2: Querying an Excel Worksheet Without Header and With Mixed Column Data Type