Bekerja dengan file Excel dengan Tugas Skrip

  • Artikel

Berlaku untuk: Integration Runtime SSIS SQL Server di Azure Data Factory

Integration Services menyediakan pengelola koneksi Excel, sumber Excel, dan tujuan Excel untuk bekerja dengan data yang disimpan dalam lembar bentang dalam format file Microsoft Excel. Teknik yang dijelaskan dalam topik ini menggunakan tugas Skrip untuk mendapatkan informasi tentang database Excel yang tersedia (file buku kerja) dan tabel (lembar kerja dan rentang bernama).

Penting

Untuk informasi mendetail tentang menyambungkan ke file Excel, dan tentang batasan dan masalah yang diketahui untuk memuat data dari atau ke file Excel, lihat Memuat data dari atau ke Excel dengan SQL Server Integration Services (SSIS).

Tip

Jika Anda ingin membuat tugas yang bisa Anda gunakan kembali di beberapa paket, pertimbangkan untuk menggunakan kode dalam sampel tugas Skrip ini sebagai titik awal untuk tugas kustom. Untuk informasi selengkapnya, lihat Mengembangkan Tugas Kustom.

Mengonfigurasi Paket untuk Menguji Sampel

Anda dapat mengonfigurasi satu paket untuk menguji semua sampel dalam topik ini. Sampel menggunakan banyak variabel paket yang sama dan kelas .NET Framework yang sama.

Untuk mengonfigurasi paket untuk digunakan dengan contoh dalam topik ini

  1. Buat proyek Integration Services baru di SQL Server Data Tools (SSDT) dan buka paket default untuk pengeditan.

  2. Variabel. Buka jendela Variabel dan tentukan variabel berikut:

    • ExcelFile, dari jenis String. Masukkan jalur lengkap dan nama file ke buku kerja Excel yang sudah ada.

    • ExcelTable, dari jenis String. Masukkan nama lembar kerja yang sudah ada atau rentang bernama dalam buku kerja bernama dalam nilai ExcelFile variabel. Nilai ini peka huruf besar/kecil.

    • ExcelFileExists, jenis Boolean.

    • ExcelTableExists, jenis Boolean.

    • ExcelFolder, dari jenis String. Masukkan jalur lengkap folder yang berisi setidaknya satu buku kerja Excel.

    • ExcelFiles, dari jenis Objek.

    • ExcelTables, dari jenis Objek.

  3. Mengimpor pernyataan. Sebagian besar sampel kode mengharuskan Anda mengimpor satu atau kedua namespace layanan .NET Framework berikut di bagian atas file skrip Anda:

    • System.IO, untuk operasi sistem file.

    • System.Data.OleDb, untuk membuka file Excel sebagai sumber data.

  4. Referensi. Sampel kode yang membaca informasi skema dari file Excel memerlukan referensi tambahan dalam proyek skrip ke namespaceSystem.Xml .

  5. Atur bahasa skrip default untuk komponen Skrip dengan menggunakan opsi Bahasa pembuatan skrip pada halaman Umum kotak dialog Opsi . Untuk informasi selengkapnya, lihat Halaman Umum.

Contoh 1 Deskripsi: Periksa Apakah Ada File Excel

Contoh ini menentukan apakah file buku kerja Excel yang ditentukan dalam ExcelFile variabel ada, lalu mengatur nilai Boolean variabel ExcelFileExists ke hasilnya. Anda dapat menggunakan nilai Boolean ini untuk percabangan dalam alur kerja paket.

Untuk mengonfigurasi contoh Tugas Skrip ini

  1. Tambahkan tugas Skrip baru ke paket dan ubah namanya menjadi ExcelFileExists.

  2. Di Editor Tugas Skrip, pada tab Skrip , klik ReadOnlyVariables dan masukkan nilai properti menggunakan salah satu metode berikut:

    • Ketik ExcelFile.

      -atau-

    • Klik tombol elipsis (...) di samping bidang properti, dan dalam kotak dialog Pilih variabel , pilih variabel ExcelFile .

  3. Klik ReadWriteVariables dan masukkan nilai properti menggunakan salah satu metode berikut:

    • Ketik ExcelFileExists.

      -atau-

    • Klik tombol elipsis (...) di samping bidang properti, dan dalam kotak dialog Pilih variabel , pilih variabel ExcelFileExists .

  4. Klik Edit Skrip untuk membuka editor skrip.

  5. Tambahkan pernyataan Impor untuk namespace System.IO di bagian atas file skrip.

  6. Tambahkan kode berikut.

