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, 9802 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
Internet
EU-Urheberrechtsreform: Zusammenfassung
Information von Frank vor 1 TagInternet

Auf golem.de gibt es eine Analyse von Friedhelm Greis, der das Thema EU-Urheberrechtsreform gut und strukturiert zusammenfasst. Zwar haben ...

Microsoft Office

Office365 Schwachstellen bei Sicherheit und Datenschutz

Information von Penny.Cilin vor 2 TagenMicrosoft Office7 Kommentare

Auf Heise+ gibt es einen Artikel bzgl. Office365 Schwachstellen. Das ist noch ein Grund mehr seine Daten nicht in ...

Sicherheit
Schwachstellen in VPN Clients
Tipp von transocean vor 4 TagenSicherheit2 Kommentare

Moin, es gibt Sicherheitslücken bei VPN Clients namhafter Hersteller, wie man hier lesen kann. Gruß Uwe

Sicherheit

Wikileaks-Gründer Julian Assange wurde festgenommen

Information von Frank vor 6 TagenSicherheit3 Kommentare

Wikileaks-Gründer Julian Assange wurde heute in London festgenommen. Die Botschaft Ecuadors, in der er seit sieben Jahren lebte, hat ...

Heiß diskutierte Inhalte
HTML
Google maps
Frage von jensgebkenHTML20 Kommentare

Hallo Gemeinschaft, hab mal ne Frage zu Maps - habe es hinbekommen, dass ich einen iframe link erstellen kann ...

Drucker und Scanner
Xerox 7328 muss jeden Tag neu installert werden
Frage von PN-SchrauberDrucker und Scanner20 Kommentare

hallo, ich habe in Problem mit einem unserer Drucker. Vorweg, die meisten unserer Netzwerkdrucker laufen über einen Druckserver, dieser ...

Tipps & Tricks
Verdammt voll erwischt
Frage von AlchimedesTipps & Tricks19 Kommentare

Folgende Mail habe ich auf meinem Freenet Account erhalten: Nun folgendes Problem: 1) Ich besuche Porno Seiten yep hat ...

Switche und Hubs
PC Verursach Probleme im Netzwerk
gelöst Frage von spoboeSwitche und Hubs16 Kommentare

Hallo zusammen, folgene Situation macht mich inzwischen ratlos: In unserem Betrieb ist ein Rechner über die Hausverkabelung an einem ...