Textdatei mittels festen Spaltenbreiten in MySQL per PHP

uspc „Textdatei mittels festen Spaltenbreiten in MySQL per PHP“

27.06.2014, 16:07 Optionen

Es gibt verschiedene Möglichkeiten. Theoretisch könntest Du auch die Textdatei direkt in die Datenbank importieren und dann per SQL in die Spalten der Zieltabelle aufteilen. Der direkte Import aus Dateien ist jedoch mit Einschränkungen verbunden und wird u.U. nicht auf externem Webspace laufen.

In sofern scheint mir ein Einlesen der Datei mit PHP und Teilen der Zeilen per substr ein sinnvoller Ansatz. Zumal damit auch noch eine sinnvolle Fehlerbehandlung - incl. Rückmeldung an den User - möglich ist.

Auf jeden Fall müssen die hochgeladenen Eingabedaten allerdings escaped und oder am besten in Verbindung mit einem Prepared Statement werden um SQL-Injections zu verhindern!

Oder wolltest Du einfach nur die Datei konvertieren und das Ergebnis dann direkt in die DB importieren? Dann würde ich dazu tendieren, das gleich mit der original Datei zu machen die in eine temporäere Tabelle zu importieren und von dort aus in SQL zu verarbeiten.

Gruß
Borlander

uspc

Borlander „Es gibt verschiedene Möglichkeiten. Theoretisch könntest ...“

27.06.2014, 18:12 Optionen

Danke Borlander für deine Informationen. Also ich wollte die Datei in Spalten einer MySQL-Tabelle aufsplitten, in der ein Kollege bestimmte Werte korrigieren kann. Danach soll die Datei wieder so wie die Ursprungsdatei zusammengeführt werden. Also eigentlich nichts kompliziertes. Dein letzter Satz wirft für mich eine Frage auf: kann ich MySQL auch innerhalb eines Feldes sagen, gib mit Pos. 13 bis 20 aus, dann 21 bis 30 usw.?

