Mysql Postleitzahlen

[Hans im Glück]

Hallo,

ich habe eine Datei mit 450.000 Datensätzen in ein char(5) Feld importiert. Leider hatte die Quelldatei die führenden "0" nicht mitgespeichert. Bei einer Lösung mit Schleife wird ja wohl der PHP Timeout zuschlagen. Kann man das nicht mit nem SQL Statement lösen?

[Gunnar Bittersmann]

@@Hans im Glück

Leider hatte die Quelldatei die führenden "0" nicht mitgespeichert.

Falscher Datentyp, hm? PLZ sind keine Zahlen.

Sind es denn alles deutsche Adressen in deiner DB? Österreichische und Schweizer PLZ sind vierstellig; da soll keine 0 davor.

😷 LLAP

[Der Martin]

Hallo,

Leider hatte die Quelldatei die führenden "0" nicht mitgespeichert.

Falscher Datentyp, hm? PLZ sind keine Zahlen.

deswegen heißen sie in anderen Sprachen auch z.B. zip code (en-US) oder postcode (nl).

Sind es denn alles deutsche Adressen in deiner DB? Österreichische und Schweizer PLZ sind vierstellig; da soll keine 0 davor.

Andere sind sechsstellig und bestehen aus Ziffern und Buchstaben.

Live long and pros healthy,
 Martin

[Hans im Glück]

Nur Deutsche PLZ

Naja, das war wohl das Problem, dass die aus der Quelle als Zahlen rüber kamen.

[Tabellenkalk]

Hallo,

Leider hatte die Quelldatei die führenden "0" nicht mitgespeichert.

Lässt sich dieser Schritt wiederholen, diesmal korrekt?

Gruß
Kalk

[Hans im Glück]

Die Daten kamen als CSV gezipt. Wenn ich die z.B. nach OO importiere sind die 0 schon weg.

[Der Martin]

Hallo,

Die Daten kamen als CSV gezipt.
Wenn ich die z.B. nach OO importiere sind die 0 schon weg.

ich weiß ja nicht, wie du den Import machst - aber du musst vorher den Spaltentyp auf "Text" festlegen.

Live long and pros healthy,
 Martin

[Tabellenkalk]

Hallo,

Die Daten kamen als CSV gezipt. Wenn ich die z.B. nach OO importiere sind die 0 schon weg.

Das sagt nix darüber aus, ob die Nullen in der csv enthalten sind oder nicht.

Gruß
Kalk

[Hans im Glück]

Also, ich habe die Zip-Datei in Openoffice geöffnet. Dabei war sie schreibgeschützt, so dass ich den Typ der Spalte nicht ändern konnte. Ich habe die Datei dann abgespeichert, jetzt war der Schreibschutz weg aber die 0 auch.

[Tabellenkalk]

Hallo,

Also, ich habe die Zip-Datei in Openoffice geöffnet. Dabei war sie schreibgeschützt, so dass ich den Typ der Spalte nicht ändern konnte. Ich habe die Datei dann abgespeichert, jetzt war der Schreibschutz weg aber die 0 auch.

aha

Gruß
Kalk

[Der Martin]

Hallo,

Also, ich habe die Zip-Datei in Openoffice geöffnet.

wie denn genau? Und womit genau?
Mit Calc (dem Excel-Äquivalent), oder mit Base (dem Access-Äquivalent)?

In beiden Fällen gilt: Zuerst den Spaltentyp festlegen, dann importieren.

Wenn du eine CSV-Datei dagegen einfach so mit einem Doppelklick öffnest, fällt Excel oder OO Calc oder LO Calc (was immer auf dem System gerade Standard ist) darüber her und interpretiert die Felder nach Gutdünken. Felder, die nur Ziffern enthalten, werden dabei automatisch als Zahl behandelt, d.h. führende Nullen fallen ebenso weg wie nicht signifikante Nullen nach dem Dezimalpunkt (falls einer da ist).

Ich habe die Datei dann abgespeichert, jetzt war der Schreibschutz weg aber die 0 auch.

Ja, die ist schon beim Einlesen der CSV-Datei unter den Tisch gefallen.

Live long and pros healthy,
 Martin

[Hans im Glück]

Was nicht da ist kann auch nicht übernommen werden. Das öffnen der Datei in einem Texteditor zeigt, dass die 0'en schon in der quelle fehlen.

[Der Martin]

Hallo Hans im Unglück,

Was nicht da ist kann auch nicht übernommen werden.
Das öffnen der Datei in einem Texteditor zeigt, dass die 0'en schon in der quelle fehlen.

