Fotgeschrittene Batch Skripte – Einführung in Batch Skripte Pt. 3

overview

Übersicht

Projekt starten

Automatisierter Dateidownload

Dateien von einem FTP Server zu laden kann dich eine Menge Zeit kosten. Euer Lieferant stellt euch beispielsweise Lagerbestände als CSV täglich auf einem FTP Server zur Verfügung. Natürlich sollen die Daten täglich aktualisiert werden. Via Batch und den richtigen Programmen kannst du dir die Zeit für das Herunterladen von Dateien sparen.

Batch und WinSCP Skripte

Die Kombination aus Batch Script und dem FTP Programm WinSCP ist hier eine hervorragende Ergänzung. Installiert euch WinSCP und verbindet euch zum FTP Server. Ihr könnt dann ein Kommando generieren, mit dem Ihr euch per CMD oder Batch auf den Server verbinden könnt.

Der Code Schnippsel sieht dann in etwa so aus:

open sftp://sftp-user1:str0ngPa$$word@192.168.178.2

WinSCP unterstützt dabei unter anderem den Befehl get mit dem ihr eine Datei von a nach b transferieren könnt.
Der Pfad wird hier absolut vom jeweiligen Wurzelverzeichnis, auf welches euer FTP User Zugriff hat, angegeben:

get "/subfolder/myFile.csv" "C:\Users\UserAdmin\Desktop\DownloadedFiles"

Ein vollständiges Download Skript für WinSCP würde sich also erst via open mit dem FTP Server verbinden, per get die Files downloaden und anschließend mit exit beendet werden.

Das komplette Skript sieht dann so aus:

open sftp://sftp-user1:str0ngPa$$word@192.168.178.2
get "/subfolder/myFile.csv" "C:\Users\UserAdmin\Desktop\DownloadedFiles"
exit

Download einer Datei

Speichert euch das Download Skript als einfache .txt Datei. Das Skript kann dann in eurem Batch Script wiederum mit WinSCP ausgeführt werden:

@echo off 
echo "--- Starte FTP Transfer ---" 
"C:\Program Files (x86)\WinSCP\WinSCP.com" /script="C:\Users\UserAdmin\Desktop\Tutorial\meinWinSCP-script.txt" 
pause

Download mehrer Dateien

Wollt ihr mehrere Dateien in einem Durchgang runter laden, versucht es mit folgendem Skript:

@echo off
set TMPPATH=%temp%\csvtmp
mkdir "%TMPPATH%"
winscp.com /command ^
    "open sftp://user:password@example.com/" ^
    "get ""/remote/path/*"" -filemask=*.txt ""%TMPPATH%""" ^
    "exit"
for /r "%TMPPATH%" %%f in ("*.*") do move "%%f" "C:\local\path\"
rmdir /s /q "%TMPPATH%"

Zu Komplex? Nehmen wir das Skript auseinander

1. Temporäre Ordner anlegen

set TMPPATH=%temp%\csvtmp
mkdir "%TMPPATH%"

Die standardmäßig definierte Variable %temp% führt zu einem fest definierten Pfad für temporär gespeicherte Dateien auf eurem Computer. In die Variable TMPPATH wird der Pfad zu eurem eigenen temporären Unterordner gesetzt und anschließend mit mkdir erstellt.

Der Ordner dient als Zwischenspeicher für die Download Dateien.

2. Download mit Filemaske

winscp.com /command ^
    "open sftp://user:password@example.com/" ^
    "get ""/remote/path/*"" -filemask=*.txt ""%TMPPATH%""" ^
    "exit"

Hier seht ihr wie man WinSCP auch direkt mit einem Skript füttern kann, ohne es vorher in einer Textdatei gespeichert zu haben. Das ist zwar etwas unübersichtlicher, macht es aber leichter eigene Variablen einzuschleusen.

Das ^ am Ende jeder Zeile sagt dem Script lediglich, dass der /command noch über mehrere Zeilen weiter geht.

Wie ihr schon wisst, dient * als Wildcard für alles was sich in unter dem Pfad befindet. Der Parameter -filemask=*.txt erklärt WinSCP alle Dateien mit Endung .txt zu beziehen. Das Ziel ist dann euer temporärer Ordner, den wir im Schritt vorher angelegt haben.

3. Verschieben zum Zielordner

