Excel oder Powershell

mibro · 17 Januar 2024

Guten Morgen,

ich Suche eine Lösung um URLs zu ersetzen die einen bestimmten Dateinamen haben.
Die Urls sind alle in einer Spalte und sind mit Pipe getrennt. Eine Zelle kann bis zu 15 Urls enthalten

Für eine Spalte hatte ich bereits eine Lösung gefunden. Welche Anpassungen sind notwendig, damit die gesamte Spalte durchsucht wird?
Also nicht nur eine Zelle.

Diese Formel funktioniert mit einer Zelle
=ERSETZEN(A15; FINDEN(B1; A15);LÄNGE(B1);"§https://www.ersetze-mich.de/2009/optimiert/"&B1&"|")

wenn ich diese Formel einsetze dann kommt im Ergebnis folgende Ausgabe #WERT!
=ERSETZEN(A1;FINDEN(B1;INDEX(A1:A36;VERGLEICH("*";A1:A36;0)));LÄNGE(B1);"§https://www.ersetze-mich.de/2009/optimiert/"&B1&"|")

Ich würde mich sehr freuen, wenn jemand eine Tipp für mich hat.

Danke im Voraus

Mit freundlichen Grüßen
Michael

mibro · 17 Januar 2024

Hallo, über Powershell kam ich leider auch nicht wirklich weiter.
Ich hoffe das Skript geht in die richtige Richtung.

# Pfade zu den Dateien definieren
$csvDatei = "Z:\Ordner1\Shopoptimierung\Bilder-optimieren\URLs.csv"
# Mapping der Dateinamen und den zugehörigen Ersetzungstexten
$ersetzungen = @{
"3_Kupplungsbolzen_fuer_rollytoys_Traktoren_Stueck_rolly_toys_Zubehoer_Trettraktoren_4006485955169_67638961.jpg"="https://www.website.de/2009/optimiert/"
"3_Minuten_Geschichten_fuer_schoene_Traeume_Kinderbuch_gondolino_Kinderbuecher_9783811234796_62280400.jpg"="https://www.website.de/2009/optimiert/"
"3_Minuten_Geschichten_fuer_schoene_Traeume_Kinderbuch_gondolino_Kinderbuecher_9783811234796_62280400_1.jpg"="https://www.website.de/2009/optimiert/"
"3_Minuten_Geschichten_fuer_schoene_Traeume_Kinderbuch_gondolino_Kinderbuecher_9783811234796_62280400_9.jpg"="https://www.website.de/2009/optimiert/"

# Weitere Dateinamen und Ersetzungstexte hinzufügen
}
# CSV-Datei einlesen
$csvInhalt = Import-Csv -Path $csvDatei -Delimiter ","
# Durch die Zeilen der CSV-Datei iterieren
foreach ($zeile in $csvInhalt) {
# Überprüfen, ob der gefundene Dateiname in der aktuellen Zeile vorkommt
foreach ($ersatz in $ersetzungen.Keys) {
if ($zeile."Dateiname" -eq $ersatz) {
# Textersetzung durchführen
$zeile."Link" = $zeile."Link" -replace "(^.*)$ersatz", $ersetzungen[$ersatz]
}
}
}
# Geänderte CSV-Datei speichern
$csvInhalt | Export-Csv -Path $csvDatei -Delimiter "," -NoTypeInformation

Mit freundlichen Grüßen
Michael

TK87 · 18 Januar 2024

Moin Michael.

mibro schrieb:
$ersetzungen = @{...}

bleibt der zu ersetzende Text wie im Beispiel für alle URL's gleich? Dann bräuchte man lediglich einen Array und keinen Hashtable. Da dies bislang unklar ist, mache ich derzeit mal mit Hashtable weiter...

$zeile."Link" = $zeile."Link" -replace "(^.*)$ersatz", $ersetzungen[$ersatz]

So wie ich das sehe, willst du doch den kompletten Link ersetzen, oder? Dann kann man den Link direkt setzen und den Replace-Operator nicht.

