Betik Görevini Kullanarak Excel Dosyalarıyla Çalışma

Şunlar için geçerlidir:SQL Server Azure Data Factory'de SSIS Tümleştirme Çalışma Zamanı

Entegrasyon Hizmetleri, Microsoft Excel dosya formatında elektronik tablolarda depolanan verilerle çalışmak için Excel bağlantı yöneticisi, Excel kaynağı ve Excel hedefi sağlar. Bu konuda açıklanan teknikler, mevcut Excel veritabanları (çalışma defteri dosyaları) ve tablolar (çalışma sayfaları ve adlandırılmış aralıklar) hakkında bilgi edinmek için Script görevini kullanır.

Important

Excel dosyalarına bağlanma ve Excel dosyalarından veya Excel dosyalarına veri yüklemeyle ilgili sınırlamalar ve bilinen sorunlar hakkında ayrıntılı bilgi için bkz. SQL Server Integration Services (SSIS) ile Excel'den veya Excel'e veri yükleme.

Tip

Birden fazla paket arasında yeniden kullanabileceğiniz bir görev oluşturmak istiyorsanız, bu Script görev örneğindeki kodu özel bir görev için başlangıç noktası olarak kullanmayı düşünün. Daha fazla bilgi için bkz. Özel Görev geliştirme.

Örnekleri Test Etmek İçin Bir Paket Yapılandırması

Bu konudaki tüm örnekleri test etmek için tek bir paket yapılandırabilirsiniz. Örnekler, aynı paket değişkenlerinin çoğunu ve aynı .NET Framework sınıflarını kullanır.

Bu konudaki örneklerle kullanılacak bir paketi yapılandırmak

1. SQL Server Veri Araçları (SSDT) ile yeni bir Entegrasyon Hizmetleri projesi oluşturun ve düzenleme için varsayılan paketi açın.

2. Değişkenler. Değişkenler penceresini açın ve aşağıdaki değişkenleri tanımlayın:

   • ExcelFile, String türünde. Mevcut bir Excel çalışma kitabının tam yolunu ve dosya adını girin.

   • ExcelTable, String türünde. Değişkenin değeriyle ExcelFile adlandırılan çalışma defterine mevcut bir çalışma sayfasının veya adlandırılmış aralığın adını girin. Bu değer harf büyüklüğüne duyarlıdır.

   • ExcelFileExists, Boolean türünde.

   • ExcelTableExists, Boolean türünde.

   • ExcelFolder, String türünde. En az bir Excel çalışma defteri içeren bir klasörün tam yolunu girin.

   • ExcelFiles, Nesne türünde.

   • ExcelTables, Nesne türünde.

3. Açıklamaları ithal eder. Kod örneklerinin çoğu, script dosyanızın üstündeki aşağıdaki .NET Framework isim alanlarından birini veya ikisini içe aktarmanızı gerektirir:

   • System.IO, dosya sistemi işlemleri için.

   • System.Data.OleDb ile Excel dosyalarını veri kaynağı olarak açmak için kullanılır.

4. Referanslar. Excel dosyalarından şema bilgisini okuyan kod örnekleri, script projesinde System.Xml isim alanına ek bir referans gerektirir.

5. Script bileşeni için varsayılan betik dilini Seçenekler diyalog kutusunun Genel sayfasındaki Scripting dili seçeneğini kullanarak ayarlayın. Daha fazla bilgi için bkz. Genel Sayfa.

Örnek 1 Açıklama: Bir Excel Dosyasının Var olup olmadığını Kontrol Edin

Bu örnek, değişkende ExcelFile belirtilen Excel çalışma defteri dosyasının var olup olmadığını belirler ve ardından değişkenin Boolean değerini ExcelFileExists sonuca ayarlar. Bu Boolean değerini paketin iş akışında dallanma için kullanabilirsiniz.

Bu Script Görevi örneğini yapılandırmak için

1. Pakete yeni bir Script görevi ekle ve adını ExcelFileExists olarak değiştir.

2. Script Görev Düzenleyici'de, Script sekmesinde ReadOnlyVariables'e tıklayın ve aşağıdaki yöntemlerden biriyle özellik değerini girin:

   • ExcelFile yazın.

     -veya-

   • Özellik alanının yanındaki üç nokta (...) düğmesine tıklayın ve Değişkenleri Seç iletişim kutusunda ExcelFile değişkenini seçin.