for /r "%TMPPATH%" %%f in ("*.*") do move "%%f" "C:\local\path\"

Das kommt euch wieder vertraut vor. Wir loopen ein mal durch den Ordner und verschieben alle Dateien aus dem temporären Ordner in einen fixen Ordner unserer Wahl. Anschließend wird der temporäre Ordner wieder entfernt mit rmdir.

Automatisierter Dateiimport via JTL Ameise

Die heruntergeladenen Dateien könntet ihr dann im Anschluss mit der Ameise direkt importieren.

Legt euch also eine Importvorlage in der Ameise an und fügt am Ende des Skripts den Importbefehl an:

echo "start import lieferantenimport"
"C:\Program Files (x86)\JTL-Software\JTL-wawi-ameise.exe" --server=".\JTLWAWI" --database=eazybusiness --dbuser=sa --dbpass="sa04jT14" --templateid=IMP56 --inputfile="C:\Users\UserAdmin\Desktop\DownloadedFiles\myFile.csv"

Oder bei mehr Dateien als Loop:

for %%f in (C:\Users\UserAdmin\Desktop\DownloadedFiles\*.csv) do (   
    "C:\Program Files (x86)\JTL-Software\JTL-wawi-ameise.exe" --server=".\JTLWAWI" --database=eazybusiness --dbuser=sa --dbpass="sa04jT14"  --templateid=IMP56 --inputfile="%%f" 
)

Am Ende könntet ihr mit forfiles alle importierten Dateien wieder löschen. Oder ihr verschiebt sie erst mittels move und einem geeigneten Zeitstempel in einen Unterordner und löscht mit jedem Durchgang anschließend nur Dateien im Unterordner, die älter als 7 Tage sind. So behaltet ihr Kontrolle über importierte Dateien.

SQLCMD

Ein extrem hilfreiches Tool ist natürlich sqlcmd mit dessen Hilfe ihr SQL Scripte per Batch ausführen und die Ergebnisse exportieren könnt.

Natürlich können wir hier nicht auf SQL im Detail eingehen. Schaut euch für eine kleine Einführung unser SQL Tutorial an.

Einführung

sqlcmd hat ähnliche Parameter wie die Ameise und mit einem vorbereiteten SQL Script ist die Automatisierung spielend gemacht. Das Besondere: Wir können das Ergebnis unserer SQL Abfrage in einem Output File sichern.

Grundlegend benötigt ihr wieder die SQL Server Zugangsdaten und ein SQL Statement eurer Wahl. Ist das SQL Statement komplexer, loht es sich die Abfrage in einer .sql Datei zu speichern und als Inputfile Parameter an sqlcmd zu übergeben.

Hier beide Varianten:

set Datum=%date:~6,4%%date:~3,2%%date:~0,2%
sqlcmd -S ".\JTLWAWI" -d "eazybusiness" -P "sa04jT14" -U "sa" ^
    -i "C:\Users\UserAdmin\Desktop\Tutorial\meineAbfrage.sql" ^
    -o "C:\Users\UserAdmin\Desktop\Tutorial\Exporte\%Datum%_meinexport.csv" ^
    -h -1 -s";" -W

set Datum=%date:~6,4%%date:~3,2%%date:~0,2%
sqlcmd -S ".\JTLWAWI" -d "eazybusiness" -P "sa04jT14" -U "sa" ^
    -q "SELECT * FROM table" ^
    -o "C:\Users\UserAdmin\Desktop\Tutorial\Exporte\%Datum%_meinexport.csv" ^
    -h -1 -s";" -W

-S , -d , -p und -u erklären sich von selbst.

-i gibt den Pfad zu eurem SQL Skript an. Alternativ könnt ihr mit -q auch direkt eine Abfrage schreiben. Das hat natürlich den Vorteil, dass ihr das Batch Script parametrisieren könnt und so die Abfrage auf einen bestimmten Parameter beschränken könnt:

... -q "Select * FROM table WHERE key=%%1" ...

Mit -o gebt ihr den Namen für den Export an.

-W kann verwendet werden um den Spalten eine feste Breite zu geben. Ohne weiteren Wert behalten die Spalten einfach die notwendige Breite für die Daten und werden nicht mit unnötigen Leerzeichen (Whitespaces) befüllt.

Das Trennzeichen gebt ihr mit -s an.