Wobei mir die "Vorbehandlung" der Datei per PHP ($Spalte_A = substr("$feld", 1, 6); ($Spalte_B = substr("$feld", 7, 12); usw. und dann die Variablen in die entsprechenden Spalten per Insert bringe einfacher erscheint...

Aber vielleicht bringt eine Trennung in der DB Vorteile. Viele Grüße, uspc

Borlander

uspc „Danke Borlander für deine Informationen. Also ich wollte ...“

27.06.2014, 18:56 Optionen

Also ich wollte die Datei in Spalten einer MySQL-Tabelle aufsplitten, in der ein Kollege bestimmte Werte korrigieren kann.

Direkt in der DB, oder per Webinterface?

Wenn es ausschließlich darum geht die Datei zu bearbeiten, dann würde ich hier eher über ein Excel + VBA-Macro nachdenken, dass eine Datei im entsprechenden Format (Text mit fester Spaltenbreite) einliest und ausgibt.

kann ich MySQL auch innerhalb eines Feldes sagen, gib mit Pos. 13 bis 20 aus, dann 21 bis 30 usw.?

http://dev.mysql.com/doc/refman/5.1/de/string-functions.html#idm47531853699216

uspc

Borlander „Direkt in der DB, oder per Webinterface? Wenn es ...“

01.07.2014, 13:42 Optionen

Danke Borlander. Also mein Versuch mit substr in PHP funktioniert zwar, aber meine Textdatei hat über 4000 Zeilen mit 28 Spalten. Das Einlesen auf dem lokalen PC dauert ein paar Sekunden und bei 400 Zeilen macht MySQL Schluß. Insert habe ich in eine Schleife gelegt, in der pro Zeile die 28 Felder eingetragen werden. Klappt - aber nur theoretisch, bis zum Abbruch. Soll man ja so auch nicht machen. Oder kann man das irgendwie beeinflussen, dass es doch geht? Also z.B. mit einer kleinen Pause bis zur nächsten Schleife? Zeit ist genug da...

Das mit VB Macro wollte ich vermeiden, da hab ich gar keine Vorkenntnisse (ist schon 10 Jahre her). Die Variante das Textfeld mit SELECT SUBSTRING zu zerstückeln ist gut, ich weiß nur nicht, wie ich das einzelne Feld nach einer Korrektur o.ä. wieder dort hinbekomme (also Update_an_die_Stelle) und dann die Textdatei im gleichen Format wie vorher erzeuge, nur mit korrigierten Werten... Bin weiter dran...

VG uspc

Borlander

uspc „Danke Borlander. Also mein Versuch mit substr in PHP ...“

01.07.2014, 20:25 Optionen

Das Einlesen auf dem lokalen PC dauert ein paar Sekunden und bei 400 Zeilen macht MySQL Schluß.

Wie liest Du denn lokal ein? LOAD DATA INFILE sollte da selbst in der Standardkonfiguration noch an keine Größenbeschränkunkung stoßen. Oder sind da noch deutlich größere Spalten mit drin? Bzw.: Wie groß ist die Eingabedatei?

Das mit VB Macro wollte ich vermeiden, da hab ich gar keine Vorkenntnisse (ist schon 10 Jahre her).

Kannst ja spaßeshalber einfach mal aufzeichnen wie Du die Textdatei mit einigen von Hand definierten Spaltenbreiten importierst. Der export wäre entsprechend auch wieder nur ein Speichern als Textdatei oder alterntiv sowas wie CSV mit leerem String als Feldtrenner.

ich weiß nur nicht, wie ich das einzelne Feld nach einer Korrektur o.ä. wieder dort hinbekomme (also Update_an_die_Stelle)

Komplette Zeile mit CONCAT wieder zusammensetzen ;-)

Gruß
Borlander

uspc

Borlander „Wie liest Du denn lokal ein? LOAD DATA INFILE sollte da ...“

02.07.2014, 08:51 Optionen

Ich zeig mal, wie ich das bis jetzt gemacht hatte:

$handle = fopen ("dateiablage/kopie.txt", "r");
while (!feof($handle)) {

       $buffer = fgets($handle);

       $SATZART = substr($buffer, 0, 4);
       $USER = substr($buffer, 5, 7);
       $FKT = substr($buffer, 8);
       $FKTVAR = substr($buffer, 9, 23);
       usw.

   $sql = "INSERT INTO tabelle (
       SATZART,
       USER,
       FKT,
       FKTVAR, usw. )
       VALUES (
       '$SATZART',
       '$USER',
       '$FKT',
       '$FKTVAR', usw.)

   $go = mysql_query($sql) or die (mysql_errno().": sql ".mysql_error);
}
fclose ($handle);
........................................

Das funktionierte, nur Abbruch bei 400-700 Datensätzen (Zeilen). Ich denke, da ist INSERT in der While-Schleife ungeeignet, es wären ca. 4500 Zeilen.
Die txt-Datei ist 765kB groß. Die Variablennamen lauten gleich wie den Spaltennamen der Tabelle.

Im neuen Versuch mache ich das so:
LOAD DATA INFILE 'kopie.txt' INTO TABLE hilfstabelle
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

Dann ist die gesamte Textdatei Zeile für Zeile in einer Tabelle, und dort in einer Spalte (inhalt), 171 Zeichen pro Zeile lang.
Das klappt in 0,irgendwas Sekunden.
Und nun möchte ich die Spalten per SQL von dieser Hilfs-Tabelle in die richtige Tabelle bringen, die wie oben auch die Spaltennamen hat.
Dazu muss SELECT SUBSTRING die einzelnen Trenner auslesen, also etwa so:

SELECT SUBSTRING(inhalt, 152, 170) FROM hilfstabelle; (das habe ich vorher per PHP gemacht).

Ist das der richtige Weg? Wie kann ich per MySQL mit SELECT mehrere Spalten ausgeben? Danke schon mal vorab, uspc.

uspc Nachtrag zu: „Ich zeig mal, wie ich das bis jetzt gemacht hatte: handle ...“

02.07.2014, 10:51 Optionen

Ich habs! Vielleicht braucht es jemand:

INSERT INTO tabelle(SpalteA,SpalteB,SpalteC...) SELECT

