Pokretanje SQL upita u Excel datotekama

Iako Excelove akcije mogu obrađivati većinu scenarija automatizacije programa Excel, SQL upiti mogu učinkovitije dohvatiti i manipulirati značajnim količinama podataka programa Excel.

Pretpostavimo da tijek mora izmijeniti samo Registre programa Excel koji sadrže određenu vrijednost. Da biste postigli ovu funkcionalnost bez SQL upita, potrebne su vam petlje, uvjeti i više Excelovih akcija.

Alternativno, ovu funkcionalnost možete implementirati s SQL upitima koristeći samo dvije akcije, Otvorite SQL vezu i Izvršite SQL naredbe.

Otvaranje SQL veze s datotekom programa Excel

Prije pokretanja SQL upita morate otvoriti vezu s Excel datotekom kojoj želite pristupiti.

Da biste uspostavili vezu, stvorite novu varijablu pod nazivom %Excel_File_Path% i inicijalizirajte je pomoću puta datoteke programa Excel. Po želji možete preskočiti ovaj korak i koristiti tvrdo kodirani put datoteke kasnije tijekom tijeka.

Sada implementirajte akciju Open SQL connection i popunite sljedeći niz za povezivanje u njegovim svojstvima.

Provider=Microsoft.ACE.OLEDB.12.0; izvor podataka=%Excel_File_Path%; Proširena svojstva="Excel 12.0 Xml;HDR=YES";

Napomena

Da biste uspješno koristili predstavljeni niz za povezivanje, morate preuzeti i instalirati modul za redistribuciju sustava Microsoft Access Database Engine 2010.

Otvaranje SQL veze s excel datotekom zaštićenom lozinkom

Drugačiji pristup potreban je u scenarijima u kojima SQL upite izvodite na Excel datotekama zaštićenim lozinkom. Akcija Otvori SQL vezu ne može se povezati s Excel datotekama zaštićenim lozinkom, pa morate ukloniti zaštitu.

Da biste to postigli, pokrenite datoteku programa Excel pomoću akcije Pokreni Excel . Datoteka je zaštićena lozinkom, stoga unesite odgovarajuću lozinku u polje Lozinka .

Zatim implementirajte odgovarajuće akcije automatizacije korisničkog sučelja i idite na Informacije o datoteci>Zaštitite radnu knjigu šifriranje lozinkom>>. Dodatne informacije o automatizaciji korisničkog sučelja i načinu korištenja odgovarajućih radnji možete pronaći u automatizaciji aplikacija za stolna računala.

Nakon što odaberete Šifriraj lozinkom , popunite prazan niz u skočnom dijalogu pomoćuakcije Populiraj tekstno polje u prozoru . Za popunjavanje praznog niza koristite sljedeći izraz: %""%.

Da biste u dijaloškom okviru pritisnuli gumb U redu i primijenili promjene, u akciju prozora implementirajte gumb Pritisnite u prozoru.

Na kraju implementirajte akciju Zatvori Excel da biste nezaštićenu radnu knjigu spremili kao novu datoteku programa Excel .

Nakon spremanja datoteke slijedite upute u odjeljku Otvaranje SQL veze s datotekom programa Excel da biste otvorili vezu s njom.

Kada se manipulacija datotekom programa Excel dovrši, pomoću akcije Izbriši datoteke izbrišite nezaštićenu kopiju datoteke programa Excel.

Čitanje sadržaja proračunske tablice programa Excel

Iako akcija radnog lista Čitanje iz programa Excel može čitati sadržaj radnog lista programa Excel, petlje mogu potrajati značajno vrijeme za ponavljanje dohvaćenih podataka.

Učinkovitiji način dohvaćanja određenih vrijednosti iz proračunskih tablica je tretiranje Excel datoteka kao baza podataka i izvršavanje SQL upita na njima. Ovaj pristup je brži i povećava performanse protoka.

Da biste dohvatili sav sadržaj proračunske tablice, možete koristiti sljedeći SQL upit u akciji Execute SQL command .

SELECT * FROM [SHEET$]

Napomena

Da biste primijenili ovaj SQL upit u tokovima, zamijenite rezervirano mjesto SHEET nazivom proračunske tablice kojoj želite pristupiti.

Za dohvaćanje redaka koji sadrže određenu vrijednost u određenom stupcu koristite sljedeći SQL upit:

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

Napomena

Da biste primijenili ovaj SQL upit u tokovima, zamijenite:

• LIST s nazivom proračunske tablice kojoj želite pristupiti.
• NAZIV STUPCA sa stupcem koji sadrži vrijednost koju želite pronaći. Stupci u prvom retku radnog lista programa Excel identificirani su kao nazivi stupaca tablice.
• VALUE s vrijednošću koju želite pronaći.

Brisanje podataka iz retka programa Excel

Iako Excel ne podržava upit DELETE SQL, upit UPDATE možete koristiti za postavljanje svih ćelija određenog retka na null.

Točnije, možete koristiti sljedeći SQL upit:

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

Tijekom razvoja tijeka morate zamijeniti rezervirano mjesto SHEET nazivom proračunske tablice kojoj želite pristupiti.

Rezervirana mjesta COLUMN1 i COLUMN2 predstavljaju nazive stupaca za rukovanje. Ovaj primjer ima dva stupca, ali u stvarnom scenariju broj stupaca može se razlikovati. Stupci u prvom retku radnog lista programa Excel identificirani su kao nazivi stupaca tablice.

Dio upita[COLUMN1]='VALUE' definira redak koji želite ažurirati. U tijeku koristite naziv stupca i vrijednost na temelju koje kombinacija jedinstveno opisuje retke.

Dohvaćanje podataka programa Excel, osim određenog retka

U nekim ćete scenarijima možda morati dohvatiti sav sadržaj proračunske tablice programa Excel osim određenog retka.

Prikladan način da se to postigne je postavljanje vrijednosti neželjenog retka na null, a zatim dohvaćanje svih vrijednosti osim null vrijednosti.

Da biste promijenili vrijednosti određenog retka u proračunskoj tablici, možete koristiti SQL upit UPDATE , kao što je prikazano u Izbriši podatke iz retka programa Excel:

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

Zatim pokrenite sljedeći SQL upit da biste dohvatili sve retke proračunske tablice koji ne sadrže null vrijednosti:

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

Rezervirana mjesta COLUMN1 i COLUMN2 predstavljaju nazive stupaca za rukovanje. Ovaj primjer ima dva stupca, ali u stvarnoj tablici broj stupaca može se razlikovati. Svi stupci u prvom retku radnog lista programa Excel identificirani su kao nazivi stupaca tablice.