Agile Coding

Datei hochladen mit WinSCP

Neulich musste ich mal wieder eine Datei auf einen SFTP Server hochladen. Und wie es immer so ist, ich konnte mich nur noch teilweise daran errinern wie es geht 🙂

Ich wusse noch das ich WINSCP für den Upload benutzt hatte und wie hübsch einfach die Powershell API ist. WINSCP ist so nett und liefert gleich eine .Net Library mit, die man einfach in jedem Powershell über Add-Type einlinken kann.

Zur Vorbereitung, um die Library nutzen zu können, braucht man natürlich erstmal den Host Name vom Server sowie User und Passwort. Zusätzlich braucht man aber auch den Fingerprint des jeweiligen Server Schlüssels. Hierfür empfehle ich einfach mit der Oberfläche von WinSCP mal eine Verbindung zu dem Server aufzubauen und unter dem Menüpunkt Sessions/Info den Finterprint nachzusehen, der Fingerprint sieht dann ungefähr so aus: 69:32:29:58:c0:21:7d:b3:6d:0f:8a:0d:48:ce:e5:f2

Nun aber zum Script, wie immer bei einem Upload sollte man damit beginnen den Ziel Ordner zu überprüfen, ob man die Daten überhaupt dort hinkopieren kann. Sprich welche Dateien liegen den im Zielordner so herum:

#add the library
Add-Type -Path $p_path_to_winscp
 
#preset the options for the session
$session_opt = New-Object WinSCP.SessionOptions
$session_opt.Protocol = [WinSCP.Protocol]::Sftp
$session_opt.HostName = "Server.irgendwo.de"
$session_opt.UserName = "Username"
$session_opt.Password = "Password"
$session_opt.SshHostKeyFingerprint = "ssh-rsa 2048 " + "69:32:29:58:c0:21:7d:b3:6d:0f:8a:0d:48:ce:e5:f2"
 
#create the session object
$session = New-Object WinSCP.Session	
 
# Connect with the set parameters
$session.Open($session_opt)
 
#query for target directory
$directory = $session.ListDirectory("Mein Zielordner auf dem Server")
 
foreach ($fileInfo in $directory.Files){
 
	#debugging
	Write-Host ("{0} with size {1}, permissions {2} and last modification at {3}" -f $fileInfo.Name, $fileInfo.Length, $fileInfo.FilePermissions, $fileInfo.LastWriteTime)
}

Soweit so gut, und jetzt wollen wir ja noch eine Datei hochladen und das sieht dann in etwa so aus:

 
# Set the transfer options, Binary is always a good choice
$transferOptions = New-Object WinSCP.TransferOptions
$transferOptions.TransferMode = [WinSCP.TransferMode]::Binary
 
$transferResult = $session.PutFiles("Path to local file", $False, $transferOptions)		
 
# Throw on any error
$transferResult.Check()		
 
# Disconnect, clean up
$session.Dispose()

Und fertig ist der Upload 🙂

1 Oktober,2018

powershell

http://www.agile-coding.net/datei-hochladen-mit-winscp/

Andreas Baßermann

Excel Dateien in XML exportieren mit MDAC

Ich hatte ja schon in diesem Blog über den Export von neueren Excel Datei Formaten mit EPPlus geschrieben. EPPlus kann aber leider nur aktuelle Formate von Excel verarbeiten. Alle älteren Formate funktionieren leider nicht.

Es gibt mehrere Möglichkeiten auch ältere Formate zu verarbeiten. Am einfachsten funktionierte für mich aber die MDAC (Microsoft Data Access Components) Varaiante. Hierfür muss man sicherlich erstmal diese etwas in die Jahre gekommene Framwork installieren. Das charmante an dieser Variante ist, dass man mit einer SQL like Abfrage arbeiten kann z.B.:

„Select * from [$Tabelle1]“

Ich hab es mir einfach gemacht und ein Skript gebaut, was alle Daten, aller Worksheets in eine XML Datei schreibt. Damit ist man im Grunde am flexiblesten, egal was man danach noch alles mit den Daten so machen möchte:

#path to the input excel file
$input="c:\temp\excel.xsl"
 