(SUBSTRING(inhalt, 1, 5)),
(SUBSTRING(inhalt, 6, 8)),
(SUBSTRING(inhalt, 9)),
(SUBSTRING(inhalt, 10,24)),
(SUBSTRING(inhalt, 25,39 )),
(SUBSTRING(inhalt, 40,41 )),
usw.

FROM hilfstabelle;

................

In der Tabelle "tabelle" die Namen der Spalten eintragen, hier im Beispiel mit "SpalteA" usw. betitelt. In der Tabelle "hilfstabelle" wurde zuvor die Textdatei in ein einziges Feld importiert (siehe weiter oben), diese Spalte heisst "inhalt". Der Knack war die Substring in einzelne Klammern zu schreiben. Eigentlich einfach.

phpMyAdmin zeigt: Zeige Datensätze 0 - 29 ( 4,527 insgesamt, Die Abfrage dauerte 0.0010 Sekunden).

Jetzt kann ich die Felder bearbeiten und muss das Ganze nur wieder zurück in die Textdatei schreiben. Danke an Borlander für die Tipps!

uspc

Borlander

uspc „Ich zeig mal, wie ich das bis jetzt gemacht hatte: handle ...“

02.07.2014, 20:03 Optionen

Das funktionierte, nur Abbruch bei 400-700 Datensätzen (Zeilen).

Jetzt würde mich allerdings noch mal ein wenig interessieren welchen Fehlercode MySQL da zurückgeliefert hat? Oder hat das Einfügen einfach so lange gedauert bis beim Ausführen des PHP-Scripts der Timeout (AFAIR 30s in Standardconfig) überschritten wurde?

uspc

Borlander „Jetzt würde mich allerdings noch mal ein wenig ...“

07.07.2014, 16:15 Optionen

Hallo Borlander, dieses Posting hab ich übersehen, sorry. Also Fehlermeldung gab es keine, es dauerte nur gefühlte 30sec, dann war der Import fertig. Also nur 400 Datensätze von 4500. Keine Fehlermeldung. Aber das klappt ja jetzt.

So, jetzt versuche ich die MySQL-Daten wieder in die Textdatei zu bekommen. SELECT * INTO OUTFILE 'test.txt' FIELDS TERMINATED BY '' FROM tabellenname; geht schon gut, aber irgendwie hab ich da so viele Leerzeichen drin... Achso, statt SELECT * werde ich noch die Spaltennamen einsetzen. Vielleicht kommen die Leerzeichen durch falsche Angaben in den Spaltenlängen (Überlappungen). Ich bin weiter dran...

VG uspc

Borlander

uspc „Hallo Borlander, dieses Posting hab ich übersehen, sorry. ...“

08.07.2014, 00:29 Optionen

Hallo Borlander, dieses Posting hab ich übersehen, sorry. Also Fehlermeldung gab es keine, es dauerte nur gefühlte 30sec, dann war der Import fertig. Also nur 400 Datensätze von 4500. Keine Fehlermeldung.

Das würde zur Timeout-Theorie passen ;-)

Borlander

uspc „Ich zeig mal, wie ich das bis jetzt gemacht hatte: handle ...“

02.07.2014, 20:14 Optionen

Im neuen Versuch mache ich das so:
LOAD DATA INFILE 'kopie.txt' INTO TABLE hilfstabelle
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

Den Schritt mit der Hilfstabelle könntest Du sogar noch sparen ;-) Und zwar wie folgt (Code ungetestet):

LOAD DATA INFILE 'kopie.txt'
INTO TABLE tabelle_mit_spalten
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(@inhalt) /* Ganze Zeile in Variable @inhalt laden */
SET SpalteA = SUBSTRING(@inhalt, 152, 170), ... ;

Du solltest übrigens auch noch prüfen/sicherstellen, dass der Standard-Feld-Trenner nicht in den Daten vorkommt, bzw. diesen ggf. ersetzen. Siehe Ref

Aber immerhin funktioniert es jetzt schon mal so wie Du es brauchst :-)

Programmieren - alles kontrollieren 4.941 Themen, 20.708 Beiträge