Top-Themen

Aktuelle Themen (A bis Z)

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

Mitglied: Feuerstein08

Feuerstein08 (Level 1) - Jetzt verbinden

20.04.2010 um 14:46 Uhr, 9843 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 ..
Ähnliche Inhalte
Batch & Shell
Powershell xlsm checkbox abfragen
gelöst Frage von H41mSh1C0RBatch & Shell6 Kommentare

Aloa in die Runde, ich habe hier einen Berg xlsm Dateien. Alle sind als Formular verbastelt und enthalten mehrere ...

Batch & Shell
Powershell - csv - Sonderzeichen
Frage von Franz-Josef-IIBatch & Shell8 Kommentare

Einen wunderschönen arbeitsreichen Tag Ich bin gerade dabei csv-Dateien mittels Powershell zu vergleichen und Unterschiede bzw Gemeinsamkeiten abzuspeichern. Mein ...

Microsoft
PowerShell: CSV in CSV kopieren
gelöst Frage von PludanMicrosoft3 Kommentare

Hallo Ich bin an einem PS Skript dran der den Inhalt von CSV 1 in CSV 2 kopiert. Wie ...

Batch & Shell
Powershell in CSV
gelöst Frage von HistorikBatch & Shell6 Kommentare

Hallo zusammen, ich fange gerade mit Powershell an und bin daher noch ein Anfänger ,allerdings ich brauche ein Skript ...

Neue Wissensbeiträge
Off Topic
Europawahl 2019 - Ein Statement der Jugend
Information von Frank vor 1 TagOff Topic22 Kommentare

Dies ist ein offener Brief. Ein Statement. Von einem großen Teil der Youtuber-Szene. Am Wochenende sind die EU-Wahlen und ...

Off Topic
Europawahl 2019
Information von Frank vor 2 TagenOff Topic48 Kommentare

Vom 23. bis 26. Mai 2019 findet die Europawahl in den Mitgliedstaaten der Europäischen Union statt (ja auch in ...

Humor (lol)

Minister wollen offenbar Ausweispflicht für .de-Domain

Information von Kraemer vor 3 TagenHumor (lol)8 Kommentare

Zitat von Golem.de: Die zuständigen Verbraucherschutzminister fordern einem Medienbericht zufolge offenbar eine Ausweispflicht für .de-Domains. Das soll Betrugsfälle mit ...

Off Topic
Was als Noob hier mal gesagt werden musste
Information von th30ther vor 4 TagenOff Topic5 Kommentare

Moinsen wertes Forum, ich möchte mich an dieser Stelle mal beim Forum generell und bei aqui speziell bedanken! Ich ...

Heiß diskutierte Inhalte
Router & Routing
Portfreigabe Fritzbox - Plex usw
Frage von chkdskRouter & Routing23 Kommentare

Moin Zusammen, ich habe zu Hause eine Fritzbox 6490 Cable zu stehen und bin via DSLite connected -> also ...

Off Topic
Europawahl 2019 - Ein Statement der Jugend
Information von FrankOff Topic22 Kommentare

Dies ist ein offener Brief. Ein Statement. Von einem großen Teil der Youtuber-Szene. Am Wochenende sind die EU-Wahlen und ...

Backup
Alles Veeam, oder was?
gelöst Frage von IT-ProBackup14 Kommentare

Hallo Kollegen, Ich blicke bei den Produktfeatures bei Veeam nicht durch. Ich würde gern mein Home-Lab mittels Veeam Produkten ...

LAN, WAN, Wireless
Welches Material ist das Richtige?
Frage von Motte990LAN, WAN, Wireless13 Kommentare

Guten Morgen Leute, ich bin aktuell damit beschäftigt in unserm neu gekauften Haus das Netzwerk einzubauen. Aktuell wurden bis ...