Der Parameter -h -1 steuert die Ausgabe der Kopfzeile. Die -1 deaktiviert dabei die Ausgabe. Wenn Ihr die Kopfzeile aktiviert lasst, druckt sqlcmd standardmäßig eine unschöne Zeile mit Trennlinien unter die Kopfzeile.

Außerdem wird je Datei eine Fußzeile mit der Anzahl der betroffenen Zeilen eurer Abfrage erzeugt.

Trennlinien und Fußzeile mit sqlcmd umgehen

Um eine schöne, importfähige CSV Datei ohne lästige Fußzeile und Trennlinien unter der Kopfzeile zu bekommen müsst ihr ein paar Anpassungen vornehmen.

Die Fußzeile werdet ihr am schnellsten los. Schreibt einfach in die erste Zeile eures SQL Skripts:

SET NOCOUNT ON
SELECT
...

Das war einfach.

Wie oben beschrieben könnt ihr die Kopfzeile mittels -h -1 komplett deaktivieren. Das entfernt zwar die unschöne Trennlinie, leider aber auch die Spaltenbezeichner.

Hier der Trick: Angenommen ihr habt folgendes SQL Statement als Ausgangslage:

SELECT
    kArtikel
    ,cArtikelNr
    ,fEKNetto
FROM
    tArtikel

Setzt einfach eine weitere Hilfs-Abfrage vor das eigentliche Statement, welches nur Spaltenbezeichner enthält, für jeden Wert eurer Abfrage einen Eintrag:

SELECT
    'interner Key'
    ,'SKU'
    ,'EK Netto'

Anschließend vereint ihr beide Abfragen mittels UNION ALL

Aber Achtung! Da die Datentypen der Spalten identisch sein müssen und eure Kopfzeile nur aus Strings, also Zeichenketten besteht, müsst Ihr in eurer Abfrage alle Zahlenwerte als String bzw. VARCHAR casten:

SET NOCOUNT ON
SELECT
    'interner Key'
    ,'SKU'
    ,'EK Netto'

UNION ALL

SELECT
    CONVERT(VARCHAR,kArtikel)
    ,cArtikelNr
    ,FORMAT(fEKNetto,'N4')
FROM
    tArtikel

Als Ergebnis erhaltet ihr eine perfekt formatierte CSV Datei, ohne Trennlinien, ohne Fußzeile.

Einziger Nachteil: Für Berechnungen müsstet Ihr die Zahlenwerte der CSV wieder formatieren. Für einen Import via Ameise, genügen diese Textzeilen allerdings allemal.

Wenn ihr die Zahlenwerte als solche behalten wollt, dann müsst ihr die Abfrage auf 2 separate Abfragen aufteilen. Die erste schreibt die Datei und die Kopfzeile wie im Beispiel oben. Die Zweite schreibt dann die Daten und wird an die Datei angehangen:

set Datum=%date:~6,4%%date:~3,2%%date:~0,2%
sqlcmd ... -i "C:\Users\UserAdmin\Desktop\Tutorial\meineHeader.sql" ^
    -o "C:\Users\UserAdmin\Desktop\Tutorial\Exporte\%Datum%_meinexport.csv" -h -1 -s";" -W

sqlcmd ... -i "C:\Users\UserAdmin\Desktop\Tutorial\meineDaten.sql" ^
    >> "C:\Users\UserAdmin\Desktop\Tutorial\Exporte\%Datum%_meinexport.csv" -h -1 -s";" -W

Zusammenfassung

In diesem Beitrag habt ihr gelernt wie man mit WinSCP und Batch Skripten Dateien von einem FTP Server bezieht, diese anschließend weiterverarbeitet, wie man Datenbankabfragen exportiert und wie man diese in die geeignete Form bringt.

Ihr beherrscht nun alle Basics, um euren Alltag zu erleichtern. Bevor ich euch noch das versprochene Cheat Sheet mit allen relevanten Befehlen zur Verfügung stelle, möchte ich im 4. Teil ein komplexes Praxisbeispiel durchgehen bei dem wir alles gelernte nochmals in Form bringen und einen komplexen Prozessablauf vollständig automatisieren. Wir gehen dabei auch auf den Windows Aufgabenplaner ein und sprechen am Ende über Probleme und Nachteile im Zusammenhang mit Batch Skripten. Also bleibt dran und viel Spaß beim Ausprobieren!