Menjalankan kueri SQL pada file Excel

Meskipun tindakan Excel dapat menangani sebagian besar skenario otomatisasi Excel, kueri SQL dapat mengambil dan memanipulasi sejumlah besar data Excel secara lebih efisien.

Misalkan alur harus memodifikasi hanya registri Excel yang berisi nilai tertentu. Untuk mencapai fungsionalitas ini tanpa kueri SQL, Anda memerlukan loop, kondisional, dan beberapa tindakan Excel.

Atau, Anda dapat menerapkan fungsi ini dengan kueri SQL hanya menggunakan dua tindakan, Buka koneksi SQL dan Jalankan pernyataan SQL.

Membuka koneksi SQL ke file Excel

Sebelum menjalankan kueri SQL, Anda harus membuka koneksi dengan file Excel yang ingin Anda akses.

Untuk membuat koneksi, buat variabel baru bernama %Excel_File_Path% dan inisialisasi dengan jalur file Excel. Secara opsional, Anda dapat melewati langkah ini dan menggunakan jalur hard code file nanti dalam alur.

Sekarang, sebarkan tindakan koneksi Open SQL dan isi string koneksi berikut di propertinya.

Penyedia = Microsoft.ACE.OLEDB.12.0; sumber data=%Excel_File_Path%; Properti yang Diperluas = "Excel 12.0 xml; HDR = YA";

Catatan

Untuk menggunakan string koneksi yang disajikan berhasil, Anda harus mengunduh dan menginstal Microsoft Access Database Engine 2010 Redistributable.

Membuka koneksi SQL ke file Excel yang dilindungi kata sandi

Pendekatan yang berbeda diperlukan dalam skenario di mana Anda menjalankan kueri SQL pada file Excel yang dilindungi kata sandi. Tindakan Buka koneksi SQL tidak dapat tersambung ke file Excel yang dilindungi kata sandi, jadi Anda harus menghapus proteksi.

Untuk mencapai itu, luncurkan file Excel menggunakan tindakan Luncurkan Excel . File dilindungi kata sandi, jadi masukkan kata sandi yang sesuai di bidang Kata Sandi .

Selanjutnya, sebarkan tindakan otomatisasi UI yang sesuai dan navigasikan ke Enkripsi Buku>Kerja Proteksi Info>File>dengan Kata Sandi. Anda dapat menemukan informasi selengkapnya tentang otomatisasi UI dan cara menggunakan tindakan masing-masing dalam aplikasi desktop Automasi.

Setelah memilih Enkripsi dengan Kata Sandi, isi string kosong di dialog pop-up menggunakan tindakan Isi bidang teks di jendela . Untuk mengisi string kosong, gunakan ekspresi berikut: %""%.

Untuk menekan tombol OK dalam dialog dan menerapkan perubahan, gunakan tombol Tekan di tindakan jendela .

Terakhir, gunakan tindakan Tutup Excel untuk menyimpan buku kerja yang tidak dilindungi sebagai file Excel baru.

Setelah menyimpan file, ikuti instruksi dalam Membuka koneksi SQL ke file Excel untuk membuka koneksi ke file.

Ketika manipulasi berkas Excel selesai, gunakan tindakan Hapus berkas untuk menghapus salinan berkas Excel yang tidak dilindungi.

Membaca konten lembar bentang Excel

Meskipun tindakan Baca dari lembar kerja Excel dapat membaca konten lembar kerja Excel, loop dapat mengambil waktu yang signifikan untuk mengulangi melalui data yang diambil.

Cara yang lebih efisien untuk mengambil nilai tertentu dari spreadsheet adalah memperlakukan file Excel sebagai database dan mengeksekusi kueri SQL pada mereka. Pendekatan ini lebih cepat dan meningkatkan kinerja aliran.

Untuk mengambil semua konten lembar bentang, Anda dapat menggunakan kueri SQL berikut dalam tindakan Jalankan pernyataan SQL.

SELECT * FROM [SHEET$]

Catatan

Untuk menerapkan kueri SQL ini di alur Anda, ganti tempat penampung SHEET dengan nama lembar bentang yang ingin Anda akses.

Untuk mengambil baris yang berisi nilai tertentu dalam kolom tertentu, gunakan kueri SQL berikut:

SELECT * FROM [SHEET$] WHERE [COLUMN NAME] = 'VALUE'

Catatan

Untuk menerapkan kueri SQL ini di alur Anda, ganti:

• SHEET dengan nama spreadsheet yang ingin Anda akses.
• NAMA KOLOM dengan kolom yang berisi nilai yang ingin Anda temukan. Kolom di baris pertama lembar kerja Excel diidentifikasi sebagai nama kolom tabel.
• VALUE dengan nilai yang ingin Anda temukan.

Menghapus data dari baris Excel

Meskipun Excel tidak mendukung kueri DELETE SQL, Anda bisa menggunakan kueri UPDATE untuk mengatur semua sel dari baris tertentu menjadi null.

Lebih tepatnya, Anda dapat menggunakan kueri SQL berikut:

UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'

Saat mengembangkan alur, Anda harus mengganti placeholder SHEET dengan nama spreadsheet yang ingin Anda akses.

Tempat penampung COLUMN1 dan COLUMN2 mewakili nama kolom yang akan ditangani. Contoh ini memiliki dua kolom, tetapi dalam skenario nyata, jumlah kolom mungkin berbeda. Kolom di baris pertama lembar kerja Excel diidentifikasi sebagai nama kolom tabel.

Bagian [COLUMN1]='VALUE' dari kueri menentukan baris yang ingin Anda perbarui. Dalam alur Anda, gunakan nama kolom dan nilai berdasarkan kombinasi mana yang menjelaskan baris secara unik.

Mengambil data Excel kecuali untuk baris tertentu

Dalam beberapa skenario, Anda mungkin perlu mengambil semua konten spreadsheet Excel kecuali untuk baris tertentu.

Cara mudah untuk mencapai ini adalah dengan mengatur nilai baris yang tidak diinginkan ke null dan kemudian mengambil semua nilai kecuali yang null.

Untuk mengubah nilai baris tertentu di spreadsheet, Anda dapat menggunakan kueri SQL UPDATE , seperti yang disajikan dalam Menghapus data dari baris Excel:

UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'

Selanjutnya, jalankan kueri SQL berikut untuk mengambil semua baris lembar bentang yang tidak berisi nilai null:

SELECT * FROM [SHEET$] WHERE [COLUMN1] IS NOT NULL OR [COLUMN2] IS NOT NULL

Tempat penampung COLUMN1 dan COLUMN2 mewakili nama kolom yang akan ditangani. Contoh ini memiliki dua kolom, tetapi dalam tabel nyata, jumlah kolom mungkin berbeda. Semua kolom di baris pertama lembar kerja Excel diidentifikasi sebagai nama kolom tabel.