Top-Themen

AppleEntwicklungHardwareInternetLinuxMicrosoftMultimediaNetzwerkeOff TopicSicherheitSonstige SystemeVirtualisierungWeiterbildungZusammenarbeit

Aktuelle Themen

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit
GELÖST

Mit Powershell eine XLSM nach CSV konvertieren

Frage Entwicklung

Mitglied: Feuerstein08

Feuerstein08 (Level 1) - Jetzt verbinden

20.04.2010 um 14:46 Uhr, 9122 Aufrufe, 7 Kommentare

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
Mitglied: filippg
21.04.2010 um 00:58 Uhr
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):
01.
		$file = $Pfad + "\" + $Dateiname 
02.
		write-host Lese $type-Daten, Datei $inFile 
03.
		 
04.
		# Aufbau einer DB-Verbindung zur Excel-Datei 
05.
		$connString = "Provider=Microsoft.Jet.OleDb.4.0;data source=$file;Extended Properties=""Excel 8.0;HDR""" 
06.
		$conn = New-Object "System.Data.OleDb.OleDbConnection" 
07.
		$conn.ConnectionString = $connString 
08.
		$conn.Open() 
09.
		 
10.
		#Auslesen des Schemas aus Excel-Datei -> ermitteln der vorhandenen Arbeitsblätter 
11.
		$SchemaTable = $conn.GetOleDbSchemaTable([System.Data.OleDb.OleDbSchemaGuid]::Tables, ($null, $null, $null, "TABLE")) 
12.
		 
13.
		#Tabelle anlegen, in der die einzelen Excel-Blätter gesammelt werden	 
14.
		$allDirs = New-Object System.Data.DataTable 
15.
		#alle Arbeitsblaetter durchgehen 
16.
		foreach($blatt in $SchemaTable.rows){ 
17.
			#Tabelle anlegen fuer aktuelles Arbeitsblatt 
18.
			$currTable = New-Object System.Data.DataTable 
19.
			 
20.
			#Auslesen des Arbeitsblattes mittels oben aufgebauter Verbindung in Dataadapter 
21.
			$blattname = $Blatt.TABLE_NAME 
22.
			$sqlcmd = new-object System.Data.OleDb.OleDbCommand("SELECT * FROM [$blattname]",$conn)	 
23.
			$da = New-Object System.Data.OleDb.OleDbDataAdapter($sqlcmd) 
24.
			 
25.
			#Blattinhalt in Tabelle uebernehmen 
26.
			$rowcount = $da.fill($currTable) #bei leerem Blatt: mit naechsten fortfahren 
27.
			if($rowcount -lt 1) 
28.
				{ continue } 
29.
		 
30.
			#aktuelle Tabelle zu der, die alle enthaelt hinzufuegen 
31.
			$allDirs.Merge($currTable) 
32.
33.
		#Alle Blaetter in der Datei bearbeitet, Datei schließen 
34.
		$conn.Close() 
35.
 
36.
$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
Bitte warten ..
Mitglied: Feuerstein08
21.04.2010 um 13:50 Uhr
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]
Bitte warten ..
Mitglied: filippg
21.04.2010 um 21:05 Uhr
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
Bitte warten ..
Mitglied: Feuerstein08
22.04.2010 um 10:41 Uhr
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:

01.
cls 
02.
		$Pfad = "C:\1" 
03.
		$Dateiname = "Liste.xlsm" 
04.
		$datei = "C:\1\Liste_neu.csv" 
05.
		$file = $Pfad + "\" + $Dateiname 
06.
		write-host Lese $type-Daten, Datei $inFile 
07.
		 
08.
		# Aufbau einer DB-Verbindung zur Excel-Datei 