3. ReadWriteVariables'e tıklayın ve özellik değerini aşağıdaki yöntemlerden biriyle girin:

   • ExcelFileExists yazın.

     -veya-

   • Özellik alanının yanındaki üç nokta (...) düğmesine tıklayın ve Değişkenleri Seç iletişim kutusunda ExcelFileExists değişkenini seçin.

4. Script editörü açmak için Edit Script'e tıklayın.

5. Script dosyasının en üstüne System.IO isim alanı için bir Imports ifadesi ekleyin.

6. Aşağıdaki kodu ekleyin.

Örnek 1 Kod

Public Class ScriptMain  
  Public Sub Main()  
    Dim fileToTest As String  
  
    fileToTest = Dts.Variables("ExcelFile").Value.ToString  
    If File.Exists(fileToTest) Then  
      Dts.Variables("ExcelFileExists").Value = True  
    Else  
      Dts.Variables("ExcelFileExists").Value = False  
    End If  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class  

public class ScriptMain  
{  
  public void Main()  
  {  
    string fileToTest;  
  
    fileToTest = Dts.Variables["ExcelFile"].Value.ToString();  
    if (File.Exists(fileToTest))  
    {  
      Dts.Variables["ExcelFileExists"].Value = true;  
    }  
    else  
    {  
      Dts.Variables["ExcelFileExists"].Value = false;  
    }  
  
    Dts.TaskResult = (int)ScriptResults.Success;  
  }  
}  

Örnek 2 Açıklama: Bir Excel Tablosu Var Olup olmadığını Kontrol Edin

Bu örnek, değişkende belirtilen Excel çalışma sayfası veya adlandırılmış aralığınınExcelTable, değişkende belirtilen Excel çalışma defteri dosyasında ExcelFile olup olmadığını belirler ve ardından değişkenin Boolean değerini ExcelTableExists sonuca ayarlar. Bu Boolean değerini paketin iş akışında dallanma için kullanabilirsiniz.

Bu Script Görevi örneğini yapılandırmak için

1. Pakete yeni bir Script görevi ekleyin ve adını ExcelTableExists olarak değiştirin.

2. Script Görev Düzenleyici'de, Script sekmesinde ReadOnlyVariables'e tıklayın ve aşağıdaki yöntemlerden biriyle özellik değerini girin:

   • ExcelTable ve ExcelFile seçeneklerini virgülle ayrılmış olarak yazın.

     -veya-

   • Özellik alanının yanındaki üç nokta (...) düğmesine tıklayın ve Değişkenleri Seç iletişim kutusunda ExcelTable ve ExcelFile değişkenlerini seçin.

3. ReadWriteVariables'e tıklayın ve özellik değerini aşağıdaki yöntemlerden biriyle girin:

   • ExcelTableExists yazın.

     -veya-

   • Özellik alanının yanındaki üç nokta (...) düğmesine tıklayın ve Değişkenleri Seç iletişim kutusunda ExcelTableExists değişkenini seçin.

4. Script editörü açmak için Edit Script'e tıklayın.

5. Script projesinde System.Xml assembly referansı ekleyin.

6. Script dosyasının üstüne System.IO ve System.Data.OleDb isim alanları için Imports ifadelerini ekleyin.

7. Aşağıdaki kodu ekleyin.

Örnek 2 Kod

Public Class ScriptMain  
  Public Sub Main()  
    Dim fileToTest As String  
    Dim tableToTest As String  
    Dim connectionString As String  
    Dim excelConnection As OleDbConnection  
    Dim excelTables As DataTable  
    Dim excelTable As DataRow  
    Dim currentTable As String  
  
    fileToTest = Dts.Variables("ExcelFile").Value.ToString  
    tableToTest = Dts.Variables("ExcelTable").Value.ToString  
  
    Dts.Variables("ExcelTableExists").Value = False  
    If File.Exists(fileToTest) Then  
      connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _  
        "Data Source=" & fileToTest & _  
        ";Extended Properties=Excel 12.0"  
      excelConnection = New OleDbConnection(connectionString)  
      excelConnection.Open()  
      excelTables = excelConnection.GetSchema("Tables")  
      For Each excelTable In excelTables.Rows  
        currentTable = excelTable.Item("TABLE_NAME").ToString  
        If currentTable = tableToTest Then  
          Dts.Variables("ExcelTableExists").Value = True  
        End If  
      Next  
    End If  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class  

