Makrolar İle Text(.txt) Dosya Yönetimi

Makrolar İle Text(.txt) Dosya Yönetimi Dersi İle Ne Öğreneceğiz

    Makrolar İle Text(.txt) Dosya Yönetimi derstinde, dersin isminden de anlaşılacağı üzere, .txt uzantılı Text Files ve .csv uzantılı Comma Seperated Value  tipindeki dosyaların makrolar ile oluşturuluması ve hazır dosyalarda yer alan verilerin de excel’e aktarılmasını öğreneceğiz. 

    Text.txt ve .csv dosyalarını oluşturma işlemlerinde https://macronimbus.com/dosya-ve-klasor-yonetimi/ dersinde kapsamlı olaran gördüğümüz Scripting kütüphanesi ve Scripting.FileSystemObject nesnesini kullanacağız. Aşağıdaki tuş ile ilgili sayfaya geçiş yapabilirsiniz. 

    Örneklere geçmeden önce yine de kısaca Scripting Lübrary’i hatılayalım. Makrolar ile dosyalar ve klasörler üzerinde yapacağımız kesme, kopyalama, taşıma, içinde değişiklik yapabilme, klasörlerin içlerini belirli dosya formatlarına göre tarama ve aklınıza gelebilecek bütün işlemler için Scripting kütüphanesi kullanıcıya yani bize çok zengin metotlar, özellikler ve de sınıflar listesi sunmaktadır. Scripting kütüphanesi veya içinde yer alan bir sınıfa ait bir referans ile bu kütüphaneye bağlandığımızda bütün bu bahsettiğimiz metot, özellik ve sınıfları kullanabilir pozisyona geliriz. 

     Bu ders içerisinde Scripting Kütüphanesine ait FileSystemObject ve TextStream isimli iki adet sınıfı ağırlıklı olarak kullanacağız. Makrolar İle Text(.txt) Dosya Yönetimi Dersi’ni artık pratiğe dönük olarak örneklerle işlemeye geçiyoruz. Bu ders kapsamda daha önceki derslerde de kullanmış olduğumuz ‘Marka ve Modeline Göre Otomobil Fiyatları’ tablosunu tekrar kullanacağız. Şimdi bu tabloyu tekrar hatırlayalım. 

Makrolar ile Text(.txt) Dosya Yönetimi

    Bildiğiniz gibi excelde yer alan tablolar birden çok hücreden meyadan gelir. Bizim örneğimizde ise tam olarak 108 adet hücreden oluşmaktadır. Farklı bir şekilde anlatmak gerekirse tablomuz 108 ayrı hücre içine yazılmış olan verilerin ve hücre özelliklerinin birleşiminden meydana gelmektedir. Excel tablolarının bu yapısı, herhangi bir tabloyu .txt veya .csv dosyasına aktarmak istediğimizde, tabloyu oluşturan hücrelerdeki değerlerin teker teker okunarak, .txt ve .csv dosyasına yazdırılabilmesine neden olmaktadır. Şimdi örneklere geçerek, basitten karmaşığa doğru, bu teoriyi pratiğe dökelim.

1- Text.txt ve .CSV Dosyalarının Oluşturulması ve TextStream Sınıfına Ait .Write, .WriteLine, .WriteBlankLine Metotları

    İlk olarak bir adet text.txt dosyası oluşturalım. 

Örnek 1 - Text.txt Dosyası Oluşturma

    Örneğimizi daha anlaşılır olabilmesi amacıyla satır satır detaylı olarak değerlendiceğiz. 

    Bu Sub-Routine’e de, Dosya ve Klasör Yönetimi Dersinlerinde yazdığımız Sub-Routine’lerde olduğu gibi, kütüphane ve sınıflara ait referansları taşıyabilecek değişkenleri tanımlamayla başlıyoruz. 

Makrolar İle Text(.txt) Dosya Yönetimi - CreateTextFile Method1 )  ‘fso’ adında Scripting.FileSystemObject sınıfına referans taşıyabilecek bir değişken tanımlıyoruz.

2 ) fso değişkenine, “Yeni Scripting.FileSystemObject” nesnesi değerini atıyoruz. Artık bu değişkeni kullanarak, sınıfa ait bütün metot ve properties’e ulaşabileceğiz.

3 ) TF (text file kısaltması olarak) değişkenini Scripting.TextStream sınıfına ait referansı taşıyabilecek bir değişken olarak tanımlıyoruz.

4 ) Taımladığımız fso değişkeni sayesinde, FileSytemObject metotlarından biri olan ‘CreateTextFile’ metodunu kullanarak, TF değişkenine yeni oluşturduğumuz bir textfile’ı değer olarak atıyoruz. Bu metodun;

    Set TF = fso.CreateTextFile( Path As String) şeklinde bir adet parametresi vardır.

