Jalankan pertanyaan SQL pada fail Excel

Walaupun tindakan Excel boleh mengendalikan kebanyakan senario automasi Excel, pertanyaan SQL boleh mengambil dan memanipulasi sejumlah besar data Excel dengan lebih cekap.

Katakan aliran perlu mengubah suai hanya pendaftaran Excel yang mengandungi nilai tertentu. Untuk mencapai kefungsian ini tanpa pertanyaan SQL, anda memerlukan gelung, bersyarat dan berbilang tindakan Excel.

Sebagai alternatif, anda boleh melaksanakan fungsi ini dengan pertanyaan SQL menggunakan hanya dua tindakan,Buka sambungan SQL dan Laksanakan penyata SQL.

Membuka sambungan SQL ke fail Excel

Sebelum menjalankan pertanyaan SQL, anda perlu membuka sambungan dengan fail Excel yang anda ingin capai.

Untuk mewujudkan sambungan, cipta pemboleh ubah %Excel_File_Path% baru bernama dan mulakannya dengan laluan fail Excel. Secara pilihan, anda boleh melangkau langkah ini dan menggunakan laluan berkod keras fail kemudian dalam aliran.

Sekarang, gunakan tindakan sambungan Open SQL dan mengisi rentetan sambungan berikut dalam sifatnya.

Pembekal=Microsoft.ACE.OLEDB.12.0; sumber data=%Excel_File_Path%; Hartanah lanjutan="Excel 12.0 xml;HDR=YES";

Nota

Untuk menggunakan rentetan sambungan yang dipersembahkan dengan jayanya, anda perlu memuat turun dan memasang Microsoft Access Database Engine 2010 Redistributable.

Membuka sambungan SQL ke fail Excel dilindungi kata laluan

Pendekatan yang berbeza diperlukan dalam senario di mana anda menjalankan pertanyaan SQL pada fail Excel yang dilindungi kata laluan. Tindakan Buka sambungan SQL tidak boleh bersambung ke fail Excel yang dilindungi kata laluan, jadi anda perlu mengalih keluar perlindungan.

Untuk mencapainya, lancarkan fail Excel menggunakan tindakan Lancarkan Excel . Fail dilindungi kata laluan, jadi masukkan kata laluan yang sesuai dalam medan Kata Laluan .

Seterusnya, gunakan tindakan automasi UI yang sesuai dan navigasi ke Maklumat Fail>Lindungi>Buku>Kerja Sulitkan dengan Kata Laluan. Anda boleh mendapatkan maklumat lanjut tentang automasi UI dan cara menggunakan tindakan masing-masing dalam aplikasi desktop Automate.

Selepas memilih Sulitkan dengan Kata Laluan, isikan rentetan kosong dalam dialog timbul menggunakan medan teks Mengisi dalam tindakan tetingkap . Untuk mengisi rentetan kosong, gunakan ungkapan berikut: %"%.

Untuk menekan butang OK dalam dialog dan gunakan perubahan, gunakan butang Tekan dalam tindakan tetingkap .

Akhir sekali, gunakan tindakan Tutup Excel untuk menyimpan buku kerja tidak dilindungi sebagai fail Excel baru.

Selepas menyimpan fail, ikuti arahan dalam Buka sambungan SQL ke fail Excel untuk membuka sambungan kepadanya.

Apabila manipulasi fail Excel selesai, gunakan tindakan Padam fail untuk memadamkan salinan fail Excel yang tidak dilindungi.

Membaca kandungan hamparan Excel

Walaupun tindakan Baca daripada lembaran kerja Excel boleh membaca kandungan lembaran kerja Excel, gelung boleh mengambil masa yang bererti untuk lelaran melalui data yang diambil.

Cara yang lebih cekap untuk mendapatkan nilai tertentu daripada hamparan adalah dengan merawat fail Excel sebagai pangkalan data dan melaksanakan pertanyaan SQL padanya. Pendekatan ini lebih cepat dan meningkatkan prestasi aliran.

Untuk mengambil semua kandungan hamparan, anda boleh menggunakan pertanyaan SQL berikut dalam tindakan Laksanakan penyata SQL.

SELECT * FROM [SHEET$]

Nota

Untuk menggunakan pertanyaan SQL ini dalam aliran anda, gantikan ruang letak SHEET dengan nama hamparan yang anda ingin capai.

Untuk mengambil baris yang mengandungi nilai tertentu dalam lajur tertentu, gunakan pertanyaan SQL berikut:

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

Nota

Untuk menggunakan pertanyaan SQL ini dalam aliran anda, gantikan:

• HELAIAN dengan nama hamparan yang anda ingin capai.
• NAMA LAJUR dengan lajur yang mengandungi nilai yang anda ingin cari. Lajur dalam baris pertama lembaran kerja Excel dikenal pasti sebagai nama lajur jadual.
• NILAI dengan nilai yang anda ingin cari.

Memadamkan data daripada baris Excel

Walaupun Excel tidak menyokong pertanyaan DELETE SQL, anda boleh menggunakan pertanyaan UPDATE untuk mengesetkan semua sel baris tertentu kepada nol.

Lebih tepat lagi, anda boleh menggunakan pertanyaan SQL berikut:

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

Semasa membangunkan aliran anda, anda perlu menggantikan ruang letak SHEET dengan nama hamparan yang anda ingin capai.

Ruang letak COLUMN1 dan COLUMN2 mewakili nama lajur untuk dikendalikan. Contoh ini mempunyai dua lajur, tetapi dalam senario sebenar, bilangan lajur mungkin berbeza. Lajur dalam baris pertama lembaran kerja Excel dikenal pasti sebagai nama lajur jadual.

Bahagian [COLUMN1]='VALUE'part pertanyaan mentakrifkan baris yang anda ingin kemas kini. Dalam aliran anda, gunakan nama lajur dan nilai berdasarkan gabungan yang menerangkan baris secara unik.

Mengambil data Excel kecuali baris tertentu

Dalam sesetengah senario, anda mungkin perlu mengambil semua kandungan hamparan Excel kecuali baris tertentu.

Cara mudah untuk mencapai matlamat ini adalah dengan menetapkan nilai baris yang tidak diingini kepada nol dan kemudian mengambil semua nilai kecuali yang nol.

Untuk mengubah nilai baris tertentu dalam hamparan, anda boleh menggunakan pertanyaan SQL UPDATE seperti yang dipersembahkan dalam Memadamkan data daripada baris Excel:

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

Seterusnya, jalankan pertanyaan SQL berikut untuk mengambil semua baris hamparan yang tidak mengandungi nilai nol:

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

Ruang letak COLUMN1 dan COLUMN2 mewakili nama lajur untuk dikendalikan. Contoh ini mempunyai dua lajur, tetapi dalam jadual sebenar, bilangan lajur mungkin berbeza. Semua lajur dalam baris pertama lembaran kerja Excel dikenal pasti sebagai nama lajur jadual.