Bagikan melalui

Mengimpor data dari Excel atau mengekspor data ke Excel dengan SQL Server Integration Services (SSIS)

  • Artikel

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

Artikel ini menjelaskan informasi koneksi yang harus Anda berikan, dan pengaturan yang harus Anda konfigurasi, untuk mengimpor data dari Excel atau mengekspor data ke Excel dengan SQL Server Integration Services (SSIS).

Bagian berikut berisi informasi yang anda butuhkan untuk berhasil menggunakan Excel dengan SSIS, dan untuk memahami dan memecahkan masalah umum:

  1. Alat yang dapat Anda gunakan.

  2. File yang Anda butuhkan.

  3. Informasi koneksi yang harus Anda berikan, dan pengaturan yang harus Anda konfigurasi, saat Anda memuat data dari atau ke Excel dengan SSIS.

  4. Masalah dan batasan yang diketahui.

Alat yang dapat Anda gunakan

Anda bisa mengimpor data dari Excel atau mengekspor data ke Excel dengan SSIS dengan menggunakan salah satu alat berikut ini:

Dapatkan file yang perlu Anda sambungkan ke Excel

Sebelum Anda bisa mengimpor data dari Excel atau mengekspor data ke Excel dengan SSIS, Anda mungkin harus mengunduh komponen konektivitas untuk Excel jika belum diinstal. Komponen konektivitas untuk Excel tidak diinstal secara default.

Gunakan tabel dalam Tidak dapat menggunakan antarmuka Access ODBC, OLEDB, atau DAO di luar Office Click-to-Run untuk memahami apakah komponen tambahan diperlukan untuk lingkungan Anda.

Catatan: Driver Sistem Office hanya didukung dalam skenario tertentu, lihat Pertimbangan untuk Automasi Office sisi server untuk panduan tertentu.

Tentukan Excel sebagai sumber data Anda

Langkah pertama adalah menunjukkan bahwa Anda ingin menyambungkan ke Excel.

Dalam SSIS

Di SSIS, buat Pengelola Sambungan Excel untuk menyambungkan ke file sumber atau tujuan Excel. Ada beberapa cara untuk membuat pengelola koneksi:

  • Di area Pengelola Sambungan, klik kanan dan pilih Koneksi baru. Dalam kotak dialog Tambahkan Pengelola Sambungan SSIS, pilih EXCEL lalu Tambahkan.

  • Pada menu SSIS , pilih Koneksi baru. Dalam kotak dialog Tambahkan Pengelola Sambungan SSIS, pilih EXCEL lalu Tambahkan.

  • Buat pengelola koneksi pada saat yang sama saat Anda mengonfigurasi Sumber Excel atau Tujuan Excel pada halaman Pengelola koneksi Editor Sumber Excel atau Editor Tujuan Excel.

Di Wizard Impor dan Ekspor SQL Server

Di Wizard Impor dan Ekspor, pada halaman Pilih Sumber Data atau Pilih Tujuan , pilih Microsoft Excel di daftar Sumber data .

Jika Anda tidak melihat Excel dalam daftar sumber data, pastikan Anda menjalankan wizard 32-bit. Komponen konektivitas Excel biasanya berupa file 32-bit dan tidak terlihat dalam wizard 64-bit.

File Excel dan jalur file

Bagian pertama dari info yang disediakan adalah jalur dan nama file untuk file Excel. Anda menyediakan info ini di Editor Pengelola Sambungan Excel dalam paket SSIS, atau pada halaman Pilih Sumber Data atau Pilih Tujuan wizard Impor dan Ekspor.

Masukkan jalur dan nama file dalam format berikut:

  • Untuk file di komputer lokal, C:\TestData.xlsx.

  • Untuk file di berbagi jaringan, \\Sales\Data\TestData.xlsx.

Atau, klik Telusuri untuk menemukan lembar bentang dengan menggunakan kotak dialog Buka .

Penting

Anda tidak dapat menyambungkan ke file Excel yang dilindungi kata sandi.

Versi Excel

Bagian kedua dari info yang akan disediakan adalah versi file Excel. Anda menyediakan info ini di Editor Pengelola Sambungan Excel dalam paket SSIS, atau pada halaman Pilih Sumber Data atau Pilih Tujuan wizard Impor dan Ekspor.