Buraya örnekteki gibi dosyanın oluşturulmasını istediğimiz dosya yolunu ve sonrasında da istediğimiz dosya adını giriyoruz.

     Artık Deneme_1.txt dosyası oluşturulmuş durumda. Şimdi ise sıra “Scripting.TextStream” sınıfına ait metotları kullanarak oluşturmuş olduğumuz dosya içerisine yazı yazdırmakta. 

Makrolar İle Text(.txt) Dosya Yönetimi - TextStreamClass

 5 ) TF. Write metodu ile kesintisiz bir şekilde text dosyasına yazı yazdırılır. Eğer yazdırmak istediğimiz cümlenin veya kodun sonuna &vbNewLine yazmazsak, sonradan gelen bütün yazılar bir alt satıra inmeden, tek satır olacak şekilde devam eder. 

    .Write ile aşağıdaki sonucu elde ederiz.   

6 ) .WriteLine, bu metot ile tek satıra yazı yazdırılır. Kodun sonuna vbNewLine yazmaya gerek yoktur. Yazdırmak istediğimiz şey otomatik olarak bir alt satırdan yazar. Genellikle .Write değil .WriteLine kullanılır.

Makrolar İle Text(.txt) Dosya Yönetimi - TextStream_WriteLine

7 )  .WriteBlankLines 2; metodu da boş satır bırakmak istediğinizde kullandığınız metottur. 2 rakamı ile biz örneğimizde 2 adet boş satır bıraktık.

8 ) Burada örneğin devamı için bir anlam ifade eden “İçerik Buradan İtibaren Başlamaktadır.” ifadesini yazdırıyoruz.

9 ) TF.Close ile ilgili .txt dosya referansını sonlandırıyoruz.

10 ) Bu noktada ise fso değişkenine atanmış olan referansı sıfırlıyoruz. 

 

Örnek 2 - Bir Worksheet Üzerinde Yer Alan Tabloya Ait Verilerin Text Dosyasına Aktarılması

    Dersin girişinde de bahsettiğim üzere excel sheet’lerde yer alan tablolardaki verilerin .txt ve .csv dosyasına aktarımı, sheet’ten sheet’e yapılan tipik bir kopyala yapıştır veya oku ve yazdır işleminden daha farklıdır. Bir tabloyu excel içerisinde bir sheet’ten başka bir sheet’e taşırken bütün tabloyu seçip kopyalama işlemini gerçekleştirebiliyoruz. Sheet’ten .txt dosyasına aktarımda ise her hücredeki veriyi ayrı ayrı kopyalayıp ayrı ayrı .txt dosyasına yazdırmak zorundayız. Ek olarak da verilerin arasına, verilerin bir birinden ayrı olduğunun anlaşılması amacıyla nokta virgül veya boşluk gibi bir işaret kullanıyoruz. Zaten eğer virgül kullanırsak otomatik olarak text dosyamız .csv dosyasına dönüşüyor. Onun için bu örnek kapsamında .txt dosyası hazırlarken Tab yani boşluk kullanarak bir adet .txt dosyası, sonrasında ise virgül kullanarak aynı dosyanın bir de .csv formatını oluşturacağız. Şimdi direk örnek üzerinde yine madde madde giderek kodu anlamaya çalışalım. 

 

    Bu Sub-Routine’in amacı teker teker tabloyu oluşturan bütün hücreleri gerzerek birer birer text dosyasına aktarmaktır. Bu kapsamda yaptığımız ilk işlemler her zamanki gibi değişkenlerin tanımlanması, değişkenlerin ihtiva edeceği referansların belirlenmesi ve değişkenlere değer atanamaktır. Değişkenler ve referanslama işlemlerinin ardından tabloyu oluşturan bütün hücreleri döngü ile taramamızı sağlayan For Each ve For Next döngülerini iç içe kurguluyoruz. Son aşamada ise her zaman ki gibi bağlantıları kopartıyoruz. Şimdi tek tek yazdığımız kodların üzerinden gidelim.

1 – 2 – 3 numaralı kod satırlarını Örnek 1 kapsamında detaylı olarak açıklamıştık.  

4 ) Bu satırda FileSystemObject(fso) sınıfına ait yeni bir metot kullanıyoruz. Metotun adı ‘OpenTextFile’. Bu metot sayesinde varolan bir text.txt dosyasını açabiliyoruz ve değişiklikler gerçekleştirebiliyoruz. 

Önemli ve çok kullanacağımız bu .OpenTextFile metodunun parametrelerini hep beraber inceleyelim.