09.
		$connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$file;Extended Properties=""Excel 12.0 Macro;HDR=YES"""; 
10.
		$conn = New-Object "System.Data.OleDb.OleDbConnection" 
11.
		$conn.ConnectionString = $connString 
12.
		$conn.Open() 
13.
		 
14.
		#Auslesen des Schemas aus Excel-Datei -> ermitteln der vorhandenen Arbeitsblätter 
15.
		$SchemaTable = $conn.GetOleDbSchemaTable([System.Data.OleDb.OleDbSchemaGuid]::Tables, ($null, $null, $null, "TABLE")) 
16.
		 
17.
		#Tabelle anlegen, in der die einzelen Excel-Blätter gesammelt werden	 
18.
		$allDirs = New-Object System.Data.DataTable 
19.
		#alle Arbeitsblaetter durchgehen 
20.
		foreach($blatt in $SchemaTable.rows){ 
21.
			#Tabelle anlegen fuer aktuelles Arbeitsblatt 
22.
			$currTable = New-Object System.Data.DataTable 
23.
			 
24.
			#Auslesen des Arbeitsblattes mittels oben aufgebauter Verbindung in Dataadapter 
25.
			$blattname = $Blatt.TABLE_NAME 
26.
			$sqlcmd = new-object System.Data.OleDb.OleDbCommand("SELECT * FROM [$blattname]",$conn)	 
27.
			$da = New-Object System.Data.OleDb.OleDbDataAdapter($sqlcmd) 
28.
			 
29.
			#Blattinhalt in Tabelle uebernehmen 
30.
			$rowcount = $da.fill($currTable) #bei leerem Blatt: mit naechsten fortfahren 
31.
			if($rowcount -lt 1) 
32.
				{ continue } 
33.
		 
34.
			#aktuelle Tabelle zu der, die alle enthaelt hinzufuegen 
35.
			$allDirs.Merge($currTable) 
36.
37.
		#Alle Blaetter in der Datei bearbeitet, Datei schließen 
38.
		$conn.Close() 
39.
 
40.
$allDirs | SELECT @{expression={($_."Spaltenname1")};Name="Name in CSV1"}, @{expression={"{0:0.000}" -f ($_.Zahlenwert)};Name="Wert"} | Export-Csv $datei -encoding utf8 -noTypeInformation 
41.
 
Heraus kommt soetwas :
01.
"Name in CSV1",Wert 
02.
03.
04.
05.
06.
,
Hast du eine Idee, woran das liegen könnte?

Danke
Feuerstein08
Bitte warten ..
Mitglied: Feuerstein08
22.04.2010 um 11:54 Uhr
.... 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 )
Bitte warten ..
Mitglied: Feuerstein08
22.04.2010 um 14:02 Uhr
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
Bitte warten ..
Mitglied: filippg
22.04.2010 um 20:42 Uhr
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):

01.
			#Blattinhalt in Tabelle uebernehmen 
02.
			$rowcount = $da.fill($currTable) #bei leerem Blatt: mit naechsten fortfahren 
03.
			if($rowcount -lt 1) 
04.
				{ continue } 
05.
 
06.
	#Erste Zeile loeschen (Header) 
07.
	$currTable.Rows.RemoveAt(0) 
08.
 
09.
		 
10.
			#aktuelle Tabelle zu der, die alle enthaelt hinzufuegen 
11.
			$allDirs.Merge($currTable)
Gruß

Filipp
Bitte warten ..
Neuester Wissensbeitrag
Internet

Unbemerkt - Telekom Netzumschaltung! - BNG - Broadband Network Gateway

(3)

Erfahrungsbericht von ashnod zum Thema Internet ...

Ähnliche Inhalte
Batch & Shell
Powershell Workflow, CSV Stapelverarbeitung Problem (6)

Frage von Rippchen zum Thema Batch & Shell ...

Windows Server
Powershell Import-CSV Komma ausschreiben plus New-ADUser Pipen (3)

Frage von Freakazoid89 zum Thema Windows Server ...

Batch & Shell
gelöst Mit PowerShell per CSV-File Ordner erstellen (19)

Frage von windelterrorist zum Thema Batch & Shell ...

Heiß diskutierte Inhalte
Windows Server
Outlook Verbindungsversuch mit Exchange (15)

Frage von xbast1x zum Thema Windows Server ...

Microsoft Office
Keine Updates für Office 2016 (13)

Frage von Motte990 zum Thema Microsoft Office ...

Grafikkarten & Monitore
Tonprobleme bei Fernseher mit angeschlossenem Laptop über HDMI (11)

Frage von Y3shix zum Thema Grafikkarten & Monitore ...