Pilih versi Microsoft Excel yang digunakan untuk membuat file, atau versi lain yang kompatibel. Misalnya, jika Anda mengalami masalah saat menginstal komponen konektivitas 2016, Anda bisa menginstal komponen 2010 dan memilih Microsoft Excel 2007-2010 dalam daftar ini.

Anda mungkin tidak dapat memilih versi Excel yang lebih baru dalam daftar jika Anda hanya memiliki versi komponen konektivitas yang lebih lama yang terinstal. Daftar versi Excel menyertakan semua versi Excel yang didukung oleh SSIS. Kehadiran item dalam daftar ini tidak menunjukkan bahwa komponen konektivitas yang diperlukan diinstal. Misalnya, Microsoft Excel 2016 muncul dalam daftar meskipun Anda belum menginstal komponen konektivitas 2016.

Baris pertama memiliki nama kolom

Jika Anda mengimpor data dari Excel, langkah selanjutnya adalah menunjukkan apakah baris pertama data berisi nama kolom. Anda menyediakan info ini di Editor Pengelola Sambungan Excel dalam paket SSIS, atau pada halaman Pilih Sumber Data dari Panduan Impor dan Ekspor.

  • Jika Anda menonaktifkan opsi ini karena data sumber tidak berisi nama kolom, wizard menggunakan F1, F2, dan sebagainya, sebagai judul kolom.
  • Jika data berisi nama kolom, tetapi Anda menonaktifkan opsi ini, wizard mengimpor nama kolom sebagai baris pertama data.
  • Jika data tidak berisi nama kolom, tetapi Anda mengaktifkan opsi ini, wizard menggunakan baris pertama data sumber sebagai nama kolom. Dalam hal ini, baris pertama data sumber tidak lagi disertakan dalam data itu sendiri.

Jika Anda mengekspor data dari Excel, dan mengaktifkan opsi ini, baris pertama data yang diekspor menyertakan nama kolom.

Lembar kerja dan rentang

Ada tiga tipe objek Excel yang bisa Anda gunakan sebagai sumber atau tujuan untuk data Anda: lembar kerja, rentang bernama, atau rentang sel yang tidak disebutkan namanya yang Anda tentukan dengan alamatnya.

  • Worksheet. Untuk menentukan lembar kerja, tambahkan $ karakter ke akhir nama lembar dan tambahkan pemisah di sekitar string - misalnya, [Sheet1$]. Atau, cari nama yang diakhir dengan $ karakter dalam daftar tabel dan tampilan yang ada.

  • Rentang bernama. Untuk menentukan rentang bernama, berikan nama rentang - misalnya, MyDataRange. Atau, cari nama yang tidak diakhir $ dengan karakter dalam daftar tabel dan tampilan yang ada.

  • Rentang yang tidak disebutkan namanya. Untuk menentukan rentang sel yang belum Anda beri nama, tambahkan karakter $ ke akhir nama lembar, tambahkan spesifikasi rentang, dan tambahkan pemisah di sekitar string - misalnya, [Sheet1$A1:B4].

Untuk memilih atau menentukan tipe objek Excel yang ingin Anda gunakan sebagai sumber atau tujuan data Anda, lakukan salah satu hal berikut ini:

Dalam SSIS

Di SSIS, pada halaman Pengelola koneksi Editor Sumber Excel atau Editor Tujuan Excel, lakukan salah satu hal berikut ini:

  • Untuk menggunakan lembar kerja atau rentang bernama, pilih Tabel atau tampilkan sebagai mode Akses data. Lalu, di daftar Nama lembar Excel, pilih lembar kerja atau rentang bernama.

  • Untuk menggunakan rentang yang tidak disebutkan namanya yang Anda tentukan dengan alamatnya, pilih perintah SQL sebagai mode Akses data. Kemudian, di bidang teks perintah SQL, masukkan kueri seperti contoh berikut:

    SELECT * FROM [Sheet1$A1:B5]

Di Wizard Impor dan Ekspor SQL Server