oh verdammt. Dann ist der Import natürlich auch vollends egal.
Dann hilft nur hinterher reparieren. Entweder in der Datenbank (wie Rolf vorschlägt) oder bei der Ausgabe (wie ich vorgeschlagen habe).

Live long and pros healthy,
 Martin

[Matthias Apsel]

Hallo Der Martin,

oh verdammt. Dann ist der Import natürlich auch vollends egal.
Dann hilft nur hinterher reparieren. Entweder in der Datenbank (wie Rolf vorschlägt) oder bei der Ausgabe (wie ich vorgeschlagen habe).

Mein Vorschlag sollte auch funktionieren. [rumheul, alle ignorieren mich 😱]

Bis demnächst
Matthias

[Rolf B]

Hallo Matthias,

*tröst*

Ja schon. Wenn Du die Zündkerzen wechseln musst, baust Du dafür vorher den Zylinderkopf ab? Oder machst es - wie jener legendäre Umschüler - durch den Auspuff?

Word ist für seine Performance in größeren Dokumenten (450000 Zeilen ≈ 7500 Seiten) bekannt. Man sagt z.B.: das Mistding kommt da überhaupt nicht mehr aus dem Quark.

Rolf

[Matthias Apsel]

Hallo Rolf B,

*tröst*

🥰

Word ist für seine Performance in größeren Dokumenten (450000 Zeilen ≈ 7500 Seiten) bekannt.

Ja, in der Tat. Ich würde es trotzdem gern mal testen. Fraglos sollte das Update deutlich schneller fertig sein, weil es einfach das richtige Werkzeug ist.

Bis demnächst
Matthias

[Rolf B]

Hallo Matthias,

ich habe mir jetzt eine CSV-Datei mit 450000 Zeilen gebastelt. Beginne mit einer Zeile, dann Copy+Paste.

1 -> 10 -> 100 -> 1000 -> 10000, das wurde mir dann zu blöd und ich habe nach Strg+V einfach das V festgehalten, bis der Editor 450000 anzeigte.

In Word 2010 geladen, ok, ging noch zügig. Ein neueres Word hab ich nicht da.

Gesucht nach [0-9]{4} - oops, wehe der Navigator geht auf. Dann dauert's erstmal, bis er sich durch die Zeilen gebrasselt hat. Das blöde Teil blockiert ja erstmal, bis es durch das Dokument durch ist. Also: zwingend die erweiterte Suche öffnen.

Dann fiel mir auf: Was ist, wenn eine 4-stellige Zahl nicht am Anfang, sondern irgenwo mittendrin steht. Die will ich ja nicht ersetzen. Nach etwas Suchen stelle ich fest, dass bei einer Suche mit Mustervergleich eine Suche nach der Absatzmarke nicht mehr möglich ist. Aua.

Also müsste man erstmal alle Absatzmarken durch Zeilenumbrüche ersetzen. Durchaus Grund zur Besorgnis, denn das Ergebnis ist ein Absatz mit 450000 Zeilen. Nach 5 Minuten, die Word auf 30% CPU lief (4-Kern CPU), habe ich es dann abgeschossen.

Nein, Word ist keine gute Idee für die Aufgabe.

Rolf

[Der Martin]

Hallo,

Wenn Du die Zündkerzen wechseln musst, baust Du dafür vorher den Zylinderkopf ab?

wenn du einen Porsche Boxster fährst, musst du zum Zündkerzenwechsel sogar den kompletten Motorblock ausbauen. Und zwar komplett mit dem Getriebe als Einheit nach unten - weil man anders gar nicht an den Zylinderkopf rankommt.

Oder machst es - wie jener legendäre Umschüler - durch den Auspuff?

Das ist ja mal kreativ! War der früher Zahnarzt?
Zahnbehandlung von hinten stelle ich mir auch sehr schwierig vor.

Word ist für seine Performance in größeren Dokumenten (450000 Zeilen ≈ 7500 Seiten) bekannt. Man sagt z.B.: das Mistding kommt da überhaupt nicht mehr aus dem Quark.

Da endet meine Erfahrung bei wenigen hundert Seiten. Und das geht noch so leidlich, aber man merkt schon, dass es allmählich zäh wird. Besonders, wenn das Dokument mit Bildern, Tabellen oder ähnlichen Schmankerln gespickt ist.

Aber keine Sorge, LibreOffice schneidet da auch nicht sehr viel besser ab.

Live long and pros healthy,
 Martin

[MudGuard]

Hi,

