none
XLSX- und CSV-Datei mittels PowerShell einlesen, vergleichen und Werte ausgeben RRS feed

  • Frage

  • Hallo,

    ich habe bislang noch keine großartig praktische Erfahrung mit PowerShell.

    Ich würde gerne automatisiert 2 Dateien einlesen (eine XLSX-Datei und eine CSV-Datei) und die Inhalte miteinander vergleichen. Die übereinstimmenden Inhalte sollten dann in einer 3. Datei oder einem neuen Excel-Sheet ausgegeben werden.

    Als Beispiel:

    XLSX-Datei:

    Mail

    a.b@z.com

    c.d@z.com

    e.f@z.com

    CSV-Datei:

    Mail

    a.b@z.com

    g.h@z.com

    c.d@z.com

    In diesem Fall würde ich also gerne a.b@z.com und c.d@z.com ausgegeben bekommen, da nur diese übereinstimmen.

    Ist das möglich?

    Falls ja - könntet ihr mir dabei behilflich sein?

    Danke und Gruß

    Dienstag, 12. März 2019 15:09

Antworten

  • $A2DWorkbook = "Path from CSV"
    $SFWorkbook = "Path from XLSX1"
    $G2MWorkbook = "Path from XLSX2"
    $ExportPath = "Path for Export"
    Remove-Item -Path $ExportPath
    $A2DImport = Import-Csv -Path $A2DWorkbook
    $SFImport = Import-Excel -Path $SFWorkbook
    $G2MImport = Import-Excel -Path $G2MWorkbook
    Compare-Object -ReferenceObject $G2MImport -DifferenceObject $A2DImport -IncludeEqual -ExcludeDifferent -Property 'Mail' | Export-Excel -Path $ExportPath -WorksheetName 'G2M'
    Compare-Object -ReferenceObject $SFImport -DifferenceObject $A2DImport -IncludeEqual -ExcludeDifferent -Property 'Mail' | Export-Excel -Path $ExportPath -WorksheetName 'SF'

    Ich habe es jetzt mit dem o.g. Code hinbekommen.

    Danke für die Tipps! ;-)

    • Als Antwort markiert SchMat_x Dienstag, 19. März 2019 07:08
    Dienstag, 19. März 2019 06:49