public class ScriptMain  
{  
    public void Main()  
        {  
            string fileToTest;  
            string tableToTest;  
            string connectionString;  
            OleDbConnection excelConnection;  
            DataTable excelTables;  
            string currentTable;  
  
            fileToTest = Dts.Variables["ExcelFile"].Value.ToString();  
            tableToTest = Dts.Variables["ExcelTable"].Value.ToString();  
  
            Dts.Variables["ExcelTableExists"].Value = false;  
            if (File.Exists(fileToTest))  
            {  
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +  
                "Data Source=" + fileToTest + ";Extended Properties=Excel 12.0";  
                excelConnection = new OleDbConnection(connectionString);  
                excelConnection.Open();  
                excelTables = excelConnection.GetSchema("Tables");  
                foreach (DataRow excelTable in excelTables.Rows)  
                {  
                    currentTable = excelTable["TABLE_NAME"].ToString();  
                    if (currentTable == tableToTest)  
                    {  
                        Dts.Variables["ExcelTableExists"].Value = true;  
                    }  
                }  
            }  
  
            Dts.TaskResult = (int)ScriptResults.Success;  
  
        }  
}  

Örnek 3 Açıklama: Bir klasörde Excel dosyalarının listesini alın

Bu örnek, değişkenin değerinde ExcelFolder belirtilen klasörde bulunan Excel dosyalarının listesini bir diziyi doldurur ve ardından diziyi değişkene ExcelFiles kopyalar. Değişken sayıcısından Foreach ile dizinin içindeki dosyaları yineleme yapabilirsiniz.

Bu Script Görevi örneğini yapılandırmak için

1. Pakete yeni bir Script görevi ekleyin ve adını GetExcelFiles olarak değiştirin.

2. Script Görev DüzenleyicisiniScript sekmesinden açın, ReadOnlyVariables'e tıklayın ve aşağıdaki yöntemlerden biriyle özellik değerini girin:

   • ExcelFolder tipi

     -veya-

   • Özellik alanının yanındaki üç nokta (...) butonuna tıklayın ve Değişkenleri Seç iletişim kutusunda ExcelFolder değişkenini seçin.

3. ReadWriteVariables'e tıklayın ve özellik değerini aşağıdaki yöntemlerden biriyle girin:

   • ExcelFiles yazın.

     -veya-

   • Özellik alanının yanındaki üç nokta (...) düğmesine tıklayın ve Değişkenleri Seç iletişim kutusundan ExcelFiles değişkenini seçin.

4. Script editörü açmak için Edit Script'e tıklayın.

5. Script dosyasının en üstüne System.IO isim alanı için bir Imports ifadesi ekleyin.

6. Aşağıdaki kodu ekleyin.

Örnek 3 Kod

Public Class ScriptMain  
  Public Sub Main()  
    Const FILE_PATTERN As String = "*.xlsx"  
  
    Dim excelFolder As String  
    Dim excelFiles As String()  
  
    excelFolder = Dts.Variables("ExcelFolder").Value.ToString  
    excelFiles = Directory.GetFiles(excelFolder, FILE_PATTERN)  
  
    Dts.Variables("ExcelFiles").Value = excelFiles  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class  

public class ScriptMain  
{  
  public void Main()  
  {  
    const string FILE_PATTERN = "*.xlsx";  
  
    string excelFolder;  
    string[] excelFiles;  
  
    excelFolder = Dts.Variables["ExcelFolder"].Value.ToString();  
    excelFiles = Directory.GetFiles(excelFolder, FILE_PATTERN);  
  
    Dts.Variables["ExcelFiles"].Value = excelFiles;  
  
    Dts.TaskResult = (int)ScriptResults.Success;  
  }  
}  

Alternatif Çözüm

Bir Script görevi kullanarak Excel dosyalarının listesini bir diziye toplamak yerine, ForEach File enumerator'u kullanarak bir klasördeki tüm Excel dosyalarını yineleme yapabilirsiniz. Daha fazla bilgi için, Foreach Döngü Konteyneri Kullanarak Excel Dosyaları ve Tabloları Döngüsü ile Döngü Döngüsü sayfasına bakınız.

Örnek 4 Açıklama: Bir Excel dosyasında tablo listesini alın

Bu örnek, değişkenin değeriyle ExcelFile belirtilen Excel çalışma defteri dosyasında bulunan çalışma sayfaları ve adlandırılmış aralıklar listesiyle bir diziyi doldurur ve ardından diziyi değişkene ExcelTables kopyalar. Değişken Sayma Cihazı'ndan Foreach kullanarak dizinin tablolarını tekrar edebilirsin.

Note

Bir Excel çalışma defterindeki tablolar listesi hem çalışma sayfalarını (ki bunlar $ ekine sahip) hem de adlandırılmış aralıkları içerir. Eğer listeyi sadece çalışma sayfaları veya sadece adlandırılmış aralıklar için filtrelemek zorundaysanız, bu amaçla ek kod eklemeniz gerekebilir.

