Advanced Search

Return to Snippet

Revision: 29775
at August 4, 2010 16:35 by hairajeshk
Initial Code 
There are many ways to import data from Excel into SQL Server. Some of them are as below:
•         Using SQL Server Integration Services. (This technique allows very detailed control over the data being imported but is complex & time-consuming to set up.) 
•         SQL Server linked servers (This technique requires that you create a permanent linked server pointing to the Excel source file.) 
•         SQL Server ad-hoc distributed queries (This is the quickest way to import data. It creates a temporary linked server to the Excel sheet on the fly & pulls the data.)

how to import data into SQL Server using the ad hoc distributed queries.
First of all, you need to make sure that "Allow Ad Hoc Distributed Queries" option in SQL Server Surface Area Configuration window is set enabled. This can be done as below:

sp_configure 'show advanced options', 1
reconfigure
GO
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
GO 
  
You can now use the below query to import data from Excel 2007/2003 (.xlsx/xls) sheet into SQL Server.
 
create table TestTable (id int, name varchar(100))
GO
INSERT INTO TestTable
SELECT *  FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:\test\Data.xlsx;Extended Properties=Excel 8.0')...[Sheet1$]

Here, we create a SQL Server table called TestTable and then pull the data from Sheet1 in the source file C:\test\Data.xlsx into the table. Note that we need to append the Excel sheetname with a $ symbol when importing the data into SQL Server. Also, make sure the data types are mapped correctly. (i.e. the query doesn’t try to insert a character value into an integer column in SQL Server and so on..)
 
In case you don’t have the 'Microsoft.ACE.OLEDB.12.0' provider installed on the system running SQL Server, the above query will error out. In that case, you can use the Microsoft.Jet.OLEDB.4.0 (if  installed) to import data only from Excel 2003 sheet. Also note that there is no Microsoft.Jet.OLEDB.4.0 provider for 64-bit systems. So, the below query will error out on 64-bit systems. The query is:
 
create table TestTable (id int, name varchar(100))
GO
INSERT INTO TestTable
SELECT *  FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\test\Data.xlsx;Extended Properties=Excel 5.0')...[Sheet1$]
Initial URL 

                                

                                                            

                                    

                                        

                                        Initial Description
                                        

                                    

                                    
Import data into SQL Server using the ad hoc distributed queries from Excel

                                

                                                            

                                    

                                        

                                        Initial Title
                                        

                                    

                                    
Import data into SQL Server using the ad hoc distributed queries from Excel

                                

                                                            

                                    

                                        

                                        Initial Tags
                                        

                                    

                                    

                                

                                                            

                                    

                                        

                                        Initial Language
                                        

                                    

                                    
SQL