gelöst Powershell - Bestimmte Daten aus Excel in neue Excel-Dateien schreiben und speichern

Mitglied: Pat.bat

Pat.bat (Level 1) - Jetzt verbinden

04.10.2019 um 12:30 Uhr, 1330 Aufrufe, 15 Kommentare

Hallo zusammen,

ich stocke bei folgender Aufgabe.
Ich habe eine Textdatei mit Zahlungsdaten, Aktenzeichen und dem Zahlungsempfänger.

Diese Datei konvertiere ich zuerst als CSV und lese sie dann in Excel ein.
Dort bereite ich dann die Daten auf. bestimmte Spalten löschen die unnötig sind und Spaltenbreite anpassen.

Nun möchte ich die Daten (Zeilen) nach Spalte F (Zahlungsempfänger) in eine eigene Excel Datei speichern, wobei die erste Zeile (Header) mit soll. Sodass ich am Ende beispielsweise 30 Excel Dateien habe wo nur die Buchungen der einzelnen ZEs drin sind.


Folgendes war meine erste Idee, welche nicht optimal ist.

Ich gehe durch jeden Eintrag in Spalte F. Wenn der Name noch nicht im Array vorhanden ist, dann speichere ihn dort ab und erstelle ein neues Worksheet [NOCH NICHT IM CODE => und kopiere den Header rein. Dort kopiere dann die aktuelle Zeile rein (Buchungsdaten).
Gehe zur nächsten Zeile, wenn diese noch nicht im Array ist, dann wie oben ansonsten kopiere Zeile, gehe zu dem Worksheet mit dem $Wert, gehe an letzte Zeile und füge ein.
usw.

Das stelle ich mir aber ziemlich PErformancelastig vor.

Einfacher wäre es wohl, die Tabelle zu Filtern.
D.h. ich hol mir erstmal ein Array mit allen möglichen ZE-Namen. Dann erstelle ich einen Loop der jeden Namen Filtert.
Ich Setze also einen Filter auf Spalte F, filter mit dem aktuellen $Wert aus dem Array und kopiere die ganze Tabelle in ein neues Worksheet und speichere das als neue Datei ab.

Geht es vll noch optimaler oder ist die Filter Variante die beste Option?
Mitglied: 141320
04.10.2019, aktualisiert um 12:40 Uhr
Group-Object ist wie dafür geschaffen
Bitte warten ..
Mitglied: erikro
LÖSUNG 04.10.2019 um 13:06 Uhr
Moin,

warum so kompliziert?


Zitat von Pat.bat:

Warum machst Du das? Naja, warum auch nicht.


Warum liest Du das in Excel ein und nimmst nicht die Powershell-Cmdlets für CSV? So wäre das viel kürzer und schneller:


Und zum Schluss:

Oder auch gleich am Stück:

hth

Erik
Bitte warten ..
Mitglied: Pat.bat
07.10.2019 um 10:28 Uhr
Hallo Erik,

danke für deine Hilfe.

in meiner Textdatei werden die Daten anhand von Leerzeichen und Tabs getrennt, daher

Also habe ich versucht, dein letztes Code-Beispiel dementsprechen anzupassen, aber ohne erfolg:

Kompletter Code:


D.h. wenn das Datasets gefüllt wird, dann soll er erst die Daten in der Textdatei richtig trennen, Tabs durch Semikolon ersetzen, dann anhand dessen die csv in Spalten aufteilen ohne die Spalten in arrCols zu nehmen.

Habe ich dort einen Denkfehler, oder wie müsste das dann geschrieben werden?

Daran scheint er sich aufzuhängen, aber ich weiß noch nicht warum.


Vielen Dank.
Bitte warten ..
Mitglied: 141320
07.10.2019, aktualisiert um 10:52 Uhr
S. Code oben, reicht vollkommen für dein Vorhaben ...
Bitte warten ..
Mitglied: erikro
07.10.2019 um 11:20 Uhr
Moin,

lies mal https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell. ... Damit kommst Du nicht weiter. Wenn Du das so machen willst, dann musst Du import-csv nehmen. Also erst die Delimiter tauschen (warum auch immer) und dann das manipulierte CSV als CSV einlesen. Dann hast Du die einzelnen Datensätze als CSV-Objekte in dem Array und kannst sie auch als CSV weiterverarbeiten.

Liebe Grüße

Erik
Bitte warten ..
Mitglied: Pat.bat
07.10.2019, aktualisiert um 11:47 Uhr
Soweit läufts schon ganz gut, ich speichere so eine Zwischendatei ab, was vll gar nicht so verkehrt ist.

Allerdings gibt es einen ZE-Name, der ein Slash (/) im Namen hat und das führt bei der Anweisung

dazu das er das als anderen Pfad ansieht und so die Fehlermeldung schmeißt, das dieser Pfad unbekannt ist.

Er macht in diesem Fall aus dem Slash ein Backslash.


Auch bekomme ich die Spalte "Betrag [€] auf/von Konto(Doppik)(...)" nicht raus gefiltert, was vermutlich an den Sonderzeichen liegen, die enthalten sind.
Bitte warten ..
Mitglied: erikro
07.10.2019 um 12:29 Uhr
Moin,

Zitat von Pat.bat:
Allerdings gibt es einen ZE-Name, der ein Slash (/) im Namen hat und das führt bei der Anweisung

dazu das er das als anderen Pfad ansieht und so die Fehlermeldung schmeißt, das dieser Pfad unbekannt ist.

Dann mit einer Regex am Besten gleich alle verbotenen Zeichen abfangen und durch z. B. Unterstrich ersetzen.
https://docs.microsoft.com/de-de/dotnet/standard/base-types/substitution ...
https://powershellexplained.com/2017-07-31-Powershell-regex-regular-expr ...

Auch bekomme ich die Spalte "Betrag [€] auf/von Konto(Doppik)(...)" nicht raus gefiltert, was vermutlich an den Sonderzeichen liegen, die enthalten sind.

Das sollte allerdings ohne Weiteres funktionieren. Tippfehler?

Liebe Grüße

Erik
Bitte warten ..
Mitglied: Pat.bat
07.10.2019 um 14:21 Uhr
Ich muss da leider nochmal umdenken, sodass xlsx Dateien dabei raus kommen. Grund, es ist eine Formatierung gewünscht, was leider nicht mit csv funktioniert.

Nun überlege ich, die CSV-Dateien trotzdem zu generieren und diese dann formatiert als xlsx abzuspeichern. Im nachhinein werden dann die csv wieder gelöscht.

Ist mit Sicherheit keine schöne Lösung, aber mir fällt da nichts besseres derzeit ein.
Bitte warten ..
Mitglied: erikro
07.10.2019 um 14:38 Uhr
Moin,

Zitat von Pat.bat:

Ich muss da leider nochmal umdenken, sodass xlsx Dateien dabei raus kommen. Grund, es ist eine Formatierung gewünscht, was leider nicht mit csv funktioniert.

Nun überlege ich, die CSV-Dateien trotzdem zu generieren und diese dann formatiert als xlsx abzuspeichern. Im nachhinein werden dann die csv wieder gelöscht.

Ist mit Sicherheit keine schöne Lösung, aber mir fällt da nichts besseres derzeit ein.

Wenn es denn eine Exceldatei als Ausgabe sein soll, dann guck Dir das mal an. Das macht das Leben mit Exceldateien unter PS sehr viel einfacher.
https://www.powershellgallery.com/packages/ImportExcel/5.4.0

Liebe Grüße

Erik
Bitte warten ..
Mitglied: Pat.bat
09.10.2019 um 14:24 Uhr
@erikro Noch eine Frage

In deinem Code-Beispiel, ist es dort auch möglich, eine Where-Abfrage einzubauen um zu sagen, welche Strings in Spalte ZE-Name er nur nehmen soll.

Damit möchte ich verhindern, das er Listen erstellt die gar nicht nötig wären.

Hatte mir das ungefähr so gedacht:


Anbei der Gesamtcode:


Vielen Dank im Voraus
Bitte warten ..
Mitglied: erikro
09.10.2019 um 14:38 Uhr
Moin,

Zitat von Pat.bat:
@erikro Noch eine Frage

In deinem Code-Beispiel, ist es dort auch möglich, eine Where-Abfrage einzubauen um zu sagen, welche Strings in Spalte ZE-Name er nur nehmen soll.
[...]

Klar geht das und es ist auch fast richtig. Guck mal hier:
https://www.windowspro.de/script/vergleichsoperatoren-powershell-eq-lt-g ...
Insbesondere der Abschnitt über -like.

Außerdem fehlt noch die Pipe zwischen dem Ende von where und dem export-csv.

Liebe Grüße

Erik
Bitte warten ..
Mitglied: Pat.bat
09.10.2019, aktualisiert um 15:12 Uhr
@erikro Super, danke .

-Like scheint dann das falsche zu sein, da ich auch ein Array übergeben möchte (Einrichtungsnamen werden in eine xml gespeichert. das Skript soll sich diese ziehen und in ein Array speichern).

Dazu muss man nun -Contains verwenden, allerdings muss der String hier exakt sein. Schöner wäre es, wenn contains like funktionieren würde.

Ursache ist, das einige Einträge in der Spalte ZE-Name hinten aus mir unerklärlichen Gründen ein Komma haben. Man in der App, die die xml erstellt aber normalerweise nur den Namen eingibt, ohne Komma.

Leider kann ich Kommas nicht generell raus nehmen, da einige Einträge Kommas mitten im String haben, welcher da auch hingehört.

Gibt es da vll ein Workaround?
Bitte warten ..
Mitglied: erikro
09.10.2019 um 15:46 Uhr
Moin,

Zitat von Pat.bat:
-Like scheint dann das falsche zu sein, da ich auch ein Array übergeben möchte (Einrichtungsnamen werden in eine xml gespeichert. das Skript soll sich diese ziehen und in ein Array speichern).

Dochdoch, -like ist schon richtig. Es fehlen bloß die Wildcards.

Ursache ist, das einige Einträge in der Spalte ZE-Name hinten aus mir unerklärlichen Gründen ein Komma haben. Man in der App, die die xml erstellt aber normalerweise nur den Namen eingibt, ohne Komma.

entfernt diese störenden Kommata am Ende des Strings. Guckst Du hier:
https://blog.stefanrehwald.de/2013/03/03/powershell-03-2-strings-bearbei ...

hth

Erik
Bitte warten ..
Mitglied: Pat.bat
09.10.2019 um 16:14 Uhr
Moin,

also mit -like funktioniert es nicht, er kann wohl nichts mit arrays anfangen:

Oder habe ich was übersehen?
Bitte warten ..
Mitglied: erikro
09.10.2019 um 16:23 Uhr
Moin,

Zitat von Pat.bat:
Oder habe ich was übersehen?

Ja, nämlich dass das so nicht in Deinem Code stand.

Guck mal hier: https://stackoverflow.com/questions/13019218/powershell-like-against-an- ...

So, nun ist Feierabend.

Liebe Grüße

Erik
Bitte warten ..
Heiß diskutierte Inhalte
Router & Routing
Lancom Router Site to Site Problem mit Außenstellen
gelöst mossoxFrageRouter & Routing24 Kommentare

Guten Tag zusammen, in der Hauptgeschäftsstelle nutzen wir einen Lancom 1781VA Router und haben i.d.R. zwei gleichzeitige IPSec Site ...

Netzwerke
Sporadische Ausfälle im gesamten Heimnetz
gelöst bilbo-dvdFrageNetzwerke20 Kommentare

Guten Morgen, ich habe einen Kabelanschluss bei Vodafone und mein Tarif wurde im März auf CableMax 1000 umgestellt. Ich ...

Router & Routing
RDP nur im internen Netz möglich nicht aber per vpn?
einfach112FrageRouter & Routing18 Kommentare

Hallo zusammen. Beim Kunden habe ich einen Server mit VMWARE laufen. Darauf ein Windows Server 2016 Essentials als VM ...

Sicherheit
Sicherheit oder bessere Benutzbarkeit?
StefanKittelFrageSicherheit17 Kommentare

Hallo, ich habe eine Webanwendung programmiert und sehe mich nun mit einer Frage zur Benutzbarkeit konfrontiert. Bei der Anmeldung ...

Internet
Über meinen WAN ist lidl.de nicht ereichbar
gelöst NetGodFrageInternet16 Kommentare

Hallo zusammen, mit meinem DTAG-Anschluß ist derzeit kein Durchkommen zu www.lidl.de möglich. Zu den selben Zeitpunkten ist es aber ...

Internet Domänen
Azure AD deactivate Identity Protection
Jannik2018FrageInternet Domänen15 Kommentare

kann mir einer sagen wie ich beim meiner MS Azure AD Domäne die Identity Protection deactiviere siehe Screenshot sollte ...

Ähnliche Inhalte
Batch & Shell
Excel mit Powershell formatieren
TastuserFrageBatch & Shell13 Kommentare

Hallo, ist es möglich eine Excel-Datei mit Powershell zu formatieren? - Bestimmte Zellen bekommen eine bestimmte Größe - Bestimmte ...

Batch & Shell
Powershell excel Zeilen entfernen
BlueEyePhoenixFrageBatch & Shell5 Kommentare

Hallo Commuity, ich hab da mal ein Problem bei dem ich nicht weiterkomme. Ich habe eine Excelliste mit ca ...

Microsoft Office

Excel: Liste aus anderer Excel-Datei erstellen

gelöst honeybeeFrageMicrosoft Office4 Kommentare

Hallo, kann man in Excel eine Dropdown-Liste (Datenüberprüfung) aus einer anderen Excel-Tabelle erstellen? Die Daten für die Liste befinden ...

VB for Applications

VB Skript Excel Datei

gelöst FragerFrageVB for Applications3 Kommentare

Hallo Zusammen, Ich brauche eure Hilfe. Ich habe eine Datei 1.xlsx nun brauche ich ein Skript, was die Datei ...

Microsoft Office

Excel Datei schreibgeschützt öffnen

xbast1xFrageMicrosoft Office2 Kommentare

Hallo zusammen, unser Vertrieb hat eine Excel Liste auf dem Netz, welche Probleme macht. User A öffnet die Liste ...

Microsoft Office

Excel 2016 - Verknüpfung zu anderer Excel Datei automatisch aktualisieren

devil7974FrageMicrosoft Office2 Kommentare

Hallo zusammen, ich habe eine Excel Datei auf meiner Dropbox liegen. Die Daten in dieser Datei werden regelmäßig gepflegt ...

Neue Fragen
Administrator Magazin
11 | 2020 Virtualisierung ist aus der IT nicht mehr wegzudenken. In der November-Ausgabe des IT-Administrator Magazins dreht sich der Schwerpunkt um das Thema "Server- und Storage-Virtualisierung". Darin erfahren Sie, wie sich die Virtualisierungstechnologie entwickelt hat, welche Varianten es im Bereich Server und Speicher gibt und wie ...
Neue Beiträge
Neue Jobangebote
Server- und Storage-VirtualisierungServer- und Storage-VirtualisierungBerechtigungs- und IdentitätsmanagementBerechtigungs- und IdentitätsmanagementWebdienste und -serverWebdienste und -serverDatenbankenDatenbankenMonitoring & SupportMonitoring & SupportHybrid CloudHybrid Cloud