Lösungsansätze: CSV-Import in DB

[AndreD]

Hallo zusammen,

Ich habe mir verschiedene Lösungsansätze für ein CSV-Imports in eine Datenbank erdacht. Meine Bitte wäre das mir jemand bei der Entscheidung hilft wie man das letztendlich am besten angehe.

Voraussetzungen:
---------------------------------------------------------------------

Umgesetzt wird das Ganze mit PHP & MySQL, im Moment nur für Projekte bei denen weniger als 1000 Datensätze einzulesen sind, aber die Lösung soll auf Dauer auch für grössere Projekte performant sein.

Es wird vorausgesetzt es sich um eine aus Excel exportierte CSV-Datei handelt, d.h. der Aufbau der CSV-Datei (Trennzeichen ist der Semikolon, Zeilenumbruch als \r\n, Werte sind nicht von Anführungszeichen eingeschlossen) ist bekannt.

Die Feldnamen sind ebenfalls festgelegt, die MySQL-Tabelle wird einmalig angelegt und bleibt bis auf weiteres unverändert.

Die CSV-Datei ist entscheidend! Der Inhalt der CSV-Datei soll später 1:1 in der Tabelle stehen. D.h. bereits vorhandene Datensätze bleiben unberührt oder werden entsprechend geändert, neue Datensätze werden angelegt und nicht mehr vorhandene werden gelöscht.

[1] Schnelle Lösung per MySQL
---------------------------------------------------------------------
-> User wählt eine lokale CSV-Datei über ein Upload-Formular aus und sendet dieses an den Server. Check auf Dateiendung *.csv und Mime-Type "text/comma-separated-values" möglich wobei mein Browser in dem Fall leider "application/octet-stream" übermittelt.

-> Ein PHP-Script kopiert die Datei in ein festgelegtes Verzeichnis (Falls dies nicht existiert wird es angelegt). Besser wäre es vielleicht es wird stattdessen die temporär angelegte Datei beim SQL-Statement verwendet (mit LOAD LOCAL DATA)?

-> Ist die Datei vorhanden wird die festgelegte Tabelle per delete komplett geleert (an der Stelle könnte möglicherweise erst ein Backup auf eine andere Tabelle erfolgen, macht das Sinn?) und das File per SQL-Statement "LOAD [LOCAL] DATA INFILE '" . $file . "' INTO TABLE ..." in die Tabelle geschrieben.

Das ganze wäre wohl in einer halben Stunde umgesetzt und würde, denke ich auch ganz ordentlich laufen. Ich bin mir jetzt halt nicht sicher ob das eine gescheite Lösung ist oder ob doch nicht irgendwo der Wurm drin ist?

[2] Alternative Lösung mit Arrays
---------------------------------------------------------------------
-> User behandelt CSV-Datei wie in [1]

-> Von der CSV-Datei wird ein mehrdimensionales Array als Abbild erzeugt und mit einem aus der DB erzeugten mehrdimensionalen Array verglichen. Evt. mit array_diff()?

Vorteil wäre dabei das dabei nur die wirklich geänderten Datensätze auch per SQL-Statement UPDATE bzw. DELETE bearbeitet würden.
Bei grösseren Datenmengen wäre diese Lösung wahrscheinlich aber langsamer als [1], da es sich bei [1] um reine DB-Operationen handelt und hier aber erst per PHP die Array erzeugt und verglichen werden müssen.

[3] Alternative Lösung mit einzelnen SQL-Statements
---------------------------------------------------------------------

-> User behandelt CSV-Datei wie in [1]

-> Für jede Zeile in der CSV-Datei wird in der Datenbank nach dem entsprechenden Eintrag gesucht. Zur Identifikation dient die ID des Datensatzes.

-> Das Script entscheidet zwischen 3 mögliche Operationen:
   a) Datensatz ist in CSV vorhanden, nicht in DB = Anlegen
   b) Datensatz ist in CSV vorhanden, dito in DB = Update
   c) Datensatz ist nicht in CSV, wohl aber in DB = Löschen

Meiner Meinung nach spricht vom Aufwand und der Performance alles für 1., aber mir ists schon fast zu simpel und ein wenig Holzhammer-Mässig :-) Bei Lösung [2] und [3] bekommt man halt von der DB entsprechende Rückgabewerte wieviel Datensätze angelegt, bearbeitet und gelöscht wurden, das geht halt bei [1] nicht.

Danke mal & Gruss AndreD

[Erwin]

Hallo ,

[1] Schnelle Lösung per MySQL

-> User wählt eine lokale CSV-Datei über ein Upload-Formular aus und sendet dieses an den Server. Check auf Dateiendung *.csv und Mime-Type "text/comma-separated-values" möglich wobei mein Browser in dem Fall leider "application/octet-stream" übermittelt.

An der Stelle ists ganz gut, die Konsistenz der CSV Datei zu prüfen bevor die DB beschrieben wird.

Konsistenz:
Jede Zeile hat dieselbe Anzahl an Trennzeichen ';'

-> Ein PHP-Script kopiert die Datei in ein festgelegtes Verzeichnis (Falls dies nicht existiert wird es angelegt). Besser

