Friday, October 30, 2015

How to Import Excel data by using T-SQL(OPENROWSET and OPENDATASOURCE)

Importing data from ms-office excel is very common task in it industries. Here I will elaborate methods to import data from excel to the sql server database by using openrowset and opendatasource.Tool needed before you need to start is  Microsoft Access Connectivity Engine (ACE) 32Bit (or) 62Bit Based on your machine configuration. download it from below link

  1. Download and install above tool for reading excel file from sql server. And check whether installation was success or not, to check go to sql server management studio>expand server objects expand linked servers and go to providers. you will find "Microsoft.ace.oledb.12.0" if the installation was successfull.
  2. Double click on the provider and check the box allow in process and dynamic parameter 
  3. Run below Script to skip step 2 process. open your sql server in administrator mode
    Execute master.dbo.sp_msset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'dynamicparameters' , 1;
    GoExecute master.dbo.sp_msset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'allowinprocess' , 1;
     4.  Now pre-process is completed.
     5.  Go to sql server management studio
     6. Run Below Script enable remote queries
EXECUTE master.dbo.sp_configure 'show advanced options', 1;
reconfigure ;
EXECUTE master.dbo.sp_configure 'ad hoc distributed queries', 1 ;
    7.  Run this query and change excel path  with your excel
         ('Microsoft.ACE.OLEDB.12.0','Data Source D:\sample.xls;
         Extended Properties = Excel 12.0')...Sheet1$

          ('Microsoft.ACE.OLEDB.12.0','Data Source = <source file path(excel)>
         ;Extended Properties = Excel 12.0')...Sheet1$

    8. Now create one table to insert excel data into it. that's it done

create table sample  (name  varchar(50), id int)
insert into sample 
SELECT*FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source                    D:\sample.xls;Extended Properties = Excel 12.0')...Sheet1$