Makrolar İle Text(.txt) Dosya Yönetimi - TableToText

    .OpenTextFile Parametreleri

Makrolar İle Text(.txt) Dosya Yönetimi - OpenTextFile_Parametres

    4.1 FileName As String: Tek zorunlu parametredir. Dosya yoluyla beraber dosya ismi yazılır.

    4.2 IOMode As IOMode. 3 farklı seçenek vardır. Sadece okumak için ForReading, tamamiyle eski dosyanın üzerine yazmak için ForWriting ve içi yazılı olan dosyanın en sonuna ekleme yapmak için ForAppending. (Default = ForReading)

    4.3 Create As Boolean. True ve False seçeneklerini seçebilirsiniz. Burada kontrol edilen durum, açılmak istenen dosya eğer yoksa o dosya oluşturulsun derseniz True, aksi halde False seçebilirsiniz. (Default = False)

    4.4 Formatın 3. haliyle ilgili bir parametredir. Genellikle belirtilmez ve default bırakılır.

    Hatırlarsanız ilk örnekte “İçerik Buradan Başlamaktadır” gibi bir ibare yazdırmıştık. İşte tablomuzun verilerinin geleceği yer burasıdır. Açtığımız dosyayı sondan eklemeli yani ForAppending ile açtık ve tablo verileri bu son satırın altına yerleşecektir.

    5 ) Tabloyu oluşturan değerler, tablonun satır ve sütunları baz alınarak oluşturulan döngü vasıtasıyla okunacak ve .txt dosyasına aktarılacaktır. Bu kapsamda tabloyu oluşturan sütun sayısı değerini tutması için N_Sutun tanmlanmaktadır. 

    6 ) For Next döngüsünde kullanılacak olan iterative değerdir.

    7 ) Tablo içerisinde döngü ile seçilen her bir hücrenin verisinin okunmasında kullanılacak Hucre değişkeni tanımlanmıştır. 

    8 ) Burada B_Sutun değişkenine değer atanmaktadır. Tablomuzu hatırlarsanız [A1] hücresinden başlamaktaydı, görseli sayfanın üstünde yer almaktadır. En üst hücreden en sağ hücreye kadar olan bütün hücrelerin dolayısıyla da bir tablonun kaç sütundan oluştuğu burada N_Sutun değişkenine değer olarak tanımlanmaktadır.

    9 ) For Each döngüsünün başıdır ve koşul ise Hucre değişkeni A1 hücresinden en A sütununun en alt hücresine doğru giderkendir. Yani her hücre için For Each içerisindeki kod çalışacak sonrasında ise bir alt hücreye geçilerek aynı işlem tekrarlanarak bir döngü yaratılmış olacaktır. 

    10 ) For Each döngüsünde yer alan her bir hücreye göre de yeni bir For Next döngüsü oluşturulmuştur. Buradaki döngüde For Each döngüsünden gelen hücre değişkeninin kendi değeri okunuyor ve sonrasında sağa doğru sütun sayısı boyunca gidilerek tek tek değerlerin okunması amaçlanıyor.

    10.1 )  Hücre değerleri okunup text dosyasına aktarılırken, değerlerin arasına boşluk konulması ve son değere gelindğinde ise bir alt hücreye geçirilmesi gerekiyor. Bu kapsamda If koşullu durumları kullanıyoruz. Bu ilk koşula göre eğer K, N_Sutun değerinden küçükse;

     10.2 ) Hucrenin değerini oku, TF dosyasına yazarken sonuna bir adet Tab ile boşluk koy.

     10.3 ) Eğer K değeri N_Sutun değerinden küçü değilse de, TF dosyasına okunan hücrenin değerini yazarken sonuna vbNewLine ifade koy ve bir sonraki değer yazılırken bir alt satırdan başlanmasını sağla. Burada sütun sayısı, yani N_Sutun, 9 olduğu için K; N_Sutun’dan küçük olmadığında eşit olduğu anlamına geliyor. K’nın da 9 olması demek satırın en sonundaki hücrenin okunup bitmesi anlamına geliyor ve böylece vbTab yerine vbNewLine diyerek son hücreden sonra bir alta geçilmiş oluyor. Burada Offset metodu kullanılırken (K-1) yazmamızın sebebi ise eğer offset değeri 9 olursa tablonun bir hücre fazla sağa kaymasına sebep olmasıdır. Sonuçta en soldaki hücre değişkeni ile beraber toplam sütun sayısı 9 adettir. Yani en soldaki hücreyi 8 kere offsetlersek 9. hücreye geliriz.

    10.4 ) If Koşullu cümlesinin kapatılması

    10.5 ) For Next döngüsünün kapatılması

    10.6 ) For Each döngüsünün kapatılması

    11 ) Text dosyasına verilen referansın sıfırlanması

    12 ) fso değişkenine verilen referansın sıfırlanması 

 

 

    Kodun çalıştırılması ile beraber de aşağıda gördüğünüz .txt dosyası oluşturulmuştur.