Contoh 1 Kode

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;  
  }  
}

Contoh 2 Deskripsi: Periksa Apakah Tabel Excel Ada

Contoh ini menentukan apakah lembar kerja Excel atau rentang bernama yang ditentukan dalam ExcelTable variabel ada dalam file buku kerja Excel yang ditentukan dalam ExcelFile variabel, lalu mengatur nilai Boolean variabel ExcelTableExists ke hasil. Anda dapat menggunakan nilai Boolean ini untuk percabangan dalam alur kerja paket.

Untuk mengonfigurasi contoh Tugas Skrip ini

  1. Tambahkan tugas Skrip baru ke paket dan ubah namanya menjadi ExcelTableExists.

  2. Di Editor Tugas Skrip, pada tab Skrip , klik ReadOnlyVariables dan masukkan nilai properti menggunakan salah satu metode berikut:

    • Ketik ExcelTable dan ExcelFile yang dipisahkan oleh koma.

      -atau-

    • Klik tombol elipsis (...) di samping bidang properti, dan dalam kotak dialog Pilih variabel , pilih variabel ExcelTable dan ExcelFile .

  3. Klik ReadWriteVariables dan masukkan nilai properti menggunakan salah satu metode berikut:

    • Ketik ExcelTableExists.

      -atau-

    • Klik tombol elipsis (...) di samping bidang properti, dan dalam kotak dialog Pilih variabel , pilih variabel ExcelTableExists .

  4. Klik Edit Skrip untuk membuka editor skrip.

  5. Tambahkan referensi ke rakitan System.Xml dalam proyek skrip.

  6. Tambahkan pernyataan Impor untuk namespace layanan System.IO dan System.Data.OleDb di bagian atas file skrip.

  7. Tambahkan kode berikut.

Contoh 2 Kode

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;  
  
        }  
}

Contoh 3 Deskripsi: Mendapatkan Daftar File Excel dalam Folder

Contoh ini mengisi array dengan daftar file Excel yang ditemukan dalam folder yang ditentukan dalam nilai ExcelFolder variabel, lalu menyalin array ke ExcelFiles dalam variabel. Anda dapat menggunakan Foreach dari Enumerator variabel untuk mengulangi file dalam array.

Untuk mengonfigurasi contoh Tugas Skrip ini

  1. Tambahkan tugas Skrip baru ke paket dan ubah namanya menjadi GetExcelFiles.

  2. Buka Editor Tugas Skrip, pada tab Skrip , klik ReadOnlyVariables dan masukkan nilai properti menggunakan salah satu metode berikut:

    • Ketik ExcelFolder

      -atau-

    • Klik tombol elipsis (...) di samping bidang properti, dan dalam kotak dialog Pilih variabel , pilih variabel ExcelFolder.

  3. Klik ReadWriteVariables dan masukkan nilai properti menggunakan salah satu metode berikut:

    • Ketik ExcelFiles.

      -atau-

    • Klik tombol elipsis (...) di samping bidang properti, dan dalam kotak dialog Pilih variabel , pilih variabel ExcelFiles.

  4. Klik Edit Skrip untuk membuka editor skrip.

  5. Tambahkan pernyataan Impor untuk namespace System.IO di bagian atas file skrip.

  6. Tambahkan kode berikut.

Contoh 3 Kode

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;  
  }  
}

Solusi Alternatif

Alih-alih menggunakan tugas Skrip untuk mengumpulkan daftar file Excel ke dalam array, Anda juga bisa menggunakan enumerator File ForEach untuk mengulangi semua file Excel dalam folder. Untuk informasi selengkapnya, lihat Perulangan melalui File dan Tabel Excel dengan Menggunakan Kontainer Perulangan Foreach.

Contoh 4 Deskripsi: Mendapatkan Daftar Tabel dalam File Excel

