Import Excel file to SQL table using TSQL

There are many tools to import Excel file to SQL database, a few examples are Import/Export Wizard in SSMS, SSIS, etc. This post summarizes a way of importing Excel to Table by using TSQL statement. Exporting can be done by using the similar (but opposite direction) way.
First, to import Excel file, one can use the following ad hoc query. If the Excel file is frequently used for importing, it is recommended to add linked server. Otherwise, ad hoc query is enough.


OPENDATASOURCE allows to open data source by using OLEDB provider. So if you don't have OLEDB provider on your machine, it's not gonna work. The query below specifies Excel file (book1.xls) as a Data Source and its sheet name 'Sheet1' in this case. Think of Sheet1 as a kind of table. The '...' between OpenDataSource and Sheet name is needed since the format follows TSQL 4 part name (server.db.user.dbOjbect).

SELECT * INTO Table1
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\Temp\book1.xls;Extended Properties=EXCEL 5.0')...[Sheet1$];

If you run the query above, you might get the following error:


Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.


This is because the SQL Server option for ad hoc distributed query is disabled. This can be enabled as follows.

sp_configure 'show advanced options', 1
go
reconfigure 
go
sp_configure 'Ad Hoc Distributed Queries', 1
go
reconfigure 
go

Now if you rerun the SELECT INTO statement above, it should work. One problem you might notice is that the column type or column size is not exactly what you want. For example, for number column in Excel can be imported into 'float' column type in SQL table.

To cope with this kind of problem, you can create SQL table first on your SQL Server and then import the data from Excel. So, you create the following table.

CREATE TABLE [dbo].[ExcelTest2](
 [Id] [int] NOT NULL PRIMARY KEY,
 [Name] [nvarchar](50) NULL,
 [Tel] [nvarchar](20) NULL
) 

And then import Excel file data into the existing SQL table. This time you have to use INSERT INTO instead of SELECT INTO since table alreasy exists.

INSERT INTO Table2
SELECT * 
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=C:\Temp\book1.xls;Extended Properties=EXCEL 5.0')...[Sheet1$];

Now, let's take a example of exporting data from SQL table to Excel.
If you want to attach SQL table data to existing Excel file, you can use the following TSQL.

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\temp\book1.xls;', 
'SELECT * FROM [Sheet1$]') 
SELECT Id,Name,Tel FROM Table1

By the way, if you open the Excel file while you run TSQL statement trying to open the same Excel file, you will get the following error. Just close your Excel.exe.

Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Note:
If you want to export SQL table/view or any SQL query result to Excel file without any coding, here is a handy tool : Convert SQL Table or Query Results to Excel file