oh verdammt. Dann ist der Import natürlich auch vollends egal.
Dann hilft nur hinterher reparieren. Entweder in der Datenbank (wie Rolf vorschlägt) oder bei der Ausgabe (wie ich vorgeschlagen habe).

nur entweder.

Weil: einmalige Aktion, statt bei JEDER Ausgabe zu bearbeiten. Und auch bei neuen Ausgabestellen drandenken zu müssen. Und nicht nur bei der Ausgabe. Es müßte ja auch bei jeder Abfrage berücksichtigt werden - da müßte ja auch eine der beiden Seiten jedesmal angepaßt werden.

cu,
Andreas a/k/a MudGuard

[Rolf B]

Hallo Hans,

okay. Dass Du Dir jetzt nicht selbst in den Fuß geschossen hast und die von Dir veränderte Datei geöffnet hast, setze ich mal voraus 😉

Also: UPDATE. Siehe unten.

Rolf

[Rolf B]

Hallo Hans,

Wenn ich die z.B. nach OO importiere sind die 0 schon weg.

das muss nichts heißen. Guck mit dem Text-Editor deines Vertrauens in die CSV Datei hinein, um das zu prüfen (z.B. Notepad++).

Welchen Typ hat die PLZ Spalte in deiner Datenbank? Abhängig davon gestaltet sich die Reparatur. Sorry, char(5), übersehen.

Hast Du das Original-ZIP noch?

Rolf

[Matthias Apsel]

Hallo Hans im Glück,

Die Daten kamen als CSV gezipt. Wenn ich die z.B. nach OO importiere sind die 0 schon weg.

in Word geht:
Suchen nach: ([0-9]{4})
Ersetzen durch: 0\1

Dazu muss im Suchen-Ersetzen Dialog das Häkchen "Platzhalter verwenden" gesetzt werden.

Bis demnächst
Matthias

[MudGuard]

Hi,

Die Daten kamen als CSV gezipt. Wenn ich die z.B. nach OO importiere sind die 0 schon weg.

in Word geht:
Suchen nach: ([0-9]{4})
Ersetzen durch: 0\1

im Wörd ist's aber schwieriger als in der DB, nur die Spalte mit den Postleitzahlen zu bearbeiten. Es könnten ja noch andere Nummern in der Tabelle stehen (z.B. Ortsvorwahl)

cu,
Andreas a/k/a MudGuard

[Der Martin]

Hallo,

in Word geht:
Suchen nach: ([0-9]{4})
Ersetzen durch: 0\1

im Wörd ist's aber schwieriger als in der DB, nur die Spalte mit den Postleitzahlen zu bearbeiten.

ähm, nö, eigentlich nicht. In Word bearbeitet Search&Replace nur den markierten Teil des Dokuments (falls etwas markiert ist).

Es könnten ja noch andere Nummern in der Tabelle stehen (z.B. Ortsvorwahl)

Ja. Also markiert man nur die Spalte mit der Postleitzahl, und los geht's.

Live long and pros healthy,
 Martin

[Der Martin]

Hi,

ich habe eine Datei mit 450.000 Datensätzen in ein char(5) Feld importiert.
Leider hatte die Quelldatei die führenden "0" nicht mitgespeichert.
Bei einer Lösung mit Schleife wird ja wohl der PHP Timeout zuschlagen.
Kann man das nicht mit nem SQL Statement lösen?

nur so zur Schadensbegrenzung: Kannst du die führenden Nullen nicht einfach bei der Ausgabe wieder ergänzen? Wenn's nur deutsche Postleitzahlen sind, weiß du ja, dass es fünf Stellen sein müssen.

Live long and pros healthy,
 Martin

[Gunnar Bittersmann]

@@Der Martin

Kannst du die führenden Nullen nicht einfach bei der Ausgabe wieder ergänzen?

Bei der Augabe sollte man Daten formatieren, nicht reparieren. In der DB sollten die Daten richtig drinstehen; deutsche PLZ also fünfstellig (wenn es sich nicht um historische Daten von vor 1993 handllt, bevor fünf Trümpf wurde).

😷 LLAP

[Der Martin]

Hi Gunnar,

Kannst du die führenden Nullen nicht einfach bei der Ausgabe wieder ergänzen?

Bei der Augabe sollte man Daten formatieren, nicht reparieren.

da bin ich prinzipiell derselben Meinung. Ich hatte nur nach einer minimalinvasiven Lösung gesucht.
Ja, ich weiß: Die erste Silbe des Wortes Notlösung ist Englisch. 😉

