SQL-query's uitvoeren op Excel-bestanden

  • Artikel

Hoewel Excel-acties de meeste Excel-automatiseringsscenario's aankunnen, kunnen SQL-query's aanzienlijke hoeveelheden Excel-gegevens efficiënter ophalen en manipuleren.

Stel dat een stroom alleen de Excel-registers hoeft te wijzigen die een bepaalde waarde bevatten. Als u deze functionaliteit wilt bereiken zonder SQL-query's, hebt u lussen, voorwaarden en meerdere Excel-acties nodig.

U kunt deze functionaliteit ook implementeren met SQL-query's met slechts twee acties, SQL-verbinding openen en SQL-instructies uitvoeren.

Een SQL-verbinding met een Excel-bestand openen

Voordat u een SQL-query uitvoert, moet u een verbinding openen met het Excel-bestand waartoe u toegang wilt krijgen.

U maakt de verbinding door een nieuwe variabele met de naam %Excel_File_Path% te maken en deze te initialiseren met het Excel-bestandspad. U kunt deze stap ook overslaan en het in code vastgelegde pad van het bestand later in de stroom gebruiken.

Schermopname van de actie Variabele instellen gevuld met het Excel-bestandspad.

Implementeer nu de Open SQL-verbinding actie en vul de volgende verbindingsreeks in de eigenschappen ervan.

Provider=Microsoft.ACE.OLEDB.12.0;gegevensbron=%Excel_File_Path% ;Extended Properties="Excel 12.0 Xml;HDR=JA";

Notitie

Als u de gepresenteerde verbindingsreeks met succes wilt gebruiken, moet u Microsoft Access Database Engine 2010 Redistributable downloaden en installeren.

Schermopname van de actie SQL-verbinding openen.

Een SQL-verbinding openen naar een met een wachtwoord beveiligd Excel-bestand

Een andere benadering is vereist in scenario's waarin u SQL-query's uitvoert op met een wachtwoord beveiligde Excel-bestanden. De Open SQL-verbinding action kan geen verbinding maken met met een wachtwoord beveiligde Excel-bestanden, dus u moet de beveiliging verwijderen.

Om dat te bereiken start u het Excel-bestand met de actie Excel starten. Het bestand is beveiligd met een wachtwoord, dus voert u het juiste wachtwoord in het veld Wachtwoord in.

Schermopname van de actie Excel starten en het veld Wachtwoord.

Implementeer vervolgens de juiste UI-automatiseringsacties en navigeer naar: Bestand>Info>Werkmap beveiligen>Versleutelen met wachtwoord. Ga voor meer informatie over UI-automatisering en het gebruik van de respectieve acties naar Bureaubladtoepassingen automatiseren.

Schermopname van de UI-acties die zijn gebruikt om de optie Versleutelen met wachtwoord te selecteren.

Na het selecteren van Versleutelen met wachtwoord vult u een lege tekenreeks in het pop-upvenster in met behulp van de actie Tekstveld vullen in venster. Gebruik de volgende expressie om een lege tekenreeks in te vullen: %""%.

Schermopname van de actie Tekstveld vullen in venster.

Als u op de knop OK wilt drukken in het dialoogvenster en de wijzigingen wilt toepassen, implementeert u de actie Op een knop in een venster drukken.

Schermopname van de actie Druk op de knop in het venster.

Implementeer tot slot de actie Excel sluiten om de niet-beveiligde werkmap op te slaan als een nieuw Excel-bestand.

Schermopname van de actie Excel sluiten met de optie Document opslaan als geselecteerd.

Volg na het opslaan van het bestand de instructies in Een SQL-verbinding naar een Excel-bestand openen om er een verbinding mee te openen.

Wanneer het manipuleren van het Excel-bestand is voltooid, gebruikt u de actie Bestand(en) verwijderen om de niet-beveiligde kopie van het Excel-bestand te verwijderen.

Schermopname van de actie Bestand(en) verwijderen.

Inhoud van een Excel-spreadsheet lezen

Hoewel de actie Lezen uit Excel-werkblad de inhoud van een Excel-werkblad kan lezen, kan het voor lussen veel tijd in beslag nemen om door de opgehaalde gegevens te itereren.

Een efficiëntere manier om specifieke waarden uit spreadsheets op te halen, is door Excel-bestanden als databases te behandelen en daarop SQL-query's uit te voeren. Deze aanpak is sneller en verhoogt de prestaties van de stroom.

Als u alle inhoud van een spreadsheet wilt ophalen, kunt u de volgende SQL-query gebruiken in de actie SQL-instructie uitvoeren.

SELECT * FROM [SHEET$]

Schermopname van SQL-instructies uitvoeren, gevuld met een SELECT-query.

Notitie

Als u deze SQL-query in uw stromen wilt toepassen, vervangt u de tijdelijke aanduiding BLAD door de naam van de spreadsheet die u wilt openen.

Gebruik de volgende SQL-query om de rijen op te halen die een bepaalde waarde in een specifieke kolom bevatten:

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

Notitie

Als u deze SQL-query in uw stromen wilt toepassen, vervangt u:

  • BLAD door de naam van de spreadsheet die u wilt openen.
  • KOLOMNAAM met de kolom die de waarde bevat die u wilt vinden. De kolommen in de eerste rij van het Excel-werkblad worden geïdentificeerd als de kolomnamen van de tabel.
  • WAARDE door de waarde die u wilt vinden.

Gegevens uit een Excel-rij verwijderen

Hoewel Excel de SQL-query DELETE niet ondersteunt, kunt u de query UPDATE gebruiken om alle cellen van een specifieke rij op null in te stellen.

Om precies te zijn: u kunt de volgende SQL-query gebruiken:

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

Schermopname van SQL-instructies uitvoeren, gevuld met een UPDATE-query.

Bij het ontwikkelen van de stroom vervangt u de tijdelijke aanduiding BLAD door de naam van de spreadsheet die u wilt openen.

De tijdelijke aanduidingen KOLOM1 en KOLOM2 vertegenwoordigen de namen van de kolommen die moeten worden verwerkt. Dit voorbeeld heeft twee kolommen, maar in het echt kan het aantal kolommen verschillen. De kolommen in de eerste rij van het Excel-werkblad worden geïdentificeerd als de kolomnamen van de tabel.

Het deel [KOLOM1]='WAARDE' van de query definieert de rij die u wilt bijwerken. Gebruik in uw stroom de kolomnaam en de waarde op basis van welke combinatie de rijen uniek beschrijft.

Excel-gegevens ophalen, behalve voor een specifieke rij

In sommige scenario's moet u mogelijk alle inhoud van een Excel-spreadsheet ophalen, behalve van een specifieke rij.

U kunt dit gemakkelijk doen door de waarden van de ongewenste rij op null in te stellen en vervolgens alle waarden op te halen, behalve de null-waarden.

ALs u de waarden van een specifieke rij in de spreadsheet wilt wijzigen, kunt u een SQL-query UPDATE gebruiken, zoals gepresenteerd in Gegevens uit Excel-werkbladrij verwijderen:

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

Schermopname van SQL-instructies uitvoeren gevuld met een UPDATE-query.

Voer vervolgens de volgende SQL-query uit om alle rijen van de spreadsheet op te halen die geen nul-waarden bevatten:

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

De tijdelijke aanduidingen KOLOM1 en KOLOM2 vertegenwoordigen de namen van de kolommen die moeten worden verwerkt. Dit voorbeeld heeft twee kolommen, maar in een echte tabel kan het aantal kolommen verschillen. Alle kolommen in de eerste rij van het Excel-werkblad worden geïdentificeerd als de kolomnamen van de tabel.