Da der Replace-Operator reguläre Ausdrücke und auch im Key Regex-Steuerzeichen enthalten sein könnten, sollte man den Key mit der Escape-Methode der Regex-Klasse escapen: "^.*$([regex]::Escape($ersatz))"

Code:

# Pfade zu den Dateien definieren
$csvDatei = "Z:\Ordner1\Shopoptimierung\Bilder-optimieren\URLs.csv"

# Mapping der Dateinamen und den zugehörigen Ersetzungstexten
$ersetzungen = @{
  "3_Kupplungsbolzen_fuer_rollytoys_Traktoren_Stueck_rolly_toys_Zubehoer_Trettraktoren_4006485955169_67638961.jpg" = "https://www.website.de/2009/optimiert/"
  "3_Minuten_Geschichten_fuer_schoene_Traeume_Kinderbuch_gondolino_Kinderbuecher_9783811234796_62280400.jpg"       = "https://www.website.de/2009/optimiert/"
  "3_Minuten_Geschichten_fuer_schoene_Traeume_Kinderbuch_gondolino_Kinderbuecher_9783811234796_62280400_1.jpg"     = "https://www.website.de/2009/optimiert/"
  "3_Minuten_Geschichten_fuer_schoene_Traeume_Kinderbuch_gondolino_Kinderbuecher_9783811234796_62280400_9.jpg"     = "https://www.website.de/2009/optimiert/"
}

# CSV-Datei einlesen
$csvInhalt = Import-Csv -Path $csvDatei -Delimiter ","

# Durch Ersetzungen iterieren
foreach ($ersetzung in $ersetzungen.GetEnumerator()) {
  # Durch CSV-Zeilen iterieren, deren Dateiname mit dem Key übereinstimmt
  foreach ($zeile in $csvInhalt | ? Dateiname -eq $ersetzung.Key) {
    $zeile.Link = $ersetzung.Value
  }
}

# Geänderte CSV-Datei speichern
$csvInhalt | Export-Csv -Path $csvDatei -Delimiter "," -NoTypeInformation

Try It Online

tio.run

Gruß Thomas

mibro · 18 Januar 2024

Guten Morgen Thomas,

im Anhang sind 2 csv-Dateien plus das Powershell-Skript als Beispiel.
Ziel ist es stets nur den vorderen Teil der Url zu ändern.

Also suche mir die Url mit dem Dateinamen xyz und ersetze mir den vorderen Teil der URL.

Beispiel

https://www.website.de/media/image/63/15/78/xyz.jpg

Ergebnis:

https://www.website.de/2009/optimiert/xyz.jpg

Die gesamten Urls sind in einer Spalte und mit Pipe getrennt.

Ich bin gespannt auf Deine Antwort.

Vielen Dank vorab.

Mit freundlichen Grüßen
Michael

TK87 · 18 Januar 2024

Du hast in deinem Skript bei Import-CSV Kommas als Delimiter gesetzt, in deiner Beispieldatei sind jedoch Semikolons gesetzt... ich nehme also Semikolons. Falls doch Kommas gesetzt sind, ersetze den Delimiter

Code:

# Quell- und Zieldatei
$source = "D:\Test\Quelle.csv"
$target = "D:\Test\Ziel.CSV"
$neueUrl = "https://www.website.de/2009/optimiert/"
$ersetzungen = @(
  "3_Kupplungsbolzen_fuer_rollytoys_Traktoren_Stueck_rolly_toys_Zubehoer_Trettraktoren_4006485955169_67638961.jpg"
  "3_Minuten_Geschichten_fuer_schoene_Traeume_Kinderbuch_gondolino_Kinderbuecher_9783811234796_62280400.jpg"
  "3_Minuten_Geschichten_fuer_schoene_Traeume_Kinderbuch_gondolino_Kinderbuecher_9783811234796_62280400_1.jpg"
  "3_Minuten_Geschichten_fuer_schoene_Traeume_Kinderbuch_gondolino_Kinderbuecher_9783811234796_62280400_9.jpg"
  #...
)