In der DB sollten die Daten richtig drinstehen; deutsche PLZ also fünfstellig (wenn es sich nicht um historische Daten von vor 1993 handllt, bevor fünf Trümpf wurde).

In einer richtig[tm] gepflegten Datenbank wäre eine solche Mischung aktueller und historischer Postleitzahlen natürlich kein Problem, man kann sie ja leicht an der unterschiedlichen Stellenzahl oder in der Übergangseit an einem vorangestellten "W" oder "O" erkennen. Ich denke aber, dass man historische Postleitzahlen bei einer aktuellen Quelle ausschließen darf.

Live long and pros healthy,
 Martin

[Rolf B]

Hallo Hans,

Kann man das nicht mit nem SQL Statement lösen?

Ja, kann man auch. Wenn die abgeschnittenen Werte linksbündig drinstehen:

UPDATE plztable SET plz = '0' + plz WHERE LEN(plz) < 5

1x ausführen reicht, da es die Postleitregion 00 nicht gibt.

Rolf

[Der Martin]

Hallo Rolf,

1x ausführen reicht, da es die Postleitregion 00 nicht gibt.

die wäre ja dann auf'm Klo. 😉

Live long and pros healthy,
 Martin

[Hans im Glück]

Na siehste :) super

[Hans im Glück]

OK, ich habs probiert :( klappt nicht.

1. ich habe die originaldatei im Texteditor angeschaut, da fehlen schon die Nullen.
2. ich habe die Datei in die Mysql Tabelle geladen
3. PLZ Feld = CHAR (5)
4. SQL Statement "UPDATE plztable SET plz = '0' + plz WHERE LEN(plz) < 5" gestartet.
5. Fehler "Funktion LEN() unbekannt".
6. LEN() durch CHAR_LENGTH() ersetzt. Klappt trotzdem nicht. Keine Fehlermeldung mehr, Spalte immer noch unverändert.

Kann es sein dass das an dem '0' + plz scheitert, weil er gar nicht den Wert von plz an der Stelle füllt?

[dedlfix]

Tach!

Kann es sein dass [...]

Bevor man ein UPDATE laufen lässt, bei dem man sich selbst nicht sicher ist, sollte man es als SELECT-Statement formulieren, und so schauen, ob die Daten entsprechend korrekt umgewandelt werden. Außedem kann man auch weitere Ausdrücke in die Feldliste einfügen, die men untersuchen muss, um Fehler zu finden.

SELECT '0' + plz, plz, CHAR_LENGTH(plz) FROM plztable

Das wäre ein solches Statement zum Testen der Gegebenheiten. Das kann zum Beispiel ergänzt werden durch ein ORDER BY 3, um die Ausgabe nach der dritten Spalte (Länge der PLZ) zu sortieren, damit man die kurzen gleich am Anfang zu sehen bekommt.

dedlfix.

[Der Martin]

Hallo,

ORDER BY 3

dass man auch einfach die Spaltennummer anstatt des Namens (oder Alias) angeben kann, wusste ich bis eben auch noch nicht. Danke für den Hinweis!

Live long and pros healthy,
 Martin

[Rolf B]

Hallo Der,

fun fact: Als ich SQL lernte (so ca 1986), war das die einzige Art zu sortieren, die man uns beibrachte...

Rolf

[Rolf B]

Hallo Hans,

grundsätzlich ist es so, dass SQL Spaces am Ende eines Strings ignoriert. Zumindest MySQL. D.h. wenn Du eine CHAR(5) Spalte hast und dort "1234" speicherst, steht darin zwar eigentlich "1234 ", aber die LENGTH sollte 4 sein. Zumindest ist das bei meinen Versuchen gerade so gewesen (obwohl ich anderes erwartet hatte).

Welche DB verwendest Du? Was kommt bei Dir raus, wenn Du, wie Dedlfix vorschlägt, erstmal einen SELECT der Menge machst, die potzenziell zu UPDATEn ist?

Rolf

[Hans im Glück]

Meine Lösung,

irgendwann war mir das alles zu blöde deshalb kam ich auf die Idee mit meinem altbewährten Texteditor "Textpad" die Blockfunktion zu nutzen. Da die Postleitzahlen im Original aufsteigend sortiert waren, waren alle 4 Zeichen lange in der Datei vorne. Einfach auf den Blockauswahl Modus umschalten und mit Block füllen bei den 4 stelligen die 0 davor. Keine 10 Minuten und gut isses.

Die einfachsten Methoden sind halt doch meist die besten :o)

Trotzdem Danke für die reichlichen Typs. Ihr seit klasse!