Freigeben über

Ausführen von SQL-Abfragen für Excel-Dateien

Obwohl Excel-Aktionen die meisten Excel-Automationsszenarien verarbeiten können, können SQL-Abfragen erhebliche Mengen an Excel-Daten effizienter abrufen und bearbeiten.

Angenommen, ein Flow muss nur die Excel-Registrierungen ändern, die einen bestimmten Wert enthalten. Um diese Funktionalität ohne SQL-Abfragen zu erreichen, sind Schleifen, Bedingungen und mehrere Excel-Aktionen erforderlich.

Alternativ lässt sich diese Funktionalität mithilfe von SQL-Abfragen mit nur zwei Aktionen implementieren. Mit der Aktion SQL-Verbindung öffnen und mit der Aktion SQL-Anweisungen ausführen.

Eine SQL-Verbindung zu einer Excel-Datei öffnen

Bevor Sie eine SQL-Abfrage ausführen, müssen Sie eine Verbindung mit der Excel-Datei herstellen, auf die Sie zugreifen möchten.

Zum Herstellen der Verbindung erstellen Sie eine neue Variable namens %Excel_File_Path%, und initialisieren Sie sie mit dem Excel-Dateipfad. Optional können Sie diesen Schritt überspringen und den hartcodierten Pfad der Datei später im Flow verwenden.

Screenshot der Aktion „Variable festlegen“, die mit dem Pfad der Excel-Datei befüllt ist.

Stellen Sie nun die Aktion Open-SQL-Verbindung bereit und füllen Sie die folgende Verbindungszeichenfolge in ihren Eigenschaften aus.

Anbieter=Microsoft.ACE.OLEDB.12.0;Datenquelle=%Excel_File_Path%;Erweiterte Eigenschaften="Excel 12.0 Xml;HDR=YES";

Notiz

Um die angezeigte Verbindungszeichenfolge erfolgreich zu verwenden, müssen Sie Microsoft Access Database Engine 2010 Redistributable herunterladen und installieren.

Screenshot der Aktion „SQL-Verbindung öffnen“.

Eine SQL-Verbindung zu einer kennwortgeschützten Excel-Datei öffnen

In Szenarien, in denen Sie SQL-Abfragen für kennwortgeschützte Excel-Dateien ausführen, ist ein anderer Ansatz erforderlich. Die Aktion Öffnen einer SQL-Verbindung kann keine Verbindung zu kennwortgeschützten Excel-Dateien herstellen, daher müssen Sie den Schutz entfernen.

Starten Sie dazu die Excel-Datei mit der Aktion Excel starten. Die Datei ist kennwortgeschützt, geben Sie also das entsprechende Kennwort in das Feld Kennwort ein.

Screenshot der Aktion „Excel starten“ und des Felds „Kennwort“.

Stellen Sie als Nächstes die entsprechenden Aktionen zur Benutzeroberflächenautomatisierung bereit und navigieren Sie zu Datei>Info>Arbeitsmappe schützen>Mit Kennwort verschlüsseln. Weitere Informationen zur Benutzeroberflächenautomatisierung und zur Verwendung der jeweiligen Aktionen finden Sie in Desktopanwendungen automatisieren.

Screenshot der Benutzeroberflächenaktionen, die zum Auswählen der Option „Mit Kennwort verschlüsseln“ verwendet werden.

Nach der Auswahl Mit Kennwort verschlüsseln füllen Sie eine leere Zeichenfolge im Popup-Dialogfeld mit der Aktion Textfeld in Fenster ausfüllen. Um eine leere Zeichenfolge aufzufüllen, verwenden Sie den folgenden Ausdruck: %""%.

Screenshot der Aktion „Textfeld im Fenster ausfüllen“.

Um die Schaltfläche OK im Dialogfeld zu drücken und die Änderungen zu übernehmen, stellen Sie die Aktion Schaltfläche im Fenster drücken bereit.

Screenshot der Aktion „Schaltfläche im Fenster drücken“.

Stellen Sie schließlich die Aktion Excel schließen bereit, um die nicht geschützte Arbeitsmappe als neue Excel-Datei zu speichern.

Screenshot der Aktion „Excel schließen“ mit der ausgewählten Option „Dokument speichern unter“.

Folgen Sie nach dem Speichern der Datei den Anweisungen in Eine SQL-Verbindung zu einer Excel-Datei öffnen, um eine Verbindung dorthin zu öffnen.