Bu Script Görevi örneğini yapılandırmak için

1. Pakete yeni bir Script görevi ekleyin ve adını GetExcelTables olarak değiştirin.

2. Script Görev DüzenleyicisiniScript sekmesinden açın, ReadOnlyVariables'e tıklayın ve aşağıdaki yöntemlerden biriyle özellik değerini girin:

   • ExcelFile yazın.

     -veya-

   • Özellik alanının yanındaki üç nokta (...) düğmesine tıklayın ve Değişkenleri Seç iletişim kutusunda ExcelFile değişkenini seçin.

3. ReadWriteVariables'e tıklayın ve özellik değerini aşağıdaki yöntemlerden biriyle girin:

   • ExcelTables yazın.

     -veya-

   • Özellik alanının yanındaki üç nokta (...) düğmesine tıklayın ve Değişkenleri Seç iletişim kutusunda ExcelTablesvariable'ı seçin.

4. Script editörü açmak için Edit Script'e tıklayın.

5. Script projesinde System.Xml isim alanına bir referans ekleyin.

6. Script dosyasının üstüne System.Data.OleDb isim alanı için bir Imports ifadesi ekleyin.

7. Aşağıdaki kodu ekleyin.

Örnek 4 Kod

Public Class ScriptMain  
  Public Sub Main()  
    Dim excelFile As String  
    Dim connectionString As String  
    Dim excelConnection As OleDbConnection  
    Dim tablesInFile As DataTable  
    Dim tableCount As Integer = 0  
    Dim tableInFile As DataRow  
    Dim currentTable As String  
    Dim tableIndex As Integer = 0  
  
    Dim excelTables As String()  
  
    excelFile = Dts.Variables("ExcelFile").Value.ToString  
    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _  
        "Data Source=" & excelFile & _  
        ";Extended Properties=Excel 12.0"  
    excelConnection = New OleDbConnection(connectionString)  
    excelConnection.Open()  
    tablesInFile = excelConnection.GetSchema("Tables")  
    tableCount = tablesInFile.Rows.Count  
    ReDim excelTables(tableCount - 1)  
    For Each tableInFile In tablesInFile.Rows  
      currentTable = tableInFile.Item("TABLE_NAME").ToString  
      excelTables(tableIndex) = currentTable  
      tableIndex += 1  
    Next  
  
    Dts.Variables("ExcelTables").Value = excelTables  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class  

public class ScriptMain  
{  
  public void Main()  
        {  
            string excelFile;  
            string connectionString;  
            OleDbConnection excelConnection;  
            DataTable tablesInFile;  
            int tableCount = 0;  
            string currentTable;  
            int tableIndex = 0;  
  
            string[] excelTables = new string[5];  
  
            excelFile = Dts.Variables["ExcelFile"].Value.ToString();  
            connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +  
                "Data Source=" + excelFile + ";Extended Properties=Excel 12.0";  
            excelConnection = new OleDbConnection(connectionString);  
            excelConnection.Open();  
            tablesInFile = excelConnection.GetSchema("Tables");  
            tableCount = tablesInFile.Rows.Count;  
  
            foreach (DataRow tableInFile in tablesInFile.Rows)  
            {  
                currentTable = tableInFile["TABLE_NAME"].ToString();  
                excelTables[tableIndex] = currentTable;  
                tableIndex += 1;  
            }  
  
            Dts.Variables["ExcelTables"].Value = excelTables;  
  
            Dts.TaskResult = (int)ScriptResults.Success;  
        }  
}  

Alternatif Çözüm

Bir Script görevi kullanarak Excel tablolarının bir listesini bir diziye toplamak yerine, ForEach ADO.NET Schema Rowset Enumerator'u kullanarak Excel çalışma defteri dosyasındaki tüm tabloları (yani çalışma sayfalarını ve adlandırılmış aralıkları) tekrar edebilirsin. Daha fazla bilgi için, Foreach Döngü Konteyneri Kullanarak Excel Dosyaları ve Tabloları Döngüsü ile Döngü Döngüsü sayfasına bakınız.

Örneklerin Sonuçlarının Görüntülenmesi

Bu konudaki her örneği aynı pakette yapılandırdıysanız, tüm Script görevlerini tüm örneklerin çıktısını gösteren ek bir Script görevine bağlayabilirsiniz.

Bu konudaki örneklerin çıktısını gösterecek bir Script görevini yapılandırmak için