# CSV-Datei einlesen
$csvInhalt = Import-CSV -d ';' -path $source

# Durch Ersetzungen iterieren
foreach ($ersetzung in $ersetzungen) {
 
  # Durch Zeilen iterieren, deren imageUrl den Dateinamen enthält
  foreach ($zeile in $csvInhalt | ? imageUrl -like "*$ersetzung*") {
 
    # Durch die Links der Zeile iterieren
    $links = foreach ($url in $zeile.imageUrl.split('|')) {
      $dateiname = $url | Split-Path -Leaf
      if ($dateiname -eq $ersetzung) {$neueUrl + $dateiname} else {$url}
    }
 
    # Alte Links durch die neuen ersetzen
    $zeile.imageUrl = $links -Join '|'
 
  }
}

# Csv-Datei ausgeben
$csvInhalt | Export-CSV -d ';' -nti -path $target

Mit etwas RegEx wird der Code noch deutlich kürzer, falls du das jedoch nicht beherrschst, ist das obere vermutlich besser verständlich.

Code:

# Quell- und Zieldatei
$source = "D:\Test\Quelle.csv"
$target = "D:\Test\Ziel.CSV"
$neueUrl = "https://www.website.de/2009/optimiert/"
$ersetzungen = @(
  "3_Kupplungsbolzen_fuer_rollytoys_Traktoren_Stueck_rolly_toys_Zubehoer_Trettraktoren_4006485955169_67638961.jpg"
  "3_Minuten_Geschichten_fuer_schoene_Traeume_Kinderbuch_gondolino_Kinderbuecher_9783811234796_62280400.jpg"
  "3_Minuten_Geschichten_fuer_schoene_Traeume_Kinderbuch_gondolino_Kinderbuecher_9783811234796_62280400_1.jpg"
  "3_Minuten_Geschichten_fuer_schoene_Traeume_Kinderbuch_gondolino_Kinderbuecher_9783811234796_62280400_9.jpg"
  #...
).Foreach({ [regex]::Escape($_) }) -Join '|'

# CSV-Datei einlesen
$csvInhalt = Import-CSV -d ';' -path $source

# Durch CSV-Zeilen
foreach ($zeile in $csvInhalt) {
  $zeile.imageUrl = $zeile.imageUrl -replace "https:[^|]+(?=$ersetzungen)",$neueUrl
}

# Csv-Datei ausgeben
$csvInhalt | Export-CSV -d ';' -nti -path $target

mibro · 18 Januar 2024

Vielen Dank für Deine Hilfe.

Ich teste nochmals alles heute Abend.

Eine andere Frage. Welche Software würdest Du persönlich empfehlen für die Skalierung von Bildern mit unterschiedlichen Formaten?
Die Bilder sind in unterschiedlichen Formaten und sollen in 1280x1280 Pixel eingepasst werden.
Später optimiere ich die Bilder tinypng.

Für die Optimierung möchte ich nur den Quell und Zielordner angeben.
Aktuell nutze ich Image Resizer. Ich bin aber mit dem Ergebnis nicht zufrieden.

Mit freundlichen Grüßen
Michael

TK87 · 18 Januar 2024

mibro schrieb:
Eine andere Frage. Welche Software würdest Du persönlich empfehlen für die Skalierung von Bildern mit unterschiedlichen Formaten?

Das ginge zwar theoretisch in Powershell auch mit Bordmitteln - ich persönlich nutze dafür aber immer lieber

ImageMagick – Create, Edit, Compose, or Convert Digital Images

ImageMagick is a free and open-source software suite used for editing and manipulating digital images. It provides a versatile set of tools for various image-related tasks. You can use ImageMagick to resize, convert, and apply various transformations to images. It supports a wide range of file...

imagemagick.org

Code:

# Quell- und Zielpfad
$source = "D:\Quelle"
$target = "D:\Ziel"

