Querying Excel pada linked Database Server

 


Berikut adalah cara menampilkan data excel dengan Query di SQL Server :

1. Untuk menampilkan data dari Sheet1 dalam file Excel_Data.xlsx gunakan perintah :

SELECT * FROM EXCELDATA...[Sheet1$]

ketika menjalankan perintah diatas maka akan ada  pesan error :


Ini biasanya terjadi karena izin yang tidak diberikan.


2. Cara untuk mengatasi ini adalah, buka SQL Server Configuration Manager: : 



3. Pilih SQL Server yang dibuat linked server, lalu  klik kanan  kemudian klik Properties::



4.  Pilih  Built an account lalu pilih Local System, lalu klik Apply



5. Klik Yes 



6.  buka SQL Server Manage Studio sebagai administrator lalu jalankan perintah dibawah pada editor queri :

SELECT * FROM EXCELDATA...Sheet1$



7.  Untuk insert data dari Sheet1 gunakan perintah :

INSERT INTO dbo.SQLTable(ID,Name)
SELECT ID, Name FROM EXCELDATA...Sheet1$

Lalu jalankan select untuk melihat hasil :



Membuat Queri data Excel menggunakan Queri terdistribusi

Untuk mengakses data ke file Excel melalui SSMS tanpa membuat server link, gunakan fungsi Transact-SQL OPENROWSET dan OPENDATASOURCE.

1. Menggunakan OPENROWSET
 
1.1. Untuk membuat koneksi dan queri data dari data Excel menggunakan fungsi OPENROWSET ketik kode SQL berikut pada editor queri:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Test\Excel_Data.xlsx;', 'SELECT * FROM [Sheet1$]')

Perintah di atas, maka menampilkan error  :



1.2. Untuk mengatasi hal tersebut, gunakan / jalankan RECONFIGURE WITH OVERRIDE fungsi dari RECONFIGURE:

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

1. 3. Setelah itu jalankan perintah dibawah kembali :

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Test\Excel_Data.xlsx;', 'SELECT * FROM [Sheet1$]')



2. Menggunakan OPENDATASOURCE

SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
  'Data Source=C:\Test\Excel_Data.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]




Baca juga artikel yang berkaitan :