1. Pakete yeni bir Script görevi ekleyin ve adını DisplayResults olarak değiştirin.

2. Dört örnek Script görevini birbirine bağlayın, böylece her görev önceki görev başarıyla tamamlandıktan sonra çalıştırılır ve dördüncü örnek görevi DisplayResults görevine bağlayın.

3. Script Task Edit'teDisplayResults görevini açın.

4. Script sekmesinde, ReadOnlyVariables'e tıklayın ve Örnekleri Test etmek için Paket Yapılandırması bölümünde listelenen yedi değişkenin tamamını eklemek için aşağıdaki yöntemlerden birini kullanın:

   • Her değişkenin adını virgülle ayrılarak yazın.

     -veya-

   • Özellik alanının yanındaki üç nokta (...) düğmesine tıklayın ve değişkenleri seç iletişim kutusunda değişkenleri seçin.

5. Script editörü açmak için Edit Script'e tıklayın.

6. Microsoft için İthalat açıklamalarını ekleyin. VisualBasic ve System.Windows. Form namespaces, script dosyasının en üstünde.

7. Aşağıdaki kodu ekleyin.

8. Paketi çalıştırın ve mesaj kutusunda gösterilen sonuçları inceleyin.

Sonuçları Göstermek İçin Kod

Public Class ScriptMain  
  Public Sub Main()  
    Const EOL As String = ControlChars.CrLf  
  
    Dim results As String  
    Dim filesInFolder As String()  
    Dim fileInFolder As String  
    Dim tablesInFile As String()  
    Dim tableInFile As String  
  
    results = _  
      "Final values of variables:" & EOL & _  
      "ExcelFile: " & Dts.Variables("ExcelFile").Value.ToString & EOL & _  
      "ExcelFileExists: " & Dts.Variables("ExcelFileExists").Value.ToString & EOL & _  
      "ExcelTable: " & Dts.Variables("ExcelTable").Value.ToString & EOL & _  
      "ExcelTableExists: " & Dts.Variables("ExcelTableExists").Value.ToString & EOL & _  
      "ExcelFolder: " & Dts.Variables("ExcelFolder").Value.ToString & EOL & _  
      EOL  
  
    results &= "Excel files in folder: " & EOL  
    filesInFolder = DirectCast(Dts.Variables("ExcelFiles").Value, String())  
    For Each fileInFolder In filesInFolder  
      results &= " " & fileInFolder & EOL  
    Next  
    results &= EOL  
  
    results &= "Excel tables in file: " & EOL  
    tablesInFile = DirectCast(Dts.Variables("ExcelTables").Value, String())  
    For Each tableInFile In tablesInFile  
      results &= " " & tableInFile & EOL  
    Next  
  
    MessageBox.Show(results, "Results", MessageBoxButtons.OK, MessageBoxIcon.Information)  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class  

public class ScriptMain  
{  
  public void Main()  
        {  
            const string EOL = "\r";  
  
            string results;  
            string[] filesInFolder;  
            //string fileInFolder;  
            string[] tablesInFile;  
            //string tableInFile;  
  
            results = "Final values of variables:" + EOL + "ExcelFile: " + Dts.Variables["ExcelFile"].Value.ToString() + EOL + "ExcelFileExists: " + Dts.Variables["ExcelFileExists"].Value.ToString() + EOL + "ExcelTable: " + Dts.Variables["ExcelTable"].Value.ToString() + EOL + "ExcelTableExists: " + Dts.Variables["ExcelTableExists"].Value.ToString() + EOL + "ExcelFolder: " + Dts.Variables["ExcelFolder"].Value.ToString() + EOL + EOL;  
  
            results += "Excel files in folder: " + EOL;  
            filesInFolder = (string[])(Dts.Variables["ExcelFiles"].Value);  
            foreach (string fileInFolder in filesInFolder)  
            {  
                results += " " + fileInFolder + EOL;  
            }  
            results += EOL;  
  
            results += "Excel tables in file: " + EOL;  
            tablesInFile = (string[])(Dts.Variables["ExcelTables"].Value);  
            foreach (string tableInFile in tablesInFile)  
            {  
                results += " " + tableInFile + EOL;  
            }  
  
            MessageBox.Show(results, "Results", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  
            Dts.TaskResult = (int)ScriptResults.Success;  
        }  
}  

İlgili içerik

• SQL Server Integration Services (SSIS) ile Verileri Excel'den içeri aktarma veya Excel'e aktarma
• Foreach Döngü Kapsayıcısı ile Excel Dosyaları ve Tablolarında Döngü Oluşturma