Mit Powershell eine XLSM nach CSV konvertieren

Geht das nicht ???

Hallo,

ich habe eine Excel-Datei (2007) mit Makros - d.h. eine XLSM Datei.
Dieses "Ding" ist ein wenig größer und das Einlesen mit Powershell in ein Array dauert eeeeeeewig.
Ein Test mit den gleichen Daten aber als CSV-Datei brachte den gewünschten Erfolg.
Nun möchte ich nicht jedesmal meine *.XLSM manuell in eine CSV verwandeln - das sollte Powershell doch auch können, oder?

Bisher habe ich aber nur mit XLSX-Dateien hinbekommen

$xlCSV = 6
$Excel = New-Object -Com Excel.Application
$Excel.visible = $true
$Excel.displayalerts=$False
$WorkBook = $Excel.Workbooks.Open("C:\DATEI_ANPASSEN.xlsx")
$Workbook.SaveAs("C:\AUSGABE.csv",$xlCSV)
$Excel.quit()

Wie bekomme ich das mit einer XLSM hin?

Bin für jeden Hinweis dankbar!
Feuerstein08

Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben

Content-ID: 141053

Url: https://administrator.de/forum/mit-powershell-eine-xlsm-nach-csv-konvertieren-141053.html

Ausgedruckt am: 22.04.2025 um 08:04 Uhr

7 Kommentare

Neuester Kommentar

Hallo,

ich bin mir bezüglich deines aktuellen Lösungswegs und der Makros nicht sicher, weiß auch nicht, was eine .xlsm-Datei ist. Aber ich stand auch kürzlich vor der erschreckenden Erkenntnis, dass das (zellenweise) Auslesen von Excel-Daten mittels COM _Ewigkeiten_ braucht. Aber es gibt einen Jet-SQL-Treiber für Excel (dazu benötigt der Server, auf dem das ausgeführt wird afaik noch nicht mal Excel installiert), mit dem geht das in Windeseile. Außerdem hat man die Daten dann gleich in den ADO.NET-Klassen (Dataset etc) und kann sie vernünftig durchsuchen. Zugegeben: ich musst etwas rumbasteln, bis es lief.

Mein Code (liest alle Blätter einer Arbeitsmappe aus und speichert sie in einer .csv):

		$file = $Pfad + "\" + $Dateiname  
		write-host Lese $type-Daten, Datei $inFile
		
		# Aufbau einer DB-Verbindung zur Excel-Datei
		$connString = "Provider=Microsoft.Jet.OleDb.4.0;data source=$file;Extended Properties=""Excel 8.0;HDR"""  
		$conn = New-Object "System.Data.OleDb.OleDbConnection"  
		$conn.ConnectionString = $connString
		$conn.Open()
		
		#Auslesen des Schemas aus Excel-Datei -> ermitteln der vorhandenen Arbeitsblätter
		$SchemaTable = $conn.GetOleDbSchemaTable([System.Data.OleDb.OleDbSchemaGuid]::Tables, ($null, $null, $null, "TABLE"))  
		
		#Tabelle anlegen, in der die einzelen Excel-Blätter gesammelt werden	
		$allDirs = New-Object System.Data.DataTable
		#alle Arbeitsblaetter durchgehen
		foreach($blatt in $SchemaTable.rows){
			#Tabelle anlegen fuer aktuelles Arbeitsblatt
			$currTable = New-Object System.Data.DataTable
			
			#Auslesen des Arbeitsblattes mittels oben aufgebauter Verbindung in Dataadapter
			$blattname = $Blatt.TABLE_NAME
			$sqlcmd = new-object System.Data.OleDb.OleDbCommand("SELECT * FROM [$blattname]",$conn)	  
			$da = New-Object System.Data.OleDb.OleDbDataAdapter($sqlcmd)
			
			#Blattinhalt in Tabelle uebernehmen
			$rowcount = $da.fill($currTable) #bei leerem Blatt: mit naechsten fortfahren
			if($rowcount -lt 1)
				{ continue }
		
			#aktuelle Tabelle zu der, die alle enthaelt hinzufuegen
			$allDirs.Merge($currTable)
		}
		#Alle Blaetter in der Datei bearbeitet, Datei schließen
		$conn.Close()

