Dateien mit Alteryx und WinSCP von einem SFTP-Server herunterladen

In Alteryx kann man sehr einfach mit Dateien auf (S)FTP-Servern interagieren, also Dateien herunterladen und teilen. Das erfolgt mithilfe des Download-Blocks.
Dort kann einfach die URL eingespeist werden, die abgerufen werden soll und dort können auch einfach Nutzername und Passwort mitgegeben werden. Herunterladen lassen sich Dateien per GET-Methode, das Hochladen erfolgt mit einem PUT-Befehl.

Neulich kam ein Kunde auf mich zu, mit denen ich vor Jahren eine solche Lösung aufgebaut hatte. Es ging darum, eine Menge automatisiert bereitgestellter CSV-Dateien weiterzuverarbeiten. Der Workflow funktionierte jetzt nicht mehr. Der Fehler kam im Download-Block: „Error transferring data: Failed initialization“.

Nach etwas Troubleshooting und Recherche kam heraus, dass der SFTP-Server ein kleines bisschen umkonfiguriert und sicherer gemacht wurde. Nur schwach verschlüsselte Verbindungen werden jetzt abgelehnt.
Das macht Sinn und ist auch empfehlenswert, aber in der Kombination mit nativen Alteryx-Tools ist das etwas nachteilig: Der Download-Block unterstützt lediglich die SHA-1-Verschlüsselung (Stand Mai 2021). Diese wird allgemein nicht mehr als besonders sicher angesehen.

Wir haben also zwei Möglichkeiten: Entweder wir lassen unsichere Verbindungen auf den SFTP-Server zu oder wir greifen nicht über den Download-Block auf die Daten zu. Natürlich machen wir letzteres (ansonsten, gäbe es den Blog auch nicht…).

Ein schönes Feature in Alteryx ist die Möglichkeit, andere Programme über die Kommandozeile auszuführen. In unserem Fall können wir hier WinSCP verwenden, ein einfacher Windows-Client für die Verbindung zu SSH-Servern, der unterschiedlichste Authentifizierungsmechanismen und Verschlüsselungsprotokolle unterstützt.
Der Kern des Workflows besteht also darin, dass die Daten mithilfe von WinSCP heruntergeladen und dann in Alteryx lokal weiterverarbeitet werden.

Schritt 1: Ausführen eines Skripts, das die Daten herunterlädt und in einem lokalen Ordner ablegt (Run Command-Block)

Schritt 2: Einlesen einer Liste von Dateien, die nun verfügbar sind (Run Command-Block)

Schritt 3: Auswählen der richtigen Dateien und einlesen mithilfe des Dynamic Input-Blocks

Im Folgenden gehen wir den Prozess einmal durch.

Einrichten der Skripte und Konfiguration des Run Command-Blocks

Voraussetzungen:

• WinSCP ist heruntergeladen. Hier geht’s zum Download. Wir haben mit Alteryx einfachen Zugriff auf die folgenden Dateien: WinSCP.exe, WinSCP.com
• Die Verbindung mit WinSCP zum SFTP-Server funktioniert über die GUI

Um WinSCP aus der Kommandozeile auszuführen, wird WinSCP.com verwendet. Dieses Programm benötigt ein Skript, in dem alle Informationen und Schritte stehen, die ausgeführt werden sollen. Insgesamt werden also zwei Skripte benötigt. Das Erste wird aus Alteryx heraus angestoßen und führt WinSCP.com aus. Das Programm wiederum verwendet ein Skript, das wir separat abspeichern und abgelegt haben.  

Das „äußere“ Skript, das aus Alteryx heraus angestoßen wird, kann folgendermaßen aussehen:

cd "C:\Program Files (x86)\WinSCP"
WinSCP.com /script=WinSCP_Script.txt /ini=nul
chcp 1252
dir "lokaler_Datei_Ablageort" > "lokaler_Workflow_Ablageort\dir.txt"

In Zeile 1 wechseln wir zum Ort, wo WinSCP.com abgelegt ist.
In Zeile 2 wird WinSCP.com ausgeführt, als Input dient das Skript „WinSCP_Script.txt“. Das muss tatsächlich als .txt-Datei vorliegen. Falls die Datei an einem anderen Ort liegt, muss der Dateipfad natürlich angegeben werden. Der switch „/ini=nul“ sagt dem Programm, dass wir im Scripting-Modus unterwegs sind. Mehr Infos dazu finden sich hier.
Zeile 3 wechselt die Codepage zu einer Version, die auch Umlaute abdeckt. Das ist wichtig, falls die Dateinamen auch ä, Ü, etc. enthalten können, da diese ansonsten in der resultierenden „Einlese-Datei“ falsch abgespeichert werden.
Zeile 3 fragt alle Inhalte des angegebenen Ordners ab (der Ordner, wo die Dateien abgelegt wurden) und schreibt sie in eine Zieldatei „dir.txt“, die wir für das Einlesen in Alteryx verwenden. Ich empfehle, die zweite Datei an einen Ort zu legen, wo sie klar identifizierbar und verwendbar ist.
Wichtig: Die Datei muss als „.bat“-Datei abgespeichert werden, damit sie von Alteryx ausgeführt werden kann.

Die Datei „WinSCP_Script.txt“ sieht dann folgendermaßen aus:

