SQL-query's uitvoeren op Excel-bestanden
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. Om deze functionaliteit te bereiken zonder SQL-query's, hebt u lussen, voorwaarden en meerdere Excel-acties nodig.
Omgekeerd kunt u deze functionaliteit met SQL-query's implementeren met slechts twee acties, de Open SQL-verbinding actie en de SQL-instructies uitvoeren actie.
Open een SQL-verbinding met een Excel-bestand
Voordat u een SQL-query uitvoert, moet u een verbinding openen met het Excel-bestand dat u wilt openen.
Om de verbinding tot stand te brengen, maakt u een nieuwe variabele met de naam %Excel_File_Path% en initialiseer het met het Excel-bestandspad. Optioneel kunt u deze stap overslaan en het hardgecodeerde pad van het bestand later in de stroom gebruiken.

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";
Note
Als u de gepresenteerde verbindingsreeks met succes wilt gebruiken, moet u Microsoft Access Database Engine 2010 Redistributable downloaden en installeren.

Open een SQL-verbinding met 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 Start Excel actie. Het bestand is beveiligd met een wachtwoord, dus vul het juiste wachtwoord in het veld Wachtwoord.

Implementeer vervolgens de juiste UI-automatiseringsacties en navigeer naar: Bestand > Info > Werkmap beveiligen > Versleutelen met wachtwoord. U kunt meer informatie vinden over UI-automatisering en het gebruik van de respectievelijke acties in Bureaubladstromen automatiseren.

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

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.

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

Volg na het opslaan van het bestand de instructies in Open een SQL-verbinding met Excel-bestanden om er een verbinding mee te openen.
Wanneer het bewerken van het Excel-bestand is voltooid, gebruikt u de actie Bestand(en) verwijderen om de niet-beveiligde kopie van het Excel-bestand te verwijderen.

Inhoud van een Excel-spreadsheet lezen
Hoewel de Lezen van Excel-werkblad actie de inhoud van een Excel-werkblad kan lezen, kan het veel tijd kosten om lussen door de opgehaalde gegevens te bladeren.
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-instructies uitvoeren.
SELECT * FROM [SHEET$]

Note
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'
Note
Als u deze SQL-query in uw stromen wilt toepassen, vervangt u:
- BLAD met 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 met de waarde die u wilt vinden
Gegevens uit Excel-werkbladrij verwijderen
Hoewel Excel de SQL-query DELETE niet ondersteunt, kunt u de query UPDATE gebruiken om alle cellen van een specifieke rij op nul in te stellen.
Om precies te zijn, kunt u de volgende SQL-query gebruiken:
UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'

Terwijl u uw stroom onwikkelt, vervangt u de tijdelijke aanduiding BLAD door de naam van de spreadsheet die u wilt openen.
De KOLOM1 en KOLOM2 tijdelijke aanduidingen vertegenwoordigen de namen van alle bestaande kolommen. In dit voorbeeld zijn de kolommen twee, maar in een echt scenario 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.
Een handige manier om dit resultaat te bereiken, is door de waarden van de ongewenste rij op nul in te stellen en vervolgens alle waarden op te halen, behalve de nul-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'

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
Note
De KOLOM1 en KOLOM2 tijdelijke aanduidingen vertegenwoordigen de namen van alle bestaande kolommen. In dit voorbeeld zijn de kolommen twee, 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.