#path to write the xml output to
$output="c:\temp\ex_ppr.xml"
 
#create the db like driver object
$oleDbConn = New-Object System.Data.OleDb.OleDbConnection
$oleDbCmd = New-Object System.Data.OleDb.OleDbCommand
 
#set the connection string
$oleDbConn.ConnectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$p_cpf_input;Extended Properties=Excel 12.0;Persist Security Info=False"
 
#open the connection
$oleDbConn.Open()
$oleDbCmd.Connection = $OleDbConn
 
#get the tables/sheet names
$tables = $oleDbConn.GetSchema("Tables")
 
#create the basic xml output nodes
[System.XML.XMLDocument]$xml=New-Object System.XML.XMLDocument
$xml = [xml] "<!--?xml version='1.0' encoding='utf-8'?-->"
[System.XML.XMLElement]$xml_root=$xml.CreateElement("root")
 
 
#init worksheet counter
$ws = 1
 
foreach($table in $tables){
 
	#get the local table name
	$t = $table.TABLE_NAME
 
	#skip all useless tables
	if($t -like "*xlnm*" -or $t -like "*deleted*"){
		continue
	}else{
 
		#tim the ' from the string
		$t = $t.Trim("'")
 
		#reset the db objects
		$oleDbAdapter = New-Object System.Data.OleDb.OleDbDataAdapter
		$dataTable = New-Object System.Data.DataTable
 
		#assemble sql statement
		$oleDbCmd.commandtext = "Select * from [$t]"
		$oleDbAdapter.SelectCommand = $OleDbCmd
 
		#execute the command
		$ret=$oleDbAdapter.Fill($dataTable)
 
		#create new worksheet level
		[System.XML.XMLElement]$xml_ws=$xml.CreateElement("worksheet")
		$t = $t.Trim("$")
		$xml_ws.SetAttribute("name", $t)
 
		#init counter
		$row = 1
 
		#per each result in the dataset
		foreach($data in $dataTable){
 
			#create the row node
			[System.XML.XMLElement]$xml_row=$xml.CreateElement("row")
			$xml_row.SetAttribute("id", $row)
 
			#init column counter
			$col = 1
 
			foreach($cell in $data.ItemArray){
 
				[System.XML.XMLElement]$xml_item=$xml.CreateElement("item")
				$xml_item.SetAttribute("column", $col)
				$xml_item.InnerText = $cell
				$xml_row.appendChild($xml_item)
 
				#count up the column
				$col ++
 
			}
 
			#append to the row
			$xml_ws.appendChild($xml_row)
 
			#count up
			$row ++
		}
		#append the worksheet to the xml
		$xml_root.appendChild($xml_ws)
	}
}
 
#append anything to the root
$xml.appendChild($xml_root)
 
#save the xml file and done
$xml.Save($output)

#path to the input excel file $input="c:\temp\excel.xsl" #path to write the xml output to $output="c:\temp\ex_ppr.xml" #create the db like driver object $oleDbConn = New-Object System.Data.OleDb.OleDbConnection $oleDbCmd = New-Object System.Data.OleDb.OleDbCommand #set the connection string $oleDbConn.ConnectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$p_cpf_input;Extended Properties=Excel 12.0;Persist Security Info=False" #open the connection $oleDbConn.Open() $oleDbCmd.Connection = $OleDbConn #get the tables/sheet names $tables = $oleDbConn.GetSchema("Tables") #create the basic xml output nodes [System.XML.XMLDocument]$xml=New-Object System.XML.XMLDocument $xml = [xml] "" [System.XML.XMLElement]$xml_root=$xml.CreateElement("root") #init worksheet counter $ws = 1 foreach($table in $tables){ #get the local table name $t = $table.TABLE_NAME #skip all useless tables if($t -like "*xlnm*" -or $t -like "*deleted*"){ continue }else{ #tim the ' from the string $t = $t.Trim("'") #reset the db objects $oleDbAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $dataTable = New-Object System.Data.DataTable #assemble sql statement $oleDbCmd.commandtext = "Select * from [$t]" $oleDbAdapter.SelectCommand = $OleDbCmd #execute the command $ret=$oleDbAdapter.Fill($dataTable) #create new worksheet level [System.XML.XMLElement]$xml_ws=$xml.CreateElement("worksheet") $t = $t.Trim("$") $xml_ws.SetAttribute("name", $t) #init counter $row = 1 #per each result in the dataset foreach($data in $dataTable){ #create the row node [System.XML.XMLElement]$xml_row=$xml.CreateElement("row") $xml_row.SetAttribute("id", $row) #init column counter $col = 1 foreach($cell in $data.ItemArray){ [System.XML.XMLElement]$xml_item=$xml.CreateElement("item") $xml_item.SetAttribute("column", $col) $xml_item.InnerText = $cell $xml_row.appendChild($xml_item) #count up the column $col ++ } #append to the row $xml_ws.appendChild($xml_row) #count up $row ++ } #append the worksheet to the xml $xml_root.appendChild($xml_ws) } } #append anything to the root $xml.appendChild($xml_root) #save the xml file and done $xml.Save($output)