$allDirs | SELECT @{expression={($_."Spaltenname1")};Name="Name in CSV1"}, @{expression={"{0:0.000}" -f ($_.Zahlenwert)};Name="Wert"} | Export-Csv $datei -encoding utf8 -noTypeInformation  

Ein paar Zeilen aus dem Skript musste ich entfernen, normalerweise verarbeitet es mehrere Dateien, die dann alle in ein .csv ausgegeben werden & bereitet die Daten noch etwas auf. Sollte aber so lauffähig sein & Prinzip sollte klar werden.

Ach ja: Wenn du einfach ein Excel-Makros aus Powershell aufrufen willst wirst du wohl hier fündig: http://www.mvps.org/access/modules/mdl0007.htm

Gruß

Filipp

Hallo Filipp,

danke für deine Hilfe, aber leider bekomme ich diesen Fehler :

Exception calling "Open" with "0" argument(s): "Die externe Tabelle hat nicht das erwartete Format."
At C:\DOCUME~1\JWA\LOCALS~1\Temp\Untitled1.ps1:11 char:13
+         $conn.Open( <<<< )

Liegt das an meiner Excel-Version (2007) ?
Was muss ich da ändern?

Danke
Feuerstein08

(... und danke für den Link zu den Excel-Makros. Spielt hier zwar keine Rolle - kann man aber immer gebrauchen

)

[Edit Biber] Codetags nachgetragen, da sonst das "+" vor dem "$conn.Open" als Überschrift-Formatierung interpretiert wird. [/Edit]

Hallo,

Liegt das an meiner Excel-Version (2007) ?

ja, offenbar. Hatte gedacht ich hätte damals auch mit xls2007 getestet, offenbar aber nicht.

Was muss ich da ändern?

$connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$file;Extended Properties=""Excel 12.0 Xml;HDR=YES""";
funktioniert. Habe ich von http://www.connectionstrings.com/excel-2007 abgeschrieben.

Übrigens gerade über nochwas gestollpert: http://www.microsoft.com/downloads/details.aspx?displaylang=en&Fami ...
Dort bekommt man den OLEDB-Treiber zum herunterladen, so dass man die Skripte auch ohne installiertes Office ausführen kann.

Und noch ein Hinweis: Die Treiber funktionieren nur im 32Bit-Modus! Auf einem 64Bit-System muss man die Powershell explizit im 32bit-Mode starten.

Gruß

Filipp

Hallo Filipp,

wir kommen der Sache langsam näher.
Das du den Hinweis auf die original Seite wieder reingepackt hast war gut, denn dort
konnte ich den Fehler im Connect-String bzgl. "Makro" entdecken und einfügen..

Nun läuft das Skript zwar durch - bringt aber noch keinen vernünftigen Output.

Hier dein (angepasstes) Skript:

cls
		$Pfad = "C:\1"  
		$Dateiname = "Liste.xlsm"  
		$datei = "C:\1\Liste_neu.csv"  
		$file = $Pfad + "\" + $Dateiname  
		write-host Lese $type-Daten, Datei $inFile
		
		# Aufbau einer DB-Verbindung zur Excel-Datei
		$connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$file;Extended Properties=""Excel 12.0 Macro;HDR=YES""";  
		$conn = New-Object "System.Data.OleDb.OleDbConnection"  
		$conn.ConnectionString = $connString
		$conn.Open()
		
		#Auslesen des Schemas aus Excel-Datei -> ermitteln der vorhandenen Arbeitsblätter
		$SchemaTable = $conn.GetOleDbSchemaTable([System.Data.OleDb.OleDbSchemaGuid]::Tables, ($null, $null, $null, "TABLE"))  
		
		#Tabelle anlegen, in der die einzelen Excel-Blätter gesammelt werden	
		$allDirs = New-Object System.Data.DataTable
		#alle Arbeitsblaetter durchgehen
		foreach($blatt in $SchemaTable.rows){
			#Tabelle anlegen fuer aktuelles Arbeitsblatt
			$currTable = New-Object System.Data.DataTable
			
			#Auslesen des Arbeitsblattes mittels oben aufgebauter Verbindung in Dataadapter
			$blattname = $Blatt.TABLE_NAME
			$sqlcmd = new-object System.Data.OleDb.OleDbCommand("SELECT * FROM [$blattname]",$conn)	  
			$da = New-Object System.Data.OleDb.OleDbDataAdapter($sqlcmd)
			
			#Blattinhalt in Tabelle uebernehmen
			$rowcount = $da.fill($currTable) #bei leerem Blatt: mit naechsten fortfahren
			if($rowcount -lt 1)
				{ continue }
		
			#aktuelle Tabelle zu der, die alle enthaelt hinzufuegen
			$allDirs.Merge($currTable)
		}
		#Alle Blaetter in der Datei bearbeitet, Datei schließen
		$conn.Close()

