Oct 04, 2019

8046

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

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.

$Textdatei = 'C:\Users\pvoelz\Desktop\Zahllisten\Zahlliste.txt'  
$CSV = 'C:\Users\pvoelz\Desktop\Zahllisten\Zahlliste_NEU.csv'  

((Get-Content $Textdatei) -replace '^\s+','' ) -replace ' *\t *',';' | convertfrom-csv -Delimiter ";" | `  
export-csv -path $CSV -delimiter ";" -Encoding utf8 -NoType  

$xlo = New-Object -ComObject excel.application
$xlo.visible = $true
$xlo.DisplayAlerts = $false
$xlw = $xlo.Workbooks.Open($CSV)
$xls = $xlw.worksheets.Item(1)

# Vorbereiten der Exceldatei für Aufteilung
$arrCols = @()
$arrCols += $xls.Range("C:C").EntireColumn  
$arrCols += $xls.Range("G:G").EntireColumn  
$arrCols += $xls.Range("H:H").EntireColumn  
$arrCols += $xls.Range("J:J").EntireColumn  
$arrCols += $xls.Range("K:K").EntireColumn  
$arrCols += $xls.Range("O:O").EntireColumn  
$arrCols | %{$_.Delete() | Out-Null}

$xlo.ActiveSheet.Range("A:I").EntireColumn.AutoFit()  

[int]$zeile = 2
[int]$spalte = 6
[string]$arrName = @()

$Wert = $xls.Cells.Item($zeile,$spalte).Text
do {
    $Wert = $xls.Cells.Item($zeile,$spalte).Text
    If (!($arrName -match $Wert)) # Wenn die Einrichtung noch nicht bekannt, dann füge Sie einem Array hinzu
    {
        $arrName += $Wert
        $LastSheet = $xlw.Worksheets|Select -Last 1
        $NewSheet = $xlw.worksheets.add($LastSheet)
        $NewSheet.Name = $Wert
        # Write-Host $Wert
    }
    $zeile++
}
while ($xls.Cells.Item($zeile,$spalte).Text.Length -gt 0) 

write-Host $arrName

# Speichern der Excel Datei
$xlo.ActiveWorkbook.SaveAS( "C:\Users\pvoelz\Desktop\Zahllisten\Neu.xlsx", [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault)  

# Cleanup
$xlo.DisplayAlerts = $true
$xls = $null
$xlw.Close()
$xlo.Quit()

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?

Please also mark the comments that contributed to the solution of the article

Content-Key: 501564

Url: https://administrator.de/contentid/501564

Printed on: April 24, 2024 at 06:04 o'clock

15 Comments

Latest comment

Group-Object ist wie dafür geschaffen

((Get-Content $Textdatei) -replace '^\s+','' ) -replace ' *\t *',';' | convertfrom-csv -Delimiter ";" | group "Zahlungsempfänger" | %{  
   $empfänger = $_.Name
$_.Group | export-csv "d:\Pfad\${empfänger}.csv" -NoType -Delimiter ":" -Encoding UTF8  
}

Moin,

warum so kompliziert?

Zitat von @Pat.bat:

$Textdatei = 'C:\Users\pvoelz\Desktop\Zahllisten\Zahlliste.txt'  
> $CSV = 'C:\Users\pvoelz\Desktop\Zahllisten\Zahlliste_NEU.csv'  
> 
> ((Get-Content $Textdatei) -replace '^\s+','' ) -replace ' *\t *',';' | convertfrom-csv -Delimiter ";" | `  
> export-csv -path $CSV -delimiter ";" -Encoding utf8 -NoType  
> 

Warum machst Du das? Naja, warum auch nicht.

$xlo = New-Object -ComObject excel.application
> $xlo.visible = $true
> $xlo.DisplayAlerts = $false
> $xlw = $xlo.Workbooks.Open($CSV)
> $xls = $xlw.worksheets.Item(1)
> 
> # Vorbereiten der Exceldatei für Aufteilung
> $arrCols = @()
> $arrCols += $xls.Range("C:C").EntireColumn  
> $arrCols += $xls.Range("G:G").EntireColumn  
> $arrCols += $xls.Range("H:H").EntireColumn  
> $arrCols += $xls.Range("J:J").EntireColumn  
> $arrCols += $xls.Range("K:K").EntireColumn  
> $arrCols += $xls.Range("O:O").EntireColumn  
> $arrCols | %{$_.Delete() | Out-Null}
> 

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:

