Ashish Patel

Import/Export Excel (.Xlsx) or (.Xls) File into SQL Server

To, Import/Export data To/From Excel (.xls) need Microsoft.Jet.OLEDB.4.0 and for Excel 2007 (.xlsx) need 2007 Office System Driver: Data Connectivity Components. You can download from here.

Export Data to Excel file
Create an Excel file named testing having the headers same as that of SQLServerTable columns
Here is Query:
Excel 2003 (.Xls) file:

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;', 'SELECT * FROM [Sheet1$]') select * from SQLServerTable

Excel 2007 (.Xlsx) file:

insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\testing.xlsx;', 'SELECT * FROM [Sheet1$]') select * from SQLServerTable

Import data from Excel to new SQL Server table
Excel 2003 (.Xls) file:

select * into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')

Excel 2007 (.Xlsx) file:

Select * into SQLServerTable FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\testing.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')

Import data from Excel to existing SQL Server table
Excel 2003 (.Xls) file:

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')

Excel 2007 (.Xlsx) file:

INSERT INTO SQLServerTable select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\testing.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')

Import data from Excel to new SQL Server table with dynamic table name (ex. SQLTABLE_200110413) 

DECLARE @table varchar(500)
DECLARE @Q varchar(2000 )
SET @table='SQLTABLE_' +(CONVERT(VARCHAR(8),GETDATE(),112))
SET @Q= 'select * into '+  @table + ' FROM OPENROWSET("Microsoft.ACE.OLEDB.12.0", "Excel 12.0;Database=D:\testing.xlsx;HDR=YES", "SELECT * FROM [Sheet1$]")'
Exec(@Q)

May be you find error like below while Import/Export data To/From Excel
Error 1:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Here is solution might work for you..

USE [master] 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 
GO

Error 2:

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.

here is solution might work for you

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE

If above Query showing following error…

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Msg 5808, Level 16, State 1, Line 2
Ad hoc update to system catalogs is not supported.

then user RECONFIGURE WITH OVERRIDE instead of RECONFIGURE..

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE WITH OVERRIDE

If above solution is not working, ere is solution link might work for you..
http://www.kodyaz.com/articles/enable-Ad-Hoc-Distributed-Queries.aspx

Thanks.

Posted in SQL ServerTagged , , , , , , 21 Comments

21 responses to “Import/Export Excel (.Xlsx) or (.Xls) File into SQL Server”

  9. GOOD summary!
    Do you have any tips for OVERWRITING the Excel destinations? I’d like to update an existing worksheet inside a workbook, either by using sheet name like [Sheet1$] or range name?

    When I use the INSERT syntax, it appends data to an existing tab, if it exists.

    I don’t know any ways to delete/drop the Excel worksheet/tab first, so that seems like a dead-end…

    Your thoughts?

    Reply

  12. I have been exploring for a bit for any high-quality articles or
    weblog posts in this sort of space . Exploring in Yahoo I ultimately
    stumbled upon this web site. Studying this info So i am glad to exhibit that I’ve a very good uncanny feeling I came upon exactly what I needed.

    I most certainly will make certain to don?t
    omit this site and provides it a glance regularly.

    Reply

  15. i am trying export to excel using provider 12.0 but getting error :

    OLE DB error trace [Non-interface error: Column 'NIP' (ordinal 1) of object 'SELECT [NIP] FROM [ABSENSI$]' reported an unsupported value for DBTYPE of 0].
    Msg 7354, Level 16, State 1, Line 1
    OLE DB provider 'Microsoft.ACE.OLEDB.12.0' supplied invalid metadata for column 'NIP'. The data type is not supported.

    do you have a solution for this error ?
    thanks before

    Reply

  16. I keep getting this error
    Cannot process the object “SELECT * FROM [Plan1$]”. The OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” indicates that either the object has no columns or the current user does not have permissions on that object.

    Reply

  17. this is all that i looking for, but i have a question. I try to import almost 170000 register, what is the best method to do it? i try dts, simple sql insert into or thist method is better? what do you think?. Thankalot

    Reply

  18. I like the helpful info you supply on your articles.
    I will bookmark your weblog and take a look at again right here
    frequently. I’m relatively sure I will be informed plenty of new stuff proper right here!
    Best of luck for the next!

    Reply

Leave a Reply