Başka Veri Kaynakları ile çalışmak

Öncelikle belirtmek isterim ki, Excel'in farklı sürümlerini kullananlarda Data menüsü farklılık göstermektedir. Şöyle ki, Excel 2016'dan itibaren Power Query teknolojisi bir add-in olmaktan çıkıp Excel'in asli bileşenlerinden biri olmuştur ve dış kaynaklarla çalışmak için bu teknolojinin kullanılması beklenmektedir.

2016'yla gelen Get & Transform düğme grubu işte bize Power Query çözümlerini vermektedir. Gerçi 2016'yı da kendi içinde yine iki ayrı gruba ayırmalıyız. Zira Office 365 çatısı altında kullananlar ile 365 olmayan sürümü kullananların Data menüsü de farklıdır. En iyisi bu farklara direkt ekran görüntülerinden bakalım.

2016 öncesinde data menüsü aşağıdaki gibiydi, sadece Get External Data grubu vardı.

365'siz Excel 2016'da ise menü şöyledir. Get & Transform grubu yeni geldi ancak mevcuttaki Get External Data hala duruyor.

365'li Excel 2016'da ise Get External Data grubu artık yok.

365li Excel'de aradığımız herşeye Get Data butonunun altındaki menülerden ulaşmamız gerekiyor. Bu yeni menünün özelliği artık herşeyi Power Query tabanlı çalıştırıyor olması. Arkada kullandığı veri sağlayıcı ise klasik Oledb değil, Oledb.Mashup'tır. Oledb hakkında detay bilgi için buraya bakınız.