import-csv $csv -delimiter ";" | select * -excludeproperty [Liste der zu löschenden Spalten]  | export-csv $csv -delimiter ";" -notypeinformation  

$xlo.ActiveSheet.Range("A:I").EntireColumn.AutoFit()  
> 
> [int]$zeile = 2
> [int]$spalte = 6
> [string]$arrName = @()
> 
> $Wert = $xls.Cells.Item($zeile,$spalte).Text
> do {
>     $Wert = $xls.Cells.Item($zeile,$spalte).Text
>     If (!($arrName -match $Wert)) # Wenn die Einrichtung noch nicht bekannt, dann füge Sie einem Array hinzu
>     {
>         $arrName += $Wert
>         $LastSheet = $xlw.Worksheets|Select -Last 1
>         $NewSheet = $xlw.worksheets.add($LastSheet)
>         $NewSheet.Name = $Wert
>         # Write-Host $Wert
>     }
>     $zeile++
> }
> while ($xls.Cells.Item($zeile,$spalte).Text.Length -gt 0) 
> 
> write-Host $arrName
> 
> # Speichern der Excel Datei
> $xlo.ActiveWorkbook.SaveAS( "C:\Users\pvoelz\Desktop\Zahllisten\Neu.xlsx", [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault)  
> 
> # Cleanup
> $xlo.DisplayAlerts = $true
> $xls = $null
> $xlw.Close()
> $xlo.Quit()

Und zum Schluss:

$datasets = import-csv $csv -delimiter ";"  
foreach($dataset in $datasets) {

   $dataset | export-csv -path "$($dataset.kundenname).csv" -Append -Delimiter ";" -NoTypeInformation  

}

Oder auch gleich am Stück:

$datasets = $(import-csv $csv -delimiter ";" | select * -excludeproperty [Liste der zu löschenden Spalten])   
foreach($dataset in $datasets) {

   $dataset | export-csv -path "$($dataset.kundenname).csv" -Append -Delimiter ";" -NoTypeInformation  

}

hth

Erik

Hallo Erik,

danke für deine Hilfe.

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

-replace '^\s+','' ) -replace ' *\t *',';'  

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

Kompletter Code:

$CSV = 'C:\Users\pvoelz\Desktop\Zahllisten\Zahlliste.txt'  

$arrCols = @("Buchungnummer", "angew.", "Abwahl", "Buchungsart", "Zahlmittel", "Betrag [€] auf/von Konto(Doppik)(...)")  

$datasets = $(((Get-Content $CSV) -replace '^\s+','' ) -replace ' *\t *',';' | convertfrom-csv $CSV -delimiter ";" | select * -excludeproperty [$arrCols])   
foreach($dataset in $datasets) {

   $dataset | export-csv -path "$($dataset.kundenname).csv" -Append -Delimiter ";" -NoTypeInformation  
}

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?

convertfrom-csv $CSV -delimiter ";"

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

Vielen Dank.

S. Code oben, reicht vollkommen für dein Vorhaben ...

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

Soweit läufts schon ganz gut, ich speichere so eine Zwischendatei ab, was vll gar nicht so verkehrt ist.

$Textdatei = 'C:\Users\pvoelz\Desktop\Zahllisten\Zahlliste.txt'  
$CSV = 'C:\Users\pvoelz\Desktop\Zahllisten\Zahlliste_NEU.csv'  
$arrCols = @("Buchungnummer", "angew.", "Abwahl", "Buchungsart", "Zahlmittel", "Betrag [€] auf/von Konto(Doppik)(...)")  