Wenn die Bearbeitung der Excel-Datei abgeschlossen ist, löschen Sie die nicht geschützte Kopie der Excel-Datei mit der Aktion Dateien löschen.

Screenshot der Aktion „Datei(en) löschen“.

Inhalt einer Excel-Tabelle lesen

Obwohl die Aktion Aus Excel-Arbeitsblatt lesen den Inhalt eines Excel-Arbeitsblatts liest, kann es sehr lange dauern, bis Schleifen die abgerufenen Daten durchlaufen haben.

Eine effizientere Möglichkeit, bestimmte Werte aus Tabellenkalkulationen abzurufen, besteht darin, Excel-Dateien als Datenbanken zu behandeln und SQL-Abfragen darauf auszuführen. Dieser Ansatz ist schneller und erhöht die Leistung des Flows.

Zum Abrufen des gesamten Tabelleninhalts können Sie die folgende SQL-Abfrage in der Aktion SQL-Anweisungen verwenden nutzen.

SELECT * FROM [SHEET$]

Screenshot der mit einer AUSWÄHLEN-Abfrage befüllten Anweisung „SQL ausführen“.

Notiz

Um diese SQL-Abfrage in Ihren Flows anzuwenden, ersetzen Sie den BLATT Platzhalter mit dem Namen der Tabelle, auf die Sie zugreifen möchten.

Um die Zeilen abzurufen, die einen bestimmten Wert in einer bestimmten Spalte enthalten, verwenden Sie die folgende SQL-Abfrage:

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

Notiz

Um diese SQL-Abfrage in Ihren Flows anzuwenden, ersetzen Sie:

  • BLATT durch dem Namen der Tabelle, auf die Sie zugreifen möchten.
  • SPALTENNAME mit der Spalte, die den gesuchten Wert enthält. Die Spalten in der ersten Zeile des Excel-Arbeitsblatts werden als Spaltennamen der Tabelle identifiziert.
  • WERT durch den Wert, den Sie suchen möchten.

Daten aus einer Excel-Zeile löschen

Obwohl Excel die SQL-Abfrage LÖSCHEN nicht unterstützt, können Sie mit der Abfrage AKTUALISIEREN alle Zellen einer bestimmten Zeile auf NULL setzen.

Genauer gesagt können Sie die folgende SQL-Abfrage verwenden:

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

Screenshot der Anweisungen „SQL ausführen“ mit einer AKTUALISIEREN-Abfrage.

Bei der Entwicklung Ihres Flows müssen Sie den Platzhalter BLATT durch den Namen der Tabelle ersetzen, auf die Sie zugreifen möchten.

Die Platzhalter SPALTE1 und SPALTE2 stellen die Namen der zu bearbeitenden Spalten dar. In diesem Beispiel sind es zwei Spalten, aber in einem realen Szenario kann die Anzahl der Spalten abweichen. Die Spalten in der ersten Zeile des Excel-Arbeitsblatts werden als Spaltennamen der Tabelle identifiziert.

Der Teil [COLUMN1]='VALUE' der Abfrage definiert die Zeile, die Sie aktualisieren möchten. Verwenden Sie in Ihrem Flow den Spaltennamen und den Wert basierend darauf, welche Kombination die Zeilen eindeutig beschreibt.

Abrufen von Excel-Daten mit Ausnahme einer bestimmten Zeile

In einigen Szenarien müssen Sie möglicherweise den gesamten Inhalt einer Excel-Tabelle mit Ausnahme einer bestimmten Zeile abrufen.

Dies lässt sich bequeme erreichen, indem die Werte der unerwünschten Zeile auf NULL gesetzt und anschließend alle Werte mit Ausnahme der NULL abgerufen werden.

Um die Werte einer bestimmten Zeile in der Tabelle zu ändern, können Sie eine AKTUALISIEREN-SQL-Abfrage verwenden, wie in Daten aus einer Excel-Zeile löschen dargestellt:

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

Screenshot der „SQL ausführen“-Anweisungen mit einer AKTUALISIEREN-Abfrage.

Führen Sie als Nächstes die folgende SQL-Abfrage aus, um alle Zeilen der Tabelle abzurufen, die keine Nullwerte enthalten:

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

Die Platzhalter „SPALTE1“ und „SPALTE2“ stellen die Namen der zu bearbeitenden Spalten dar. In diesem Beispiel sind es zwei Spalten, aber in einer realen Tabelle kann die Anzahl der Spalten abweichen. Alle Spalten in der ersten Zeile des Excel-Arbeitsblatts werden als Spaltennamen der Tabelle identifiziert.