Das skript ignoriert alle möglichen „sinnlosen“ Worksheets. Wie ich seht, überlese ich alles was im Tabellennamen irgendwas mit „delete“ oder „xlnm“. Probiert mal mit ein paar Excel Dateien, ihr werdet sehen dass da aller Hand an Backups und irgendwelcher Datenmüll in den Excel Dateien gespeichert ist. Komischerweise stecken auch in den Tabllennamen hier und da Anführungszeichen….auch diese entferne ich damit man sauber durch alle Zellen aller (wirklichen) Worksheets durchlaufen kann.

31 Oktober,2017

powershell

http://www.agile-coding.net/excel-dateien-in-xml-exportieren-mit-mdac/

Andreas Baßermann

Excel Dateien in XML exportieren mit EPPlus und Powershell

Neulich musste ich für ein Kundenprojekt eine Excel Datei auslesen. Beim Suchen im Internet fand ich viele Libaries und auch natürlich die Application Com-Object Variante von Microsoft. Da ich aber auf dem Server jetzt kein Excel installieren wollte, fiel die Com-Object Variante aber gleich wieder raus aus der Recherche.

Ich hab mich dann erstmal auf epplus konzentriet und siehe da, es hat einfach nur bestens funktioniert. Im folgenden Powershell Skript, hab ich versucht keine Businesslogik einzubauen, sondern einfach die im Excel enthaltenen Daten in einer XML Datei zu schreiben. Und die Businesslogik dann entsprechend auf das XML anzuwenden:

param(
	#path to the epplus.dll
	$p_epp_plus="C:\Excel\EPPlus.dll",
	#path to the input excel file
	$p_input="C:\Excel\test.xlsm",
	#path to write the xml output to
	$p_output="C:\Excel\test.xml"
)
#isolate the column letter from the address
function get_column($str){
	$str = [regex]::Matches($str, "[a-zA-Z]*")
	$str = $str.groups[0].value
	return $str
}
#isolate the row number from address
function get_row($str){
	$str = [regex]::Matches($str, "[0-9]+")
	$str = $str.groups[0].value
	return $str
}
try{
 
	#adding the libary
	Add-Type -Path $p_epp_plus
 
	#creating the excel object and open the file
	$obj = New-Object OfficeOpenXML.ExcelPackage($p_input)
 
	#creating the xml object
	[System.XML.XMLDocument]$xml=New-Object System.XML.XMLDocument
	$xml = [xml] "<!--?xml version='1.0' encoding='utf-8'?-->"
	[System.XML.XMLElement]$xml_root=$xml.CreateElement("root")
 
	#going thru each of the worksheets
	foreach($ws in $obj.Workbook.Worksheets){
 
		#create an xml node for each worksheet
		[System.XML.XMLElement]$xml_worksheet=$xml.CreateElement("worksheet")
		$xml_worksheet.SetAttribute("index",$ws.index)
		$xml_worksheet.SetAttribute("name",$ws.name)
 
		#going thru each cell of the current worksheet
		foreach($cell in $ws.cells){
 
			#only is a field is not empty
			if($cell.text -ne ""){
				#create an xml node for each cell
				[System.XML.XMLElement]$xml_cell=$xml.CreateElement("cell") 
 
				#getting the address
				$xml_cell.SetAttribute("Address",$cell.Address)
 
				#getting the column
				$column = get_column($cell.Address) 
				$xml_cell.SetAttribute("column", $column)
 
				#getting the row
				$row = get_row($cell.Address)
				$xml_cell.SetAttribute("row", $row)
 
				#content of the cell
				$xml_cell.SetAttribute("text",$cell.text)
 
				#finally adding the row nodes to the worksheet
				$xml_worksheet.appendChild($xml_cell) | out-null
			}
		}
		#finally adding the row nodes to the worksheet
		$xml_root.appendChild($xml_worksheet) | out-null
	}
	#adding everything to the root xml node
	$xml.appendChild($xml_root) | out-null
	#save to given output path and filename
	$xml.Save($p_output)	
}
catch{
	write-host $_.Exception.Message
}