((Get-Content $Textdatei) -replace '^\s+','' ) -replace ' *\t *',';' | convertfrom-csv -Delimiter ";" | select * -excludeproperty $arrCols | `  
export-csv -path $CSV -delimiter ";" -Encoding utf8 -NoTypeInformation  

$datasets = $(import-csv $csv -delimiter ";")   
foreach($dataset in $datasets) {

   $dataset | export-csv -path "C:\Users\pvoelz\Desktop\Zahllisten\ZL\$($dataset.'ZE-Name').csv" -Append -Delimiter ";" -Encoding UTF8 -NoTypeInformation -Force  

}

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

 $dataset | export-csv -path "C:\Users\pvoelz\Desktop\Zahllisten\ZL\$($dataset.'ZE-Name').csv" -Append -Delimiter ";" -Encoding UTF8 -NoTypeInformation -Force  

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

export-csv : Ein Teil des Pfades "C:\Users\pvoelz\Desktop\Zahllisten\ZL\Landkreis Rostock\Schulve.,.csv" konnte nicht gefunden werden.  
In C:\Users\pvoelz\Desktop\TestZE.ps1:11 Zeichen:15
+ ...  $dataset | export-csv -path "C:\Users\pvoelz\Desktop\Zahllisten\ZL\$ ...  
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OpenError: (:) [Export-Csv], DirectoryNotFoundException
    + FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.ExportCsvCommand

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.

Moin,

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

 $dataset | export-csv -path "C:\Users\pvoelz\Desktop\Zahllisten\ZL\$($dataset.'ZE-Name').csv" -Append -Delimiter ";" -Encoding UTF8 -NoTypeInformation -Force  

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

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.

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

@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:

((Get-Content "$PfadText") -replace '^\s+','' ) -replace ' *\t *',';' | convertfrom-csv -Delimiter ";" | select * -excludeproperty $arrCols | where {$_."ZE-Name" -Like "ANKER"} `  
export-csv -path $CSV -delimiter ";" -Encoding utf8 -NoTypeInformation  

Anbei der Gesamtcode:

param(
    [string]$PfadText,
    [string]$Speicherpfad
)

$CSV = 'C:\Users\pvoelz\Desktop\Zahllisten\Zahlliste_NEU.csv' # in Temp Ordner generieren  
$arrCols = @("Buchungnummer", "angew.", "Abwahl")  
$arrNames = @("")  

((Get-Content "$PfadText") -replace '^\s+','' ) -replace ' *\t *',';' | convertfrom-csv -Delimiter ";" | select * -excludeproperty $arrCols | where {$_."ZE-Name" -Like "ANKER"} `  
export-csv -path $CSV -delimiter ";" -Encoding utf8 -NoTypeInformation  

$datasets = $(import-csv $csv -delimiter ";")   
foreach($dataset in $datasets) {

    $dataset | export-csv -path "$Speicherpfad$($dataset.'ZE-Name').csv" -Append -Delimiter ";" -Encoding UTF8 -NoTypeInformation -Force  

}

Vielen Dank im Voraus

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.
[...]

((Get-Content "$PfadText") -replace '^\s+','' ) -replace ' *\t *',';' | convertfrom-csv -Delimiter ";" | select * -excludeproperty $arrCols | where {$_."ZE-Name" -Like "ANKER"} `  
> export-csv -path $CSV -delimiter ";" -Encoding utf8 -NoTypeInformation  

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

@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.

ZE-Name
Volkssolidarität KV Rostock-Stadt e.V., 
Lebenshilfewerk Mölln-Hagenow gGmbH,

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?

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.

where-object {$_.ZE-Name -like "*ANKER*"}  

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.

$_.ZE-Name.trimend(",")

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

Moin,

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

$arrCols = @("Buchungnummer", "angew.", "Abwahl")  
$arrNames = @("*ANKER*", "*Kloster*")  

((Get-Content "$PfadText") -replace '^\s+','' ) -replace ' *\t *',';' | `  
convertfrom-csv -Delimiter ";" | `  
select * -excludeproperty $arrCols | `
where { $_."ZE-Name" -like $arrNames } |  `  
export-csv -path $CSV -delimiter ";" -Encoding utf8 -NoTypeInformation  

Oder habe ich was übersehen?

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

German solved Question Batch, Shell Development

Hotly discussed

Check of ZFW Firewallgleixnerd - 5 Comments

Wireguard VPN on UDM Pro behind Fritzbox - Handshake did not completejstricker - 3 Comments

How to set up and configure a Linux GRE tunnelAlexWisha - 3 Comments