Makrolar İle Text(.txt) Dosyala Yönetimi - OpenTextFiles

 

    Gördüğünüz gibi tablomuz, Deneme_1.txt dosyasının altına eklenmiş durumdadır. Hücre içeriklerinin uzun ve kısalığına göre yatay doğrultuda biraz kayma meydana gelmiştir ancak verilerde hernagi bir kayıp yaşanmamıştır. 

 

Örnek 3 - Bir Worksheet Üzerinde Yer Alan Tabloya Ait Verilerin .csv Dosyasına Aktarılması

    Bu örneğimizde fso.OpenTextFile metodunda yer alan ‘Create As Boolean‘ parametresini (bkz. Örnek 2 Madde 4.3) ‘True’ yapacağız ve eğer açmak istediğimiz yoksa metodun yeni gir dosya oluşturmasını sağlayacağız. 

     Örnek 2 ile örnek 3 arasında 3 adet fark vardır. Bu farklar;

Set TF = fso.OpenTextFile _
    ("F:\macronimbus\Yazılar_TextFiles\Text\Deneme_1.csv", ForAppending, True)

1 ) ÜStteki komutta yer alan ‘Create As Boolean’ parametresi için girilmiş olan ‘True’ değeri ve 

2 ) Dosya yolunda, dosya isminde yer alan .txt yerine yazdığımız .csv uzantısıdır. Bu satır sayesinde halihazırda var olmayan Deneme_1.csv dosyası oluşturulacaktır. 

3 ) Son değişiklik ise 

 

    If koşulunda yer alan & vbTab yerine kullanılan “,” virgüldür. Bu sayede text dosyası oluşturmak için koyduğumuz boşluk yerine, VİRGÜL kullanarak .csv yani ‘Virgül ile Ayrılmış Değerler‘ dosyasını oluşturabilmekteyiz.

Makrolar İle Text(.txt) Dosya Yönetimi - CreateTextFile_CSV

    

2- OpenText Metodu İle Text.txt ve .CSV Dosyalarının Excel Worksheet Aktarılması

    Şimdiye kadar gördüğümüz örneklerde excel sayfalardan .txt ve .csv dosyalara nasıl veri aktarıldığını gördük. Genellikle 20 satırlık Sub-Routine’ler kullanarak gerçekleştirdiğimiz bu işlemleri şimdi tek bir metot yardımıyla halledeceğiz. Kullanacağımız metot “.OpenText” metotudur.

    Gördüğünüz gibi sadece tek bir  mecburi parametresi vardır. Ancak düzenli bir şekilde verilerin aktarılabilmesi için bu parametrelerden bir tanesi çok önemlidir. O parametre de ‘[Tab]’ parametresidir. Burada True veya False değeri alır. 

     Eğer Örnek 2 ile oluşturmuş olduğumuz Deneme_1.txt dosyayasını, bir excel worksheet’e aktarmak istersek aşağıda paylaşılan kodu yazmamız yeterli olacaktır.

Sub TextDosyasınıWorkbookOlarakAcmak()
    Workbooks.OpenText Filename:="F:\macronimbus\Yazılar_TextFiles\Text\Deneme_1.txt", Tab:=True
End Sub

    Burada önemli olan nokta verilerin bir biririnden ayrılmasında kullanılan noktalamanın bilinmesidir. Biz .txt dosyasını oluştururken vbTab kullandığımız için burada parametre olarak tab ‘Tab:=True’ yazdık. Eğer .csv dosyasını bir excel sheet’e aktarmak istesydik o zaman da Comma:=True yazmamız yeterli olurdu. 

    Kodu çalıştrımanızla beraber içerisine text dosyasının aktarıldığı yeni bir workbook açılacaktır. Herhangi bir sıkıntı olmaz ise aşağıdaki görüntü elde edilir.

    İyi çalışmalar…

Dünya Çapında Araştırmacılara Ait Çalışmaları Okumak İsterseniz https://www.researchgate.net/profile/Ender_Dagdelen adresini Ziyaret Edebilirsiniz. Ana Sayfaya Dönmek İsterseniz De Aşağıdaki Tuşu Tıklayınız.

 

Herkese Kolay Gelsin.

Soru ve Önerileriniz İçin;

[email protected]