0

PowerShell: CSV zu XLSX konvertieren

PowerShell wird in der Regel verwendet um Dinge in der IT zu automatisieren. Somit kann man auch ganz einfach Excel bedienen und die Daten in andere Dateiformate wandeln. Auch Excel zu JSON ist möglich. In unserem Fall möchten wir aber eine CSV zu XLSX konvertieren. Das ist mit wenigen Zeilen Powershell Code möglich.

CSV zu XLSX mit PowerShell

Mit wenigen Zeilen PowerShell Code ist es möglich eine CSV Datei zu einer XLSX Datei zu wandeln. Hierfür sind verschiedene Schritte notwendig.

1. In der Zeile 2-4 wird der Pfad zur CSV und der neue Speicherort der XLSX Datei angegeben. Außerdem wird das Trennzeichen der CSV Datei angegeben.
2. Um mit Daten in Excel arbeiten zu können, wird in Zeile 7-9 eine Excel-Objekt und dessen Arbeitsmappen erstellt.
3. Die CSV Datei wird möglichst effizient mit einer Query geladen. Alle Eigenschaften hierzu ist in Zeile 12-18 zu finden
4. Danach wird die Datei noch als XLSX abgespeichert in Zeile 25.

Der PowerShell Code

# Define locations and delimiter
$csv = "c:/pathtofile/your.csv" # source
$xlsx = "c:/pathtofile/yournew.xlsx" # output
$delimiter = ";" # delimiter used in the file

# Create a new Excel workbook with one empty sheet
$excel = New-Object -ComObject excel.application 
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)

# Build the Query. Add command and reformat data
$TxtConnector = ("TEXT;" + $csv)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)
$query.TextFileOtherDelimiter = $delimiter
$query.TextFileParseType  = 1
$query.TextFileColumnDataTypes = ,1 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1

# Execute and delete the query
$query.Refresh()
$query.Delete()

# Save & close the Workbook as XLSX
$Workbook.SaveAs($xlsx,51)
$excel.Quit()

Wird das Script ausgeführt, so sollte in Kürze die CSV zu einer XLSX Datei gewandelt sein. Hierfür ist PowerShell richtig praktisch.

CSV zu XLSX: weitere Optionen

Generell sollte das Konvertieren somit erledigt sein. Doch es gibt Ausnahmen und weitere Optionen, welche für das Umwandeln mit PowerShell relevant sein können.

Umlaute Ä Ö Ü

Werden in der CSV Datei Umlaute Ä, Ö oder Ü verwendet, so können diese im Standard mit PowerShell nicht umgewandelt werden. Hier macht uns die Query einen Strich durch die Rechnung. Mit folgendem PowerShell Code funktioniert dies aber und muss natürlich dem Script hinzugefügt werden:

$query.TextFilePlatform = 65001

Zellen-Format

Im angegeben Code ist das Zellen-Format “General”. Möchte man das Format in “Text” ändern, so muss TextFileColumnDataTypes von 1 zu 2 geändert werden.

XLSX Datei bereits vorhanden

Ist die Ausgabe-Datei im XLSX Format bereits vorhanden, so erscheint beim Speichern der Excel-Datei ein Fehler. Mit folgendem Code werden Fehler unterdrückt und die Datei überschrieben:

$excel.DisplayAlerts = $False