OPEN sftp://username:password@ftp-server/ -hostkey="Server_host_key_fingerprint"
GET / “Zielordner”
CLOSE
EXIT

In Zeile 1 sind verschiedene Platzhalter auszufüllen. Hier wird die Verbindung zum Server aufgebaut. Man muss hier die Credentials im Klartext hinterlegen, und zwar in der angegebenen Form. Alternativ zum Klartext könnte man auch über ein Alteryx-Macro das Script generieren lassen und den Nutzer seine Daten eingeben lassen, das wäre aber etwas zu umfangreich für diesen Blog.
Nutzername, Passwort und Adresse des FTP-Servers sollten einfach sein. Interessanter ist der Host-Key-Fingerprint. Diesen kann ich z. B. aus der grafischen Oberfläche extrahieren, wenn ich mich mit WinSCP separat zum Server verbunden habe. Ein Beispielformat ist ansonsten auch hier gezeigt.

In Zeile zwei wird in unserem Beispielfall einfach die root-Directory abgefragt, also „/“. Dieser Befehl lädt alle Dateien in diesem Ordner herunter, und zwar in den angegebenen lokalen Zielordner. Alternativ kann anstelle von „/“ auch ein anderer Pfad oder eine Datei angegeben werden.
Der Befehl CLOSE schließt die Verbindung zum Server und der Befehl EXIT verlässt das Script. Das ist wichtig, weil ansonsten das „äußere“ Script nicht weiterlaufen kann.

An diesem Punkt haben wir also ein Script, das WinSCP benutzt, um Dateien aus einem Ordner an einen bestimmten Ort herunterzuladen und das diese Dateien schön in einer Datei auflistet, die in Alteryx verwendet werden kann. Diese Bestandteile verwenden wir jetzt im Run Command-Block in Alteryx.

Der Run Command-Block wird folgendermaßen konfiguriert.

1. Angabe des „äußeren“ Scripts
2. Angabe der Working Directory. Das ist optional, da im Script ja auch die Directory geändert wird. Im Beispielfall verwende ich relative Pfade und die Skripte etc. liegen im gleichen Ordner wie mein Workflow, also „.“. (Tipp: Damit „.“ Richtig funktioniert, muss der Workflow erst mal abgespeichert werden, ansonsten liegt er in einem wilden Temp-Verzeichnis)
3. Input der Liste, die die heruntergeladenen Dateien enthält

Wenn man den Workflow laufen lässt, sollte man dann idealerweise eine Ausgabe sehen, die in etwa so aussieht:

Diese Liste muss natürlich noch etwas aufbereitet werden, damit sie vom Dynamic Input-Block verwendet werden kann.

Aufbereiten der Datei-Liste für den Input

Im nächsten Schritt des Workflows würde man üblicherweise die Dateien auswählen, die eingelesen werden sollen. Ggf. hat man ja viele historische Daten oder unterschiedliche Dateiformate in dem Ablageordner liegen. Im Beispiel geht es darum, alle CSV-Dateien einzulesen, die ein bestimmtes Namensschema haben.

In (1) wird zunächst einmal nach allen Zeilen gefiltert, die die Endung „.csv“ haben.
In (2) werden Informationen mithilfe eines Regex-Blocks extrahiert. Das kann alternativ auch mit Formeln, Split to Columns-Blöcken oder anderen Methoden gemacht werden. Im Beispiel sieht man aber den gewünschten Output. Hier kann man sehen, dass es drei Dateien im erwarteten Format gibt und eine Datei, die anders benannt ist.

Ich bevorzuge es, beim Dynamic Input-Block für Dateien den vollen Pfad anzugeben. Daher erstelle ich mithilfe eines Formel-Tools ein neues Feld, dass diese Information enthält. Alternativ können hier auch relative Pfade verwendet werden, was den Flow flexibler machen würde.

Einlesen der Dateien mit einem Dynamic Input-Block

Mit dem Dynamic Input-Block können diese Dateien dann alle zusammen eingelesen werden. Dafür bindet man eine Beispieldatei an und ersetzt dann den Dateipfad für die Dateien, die verwendet werden sollen. Tipp: Lassen Sie sich den Dateinamen mit ausgeben, um die Daten später unterscheiden zu können!

Der gesamte Workflow sieht in meinem Beispiel folgendermaßen aus:

Optional ist hier die Datumsumwandlung. Das kann in bestimmten Szenarien für Filterlogiken verwendet werden, um nur die neuesten Dateien zu lesen oder Ähnliches. Auch die anderen Bestandteile können je nach Struktur natürlich anders aussehen. Die grundsätzliche Logik bleibt allerdings die gleiche:

Daten über WinSCP herunterladen > Liste der Dateien in Alteryx einlesen > Dateien in Alteryx einlesen

Man kann das Konzept noch deutlich weitertreiben. So ist etwa ein Macro denkbar, dass die Nutzercredentials abfragt. Oder verschiedene Arten von Dateien können in separaten Datenströmen unterschiedlich verarbeitet werden.

Der Upload von Dateien würde entsprechend ähnlich erfolgen, hier müsste das Script dann nicht das Herunterladen definieren, sondern das Hochladen (PUT-Befehl). Die Infos dazu finden sich erneut hier.