Rularea interogărilor SQL pe fișiere Excel

Deși acțiunile Excel pot gestiona majoritatea scenariilor de automatizare Excel, interogările SQL pot prelua și manipula cantități semnificative de date Excel mai eficient.

Să presupunem că un flux trebuie să modifice numai registrele Excel care conțin o anumită valoare. Pentru a realiza această funcționalitate fără interogări SQL, aveți nevoie de bucle, condiționale și mai multe acțiuni Excel.

Alternativ, puteți implementa această funcționalitate cu interogări SQL folosind doar două acțiuni, Deschideți conexiunea SQL și Executați instrucțiuni SQL.

Deschideți o conexiune SQL la un fișier Excel

Înainte de a rula o interogare SQL, trebuie să deschideți o conexiune cu fișierul Excel pe care doriți să-l accesați.

Pentru a stabili conexiunea, creați o nouă variabilă numită %Excel_File_Path% și inițializați-o cu calea fișierului Excel. Opțional, puteți sări peste acest pas și să utilizați calea codificată a fișierului mai târziu în flux.

Acum, implementați acțiunea Open SQL connection și completați următorul șir de conexiune în proprietățile sale.

Provider=Microsoft.ACE.OLEDB.12.0;sursă de date=%Excel_File_Path%;Extended Properties="Excel 12.0 Xml;HDR=YES";

Notă

Pentru a utiliza cu succes șirul de conexiune prezentat, trebuie să descărcați și să instalați Microsoft Access Database Engine 2010 Redistributable.

Deschideți o conexiune SQL la un fișier Excel protejat prin parolă

O abordare diferită este necesară în scenariile în care rulați interogări SQL pe fișiere Excel protejate cu parolă. Acțiunea Open SQL connection nu se poate conecta la fișiere Excel protejate prin parolă, așa că trebuie să eliminați protecția.

Pentru a realiza acest lucru, lansați fișierul Excel utilizând acțiunea Lans Excel . Fișierul este protejat prin parolă, așa că introduceți parola corespunzătoare în câmpul Parolă .

Apoi, implementați acțiunile corespunzătoare de automatizare a interfeței de utilizare și navigați la Fișier>Informații>Protect Workbook>Criptați cu parolă. Puteți găsi mai multe informații despre automatizarea UI și despre cum să utilizați acțiunile respective în Automatizarea aplicațiilor desktop.

După ce selectați Criptați cu parolă, completați un șir gol în caseta de dialog pop-up utilizând Populați câmpul de text din fereastră actiune. Pentru a completa un șir gol, utilizați următoarea expresie: %""%.

Pentru a apăsa butonul OK din caseta de dialog și a aplica modificările, implementați acțiunea Apăsați butonul în fereastră .

În cele din urmă, implementați acțiunea Închideți Excel pentru a salva registrul de lucru neprotejat ca un nou fișier Excel.

După salvarea fișierului, urmați instrucțiunile din Deschideți o conexiune SQL la un fișier Excel pentru a deschide o conexiune la acesta.

Când manipularea fișierului Excel este completă, utilizați acțiunea Ștergeți fișierele pentru a șterge copia neprotejată a fișierului Excel.

Citiți conținutul unei foi de calcul Excel

Deși acțiunea Citiți din foaia de lucru Excel poate citi conținutul unei foi de lucru Excel, buclele pot dura mult timp pentru a repeta prin datele preluate.

O modalitate mai eficientă de a prelua anumite valori din foile de calcul este de a trata fișierele Excel ca baze de date și de a executa interogări SQL pe ele. Această abordare este mai rapidă și crește performanța fluxului.

Pentru a prelua tot conținutul unei foi de calcul, puteți utiliza următoarea interogare SQL în acțiunea Execut SQL statement .

SELECT * FROM [SHEET$]

Notă

Pentru a aplica această interogare SQL în fluxurile dvs., înlocuiți SHEET substituent cu numele foii de calcul pe care doriți să o accesați.

Pentru a prelua rândurile care conțin o anumită valoare într-o anumită coloană, utilizați următoarea interogare SQL:

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

Notă

Pentru a aplica această interogare SQL în fluxurile dvs., înlocuiți:

• SHEET cu numele foii de calcul pe care doriți să o accesați.
• COLUMN NAME cu coloana care conține valoarea pe care doriți să o găsiți. Coloanele din primul rând al foii de lucru Excel sunt identificate ca nume de coloane ale tabelului.
• VALUE cu valoarea pe care doriți să o găsiți.

Ștergeți datele dintr-un rând Excel

Deși Excel nu acceptă interogarea DELETE SQL, puteți utiliza interogarea UPDATE pentru a seta toate celulele a unui anumit rând la nul.

Mai precis, puteți utiliza următoarea interogare SQL:

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

În timp ce vă dezvoltați fluxul, trebuie să înlocuiți substituentul SHEET cu numele foii de calcul pe care doriți să o accesați.

Substituenții COLUMN1 și COLUMN2 reprezintă numele coloanelor de tratat. Acest exemplu are două coloane, dar într-un scenariu real, numărul coloanelor poate diferi. Coloanele din primul rând al foii de lucru Excel sunt identificate ca nume de coloane ale tabelului.

Partea [COLUMN1]='VALUE' partea interogării definește rândul pe care doriți să îl actualizați. În fluxul dvs., utilizați numele coloanei și valoarea în funcție de combinația care descrie rândurile în mod unic.

Preluați datele Excel, cu excepția unui anumit rând

În unele scenarii, poate fi necesar să preluați tot conținutul unei foi de calcul Excel, cu excepția unui anumit rând.

O modalitate convenabilă de a realiza acest lucru este să setați valorile rândului nedorit la nul și apoi să preluați toate valorile, cu excepția celor nule.

Pentru a modifica valorile unui anumit rând din foaia de calcul, puteți utiliza o UPDATE interogare SQL, așa cum este prezentată în Ștergerea datelor dintr-un rând Excel:

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

Apoi, rulați următoarea interogare SQL pentru a prelua toate rândurile foii de calcul care nu conțin valori nule:

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

Substituenții COLUMN1 și COLUMN2 reprezintă numele coloanelor de tratat. Acest exemplu are două coloane, dar într-un tabel real, numărul coloanelor poate diferi. Toate coloanele din primul rând al foii de lucru Excel sunt identificate ca nume de coloane ale tabelului.