$allDirs | SELECT @{expression={($_."Spaltenname1")};Name="Name in CSV1"}, @{expression={"{0:0.000}" -f ($_.Zahlenwert)};Name="Wert"} | Export-Csv $datei -encoding utf8 -noTypeInformation  

Heraus kommt soetwas :

"Name in CSV1",Wert  
,
,
,
,
,

Hast du eine Idee, woran das liegen könnte?

Danke
Feuerstein08

LESEN .... verstehen und umsetzen ist dann einfach(er).....

Wenn ich Trottel die letzte Zeile ein wenig genauer gelesen hätte, dann wäre die obige Antwort überflüssig gewesen.
Das klappt also - Super!

Nun muss ich noch ein wenig am "Header" basteln, denn mein Header besteht aus 5 Zeilen ...

KLASSE Filipp - vielen Dank du hast mir sehr geholfen!

Feuerstein08

(hmm - lag eventuell auch an meiner Pollenallergie - das stört den Denkapparat

)

Hi Filipp,

vielleicht noch eine Frage on Top ?

Wie bekomme ich am einfachsten Daten aus einem großen multidim Array in ein xxx.XLSM zurück?

Hintergrund: Ich habe ein großes XLSM File. Die Daten wollte ich in Excel in ein Array packen um sie zu bearbeiten (suchen, erstetzen, anfügen)
Das das aber seeeeehr langsam war, ist die Idee mit der CSV entstanden.

Das mit der CSV klappt ja nun soweit schon ganz gut (ich weiß nur noch nicht, wie ich 5 Header-Zeilen rausfiltern soll ...), die
Aktualisierung sollte (hoffentlich dann auch klappen) und dann wollte ich den ganzen Spaß (also das Array) wieder in eine neue, leere (die 5 Zeilen Header sind dann wieder da) XLSM-Datei reinkippen.

???

Danke
Feuerstein08

Hallo,

Wie bekomme ich am einfachsten Daten aus einem großen multidim Array in ein xxx.XLSM zurück?

Keine Ahnung.
Für einen einfachen Array würde ich versuchen den Weg rückwärts zu gehen: Daten in ein Dataset, und dann dieses über den Excel-OLE-DB-Treiber (eingangs von mir noch Jet genannt, ist aber nicht mehr die Jet-Engine) exportieren. Zumindest kannst du m.W. auch INSERT-Befehle (SQL) auf die Excel-Verbindung anwenden.

Hintergrund: Ich habe ein großes XLSM File. Die Daten wollte ich in Excel in ein Array packen um sie zu bearbeiten (suchen,
erstetzen, anfügen)

Jaaa... aber dann spare dir doch den .csv-Kram. Du kannst in eine DataSet ziemlich gut (und m.E. auch performant) Daten bearbeiten. Doku dazu: es handelt sich um die ganz normalen .NET-Klassen, so wie sie auch für C# oder VB.NET verwendet werden. Du kannst also als Vorlage Tutorials für jede dieser Sprachen verwenden (da gibt es viel....) und halt entsprechend in ps umsetzen.

(ich weiß nur noch nicht, wie ich 5 Header-Zeilen rausfiltern soll ...),

Null Problemo. Auf die Daten in einer DataTable kannst du auch über einen Index zugreifen (das geht wiederum bei reinem SQL nicht), ich lösche so auch meine erste Zeile (hatte es nur rausgenommen um nicht zu verwirren):

			#Blattinhalt in Tabelle uebernehmen
			$rowcount = $da.fill($currTable) #bei leerem Blatt: mit naechsten fortfahren
			if($rowcount -lt 1)
				{ continue }

	#Erste Zeile loeschen (Header)
	$currTable.Rows.RemoveAt(0)

		
			#aktuelle Tabelle zu der, die alle enthaelt hinzufuegen
			$allDirs.Merge($currTable)