Da würde ich ein festes Verzeichnis nehmen und den Dateinamen mit der Prozess ID des CGIs koppeln.

Das ganze wäre wohl in einer halben Stunde umgesetzt und würde, denke ich auch ganz ordentlich laufen. Ich bin mir jetzt halt nicht sicher ob das eine gescheite Lösung ist oder ob doch nicht irgendwo der Wurm drin ist?

Du musst auf jeden Fall dafür sorgen, dass deine Daten auch nach dem Import konsistent sind.

Erwin

[AndreD]

Hallo Erwin,

An der Stelle ists ganz gut, die Konsistenz der CSV Datei zu prüfen bevor die DB beschrieben wird.

Da geb ich Dir recht, auch wenn das bisher nach dem Shit-in-shit-out-Prinzip gehandelt wurde :-)

-> Ein PHP-Script kopiert die Datei in ein festgelegtes Verzeichnis (Falls dies nicht existiert wird es angelegt). Besser

Nee, ich glaube inzwischen das ich das mittels LOAD LOCAL DATA mache, d.h. es wird die temporäre Datei vom Client genommen. Somit brauche ich mir wegen Verzeichnisse, Lese- und Schreibrechte keinen Kopf machen.

Du musst auf jeden Fall dafür sorgen, dass deine Daten auch nach dem Import konsistent sind.

FullACK!

Danke für Deine Meinung,
Gruss AndreD

[Tom]

Hello,

Es wird vorausgesetzt es sich um eine aus Excel exportierte CSV-Datei handelt, d.h. der Aufbau der CSV-Datei (Trennzeichen ist der Semikolon, Zeilenumbruch als \r\n, Werte sind nicht von Anführungszeichen eingeschlossen) ist bekannt.

Die Feldnamen sind ebenfalls festgelegt, die MySQL-Tabelle wird einmalig angelegt und bleibt bis auf weiteres unverändert.

Das ist wesentlich
1. Zeile enthält Feldnamen?
2. Felder Übereinstimmung prüfen
3. Lokale Default-Werte der DB ergänzen (Timestamp, IP, Bearbeiter...)
4. Felder passend zuordnen

a) Datensatz ist in CSV vorhanden, nicht in DB = Anlegen
   b) Datensatz ist in CSV vorhanden, dito in DB = Update
   c) Datensatz ist nicht in CSV, wohl aber in DB = Löschen

Besser:

a) nur hinzufügen
   alle Datensäzte der CSV, die nicht in der DB stehen, werden
   hinzugefügt. Das erfordert entweder einen sauberen Unique Key,
   oder aber ein Select before Insert _pro_ Datensatz.

b) hinzufügen und updaten -> REPLACE
   Das wäre sicher die normale Methode. Es werden aber nur Felder
   überschreiben, die in der CSV-Tabelle vorhanden sind
   NULL = ",," Leer = ", ," oder ein Steuerzeichen ,#,
   Ob Excel das kann, weiß ich nicht

c) entfernen
   Negativliste ist sehr praktisch.:
   - Die Austritte des Jahres...
   - Abgelaufene Ankündigungen
   - ungülitge Ranglisten
   Das spart Arbeit im Office und Traffic
   Wobei man überlegen sollte, ob man den Datensatz da nicht einfach
   mit einem Sperrmerker versieht

Dann sollte der Key sich aber aus zwei Feldern zusammensetzen:
   Eigentlicher Datensatzschlüssel + lfd. Nummer des Standes

Grüße

Tom

[AndreD]

Hallo,

a) nur hinzufügen
   alle Datensäzte der CSV, die nicht in der DB stehen, werden
   hinzugefügt. Das erfordert entweder einen sauberen Unique Key,
   oder aber ein Select before Insert _pro_ Datensatz.

Eine alte Lösung von mir funktionierte mit einem Select, eben das möchte ich bei einer neuen Lösung umgehen.

b) hinzufügen und updaten -> REPLACE
   Das wäre sicher die normale Methode. Es werden aber nur Felder
   überschreiben, die in der CSV-Tabelle vorhanden sind
   NULL = ",," Leer = ", ," oder ein Steuerzeichen ,#,
   Ob Excel das kann, weiß ich nicht

Klar, eigentlich schon. Beim Export ins CSV hat Excel zwar seine eigene Konventionen wie die CSV-Datei auszusehen hat, aber leere, bzw. nicht ausgefüllte Felder werden schon so behandelt wie Du beschrieben hast.

c) entfernen
   Negativliste ist sehr praktisch.:
   - Die Austritte des Jahres...
   - Abgelaufene Ankündigungen
   - ungülitge Ranglisten
   Das spart Arbeit im Office und Traffic
   Wobei man überlegen sollte, ob man den Datensatz da nicht einfach
   mit einem Sperrmerker versieht

Es ist schon vorgesehen das über ein Flag der Datensatz freigeschaltet oder gesperrt werden kann. Das wird bereits vom User in der Datei so festgeschrieben.

Dann sollte der Key sich aber aus zwei Feldern zusammensetzen:
   Eigentlicher Datensatzschlüssel + lfd. Nummer des Standes

Dazu muss ich mir mal Gedanken machen...

Einstweilen vielen Dank für die Anregungen!

Gruss AndreD