Di Wizard Impor dan Ekspor, lakukan salah satu hal berikut ini:

  • Saat Anda mengimpor dari Excel, lakukan salah satu hal berikut ini:

    • Untuk menggunakan lembar kerja atau rentang bernama, pada halaman Tentukan salinan tabel atau kueri , pilih Salin data dari satu atau beberapa tabel atau tampilan. Lalu, pada halaman Pilih Tabel Sumber dan Tampilan , di kolom Sumber , pilih lembar kerja sumber dan rentang bernama.

    • Untuk menggunakan rentang yang tidak disebutkan namanya yang Anda tentukan dengan alamatnya, pada halaman Tentukan salinan tabel atau kueri, pilih Tulis kueri untuk menentukan data yang akan ditransfer. Kemudian, pada halaman Sediakan Kueri Sumber, berikan kueri yang mirip dengan contoh berikut:

      SELECT * FROM [Sheet1$A1:B5]

  • Saat Anda mengekspor ke Excel, lakukan salah satu hal berikut ini:

    • Untuk menggunakan lembar kerja atau rentang bernama, pada halaman Pilih Tabel Sumber dan Tampilan , di kolom Tujuan , pilih lembar kerja tujuan dan rentang bernama.

    • Untuk menggunakan rentang yang tidak disebutkan namanya yang Anda tentukan dengan alamatnya, pada halaman Pilih Tabel Sumber dan Tampilan, di kolom Tujuan, masukkan rentang dalam format berikut tanpa pemisah: Sheet1$A1:B5. Wizard menambahkan pemisah.

Setelah Anda memilih atau memasukkan objek Excel untuk diimpor atau diekspor, Anda juga bisa melakukan hal-hal berikut ini pada halaman Pilih Tabel Sumber dan Tampilan panduan:

  • Tinjau pemetaan kolom antara sumber dan tujuan dengan memilih Edit Pemetaan.

  • Pratinjau data sampel untuk memastikan itu adalah apa yang Anda harapkan dengan memilih Pratinjau.

Masalah dengan jenis data

Jenis data

Driver Excel hanya mengenali sekumpulan tipe data terbatas. Misalnya, semua kolom numerik ditafsirkan sebagai ganda (DT_R8), dan semua kolom string (selain kolom memo) ditafsirkan sebagai string Unicode 255 karakter (DT_WSTR). SSIS memetakan tipe data Excel sebagai berikut:

  • Numerik - float presisi ganda (DT_R8)

  • Mata uang - mata uang (DT_CY)

  • Boolean - Boolean (DT_BOOL)

  • Tanggal/waktu - tanggalwaktu (DT_DATE)

  • String - Untai (karakter) Unicode, panjang 255 (DT_WSTR)

  • Memo - Aliran teks Unicode (DT_NTEXT)

Jenis data dan konversi panjang

SSIS tidak mengonversi jenis data secara implisit. Akibatnya, Anda mungkin harus menggunakan transformasi Kolom Turunan atau Konversi Data untuk mengonversi data Excel secara eksplisit sebelum memuatnya ke tujuan selain Excel, atau untuk mengonversi data dari sumber selain Excel sebelum memuatnya ke tujuan Excel.

Berikut adalah beberapa contoh konversi yang mungkin diperlukan:

  • Konversi antara kolom string Excel Unicode dan kolom string non-Unicode dengan halaman kode tertentu.

  • Konversi antara kolom string Excel 255 karakter dan kolom string dengan panjang yang berbeda.

  • Konversi antara kolom numerik Excel presisi ganda dan kolom numerik dari jenis lain.

Tip

Jika Anda menggunakan Wizard Impor dan Ekspor, dan data Anda memerlukan beberapa konversi ini, wizard mengonfigurasi konversi yang diperlukan untuk Anda. Akibatnya, bahkan ketika Anda ingin menggunakan paket SSIS, mungkin berguna untuk membuat paket awal dengan menggunakan Wizard Impor dan Ekspor. Biarkan wizard membuat dan mengonfigurasi manajer koneksi, sumber, transformasi, dan tujuan untuk Anda.

Masalah dengan mengimpor

Baris kosong

Saat Anda menentukan lembar kerja atau rentang bernama sebagai sumber, pengandar membaca blok sel yang bersebelahan dimulai dengan sel pertama yang tidak kosong di sudut kiri atas lembar kerja atau rentang. Akibatnya, data Anda tidak harus dimulai di baris 1, tetapi Anda tidak dapat memiliki baris kosong dalam data sumber. Misalnya, Anda tidak bisa memiliki baris kosong antara header kolom dan baris data, atau judul diikuti dengan baris kosong di bagian atas lembar kerja.

Jika ada baris kosong di atas data Anda, Anda tidak bisa mengkueri data sebagai lembar kerja. Di Excel, Anda harus memilih rentang data Anda dan menetapkan nama ke rentang, lalu mengkueri rentang bernama, bukan lembar kerja.

