Köra SQL-frågor på Excel-filer

Excel-åtgärder kan hantera de flesta automatiseringsscenarier i Excel, men SQL-frågor kan hämta och hantera stora mängder Excel-data mer effektivt.

Anta att ett flöde endast måste ändra de Excel-flöden som innehåller ett visst värde. För att uppnå den här funktionen utan SQL-frågor behöver du loopar, villkor och flera Excel-åtgärder.

Alternativt kan du även implementera den här funktionen med SQL-frågor med bara två åtgärder, åtgärden Öppna SQL-anslutning och åtgärden Kör SQL-uttryck.

Öppna en SQL-anslutning till en Excel-fil

Innan du kör en SQL-fråga måste du öppna en anslutning till den Excel-fil du vill komma åt.

Om du vill upprätta anslutningen skapar du en ny variabel med namnet %Excel_File_Path% och initierar den med sökvägen till Excel-filen. Alternativt kan du hoppa över det här steget och använda den hårdkodade sökvägen till filen senare i flödet.

Distribuera nu åtgärden Öppna SQL-anslutning och fyll i följande anslutningssträng i sina egenskaper.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%Excel_File_Path%;Extended Properties="Excel 12.0 Xml;HDR=YES";

Kommentar

För att du ska kunna använda den visade anslutningssträngen måste du hämta och installera Microsoft Access Database Engine 2010 Redistributable.

Öppna en SQL-anslutning till en lösenordsskyddad Excel-fil

En annan metod krävs i scenarier där du kör SQL-frågor på lösenordsskyddade Excel-filer. Åtgärden Öppna SQL-anslutning kan inte ansluta till lösenordsskyddade Excel-filer, så du måste ta bort skyddet.

Det gör du genom att starta Excel-filen med åtgärden Starta Excel. Filen är lösenordsskyddad. Ange därför rätt lösenord i fältet Lösenord.

Nu ska du distribuera lämpliga automatiseringsåtgärder för användargränssnitt och navigera till Filinformation>Info>Skydda arbetsbok>Kryptera med lösenord. Du hittar mer information om automatisering av användargränssnitt och hur du använder respektive åtgärder i Automatisera stationära tillämpningar.

När du har valt Kryptera med lösenord fyller du i en tom sträng i popup-dialogen med åtgärden Fyll i textfält i fönster. Om du vill fylla i en tom sträng använder du följande uttryck: %""%.

Om du vill trycka på OK i dialogen och tillämpa ändringarna använder du åtgärden Tryck på knappen i fönstret.

Använd åtgärden Stäng Excel för att spara den icke-skyddade arbetsboken som en ny Excel-fil.

När du har sparat filen följer du instruktionerna i Öppna en SQL-anslutning till en Excel-fil för att öppna en anslutning till den.

När Excel-filen är färdigredigerad använder du åtgärden Ta bort fil(er) för att ta bort den icke-skyddade kopian av Excel-filen.

Läsa innehållet i ett Excel-kalkylblad

Även om åtgärden Läs från Excel-kalkylblad kan läsa innehållet i ett Excel-kalkylblad kan loopar ta lång tid att iterera genom hämtade data.

Ett effektivare sätt att hämta specifika värden från kalkylblad är att använda Excel-filer som databaser och köra SQL-frågor på dem. Den här metoden går snabbare och ökar flödets prestanda.

Om du vill hämta allt innehåll i ett kalkylblad kan du använda följande SQL-fråga i åtgärden Kör SQL-uttryck.

SELECT * FROM [SHEET$]

Kommentar

Om du vill använda den här SQL-frågan i dina flöden ersätter du platshållare för SHEET med namnet på kalkylbladet som du vill använda.

Använd följande SQL-fråga om du vill hämta raderna som innehåller ett visst värde i en viss kolumn:

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

Kommentar

Om du vill använda den här SQL-frågan i dina flöden ersätter du:

• ARK med namnet på kalkylbladet som du vill komma åt.
• COLUMN NAME med kolumnen som innehåller värdet du vill söka efter. Kolumnerna på den första raden i Excel-kalkylbladet identifieras som tabellens kolumnnamn.
• VÄRDE med det värde du vill söka efter.

Ta bort data från en Excel-rad

Även om Excel inte stöder SQL-frågan DELETE kan du använda UPDATE-frågan för att ange null för alla celler på en viss rad.

Mer exakt kan du använda följande SQL-fråga:

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

När du utvecklar ett flöde ska du ersätta platshållare för SHEET med namnet på kalkylbladet som du vill använda.

Platshållarna COLUMN1 och COLUMN2 representerar namnen på alla kolumner som ska hanteras. I detta exempel finns två kolumner, men i ett verkligt scenario kan antalet kolumner variera. Kolumnerna på den första raden i Excel-kalkylbladet identifieras som tabellens kolumnnamn.

[COLUMN1]='VALUE'-delen av frågan definierar den rad du vill uppdatera. Använd kolumnnamnet och värdet utifrån vilken kombination som beskriver raderna unikt i flödet.

Hämta Excel-data förutom en specifik rad

I vissa situationer kan du behöva hämta allt innehåll i ett Excel-kalkylblad förutom en specifik rad.

Ett bra sätt att uppnå detta resultat är att ange värden för den oönskade raden till null och sedan hämta alla värden utom null-värdena.

Om du vill ändra värdena för en specifik rad i kalkylbladet kan du använda en UPDATE-SQL-fråga så som visas i Ta bort data från en Excel-rad:

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

Kör sedan följande SQL-fråga för att hämta alla rader i kalkylbladet som inte innehåller null-värden:

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

Platshållarna COLUMN1 och COLUMN2 representerar namnen på de kolumner som ska hanteras. I detta exempel finns två kolumner, men i en verklig tabell kan antalet kolumner komma att variera. Alla kolumnerna på den första raden i Excel-kalkylbladet identifieras som tabellens kolumnnamn.