foreach ($jpg in Get-ChildItem "$Source\*.jpg") {
  # Ausgabe als "<Zielpfad>\<Name>_1280.jpg"
  $output = "{0}\{1}_1280.jpg" -f $target, $jpg.BaseName

  # Bild skalieren
  magick $jpg.fullname -scale "1280x1280" $output
}

mibro · 18 Januar 2024

Hallo Thomas, ist es normal das der Prozess bei ca. 13800 Zeilen sehr lange dauert?
Aktuell sind es bereits ca. 20 Minuten ohne eine Ausgabe.

Mit freundlichen Grüßen
Michael

TK87 · 19 Januar 2024

13800 Zeilen ist für Powershell eigentlich nicht sonderlich viel. Ich weiß zwar nicht weiß, wie viele Links pro Zeile enthalten sind - aber 20 Minuten ist definitiv zu lange.

Als erstes solltest du mal die Ausführungsrichtlinie setzen. Ich habe schon mal erlebt, das ein Skript trotz dem Bestätigen der Meldung aus deinem Screenshot nicht weiter gelaufen ist.

Falls du nur einen Benutzer auf dem System hast, kannst du die Richtlinie für diesen setzen, indem du folgenden Befehl einmalig ausführst:

Code:

Set-ExecutionPolicy -Scope CurrentUser -force RemoteSigned

Falls du mehrere Benutzer auf dem System hast, kannst du die Richtlinie auch für alle Benutzer setzen. Indem Fall musst du Powershell als Administrator starten und folgenden Befehl absetzen

Code:

Set-ExecutionPolicy -force RemoteSigned

Anschließend kannst du es noch mal starten.
BTW: Hast du schon beide Varianten ausprobiert? Die zweite könnte eine bessere Performance haben.

Gruß Thomas

mibro · 19 Januar 2024

Guten Morgen Thomas,

diesen Code einfach in das Skript einfügen?
Set-ExecutionPolicy -force RemoteSigned

Ich starte die angelegte Datei stets über das Kontextmenü von Windows 10 mit Powershell starten.

Wenn ich das gleiche Skript stark einkürze, dann funktioniert alles.
Also mit wenig Such- bzw. Ersetzungsbefehlen klappt alles.

Mit freundlichen Grüßen
Michael

TK87 · 19 Januar 2024

mibro schrieb:
diesen Code einfach in das Skript einfügen?
Set-ExecutionPolicy -force RemoteSigned

Nein, öffne Powershell und führe den Befehl einmalig aus - wenn du die Version ohne das -Scope CurrentUser nimmst, musst du wie gesagt Powershell als Administrator starten, um das ausführen zu können.

Standardmäßig ist die Ausführung von Powershell-Skripten deaktiviert. Der Code bewirkt, dass Powershell Skripte, die lokal auf dem Computer erstellt wurden, ausgeführt werden dürfen. Skripte, welche aus dem Internet heruntergeladen wurden, müssen hingegen von einem Vertrauenswürdigen Hersteller signiert sein.

mibro · 19 Januar 2024

ich denke der Befehl wurde jetzt angenommen, da nach dem ausführen das Skript sofort startet. (blaues Fenster und blinkender Cuser oben Links)
Mal sehen wie lange es dauert.
Startzeitpunkt 09:04 Uhr

mibro · 19 Januar 2024

09:31Uhr war Powershell mit dem Prozess fertig.
Danke für Deine Hilfe.
Ich prüfe später die geänderten Daten.

Mit freundlichen Grüßen
Michael

mibro · 20 Januar 2024

Hallo Thomas, die erstellte csv-Datei sieht sehr gut aus.

Über das empfohlene Bild-Tool erhalten ich teilweise folgende Ausgaben. Ich lasse zwei unterschiedliche Skripte für jpg und png durchlaufen.

