Cuma , 23 Haziran 2017

SQL Server Excel Linked Server Tanımlama

Veri ile uğraşan insanların Excel kullanmaması kaçınılmazdır. Excel’den veri okumak veya Excel’e veri yazmak gibi işlemler büyük boyutlarda yapıldığında Integration servis paketleri kullanılsa da bazen işin içine hiç Integration servisi karıştırmadan Excel’deki verilerimizi SQL Serverdan sorgulama ve gerektiğinde bu veriler üzerinden işlem yapma ihtiyacı duyabiliriz.

Sadece Excel değil farklı veri kaynaklarına SQL Server üzerinde erişip verileri sorgulamak için en bilindik yöntem olan Linked Server ,Openrowset veya linked server tabanlı OpenQuery gibi yapıları kullanabiliriz. Örneklere başlamadan önce SQL server kurulu olan bilgisayarın C diskinde test.xlsx adlı aşağıdaki gibi bir tane Excel dosyası oluşturalım.

Linked1

Oluşturduğumuz bu Excel dosyasını kaydettikten sonra SQL Serverdan bağlanıp bu verileri sorgulamak için bir tane Linked Server oluşturalım.

exec sp_addLinkedServer@server=’ExcelLink’,

@srvproduct=’ACE 12.0′,

@provider=’Microsoft.ACE.OLEDB.12.0′,

@datasrc=’C:\Test.xlsx’,

@provstr=’Excel 12.0;HDR=Yes’;

 

Linked Server eklemek için ilk yöntem olarak sp_addLinkedServer adlı sistem stored procedurumuzu kullanabiliriz. Alternatif bir yol olarak aşağıdaki gibi SQL Server Management stdio kullanarak da linked server ekleyebiliriz.

Linked2

sp_addLinkedServer sistem stored procedurumuze oluşturacağımız serverın adını, Ürünün tanımını ve bağlanmak istediğimiz excel dosyasının yolunu belirtiyoruz. Burada dikkat edeceğimiz en önemli nokta @provider parametresi ile belirtilen  değerin Microsoft.ACE.OLEDB.12.0 olmasıdır. Bu Provider Excelin 2007 sürümü ile beraber gelmektedir. Bu provider Excel 2007 veya üstü bir verisyonun yüklenmesi ile bilgisayarımıza yüklenmektedir. Excelin 2007 öncesi sürümleri içinse provider olarak Microsoft Jet 4.0 OLE DB Provider kullanılır.

@datasrc parametresi ile de bağlanmak istediğimiz Excel dosyasının yolunu belirttikten sonra bir diğer parametre olan @provstr parametresinde ürün adı olarak Excel 12.0 seçilmelidir. Ayrıca HDR seçeneği ile Exceldeki verilerimizde bir başlık sütunu olup olmadığını belirtiriz. Bizim dosyamızda ilk satırımız kolon başlıklarını içerdiği için HDR=Yes şeklinde belirttik. Eğer kolon isimlerimiz yazılmamış olsaydı HDR=NO şeklinde belirtmemiz gerekirdi. Bu durumda SQL Server bizim için otomatik olarak her kolona F1,F2,F3 gibi sembolik isimler verecektir.

Bu kadar ayrıntıdan sonra Linked Serverımızı ekleyelim. Ve ekledikten sonra aşağıdaki gibi sorgulayalım.

select * from ExcelLink…[Sayfa1$]

Yukarıdaki sorgumuzda ExcelLink eklediğimiz Linked Serverını adını temsil ederken, Sayfa1 ise yukarıda oluşturduğumuz Excel dosyamızın içindeki tablonun adıdır. Eğer Exceldeki sayfa adınız farklı ise sorgumuzuda o şekilde değiştirmeliyiz. Örneğin eğer Excelin ingilizce sürümü yüklü ise default gelen sayfa adı Sheet olacağı için sorgumuzdaki tablo adını değiştirmeliyiz.

Şimdi sorgumuzu çalıştırıp sonucu görelim.

Linked3

 

Sorgu çıktımızda gördüğümüz gibi Exceldeki bir sayfayı sanki SQL Serverda bir tabloymuş gibi sorgulayabiliyoruz. Alternatif bir yıol olarak Linked Server ekledikten sonra sorgulama için Openquery komutunu kullanabiliriz. Openquery komutu sırasıyal Linked Server adını ve çalıştırılacak olan sorguyu parametre olarak alıp sonucu döndürecektir. Örneğin Openquery ile sadece A1,A2 ve A3 hücrelerindeki verileri sorgulayalım.

select * from openquery(ExcelLink, ‘SELECT * FROM [Sayfa1$A1:A3]‘)

Linked4

 

Çıktımızda sadece A1,A2 ve A3 hücrelerindeki verilerin listelendiğini görmüş olduk. Herhangi bir Excel dosyasını linked server olarak ekledikten sonra içindeki sayfaları birer tablo gibi sorgulayabiliyoruz. Fakat Linked Server kavramı bizim burada anlattığımız gibi sadece Excelle sınırlı bir konu değil. En basit ifadeyle bir text dosyasını linked server olarak ekleyebileceğimiz gibi başka bir SQL Serverı, Oracle veya DB2 gibi bir ürünü hatta kullanıyorsa Analiz Servise bile Linked server tanımlayarak erişebilir istediğimiz gibi sorgulayabiliriz.

Hakkında ismailadar

Cevapla

E-posta adresiniz yayınlanmayacak. Required fields are marked *

*


*

Şu HTML etiketlerini ve özelliklerini kullanabilirsiniz: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>