Ancak "ben Power Query'yi sevmedim, ona bi türlü alışamadım ve alışmak da istemiyorum, eski yöntemleri kullanmak istiyorum" (ki bence böyle demeyin ve bir an önce Power Query'yi öğrenmeye çalışın) diyorsanız ve tabi 365 Excel kullanıyorsanız, eski dostlarınıza File>Options üzerinden aşağıdaki legacy kısmından istediklerinizi seçerek kavuşabilirsiniz. Burada MS Query'yi aramayın, o zaten Get Data altında, From Other Sources içinde duruyor.

Bu seçimi yapınca Get Data altında Legacy Wizards gelir.

Biz bu bölüme Power Query'ye değil, eski yöntemlere bakacağız. Power Query ve diğer Power BI araçlarına bu sayfadan ulaşabilrsiniz.

Ayrca dış veriye VBA(Makro) ile ulaşma yöntemlerini öğrenmek için buraya tıklayın ve Veritabanıyla olan linkleri inceleyin.

Şimdi sırayla farklı veri kaynaklarından veri nasıl çekilir bir bakalım.  Burdaki tüm kaynaklara yine Legacy Wizards altından erişeceğiz.

SQL Server

Ben evimdeki PC'den bu sayfaları hazırladığım için localhostta bulunan bir database yarattım ve oradan veri çekeceğim.

Server adı olarak siz de ilgili server adını girebilirsiniz. Credential olarak ilgili servera nasıl bağlanıyorsanız onu seçin, Windows açılış bilgileriyle girebilecğeiniz gibi ayrı belirlenmiş bir kullanıcı adı ve şifreniz de olabilir. Sonra Next deyin;

Bağlanmak istediğiniz veritabanını ve tabloyu seçin.

Next deyin;

Finish'e basın, sonuç aşağıdaki gibi gelir.

Şimdi bu tablo üzerinden herhangi bir hücre seçiliyken ya Design menüsünden veya Data menüsünden Properties'i bulup seçin, çıkan pencerede aşağıdaki kırımızlı butona basın,

Açılan pencerenin Definition sekmesine geldiğinizde bağlantı bilgileriniz(Connection String) ve bağlandığınız tablo/sorgu/sql metni neyse onu görebilir ve gerektiğinde burada istediğiniz değişkliği yapabilirsiniz.

Şuan doğrudan bir tabloya bağlandığımız için Command type=Table olarak görünmektedir ama siz bunu SQL olarak değiştirip aşağıdaki gibi bir SQL yazabilirsiniz.

Klasik(legacy) yöntemle SQL Servera bağlanırken malesef bağlantı anında SQL yazamıyoruz, mecburen bu yukardaki yöntemi kullanıyoruz. Ancak Power Query bağlantılarında ilk bağlantı sırasında da SQL metni yazabilyoruz.

Bu arada eğer çoklu tablo seçimi yaparsak aralarında ilişki kurmamıza sağlayan bir araç olan Data Model(Excel 2013) ile ister table olarak ister Pivot Table olarak bir çıktı oluşturabilriiz. Ancak bu konuya da yine Power BI araçlarını işlediğimiz yerde göreceğiz.

Özet olarak iki tablo arasında join kurma yöntemlerine bakacak olursak;

• SQL Server üzerinde(Management Studio gibi bir tool ile) joinleyip bir sorgu(view) olarak kaydetmelk
• Yukarda bahsettiğim gibi Data Model ile(Power BI araçlarında göreceğiz) joinlemek
• Command Text kısmına joini sağlayan SQL yazmak
• MS Query üzerinde birleştirmek(aşağıda göreceğiz)
• Legacy yöntem yerine Power Query bağlantısını sağlayan bir bağlantı kurmak(iset SQL yazarak ister grafiksel aryüzde bağlantı kurarak)

Access

Bu sefer kaynak olarak Accessi seçelim. Sonra hangi access dosyasını istiyorsak çıkan pencerde de onu seçelim. Karşımıza aşağıda liste çıkacaktır.

Tablo seçimimizi yaptıktan sonra, son pencere çıkar,

Biz Table olarak getirmek istiyoruz. Tablelarla neler yapılabildiğine buradan bakabilirsiniz. Eğer ki bir önceki "Select Table" kutusunda çoklu tablo seçimine izin verirsek bunlar dersek data modele yüklenir ve seçilen tablolar joinlenerek buradan pivot bir tablo üretmemiz beklenir. Yukarda belirttiğim gibi bu Data Model konusu Power toollarını gerektirdiği için şimdilik bu detaya girmiyoruz, ve sonucu Table olarak getiriyoruz.

Bu Legacy yöntemle SQL Serverda olduğu gibi sadece tablo veya sorgular import edilebilir ve join yöntemi olarak SQL serverda yazılanlar geçerlidir.

Design Menüsü

Şimdi hem SQL Server hem Accesste elde ettiğimz Table'a ait Design menüsünden Connectionlarla ilgili olarak neler yapılabildiğine bakalım.

Convert to range: Bu işlem hem veri kaynağı ile olan bağı koparar hem de Table formatını(görüntü olarak değil işleyiş olarak) bozar.

Unlink: Bu işlem ise sadece kaynakla bağı koparır, Table formatı kalır.

Properties:

İlk penceredeki birçok şey açıkça kendisini anlatıyor, o yüzden onları ayrıca burada açıklamama gerek yok sanırım.

Olası formül sorunu

Bir önemli nokta var ki, eğer tablomuz oluştuktan sonra bir kolona manuel olarak bir formül yazdıysak sonraki refreshlerde yeni gelen data için formüllerin aşağı inme sorunu yaşanabilmektedir. Bu olay Properties penceresinde Preserve column sort/filter/layout seçeneğinin işaretlenmediği durumda gerçekleşir. Çözümü (Bu çözüm bu sayfayı yazdığım 2018'de kullanılmaktaydı, belki ilerleyen yıllarda Microsoft buna bir çözüm bulmuş olabilir) ise şöyledir:

Ben tabiki formülü manuel olarak aşağı çekmekten bashetmeyeceğim, bu sadece geçici biz çözümdür zira her refreshte bu sorun devam edecektir. Gerçek çözüm şöyledir:

• Preserve column sort/filter/layout seçeneğini işaretleyin,
• İlgili kolonu silin ve kolonu tekrar oluşturup formülünüzü yazın. Formül otomatik aşağı inecek ve sonraki refreshlerde de düzgün çalışacaktır.(Tüm kolonu silmek yerine ilk satır hariç tüm içeriği silip, sonra formülü aşağıda indirerek de yapabilirsiniz)

Aşağıdaki örnekten gidecek olursak, öncelikle Propertiesten ilgili seçeneği kaldırdım.

Sonra Accese gidip tabloya bir satır ekledim. Ve Excelde refresh yaptım.

Ggördüğünüz gibi bir satırın formülü gelmedi, üstelik bu satır yeni eklediğime ait değil. Normalde 1866.satırda olmasını beklerdik ama 1867de oluştu. Bunun sebebini tam bilmiyorum ama sanırım datayı rasgele bir sırada çektiği için olsa gerek.

Sonra propertiese gidip seçeneği tekrar işaretledim, ilk hücre hariç tüm içeriği sildim. 

Son olarak Accese gidip bi satır daha ekledim, ve Excelde Refresh yaptım, bu sefer tüm hücrelerde formül geldi.

Connection Properties

Bu pencerenin diğer önemli kısımları Connection>Name yazan yerdeki düğmeye basınca çıkar.

Usage sayfasında Refresh control kısmında yazanlar önemli. Eğer belirli bir aralıkta dosyanın refresh olmasını istiyorsanız bunu Refresh every 60 minutes yazan yerde yapabilirsinz, bu da departman ortasındaki bir televizyona bağlanmış Güncel Rakamsal Dashboard fikri için güzel bir imkan sağlamış olur. Keza dosya açılır açılmaz refresh olmasını istiyorsanız da bir alttaki seçenek işaretlenir.

Definition kısmında ise daha önce bahsettiğim gibi Connection string ve Tablo/View(Query)/SQL metinleri bulunur. Bunlar manuel olarak veya makro ile değiştirilebilirler.

Edit Query ve Parameters bu bağlantı yöntemlerinde pasif gelir. Aktif geldiği kısımlar ve kullanımları için MS Query kısmına bakın. 

Text/csv

Bir text dosyasını import etmek için Legacy'den Text'i seçelim. Önümüzde iki ana seçenek vardır. Eğer ilgli dosyada kolonlar belirli karaketerlerle(virgül, boşluk v.s) ayrılmışsa Delimited seçeneğini seçip ilerleriz, ki benim şuana kadar karşıma çıkan dosyaların neredeyse hep bu formattaydı. Diğer format ise kolonların sabit uzunlukta birbirinden ayrıldığı Fixed formattır.

Start import at row:Genelde 1 bırakılır

File origin:Eğer türkçe karakterler de varsa 1254-turkish windows seçilir.

Delimited formatı

2.adımda uygun delimiter seçilir, eğer seçeneklerden biri burada yoksa Other içine uygun delimeter yazılır.

Text qualifier: Import edilecek dosyadaki metinleri çevreleyen bir karakter varsa bu seçilir, yoksa none bırakıldır. Önrneğin metin şu formattaysa "başkent", "şube1", "2016", "532" text qualifer olarak çift tırnak(") seçilir.

Son aşamada ise almak istemediğiniz bir kolon varsa bunu işaretleyebilir, ayrıca kolonların veritipini de belirleyebilirsiniz.

Bir nedenden Excel, çıktıyı istediğiniz formata çevirmezse bile import işleminden sonra da istediğiniz formata çevirebilirsiniz.

Mesela text tipinde gelenleri sayıya çevirmek için Number tipini uygulayabilirsiniz ancak bu data tipini Number yapmakla birlikte sağa dayalı göstermez; bunun için Text to Collumns aracını kullanmanız gerekir.

Advanced seçeneği içinde de binlik ve ondalık ayraç seçimi ve negatif sayılarla ilgili bir seçim yapılır.

Fixed format

Fixed formatta seçim, aşağıdaki gibi her kolonun bitimine uygun çizgiler koyarak yapılır.

Diğer herşey Delimited format ile aynıdır.

XML

Xml dosya formatı platformlar arasında taşınabilen bir dosya formatıdır. Bir çok dosya okuyucu XML'i okuyabilir. Excel de bunlardan biridir. Okuyacağımız bu dosya yerel bir dosya olabileceği gibi internet üzerindeki bir xml dosya da olabilir.

Bununu için yine Legacy'den XML seçeriz. Kaynak olarak aşağıdaki sitenin site haritasını girdim.

Bazı dosyalarda aşağıdaki gibi bir uyarı verir, OK diyip geçelim.

Import işlemini nereye yapacağınızı da seçtikten sonra işlem tamamdır.

Siz de benim sitem için deneyebilirsiniz: http://www.excelinefendisi.com/Sitemap.xml

XML importu için daha detay bilgi için bu ve şu sayfalara bakabilirsiniz.

Mevcuttaki bir excel tablosunu Xml olarak export etmek için ise buraya bakınız.

Bu örnekte kendi web sitemin sitemap.aspx sayfasından veri çekeceğim. Legacyden Web'i seçince aşağıdaki pencere açılır. Adres çubuğuna sayfa adresini yazıp Go tuşuna basınca aşağıya sayfanın içeriği geldi. Bazı komut dizesi hataları çıktı, bunlara ok diyip geçtim. Sonra kırmızı işaretli yatay oka tıklayınca mavi çerçeve berlidi ve Excel bize o kısmı import edeceğini söylemiş oldu.

Sonra import dedim ve sayfadaki importlanabilir veri Excele gelmiş oldu. Gelmiş oldu ama istemediğim birçok veri de gelmiş oldu, ilk 55 satır benim için çöp, bunları sildim ve istediğim data bana kalmış oldu.

Bu veri sağ tıklanarak refresh edilebilir durumdadır.

Bununla beraber webden veri çekme, bu haliyle çok kullanışlı değildir. Zira her sayfadaki veri bu yöntemle çekilmeye uygun olmayacaktır. Mesela siz de https://kur.doviz.com/ sitesinden veri çekmeye çalışın, çok nitelikli bir veri olmayacaktır.

Web sitelerinden daha uygun bir veri çekmek için gelişmiş progralama dillerini kullanabilir ve sadece birkaç satırlık kod ile şık formatlı veriler çekebilirsiniz.  Ancak koda Excel içnde ihtiyacınız varsa ve düzenli olarak refreshlenebilir olmasını istiyorsanız VBA de kullanabilirsiniz, ancak standart VBA kodunun ötesinde HTML ve Internet Explorer kütüphanelerini kullanabiliyor olmalısınız, ve ayrıca biraz HTML ve Javascript bilgisi fena olmayacaktır.

Şu sayfada konuyla ilgili bilgileri bulabilirsiniz.

Evet, geldik eski zamanların en güçlü aracına, Power Query'nin öncülü MS Query'ye. Bu araç ile birçok veri kaynağından ODBC bağlantısı kurarak veri çekebiliyoruz.

Bu araç Legacy içinde bulumuyor, Other sources içinde bulunuyor(farklı Excel versiyonlarında yeri değişebilir, arayıp bulacağınızdan eminim)

Biz veri kaynağı olarak yine Access seçelim ve sonrasında çıkan pencereden ilgili Access dosyamızı seçelim.

Bir Query Wizard çıkar ve hangi tablolardan hangi kolonları seçmemiz gerektiğini bize sorar, ihtiyacımız olanları seçelim.

Arkasından gelen kutudaki Filter ve Sortu şimdilik olduğu gibi geçebiliriz, sonra son kutumuz çıkar.

Return dersek direkt Excele atar, biz View diyelim ve Editörü açalım.

Burada başkta tablolarla görsel join yapabilir, kriter koyabilir  ve hatta mevcut oluşan SQL'i manuel bir SQL ile değiştirebiliriz.

Şimdi diyebilirsiniz ki, ben bu joini Access içinde kurup da yapabilirim, sorguyu da Access içinde kaydeder ve direkt o sorguyu import ederim. Haklsınız ancak bazı durumlarda ilgili databasede sorgu oluşturma ve kaydetme hakkınız olmayabilir. İşte bu durumlar için MS Query oldukça faydalıdır. Tabi PowerQuery'nin yanında MS Query'nin esamesi okunmaz ama yine de öğrenelim, zira bir Oracle bağlantısı için 365li Excelin Home versiyonunuda Power Query ile Oracle bağlantısını direkt yapamıyorsunuz, ya professional versiyonunuz olmalı ya da 365siz Exceliniz. Ama MS Query hep orada, o yüzden öğrenmekte fayda var.

Oluşan SQL'i SQL butonundan görebilirsiniz. İsterseniz hazır SQL'i buraya direkt yapıştırabilirsiniz veya bunu Excele attıktan sonra Properties'ten de yapabilirsiniz.

Son olarak nihai datayı Excele çıkmak için File>Return Data do Excel deriz.

Oracle örneği üzerine not:Çalıştğım bilgisayarda 64 bit Windows kullanıyorum, Office versiyonu ise 32 bit. Bilgisayara ise Windows uyumu nedeniyle Oracle'ın 64 bitini kurdum. Office ile Oracle'ın bit uyuşmazlığı nedeniyle oradan bir örnek yapamadım malesef ama Accesste nasıl yapıyorsanız aynı mantık Oracle veya başka bir veri kayanğı için de geçerli. Zaten bir kez bi Oracle veritabanına bağlantı kurduktan sonra artık yeni bağlantılar için MS Queryde çalışmak yerine direkt Properties'ten SQL metnini değiştirmeniz yeterli.

Query Editörü ve Parametreler

Çalıştırdığımız sorguyu değiştirmek için doğrudan SQL metnini değiştirebileceğimiz gibi, MS Query editörüne geçip orada da işlem yapabiliriz.

Properties>Connection Properties'ten Edit Query dediğimizde editör ekranımız açılır. Burada mesela aşağıdaki gibi bir filtre koyabiliriz. Filtre koymak için editörün View menüsünden Criteria seçeneğini işaretleyin, aşağıdaki gibi kriter alanı açılacaktır. Oraya [ ] içinde istediğiniz bilgiyi girip Entera basınca sizdenilgili kriteri girmenizi isteyen bir kutu çıkacaktır.

Böylece her refresh sırasında ürün bilgisini soran bu kutu çıkacaktır.

Dinamik bir sorgu için bu bir yöntemdir ancak daha şık bir yöntem, olası seçenekleri bir hücreye Validation List olarak girip, oradan seçmektir.

Bunun için Connection Properties'te Parameters'a tıklarız. Tabi bunu yapabilmek için hali hazırda bir kriter uygulanmış olması lazım, yoksa bu düğme pasif gelecektir. Ancak biz kriter uyguladığımız için aşağda gördüğüüz üzere bu düğme aktifir.

Bu düğmeye tıklayalım. Aşağıdaki pencere gelecektir. İlk başta en üstteki seçenek seçilidir. İkinci seçenekte sabit bir değer girilir ki bence bu çok anlamsız bir seçenek, zira bunu gerek editör ekranını kullanarak veya doğrudan SQL içinde kendimiz de girebiliriz. Üçüncü seçenek ise bizim aradığımız seçenektir. Bununla Excele "Kriteri şu hüreden al" demiş oluyoruz, aynı zamanda altındaki seçeneği de işaretleriz ki her değişiklikten sonra bir de manuel refresh yapmak zorunda kalmayalım, seçim yapılınca refresh de otomatik olsun.

G1 hücresinden Ürün3'ü seçince sonuç da böyle olur.

Dataya ulaşmak için yine Legacy'de bulunan Data Connection Wizard'ı da kullanabilir.z Bu sihirbaz bize ODBC veya OLEDB başta olmak üzere çeşitli bağlantılar kurmamızı sağlayabilir.

Biz bunlardan ODBC ve en alttaki Other/Advanced'ı kullanıcaz.

ODBC

ODBC örneğinde de yine Access'e bağlanalım.

Aynı dosyayı seçelim.

Sonra tablo seçimini yapalım.

İşlem tamamdır. Şimdi Properties'ten connection stringe bakalım. Gördüğünüz gibi bunda connection string ODBCdir(ODBC ifadesini doğrudan görmüyoruz ama DSN yazmasından bunun ODBC olduğunu anlıyoruz) ve sadece ODBC bağlantılarda kullanılanbilen Edit Query butonu aktif durumdadır. Buna basınca MS Query editörü açılacaktır.

Other/Advanced seçimi

Bu sefer karşımıza Oledb sağlayıcılar çıkar.

Yine aynı Access dosyasına bağlanalım ve bağlantımızı test edelim.

İlgili tabloları seçelim

Ve oluşan Connection stringe bir bakalım. Gördüğünüz gibi bunda Connection String OLEDB'dir ve sadece ODBC bağlantılarda kullanılanbilen Edit Query butonu pasif durumdadır.