param( #path to the epplus.dll $p_epp_plus="C:\Excel\EPPlus.dll", #path to the input excel file $p_input="C:\Excel\test.xlsm", #path to write the xml output to $p_output="C:\Excel\test.xml" ) #isolate the column letter from the address function get_column($str){ $str = [regex]::Matches($str, "[a-zA-Z]*") $str = $str.groups[0].value return $str } #isolate the row number from address function get_row($str){ $str = [regex]::Matches($str, "[0-9]+") $str = $str.groups[0].value return $str } try{ #adding the libary Add-Type -Path $p_epp_plus #creating the excel object and open the file $obj = New-Object OfficeOpenXML.ExcelPackage($p_input) #creating the xml object [System.XML.XMLDocument]$xml=New-Object System.XML.XMLDocument $xml = [xml] "" [System.XML.XMLElement]$xml_root=$xml.CreateElement("root") #going thru each of the worksheets foreach($ws in $obj.Workbook.Worksheets){ #create an xml node for each worksheet [System.XML.XMLElement]$xml_worksheet=$xml.CreateElement("worksheet") $xml_worksheet.SetAttribute("index",$ws.index) $xml_worksheet.SetAttribute("name",$ws.name) #going thru each cell of the current worksheet foreach($cell in $ws.cells){ #only is a field is not empty if($cell.text -ne ""){ #create an xml node for each cell [System.XML.XMLElement]$xml_cell=$xml.CreateElement("cell") #getting the address $xml_cell.SetAttribute("Address",$cell.Address) #getting the column $column = get_column($cell.Address) $xml_cell.SetAttribute("column", $column) #getting the row $row = get_row($cell.Address) $xml_cell.SetAttribute("row", $row) #content of the cell $xml_cell.SetAttribute("text",$cell.text) #finally adding the row nodes to the worksheet $xml_worksheet.appendChild($xml_cell) | out-null } } #finally adding the row nodes to the worksheet $xml_root.appendChild($xml_worksheet) | out-null } #adding everything to the root xml node $xml.appendChild($xml_root) | out-null #save to given output path and filename $xml.Save($p_output) } catch{ write-host $_.Exception.Message }

Das Skript liest einfach alle Felder in allen Tabellenblätter (worksheet) und legt alle gefüllten Felder in einem eigenen XML Knoten ab. Zusätzlich wird die Koordinate als Attribute gespeichert, damit man den Inhalt jeweils der gelesenen Postionen auch später noch zuordnen kann.

Der Output sieht im Grunde so aus:

Viel Spaß beim Excel Daten auslesen 🙂

28 Mai,2017

http://www.agile-coding.net/excel-dateien-in-xml-exportieren-mit-epplus-und-powershell/

Andreas Baßermann

IMAPX mit Powershell nutzen um Emails abzufragen via IMAP

IMAP ist ein Protokoll, über dem man mit einem Mail Server kommunizieren kann, und beispielsweise Emails abzufragen. Es gibt eine ganze menge IMAP Komponenten im Netz verfügbar. Wegen einem Projekt musste ich mir ein paar davon anschauen und bin schlussendlich bei IMAPX stehen geblieben. Von allen gab es hier die meisten IMAP Funktionen und man kann es, dank der DLL, in eigene Projekte leicht einbinden.

Hier ein Beispiel Powershell Skript zum Abfragen von Emails im Posteingangsordner des Mail Servers:

# set path to libary
$lib = "C:\imapx.dll"
# Import the dll
[Reflection.Assembly]::LoadFile($lib)
# Create a imap object
$imap = New-Object ImapX.ImapClient
# set the fetching mode to retrieve message 
$imap.Behavior.MessageFetchMode = "Full"
# set address of IMAP server
$imap.Host = $server
# set port of server to provide IMAP
$imap.Port = $port
# use encrypted communication
$imap.UseSsl = $true
# try to connect the IMAP server
$imap.Connect()
# setting credantials
$user = "User"
$password = "Password"
# Login with given credentials
$imap.Login($user,$password)
# go and get the message at IMAP server
$messages = $imap.Folders.Inbox.Search("ALL", $imap.Behavior.MessageFetchMode, 1000)
#
foreach($m in $messages){
	write-host 'uid' $m.uid
	write-host 'subject' $m.Subject
	write-host 'from object' $m.from
	write-host 'DisplayName' $m.from.DisplayName
	write-host 'email Address' $m.from.Address
	# get either HTML or text version of the email
	#write-host 'mail body txt' $m.body.text
	write-host 'mail body html' $m.body.html
 }

Die IMAPX Komponente bietet eine Fülle von IMAP Funktionen, z.B. Löschen, Herunterladen, Verschieben, etc.

*UPDATE*

Das IMAPX Projekt wird schon eine ganze Zeit nicht mehr gepflegt. Ich habe meine IMAP Implementierungen nun auf MailKit umgestellt. Ich habe einen neuen Beitrag mit einem Beispiel für MailKit veröffentlicht.

13 Februar,2016

powershell

http://www.agile-coding.net/imapx-mit-powershell-nutzen-um-emails-abzufragen-via-imap/

Andreas Baßermann

BitTorrent Download mit Aria2 und Powershell

Im Rahmen von einem Projekt musste ich mich vor kurzem mit einem Dateidownload via BitTorrent intensiv beschäftigen. Dabei hab ich mir ein paar Open Source varianten angeschaut und bin schliesslich bei Aria2 hängen geblieben. Das Aria Projekt bietet eine ganze menge an Protokollen (HTTP, SFTP, FTP ,etc.) an, um Daten herunterzuladen. Bei der Analyse bin auf die RPC Schnittstelle gestossen, über die man Aria quasi fernbedienen kann. Die RPC Schnittstelle kann entweder Json oder XML Datenstrukturen verarbeiten.

Bei testen hab ich schnell ein paar Powershell skripte genutzt, um die Interaktion mit Aria besser zu verstehen. Als Vorbereitung muss man erstmal Aria konfgiurieren, damit die RPC Schnistelle überhaupt verfügbar ist. Man kann entwender alle Konfig-Parameter über die aria.config Datei einstellen oder man gibt einfach ein paar Kommandozeilenparameter beim Starten von Aria mit:

aria2c.exe --enable-rpc --rpc-listen-all

Bei der option –rpc-listen-all geht es nicht um den Port, sondern um das Network-Interface. D.h. ob Aria reagiert auf Call für localhost oder IP. Zum testen kann man einfach alle Interface anschalten und gut. Für später sollte man sich aber die Absicherungsmethoden wie SSL und Secrete-Token mal genauer anschauen.

Mit dem folgenden Powershell-Skript spricht man die RPC Schnittstelle an und übergibt einen Torrent File. Der Torrent File beinhaltet qausi die Informationen, welche Dateien von welchem BitTorrent Server geladen werden soll:

# setting vars
# url points to aria2 rpc host
$url = "http://localhost:6800/jsonrpc"
#path to torrent file
$torrent = "C:\002315_0001.torrent"
#directory to download to
$dir = "C:\\temp\\download"
 
#read the content of torrent file and encode base64
$enc = [convert]::ToBase64String((get-content $torrent -encoding byte))
 
#setting the command for rpc
$command = @"{"jsonrpc": "2.0","id": 11,"method": "aria2.addTorrent", "params": ["$enc",[],{"dir": "$dir"}]}
"@
 
#debug print 
write-host $command
 
#setting the weblient
$bytes = [System.Text.Encoding]::UTF8.GetBytes($command)
$web = [System.Net.WebRequest]::Create($url)
$web.Method = "POST"
$web.ContentLength = $bytes.Length
$web.ContentType = "application/x-www-form-urlencoded"
 
#executing against the rpc
$stream = $web.GetRequestStream()
$stream.Write($bytes,0,$bytes.Length)
$stream.close()
 
#read the responds from rpc
$reader = New-Object System.IO.Streamreader -ArgumentList $web.GetResponse().GetResponseStream()
$json = $reader.ReadToEnd() 
$reader.Close()
 
#convert the repond json in object
$null = [System.Reflection.Assembly]::LoadWithPartialName("System.Web.Extensions")
$ser = New-Object System.Web.Script.Serialization.JavaScriptSerializer
$obj = $ser.DeserializeObject($json)
 
#looking in the result
$gid = $obj.result
 
#debug print result
write-host $gid

Als erfolgreiche Rückantwort von der RPC Schnittstelle erhält man eine GID zurück. Das ist im Grunde nur ein eindeutiger Schlüssel für den gestarteten Download, über den man im nächsten Schritt abfragen kann, ob der Download fertig ist oder ob es schiefging. D.h. man fragt quasi den Status ab:

# setting vars
# url points to aria2 rpc host
$url = "http://localhost:6800/jsonrpc"
#gid unique identifier for the download job
$gid = "401ffd2682ba057c"
 
$command = '{"jsonrpc": "2.0", "method": "aria2.tellStatus", "params":["$gid"], "id": 1}'
 
#debug print 
write-host $command
 
#setting the weblient
$bytes = [System.Text.Encoding]::UTF8.GetBytes($command)
$web = [System.Net.WebRequest]::Create($url)
$web.Method = "POST"
$web.ContentLength = $bytes.Length
$web.ContentType = "application/x-www-form-urlencoded"
 
#executing against the rpc
$stream = $web.GetRequestStream()
$stream.Write($bytes,0,$bytes.Length)
$stream.close()
 
#read the responds from rpc
$reader = New-Object System.IO.Streamreader -ArgumentList $web.GetResponse().GetResponseStream()
$json = $reader.ReadToEnd() 
$reader.Close()
 
#convert the repond json in object
$null = [System.Reflection.Assembly]::LoadWithPartialName("System.Web.Extensions")
$ser = New-Object System.Web.Script.Serialization.JavaScriptSerializer
$obj = $ser.DeserializeObject($json)
 
#debug print result
write-host ($obj.result | Format-Table | Out-String)
 
#debug of status
$obj.result["status"]

Im Attribut Status stehen dann klar informationen wie Active, Complete, Error, etc. drinnen. Somit kann man dann entsprechend auf den Status reagieren und entweder die heruntergeladenen Daten im Download Verzeichnis abholen oder entsprechend neuaufsetzen und den Download noch mal starten, falls es einen Fehler gab.

Kleine Tipp noch am Rande, es gibt ein WebUi für Aria, der ebenfalls die RPC Schnittstelle nutzt. Das ist super praktisch wenn man debuggen möchte und man kann sich ein paar Beispiele anschauen, wie man mit der Schnittstelle umgeht falls man mehrere Befehle auf einmal an die RPC Schnittstelle senden möchte. Die Schnittstelle unterstützt nämlich Mulitcalls, d.h. man kann mehrere Befehle in einem Request an den Server stellen und erhält dem entsprechend auf mehrere Json formatierte Rückantworten.

13 Februar,2016

powershell

http://www.agile-coding.net/bittorrent-download-mit-aria2-und-powershell/

Andreas Baßermann

Agile Softwareentwicklung mit Open Source

Datei hochladen mit WinSCP

Excel Dateien in XML exportieren mit MDAC

Excel Dateien in XML exportieren mit EPPlus und Powershell

IMAPX mit Powershell nutzen um Emails abzufragen via IMAP

BitTorrent Download mit Aria2 und Powershell