Select data dari Excel di SQL Server 2005

Ketika kita sudah terbiasa dengan syntax query SQL, pengolahan data rasanya lebih mudah jika dilakukan dengan SQL query. Modifikasi dapat dilakukan dengan SQL query sehingga data yang tampil sesuai dengan yang kita inginkan.

Pernah suatu ketika saya memperoleh data yang banyak dalam dua file Excel. Saya harus membandingkan data-datanya. Ya....,  saya mencari cara untuk memindahkan data Excel tersebut kedalam SQL. Berikut caranya :

SELECT *
FROM   OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]



SELECT *
FROM   OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                  'Excel 8.0;Database=C:\test\xltest.xls',
                  [Customers$])



SELECT *
FROM   OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                  'Excel 8.0;Database=C:\test\xltest.xls',
                  'SELECT * FROM   [Customers$]') 

Ketiga syntax diatas dapat di run. Customers$ itu adalah nama sheet dalam file Excelnya. Nama sheet tersebut diakhiri dengan tanda $. Data pada row pertama di Excel diambil sebagai Nama Kolom pada Sql Server.  Namun biasanya muncul error seperti berikut ketika menjalankan syntax diatas:

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.

Untuk mengatasinya kita dapat mengaktifkan As Hoc Distributed Queries dengan sp_configure :

SELECT *
FROM   sys.configurations
ORDER  BY name;
GO

Sp_configure 'show advanced options', 1;
GO
 

RECONFIGURE;
GO

Sp_configure 'Ad Hoc Distributed Queries', 1;
GO

RECONFIGURE;
GO 

Untuk memasukan data Excel tersebut kedalam table sementara di SQL server kita dapat menggunakan :

SELECT *
INTO   #temp
FROM   OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                  'Excel 8.0;Database=C:\Test\data.xls',
                  'SELECT * FROM   [Sheet1$]') 

Semoga cara ini bisa membantu kawan-kawan untuk mengambil data dari excel.