Alle Antworten

  • Ist das möglich?

    Klar ... kein Problem ;-)

    ... könntet ihr mir dabei behilflich sein?

    Klar ... kein Problem ... Wir schreiben hier aber keinen gebrauchsfertigen Code auf Bestellung. Du wirst also die Hauptarbeit selbst erledigen müssen. ;-) Wenn Du das noch nicht getan hast, würde ich empfehlen, damit zu beginnen, die Grundlagen von Powershell zu erlernen.

    Das geht mit einem guten Buch, einem Kurs oder mit online verfügbaren Tutorials oder sogar Videos. Einen guten kostenlosen Video-Kurs gibt es bei der Microsoft Virtual Academy - Getting Started with Powershell. Du solltest nur nicht zu lange warten. MS plant die MVA zu schließen und stattdessen mit Microsoft Learning neu zu starten.

    Noch'n Tipp zum Einlesen von Excel-Dateien mit Powershell: standardmäßig ist da nix eingebaut. Aber mit dem sehr guten Modul von Doug Finke, ImportExcel, kann man sich das Leben viel leichter machen. ;-)

    Und wenn Du dann schon mal Code hast, den Du zeigen magst, kannst Du den hier posten und wir helfen Dir weiter wenn Du stecken bleibst.  ;-)  :-D

    Na denn viel Spaß!


    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''


    • Bearbeitet BOfH-666 Dienstag, 12. März 2019 15:44
    Dienstag, 12. März 2019 15:43
  • Da es sich um Exceldateien handelt, ist es einfacher dies direkt in Excel und VBA zu erledigen, da Excel native CSV's verarbeiten kann.
    Mittels Powershell die Excel.Application als "COM-Server" zu steuern verkompliziert die Lage von Powershell noch.
    Dienstag, 12. März 2019 17:39
  • ....  ist es einfacher dies direkt in Excel und VBA zu erledigen, da Excel native CSV's verarbeiten kann.
    Welch Frevel ....  wir sind hier im Powershell-Forum!!!!!   ;-)  :-D  :-P

    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''


    • Bearbeitet BOfH-666 Dienstag, 12. März 2019 18:11
    Dienstag, 12. März 2019 17:59
  • Mittels Powershell die Excel.Application als "COM-Server" zu steuern verkompliziert die Lage von Powershell noch.
    ...deswegen erledigt das oben zitierte Modul von Doug Finke das auch ohne Excel ;-) Lang lebe die Format-Spezifikation!

    Evgenij Smirnov

    I work @ msg services ag, Berlin -> http://www.msg-services.de
    I blog (in German) @ http://it-pro-berlin.de
    my stuff in PSGallery --> https://www.powershellgallery.com/profiles/it-pro-berlin.de/
    Exchange User Group, Berlin -> https://exusg.de
    Windows Server User Group, Berlin -> http://www.winsvr-berlin.de
    Mark Minasi Technical Forum, reloaded -> http://newforum.minasi.com


    In theory, there is no difference between theory and practice. In practice, there is.

    Dienstag, 12. März 2019 18:39
  • Das macht doch nichts;-), das heißt ja nicht, dass es nicht auch ohne geht. Ich bin nicht umsonst auch Querdenker.

    • Bearbeitet bfuerchau Dienstag, 12. März 2019 20:58
    Dienstag, 12. März 2019 20:55
  • Tjaaaaa ...  jetzt müssen wir abwarten, ob Kollege SchMat_x lieber das kleine, auf Office eingeschränkte und komplizierte vba benutzen möchte oder DAS GROSSE, UNIVERSELLE, MÄCHTIGE, LEICHT ZU ERLERNENDE POWERSHELL ....   ;-) :-D :-P

    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''


    • Bearbeitet BOfH-666 Dienstag, 12. März 2019 23:35
    Dienstag, 12. März 2019 23:34
  • Nun ja, was wäre die Powershell ohne die vielen tausend Helferlein, die sich um die speziellen Themen kümmern, die Powershell von alleine nicht kann?

    Stichwort: Installiere doch bitte dieses und jenes AddIn noch?

    VBA kompliziert?
    Das wage ich ja doch noch zu bezweifeln. Immerhin ist VBA ein Derivat von VB6 und VB6 war schlechthin die Anwendungsentwicklung.
    Es gibt immer noch genug Anwendungen, die sich mit VBA leichter und effektiver lösen lassen als mit dem komplizierten und nicht leicht zu erlernenden Powershell;-).

    Ich mache da noch vieles mit VBScript oder WSH.


    • Bearbeitet bfuerchau Mittwoch, 13. März 2019 17:43
    Mittwoch, 13. März 2019 17:42
  • Um das oben geschilderte Problem einmalig zu lösen braucht man weder PowerShell noch VBA, die eingebauten Excel Funktionen reichen hierfür schon aus. Aber wie heißt es so schön: viele Wege führen nach Rom und Reisende soll man nicht aufhalten.

    Mittwoch, 13. März 2019 21:05
  • Danke für die Tipps.

    Ich habe es jetzt tatsächlich mal rein mit Excel versucht und soweit auch zunächst für die manuelle Ausführung ein brauchbares Ergebnis bekommen.

    Folgende Vorgehensweise
    1. XLSX-Datei öffnen
    2. CSV-Datei in einem neuen Sheet importieren
    3. in dem Sheet in einer freien Spalte ("F" in diesem Fall) folgende Formel nutzen (=MATCH(B1,GültigeMails!C:C,0)) und für alle Zeilen anwenden
    4. falls eine Mail-Adresse sowohl in dem neuen Sheet, als auch in dem Sheet "GültigeMails" gelistet ist, taucht aufgrund der Formel nun in der entsprechenden Zeile der doppelten Mail-Adresse die Zeilennummer für die doppelte Mail-Adresse aus dem Sheet "GültigeMails" auf
    5. Conditional Formatting mit der Formel "=$F1>=1" für die Spalte "B" (hier sind die Mail-Adressen gelistet) anwenden, dadurch werden die doppelt vorhandenen Mail-Adressen farblich markiert

    Lässt sich das ggf. auch simpel mittels PowerShell/Macro/VBA automatisieren?
    Sodass man sich zumindest Schritt 2 bis 4 spart, falls möglich aber auch den ganzen Prozess.

    Donnerstag, 14. März 2019 09:38
  • Lässt sich das ggf. auch simpel mittels PowerShell/Macro/VBA automatisieren?

    Hmmm ... da würd' ich eigentlich gleich wieder auf meine erste Antwort verweisen.  ;-)  ... für einen erfahrenen Powersheller dürfte das keine große Herausforderung sein  ... ohne die Grundlagen von Powershell zu kennen, wird's vermutlich knifflig.

    Ohne Deinen beruflichen Hintergrund zu kennen, würde ich Dir aber sowieso empfehlen, Dir die Grundlagen von Powershell anzueignen. Wenn man administrativ  in "Windows-System-Umgebungen" unterwegs ist, kommt man eigentlich nicht mehr an Powershell vorbei, bzw. macht man sich das Leben deutlich einfacher.

    Wenn Du zwischendurch Zeit hast, schau Dir doch einfach mal den oben verlinkten MVA-Video-Kurs an (oder wenigstens die ersten Lektionen), dann siehst Du ja, ob es was für Dich ist.


    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''


    • Bearbeitet BOfH-666 Donnerstag, 14. März 2019 10:52
    Donnerstag, 14. März 2019 10:51
  • Ich hab es mittels Macros zumindest schon soweit hinbekommen, dass ich mir Schritt 3-5 spare.

    Ich schaue mal ob ich die anderen beiden Schritte auch noch mittels PowerShell o.ä. selbst hinbekomme.
    Ich wollte einfach nicht zu viel Zeit darein investieren, da die PowerShell keinen direkten Einfluss auf meinen Beruf hat, aber schaden tut es ja auch nicht ;-)

    Donnerstag, 14. März 2019 12:00
  • Hmmm ... was machst Du denn beruflich? ... wenn ich mal so neugierig sein darf ...

    OK, nur mal so "zum Anfüttern" ... in Powershell wären das - vorausgesetzt, Du hast das zusätzlich nötige Modul ImportExcel bereits installiert (was aber auch nur einmal ein Befehl wäre) - genau 3 Zeilen Code:
    1. Einlesen der CSV-Datei und Zuweisen der Daten zu einer Variablen mittels Import-CSV.

    2. Einlesen der XLSX-Datei und zuweisen der Daten zu einer Variablen mittels Import-Excel.

    3. Vergleichen der beiden Variablen mittels Compare-Object und Ausgabe des Ergebnisses mittels Pipe an Export-CSV oder Export-Excel.


    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''





    • Bearbeitet BOfH-666 Donnerstag, 14. März 2019 15:15
    Donnerstag, 14. März 2019 13:56
  • Ohne nun ins Detail zu gehen, ist das auch in Excel-VBA nicht sehr viel mehr Code.
    2 OpenFile's (da CSV direkt geöffent werden kann)

    Theoretisch kann ich nun auch mit SQL die beiden Tabellen vergleichen.

    "Hmmm ... was machst Du denn beruflich? ... wenn ich mal so neugierig sein darf ... "?
    Ganz einfach:
    www.fuerchau.de (freiberuflicher Freelancer IBM iSeries (RPG/COBOL), VB, VBA, VB6, C#, C++, ASP,...)
    www.ftsolutions.de (GF + Entwickler der gesamten BI-Suite)

    Reicht das so als Referenz?

    Donnerstag, 14. März 2019 17:50
  • "Hmmm ... was machst Du denn beruflich? ... wenn ich mal so neugierig sein darf ... "?
    Eigentlich meinte ich den OP. Aber trotzdem Danke.  ;-) :-D

    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''

    Donnerstag, 14. März 2019 18:02
  • Wenn man sich das Excel-Paket so ansieht, ist es wohl mit der Zeile 2 nicht ganz so einfach, wie du das vorstellst.
    Alleine sich durch das Paket-Modell zu kämpfen ist schon äußerst mühsam.
    Eine zusammenfassende Doku und Beschreibung der Funktionen und Parameter muss man sich an Hand der Beispiele selber zusammen suchen. Ohne die Beispiele auszuprobieren, ist ja kaum zu verstehen, was da gemacht wird. Ich persönlich finde das das Excel-Objektmodell erheblich übersichtlicher.
    Es wäre nett gewesen, wenn sich der Author daran gehalten hätte oder zumindest mal dokumentiert, wie sein Obekt-Modell denn aussieht.

    Da erscheint es mir doch schon schwierig, die richtige Code-"Zeile" zu produzieren, die ein Objekt identisch zum Import-CSV erzeugt um es überhaupt per Compare-Objekt vergleichen zu können.

    Freitag, 15. März 2019 10:52
  • Na, n' bissl Jugend forscht ist bei den Community-Modulen halt immer mit dabei.

    Persönlich hab ich's noch nie gebraucht. Ich erzeuge meine Quellen entweder selbst oder verdonnere meine Lieferanten einfach dazu, es in validem CSV anzuliefern.  ;-)

    Doug Finke hat ein paar Erklär-Videos dazu produziert. Vielleicht erleuchten Dich die ja ein wenig mehr als die quasi nicht vorhandene Dokumentation/Hilfe. 


    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''

    Freitag, 15. März 2019 11:11
  • Du und ich werden mit sowas u.U. zurechtkommen aber ein User wie SchMat_X?
    Wenn einer meiner Kunden was mit Excelfiles machen will, so muss er auch Excel installiert haben, sonst macht das Ganze überhaupt keinen Sinn.
    Da soll er, wie du schon sagtest, eben direkt CSV's schicken lassen.
    Freitag, 15. März 2019 12:41
  • Also ich habe mich jetzt inzwischen noch ein wenig mehr damit befasst.

    Die Durchführung mittels Makros und PowerShell klappt jetzt soweit voll automatisiert.
    Das einzige Problem hier ist, dass ich noch nicht herausgefunden habe, wie man mittels Makro eine CSV-Datei per Wildcard einließt, da sich der Name der CSV-Datei aus dem Zeitstempel generiert.
    Hat hier jemand eine Idee?

    Zusätzlich habe ich jetzt versucht es ausschließlich mittels PowerShell zu lösen.
    Ganz so einfach wie hier dargestellt ist es leider nicht, auch aufgrund des Sicherheitsstandards unseres Unternehmens.
    Dadurch ist es z.B. nicht möglich per PowerShell aus einer Online-Repository ein Modul zu ziehen.
    Ich habe das Modul jetzt manuell heruntergeladen und in den entsprechenden Windows PowerShell-Ordner kopiert, dadurch lässt es sich nun nutzen.

    Das Script umfasst zurzeit folgenden Code:

    $SFWorkbook = "C:\Temp\File1"
    $ATDWorkbook = "C:\Temp\File2"
    $CSVImport = Import-Csv -Path $ATDWORKBOOK
    $ExcelImport = Import-Excel -Path $SFWORKBOOK
    $Compare-Object -ReferenceObject $(Get-Content $ExcelImport) -DifferenceObject $(Get-Content $CSVImport) -IncludeEqual | Export-Excel -Path "C:\Temp\File3"
    Bei Zeile 5 gibt es aber noch Probleme.

    Wie kriege ich es hier hin, dass er eine bestimmte Spalte aus der CSV-Datei mit einer bestimmten Spalte aus der Excel-Datei vergleicht und nur die Mail-Adressen in "File3" schreibt, die sowohl in "File1", als auch "File2" auftauchen?


    Freitag, 15. März 2019 14:24
  • Bitte Code hier im Forum als Code formatieren! Danke.

    Wenn es an den fehlenden Admin-Rechten klemmt, kann man das Modul auch nur für den aktuell angemeldeten Benutzer instalieren:

    Find-Module -Name ImportExcel |
        Install-Module -Scope CurrentUser -Force

    Das muss man, wie oben schon erwähnt nur ein einziges Mal machen.

    Du kannst ja mal versuchen, die relevante Tabelle der Excel-Datei als CSV abzuspeichern - dann geht's auch ohne extra Modul.

    Gesetzt den Fall, dass Du zwei CSV-Dateien hast, die wenigstens eine Spalte gemeinsam haben, deren Spaltenkopf jeweils "MailAdresse" lautet, sollten diese 3 Zeilen ausreichen:

    $Daten01 = Import-Csv -Path 'C:\Temp\File1.csv' -Delimiter ';' -Encoding UTF8
    $Daten02 = Import-Csv -Path 'C:\Temp\File2.csv' -Delimiter ';' -Encoding UTF8
    Compare-Object -ReferenceObject $Daten01 -DifferenceObject $Daten02 -IncludeEqual -ExcludeDifferent -Property 'MailAdresse' | 
        Export-Csv -Path 'C:\Temp\File3.csv' -Delimiter ';' -NoTypeInformation -Encoding UTF8

    Wenigstens kannst Du damit schon mal ein bissl "spielen". Du kannst ja erstmal den Export in eine Datei weglassen und Dir das Ergebnis auf der Konsole anschauen.

    Viel Spaß!  ;-)


    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''



    • Bearbeitet BOfH-666 Freitag, 15. März 2019 15:38
    Freitag, 15. März 2019 15:35
  • Mittels Get-Childitem lassen sich die Dateien, die du benötigst ermitteln:
    https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.management/get-childitem?view=powershell-6

    Beim Compare-Object kann man per "-properties" ein Array der Eigenschaften (in diesem fall Spalten-Name) angeben.

    https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/compare-object?view=powershell-6

    Das setzt natürlich voraus, dass die Excel-Datei entspprechende Spaltennamen hat.

    Freitag, 15. März 2019 15:40
  • $A2DWorkbook = "Path from CSV"
    $SFWorkbook = "Path from XLSX1"
    $G2MWorkbook = "Path from XLSX2"
    $ExportPath = "Path for Export"
    Remove-Item -Path $ExportPath
    $A2DImport = Import-Csv -Path $A2DWorkbook
    $SFImport = Import-Excel -Path $SFWorkbook
    $G2MImport = Import-Excel -Path $G2MWorkbook
    Compare-Object -ReferenceObject $G2MImport -DifferenceObject $A2DImport -IncludeEqual -ExcludeDifferent -Property 'Mail' | Export-Excel -Path $ExportPath -WorksheetName 'G2M'
    Compare-Object -ReferenceObject $SFImport -DifferenceObject $A2DImport -IncludeEqual -ExcludeDifferent -Property 'Mail' | Export-Excel -Path $ExportPath -WorksheetName 'SF'

    Ich habe es jetzt mit dem o.g. Code hinbekommen.

    Danke für die Tipps! ;-)

    • Als Antwort markiert SchMat_x Dienstag, 19. März 2019 07:08
    Dienstag, 19. März 2019 06:49