Contoh ini mengisi array dengan daftar lembar kerja dan rentang bernama yang ditemukan dalam file buku kerja Excel yang ditentukan oleh nilai ExcelFile variabel, lalu menyalin array ke dalam ExcelTables variabel. Anda dapat menggunakan Foreach dari Enumerator Variabel untuk mengulangi tabel dalam array.

Catatan

Daftar tabel dalam buku kerja Excel menyertakan kedua lembar kerja (yang memiliki akhiran $) dan rentang bernama. Jika Anda harus memfilter daftar hanya untuk lembar kerja atau hanya rentang bernama, Anda mungkin harus menambahkan kode tambahan untuk tujuan ini.

Untuk mengonfigurasi contoh Tugas Skrip ini

  1. Tambahkan tugas Skrip baru ke paket dan ubah namanya menjadi GetExcelTables.

  2. Buka Editor Tugas Skrip, pada tab Skrip , klik ReadOnlyVariables dan masukkan nilai properti menggunakan salah satu metode berikut:

    • Ketik ExcelFile.

      -atau-

    • Klik tombol elipsis (...) di samping bidang properti, dan dalam kotak dialog Pilih variabel , pilih variabel ExcelFile.

  3. Klik ReadWriteVariables dan masukkan nilai properti menggunakan salah satu metode berikut:

    • Ketik ExcelTables.

      -atau-

    • Klik tombol elipsis (...) di samping bidang properti, dan dalam kotak dialog Pilih variabel , pilih ExcelTablesvariable.

  4. Klik Edit Skrip untuk membuka editor skrip.

  5. Tambahkan referensi ke namespaceSystem.Xml dalam proyek skrip.

  6. Tambahkan pernyataan Impor untuk namespace System.Data.OleDb di bagian atas file skrip.

  7. Tambahkan kode berikut.

Contoh 4 Kode

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;  
        }  
}

Solusi Alternatif

Alih-alih menggunakan tugas Skrip untuk mengumpulkan daftar tabel Excel ke dalam array, Anda juga bisa menggunakan ForEach ADO.NET Schema Rowset Enumerator untuk mengulangi semua tabel (yaitu, lembar kerja dan rentang bernama) dalam file buku kerja Excel. Untuk informasi selengkapnya, lihat Mengulang File dan Tabel Excel dengan Menggunakan Kontainer Perulangan Foreach.

Menampilkan Hasil Sampel

Jika Anda telah mengonfigurasi setiap contoh dalam topik ini dalam paket yang sama, Anda dapat menyambungkan semua tugas Skrip ke tugas Skrip tambahan yang menampilkan output dari semua contoh.

Untuk mengonfigurasi tugas Skrip untuk menampilkan output contoh dalam topik ini

  1. Tambahkan tugas Skrip baru ke paket dan ubah namanya menjadi DisplayResults.

  2. Sambungkan masing-masing dari empat contoh tugas Skrip satu sama lain, sehingga setiap tugas berjalan setelah tugas sebelumnya berhasil diselesaikan, dan sambungkan tugas contoh keempat ke tugas DisplayResults .

  3. Buka tugas DisplayResults di Editor Tugas Skrip.

  4. Pada tab Skrip , klik ReadOnlyVariables dan gunakan salah satu metode berikut untuk menambahkan ketujuh variabel yang tercantum dalam Mengonfigurasi Paket untuk Menguji Sampel:

    • Ketik nama setiap variabel yang dipisahkan oleh koma.

      -atau-

    • Klik tombol elipsis (...) di samping bidang properti, dan dalam kotak dialog Pilih variabel , memilih variabel.

  5. Klik Edit Skrip untuk membuka editor skrip.

  6. Tambahkan pernyataan Impor untuk namespace Microsoft.VisualBasic dan System.Windows.Forms di bagian atas file skrip.

  7. Tambahkan kode berikut.

  8. Jalankan paket dan periksa hasil yang ditampilkan dalam kotak pesan.

Kode untuk Menampilkan Hasil

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;  
        }  
}

Lihat juga

Memuat data dari atau ke Excel dengan SQL Server Integration Services (SSIS)
Perulangan melalui File dan Tabel Excel dengan Menggunakan Kontainer Perulangan Foreach