Nilai yang hilang

Driver Excel membaca sejumlah baris tertentu (secara default, delapan baris) di sumber yang ditentukan untuk menebak tipe data setiap kolom. Ketika kolom tampaknya berisi tipe data campuran, terutama data numerik yang dicampur dengan data teks, driver memutuskan untuk mendukung jenis data mayoritas, dan mengembalikan nilai null untuk sel yang berisi data jenis lain. (Dalam dasi, jenis numerik menang.) Sebagian besar opsi pemformatan sel di lembar kerja Excel tampaknya tidak memengaruhi penentuan tipe data ini.

Anda bisa mengubah perilaku pengandar Excel ini dengan menentukan Mode Impor untuk mengimpor semua nilai sebagai teks. Untuk menentukan Mode Impor, tambahkan IMEX=1 ke nilai Properti yang Diperluas di string koneksi pengelola koneksi Excel di jendela Properti.

Teks terpotong

Ketika driver menentukan bahwa kolom Excel berisi data teks, driver memilih jenis data (string atau memo) berdasarkan nilai terpanjang yang diambil sampelnya. Jika driver tidak menemukan nilai apa pun yang lebih panjang dari 255 karakter dalam baris yang diambil sampelnya, driver akan memperlakukan kolom sebagai kolom string 255 karakter, bukan kolom memo. Oleh karena itu, nilai yang lebih panjang dari 255 karakter dapat dipotong.

Untuk mengimpor data dari kolom memo tanpa pemotongan, Anda memiliki dua opsi:

  • Pastikan bahwa kolom memo di setidaknya salah satu baris sampel berisi nilai yang lebih panjang dari 255 karakter

  • Tingkatkan jumlah baris yang diambil sampelnya oleh driver untuk menyertakan baris tersebut. Anda dapat meningkatkan jumlah baris yang diambil sampelnya dengan meningkatkan nilai TypeGuessRows di bawah kunci registri berikut:

Versi komponen yang dapat didistribusikan ulang Kunci Registri
Excel 2016 HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel
Excel 2010 HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

Masalah dengan ekspor

Membuat file tujuan baru

Dalam SSIS

Buat Pengelola Sambungan Excel dengan jalur dan nama file file Excel baru yang ingin Anda buat. Lalu, di Editor Tujuan Excel, untuk Nama lembar Excel, pilih Baru untuk membuat lembar kerja tujuan. Pada titik ini, SSIS membuat file Excel baru dengan lembar kerja yang ditentukan.

Di Wizard Impor dan Ekspor SQL Server

Pada halaman Pilih Tujuan , pilih Telusuri. Dalam kotak dialog Buka , navigasi ke folder tempat Anda ingin file Excel baru dibuat, berikan nama untuk file baru, lalu pilih Buka.

Ekspor ke rentang yang cukup besar

Saat Anda menentukan rentang sebagai tujuan, kesalahan terjadi jika rentang memiliki lebih sedikit kolom daripada data sumber. Namun, jika rentang yang Anda tentukan memiliki lebih sedikit baris daripada data sumber, wizard terus menulis baris tanpa kesalahan dan memperluas definisi rentang agar sesuai dengan jumlah baris baru.

Mengekspor nilai teks panjang

Sebelum berhasil menyimpan string yang lebih panjang dari 255 karakter ke kolom Excel, driver harus mengenali tipe data kolom tujuan sebagai memo dan bukan string.

  • Jika tabel tujuan yang sudah ada sudah berisi baris data, maka beberapa baris pertama yang diambil sampelnya oleh driver harus berisi setidaknya satu instans nilai yang lebih panjang dari 255 karakter di kolom memo.

Konten terkait

Untuk informasi selengkapnya tentang komponen dan prosedur yang dijelaskan dalam artikel ini, lihat artikel berikut ini:

Tentang SSIS

Pengelola Koneksi Excel
Sumber Excel
Tujuan Excel
Mengulangi File dan Tabel Excel dengan Menggunakan Kontainer Perulangan Foreach
Bekerja dengan File Excel dengan Tugas Skrip

Tentang Wizard Impor dan Ekspor SQL Server

Menyambungkan ke Sumber Data Excel
Mulai menggunakan contoh sederhana Panduan Impor dan Ekspor ini

Mengimpor data dari Excel ke SQL Server atau Azure SQL Database