Køre SQL-forespørgsler på Excel-filer

Selvom Excel-handlinger kan håndtere de fleste automatiseringsscenarier i Excel, kan SQL-forespørgsler hente og manipulere store mængder Excel-data mere effektivt.

Antag, at et flow kun skal ændre de Excel-regisposter, der indeholder en bestemt værdi. Hvis du vil opnå denne funktion uden SQL-forespørgsler, skal du bruge løkker, betingelser og flere Excel-handlinger.

Du kan også vælge at anvende denne funktionalitet med SQL-forespørgsler med kun to handlinger – Åbn SQL-forbindelse og Udfør SQL-sætninger.

Åbn en SQL-forbindelse til en Excel-fil

Før du kører en SQL-forespørgsel, skal du åbne en forbindelse til den Excel-fil, du vil have adgang til.

For at oprette forbindelse skal du oprette en ny variabel, der kaldes %Excel_File_Path%, og initialisere den med stien til Excel-filen. Du kan også springe dette trin over og bruge den hard-coded sti til filen senere i flowet.

Installer nu handlingen Åbn SQL-forbindelse, og udfyld følgende forbindelsesstreng i egenskaberne.

Provider=Microsoft.ACE.OLEDB.12.0;datakilde=%Excel_File_Path%;Udvidede egenskaber="Excel 12.0 Xml;HDR=Ja";

Bemærk

Hvis du vil bruge den angivne forbindelsesstreng korrekt, skal du hente og installere Microsoft Access Database Engine 2010 Redistributable.

Åbne en SQL-forbindelse til en adgangskodebeskyttet Excel-fil

Der kræves en anden fremgangsmåde i scenarier, hvor du kører SQL-forespørgsler på adgangskodebeskyttede Excel-filer. Handlingen Åbn SQL-forbindelse kan ikke oprette forbindelse til adgangskodebeskyttede Excel-filer, så du skal fjerne beskyttelsen.

Det kan du gøre ved at starte Excel-filen ved hjælp af handlingen Start Excel. Filen er beskyttet med adgangskode, så du skal angive den korrekte adgangskode i feltet Adgangskode.

Derefter skal du implementere de relevante handlinger til automatisering af brugergrænsefladen og navigere til Filer>Oplysninger>Beskyt projektmappe>Krypter med adgangskode. Du kan finde flere oplysninger om automatisering af brugergrænsefladen og om, hvordan du kan bruge de respektive handlinger, i Automatisere skrivebordsapplikationer.

Når du har valgt Krypter med adgangskode, skal du udfylde en tom streng i pop op-dialogboksen ved hjælp af handlingen Udfyld tekstfelt i vindue. Hvis du vil udfylde en tom streng, skal du bruge følgende udtryk: %""%.

Hvis du vil trykke på OK-knappen i dialogboksen og anvende ændringerne, skal du installere udrulle handlingen Tryk på knappen i vindue.

Endelig skal du udrulle handlingen Luk Excel for at gemme den ikke-beskyttede projektmappe som en ny Excel-fil.

Når du har gemt filen, skal du følge instruktionerne i Åbn en SQL-forbindelse til en Excel-fil for at åbne en forbindelse til den.

Når manipulationen af Excel-filen er fuldført, skal du bruge handlingen Slet fil(er) til at slette den ikke-beskyttede kopi af Excel-filen.

Læs indholdet af et Excel-regneark

Selvom handlingen Læs fra Excel-regneark kan læse indholdet af et Excel-regneark, kan det tage lang tid at læse løkker gennem de hentede data.

En mere effektiv måde at hente bestemte værdier fra regneark på er at behandle Excel-filer som databaser og udføre SQL-forespørgsler på dem. Denne fremgangsmåde er hurtigere og øger flowet.

Hvis du vil hente alt indholdet i et regneark, kan du bruge følgende SQL-forespørgsel i handlingen Udfør SQL-sætning.

SELECT * FROM [SHEET$]

Bemærk

Hvis du vil anvende denne SQL-forespørgsel i flowet, skal du erstatte pladsholderen ARK med navnet på det regneark, du vil have adgang til.

Hvis du vil hente de rækker, der indeholder en bestemt værdi i en bestemt kolonne, skal du bruge følgende SQL-forespørgsel:

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

Bemærk

Hvis du vil anvende denne SQL-forespørgsel i dine flow, skal du erstatte følgende:

• ARK med navnet på det regneark, du vil have adgang til.
• KOLONNENAVN med den kolonne, der indeholder den værdi, du vil søge efter. Kolonnerne i første række i Excel-regnearket identificeres som kolonnenavnene i tabellen.
• VÆRDI med den værdi, du vil søge efter.

Slet data fra en Excel-række

Selvom Excel ikke understøtter SQL-forespørgslen DELETE, kan du bruge UPDATE-forespørgslen til at angive alle cellerne i en bestemt række til null.

Helt konkret kan du bruge følgende SQL-forespørgsel:

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

Mens du udvikler dit flow, skal du erstatte pladsholderen ARK med navnet på det regneark, du vil have adgang til.

Pladsholderne COLUMN1 og COLUMN2 repræsenterer navnene på alle de kolonner, der skal håndteres. I dette eksempel er der to kolonner, men i et virkeligt scenarie kan der være et andet antal kolonner. Kolonnerne i første række i Excel-regnearket identificeres som kolonnenavnene i tabellen.

I [COLUMN1]='VALUE'-delen af forespørgslen defineres den række, du vil opdatere. Brug kolonnenavnet og værdien i flowet, afhængigt af hvilken kombination der beskriver rækkerne entydigt.

Hente Excel-data med undtagelse af en bestemt række

I nogle scenarier skal du muligvis hente alt indholdet i et Excel-regneark med undtagelse af en bestemt række.

En praktisk måde at gøre det på er at angive værdierne for den uønskede række til null og derefter hente alle værdierne undtagen null-værdierne.

Hvis du vil ændre værdierne for en bestemt række i regnearket, kan du bruge en UPDATE SQL-forespørgsel, som vises i Slet data fra en Excel-række:

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

Kør derefter følgende SQL-forespørgsel for at hente alle rækker i regnearket, der ikke indeholder null-værdier:

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

Pladsholderne COLUMN1 og COLUMN2 repræsenterer navnene på alle de kolonner, der skal håndteres. I dette eksempel er der to kolonner, men i en virkelig tabel kan der være et andet antal kolonner. Alle kolonnerne i første række i Excel-regnearket identificeres som kolonnenavnene i tabellen.