magick.exe: Corrupt JPEG data: premature end of data segment `C:\Bilder-Quelle\Haengematte_Moskito_Traveller_Amazonas_Outdoor_4030454000475_12014822_10.jpg' @ warning/jpeg.c/JPEGWarningHandler/403.
magick.exe: Corrupt JPEG data: 6591 extraneous bytes before marker 0xfe `C:\Bilder-Quelle\Haengematte_Moskito_Traveller_Amazonas_Outdoor_4030454000475_12014822_10.jpg' @ warning/jpeg.c/JPEGWarningHandler/403.
magick.exe: Corrupt JPEG data: 17679 extraneous bytes before marker 0xda `C:\Bilder-Quelle\Haengematte_Moskito_Traveller_Amazonas_Outdoor_4030454000475_12014822_10.jpg' @ warning/jpeg.c/JPEGWarningHandler/403.
magick.exe: Premature end of JPEG file `C:\Bilder-Quelle\Haengematte_Moskito_Traveller_Amazonas_Outdoor_4030454000475_12014822_10.jpg' @ warning/jpeg.c/JPEGWarningHandler/403.

für jpg
# Quell- und Zielpfad Original
$source = "C:\Bilder-Quelle"
$target = "C:\Bilder-Ziel"

foreach ($jpg in Get-ChildItem "$Source\*.jpg") {
# Ausgabe als "<Zielpfad>\<Name>.jpg"
$output = "{0}\{1}.jpg" -f $target, $jpg.BaseName

# Bild skalieren
magick $jpg.fullname -scale "1280x1280" $output
}

für png
# Quell- und Zielpfad
$source = "C:\Bilder-Quelle"
$target = "C:\Bilder-Ziel"
foreach ($image in Get-ChildItem "$source\*.png") {
# Überprüfen, ob es sich um ein Bild handelt
if ($image.Extension -in @(".png")) {
# Ausgabe als "<Zielpfad>\<Name>.png"
$output = "{0}\{1}.png" -f $target, $image.BaseName
# Bild skalieren
& 'magick' $image.FullName -scale "1280x1280" $output
}
}

Mit freundlichen Grüßen
Michael

TK87 · 20 Januar 2024

mibro schrieb:
Über das empfohlene Bild-Tool erhalten ich teilweise folgende Ausgaben. Ich lasse zwei unterschiedliche Skripte für jpg und png durchlaufen.

magick.exe: Corrupt JPEG data: premature end of data segment `C:\Bilder-Quelle\Haengematte_Moskito_Traveller_Amazonas_Outdoor_4030454000475_12014822_10.jpg' @ warning/jpeg.c/JPEGWarningHandler/403.
magick.exe: Corrupt JPEG data: 6591 extraneous bytes before marker 0xfe `C:\Bilder-Quelle\Haengematte_Moskito_Traveller_Amazonas_Outdoor_4030454000475_12014822_10.jpg' @ warning/jpeg.c/JPEGWarningHandler/403.
magick.exe: Corrupt JPEG data: 17679 extraneous bytes before marker 0xda `C:\Bilder-Quelle\Haengematte_Moskito_Traveller_Amazonas_Outdoor_4030454000475_12014822_10.jpg' @ warning/jpeg.c/JPEGWarningHandler/403.
magick.exe: Premature end of JPEG file `C:\Bilder-Quelle\Haengematte_Moskito_Traveller_Amazonas_Outdoor_4030454000475_12014822_10.jpg' @ warning/jpeg.c/JPEGWarningHandler/403.

Die Fehlermeldungen sind doch eindeutig. Da scheinen einige Bilder beschädigt zu sein. Du kannst mal gucken, ob sich die betroffenen Bilder mit einem anderen Bildbearbeitungstoll retten lassen.

Excel oder Powershell

mibro

kennt sich schon aus

mibro

kennt sich schon aus

TK87

treuer Stammgast

Try It Online

mibro

kennt sich schon aus

Anhänge

TK87

treuer Stammgast

mibro

kennt sich schon aus

TK87

treuer Stammgast

ImageMagick – Create, Edit, Compose, or Convert Digital Images

mibro

kennt sich schon aus

Anhänge

TK87

treuer Stammgast

mibro

kennt sich schon aus

TK87

treuer Stammgast

mibro

kennt sich schon aus

mibro

kennt sich schon aus

mibro

kennt sich schon aus

TK87

treuer Stammgast