Wenn Sie Daten in einer Textdatei haben, können Sie diese einfach in eine oder mehrere Tabellen in einer Datenbank hochladen.
In MySQL-Datenbanken (oder MariaDB) können Sie mit dem Befehl „load data infile“ Daten aus einer Textdatei in Tabellen hochladen.
Der Befehl „load data infile“ bietet mehrere flexible Optionen, um verschiedene Datenformate aus einer Textdatei in Tabellen zu laden.
Die folgenden Beispiele zum Laden von Daten werden in diesem Tutorial behandelt:
- Grundlegendes Beispiel zum Laden von Daten aus einer Textdatei
- Daten hochladen mit der Option „Felder abgeschlossen durch“
- Daten hochladen mit der Option „Eingeschlossen von“
- Escape-Zeichen in Textdateidaten verwenden
- Daten hochladen mit der Option „Zeilen abgeschlossen durch“
- Zeilenpräfix in Upload-Datei ignorieren mit der Option „Beginnend mit“ Option
- Kopfzeile in der Upload-Datei ignorieren
- Nur bestimmte Spalten (und andere ignorieren) aus der Upload-Datei hochladen
- Variable beim Upload mit „Set“-Option verwenden
- Shell-Skript schreiben, um Daten aus Textdatei zu laden
Grundlegendes Beispiel zum Laden von Daten aus Textdatei
Im folgenden Beispiel, die Datei employee1.txt-Datei die Feldwerte, die durch Tabulator getrennt sind.
# cat employee1.txt 100 Thomas Sales 5000200 Jason Technology 5500300 Mayla Technology 7000400 Nisha Marketing 9500500 Randy Technology 6000
Standardmäßig verwendet der Befehl „Daten aus Datei laden“ TAB als Standard-Feldbegrenzer.
Zuerst gehen Sie zu der Datenbank, in die Sie die Textdatei hochladen möchten. In diesem Beispiel laden wir die obige Datei employee1.txt in die Tabelle employee hoch, die sich unter der mysql-Datenbank thegeekstuff befindet.
USE thegeekstuff;
Der folgende MySQL-Befehl lädt die Datensätze aus der obigen Datei employee1.txt in die Tabelle employee wie unten gezeigt. Dieser Befehl verwendet keine zusätzlichen Optionen.
LOAD DATA INFILE 'employee1.txt' INTO TABLE employee;
Hinweis: Im obigen Beispiel geht der Befehl davon aus, dass sich die Datei employee1.txt im Datenbankverzeichnis befindet. Wenn Sie z. B. den obigen Befehl in der Datenbankgeekstuff ausführen, dann legen Sie die Datei unter: /var/lib/mysql/thegeekstuff/
Das Folgende ist die Ausgabe des obigen Befehls.
Query OK, 5 rows affected (0.00 sec) Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
In der obigen Darstellung:
- Die erste Zeile „Query OK“ sagt, dass die Abfrage ohne Fehler ausgeführt wurde. Sie besagt auch, dass insgesamt 5 Zeilen in die Tabelle hochgeladen wurden. Hier wird auch die Zeit in Sekunden angezeigt, die für das Hochladen der Daten aus der Textdatei in die Tabelle benötigt wurde.
- Die zweite Zeile zeigt die insgesamt hochgeladenen Zeilen an, wie viele übersprungen wurden und wie viele Datensätze während des Hochladens Warnungen anzeigten.
Nach dem Laden der Daten sehen wir in der Mitarbeitertabelle folgendes.
Hinweis: Wenn Sie Ihre gesamte MySQL-Datenbank sichern und wiederherstellen möchten, verwenden Sie den Befehl mysqldump.
Daten hochladen mit der Option „Felder abgeschlossen durch“
Im folgenden Beispiel sind in der Eingabedatei employee2.txt die Feldwerte durch Kommas getrennt.
Um die oben genannten Datensätze in die Tabelle employee hochzuladen, verwenden Sie den folgenden Befehl.
Während des Uploads können Sie mit der Option „FIELDS TERMINATED BY“ das Feldtrennzeichen Komma wie unten gezeigt angeben.
LOAD DATA INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED BY ',';
Auch diese Option sollten Sie nur verwenden, wenn die Feldwerte durch etwas anderes als TAB getrennt sind. Wenn die Felder durch einen Doppelpunkt getrennt sind, verwenden Sie die folgende Option im obigen Befehl:
FIELDS TERMINATED BY ':';
Wenn Sie neu in MySQL sind, lesen Sie dies: MySQL-Tutorial: Installieren, DB und Tabelle erstellen, Datensätze einfügen und auswählen
Nachfolgend finden Sie einige grundlegende Fehler, die beim Hochladen von MySQL auftreten können
Fehler 1: Wenn sich die Textdatei nicht im richtigen Verzeichnis befindet, erhalten Sie möglicherweise die folgende Fehlermeldung: „ERROR 13 (HY000) Can’t get stat of (Errcode: 2)“.
Sie können auch den vollständigen Pfad zur Datei im Befehl load data infile angeben, wie unten gezeigt. Wenn Sie dies tun, stellen Sie sicher, dass mysql auf die Datei zugreifen kann. Wenn nicht, ändern Sie die Eigentümerschaft entsprechend auf mysql. Wenn nicht, erhalten Sie die Fehlermeldung „load data infile permission denied“.
MariaDB > LOAD DATA INFILE '/data/employee2.txt' INTO TABLE employee;
Fehler 2: Wenn Sie nicht die richtigen Felder mit der Endung „by“ angeben, kommt es beim Hochladen zu Problemen. In diesem Beispiel wurde nur das erste Feld „id“ hochgeladen. Der Wert aller anderen Felder ist NULL. Das liegt daran, dass der folgende Befehl die Option „Feldende durch“ nicht angibt, da die Eingabedatei ein Komma als Feldbegrenzer hat.
Daten mit der Option „Umschlossen von“ hochladen
Im folgenden Beispiel hat die Eingabetextdatei die Textfeldwerte in Anführungszeichen eingeschlossen, d. h. die Werte für „Name“ und „Abteilung“ sind in Anführungszeichen eingeschlossen.
Verwenden Sie in diesem Fall die Option „Umschlossen von“ wie unten gezeigt.
LOAD DATA INFILE 'employee3.txt' INTO TABLE employee FIELDS TERMINATED BY ',' ENCLOSED BY '"';
Der obige Befehl lädt die Datensätze korrekt hoch, wie unten durch den mysql select-Befehl gezeigt wird:
Bitte beachten Sie, dass Sie bei der Kombination von „fields terminated by“ und „fields enclosed by“ das Schlüsselwort „FIELDS“ nicht zweimal verwenden müssen, wie unten gezeigt, was zu folgender Fehlermeldung führt:
FIELDS TERMINATED BY ',' FIELDS ENCLOSED BY '"';
Das obige Beispiel zeigt die folgende Fehlermeldung „ERROR 1064 (42000)“ an:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FIELDS ENCLOSED BY '"'' at line 4
Verwendung von Escape-Zeichen in Textdateidaten
Angenommen, Sie haben ein Komma im Wert eines bestimmten Feldes.
Im folgenden Beispiel hat z. B. der 2. Feldname den Wert in diesem Format: „Vorname, Nachname“.
Wenn Sie die obige Datei mit dem folgenden Befehl laden, werden Sie sehen, dass „10 Warnungen“ angezeigt werden.
Die Datensätze werden auch nicht richtig geladen, weil ein Komma im Wert eines der Felder steht.
Korrekte Datei: Um das obige Problem zu lösen, verwenden Sie einen umgekehrten Schrägstrich (\) vor dem Komma im Wert des Namensfeldes, wie unten gezeigt.
Das Folgende wird diesmal ohne Fehler funktionieren, da wir \ als Escape-Zeichen haben.
Sie können auch ein anderes Escape-Zeichen verwenden, wie unten gezeigt. In diesem Beispiel verwenden wir ^ als Escape-Zeichen anstelle des Standardzeichens \.
In diesem Fall verwenden Sie die Option „ESCAPED BY“ wie unten gezeigt.
LOAD DATA INFILE 'employee41.txt' INTO TABLE employee FIELDS TERMINATED BY ',' ESCAPED BY '\^'
Bitte beachten Sie, dass einige der Zeichen nicht als Escape-Zeichen verwendet werden können. Wenn Sie z. B. % als Escape-Zeichen verwenden, erhalten Sie folgende Fehlermeldung.
LOAD DATA INFILE 'employee41.txt' INTO TABLE employee FIELDS TERMINATED BY ',' ESCAPED BY '\%'ERROR 1083 (42000): Field separator argument is not what is expected; check the manual
Daten mit der Option „Zeilen abgeschlossen durch“ hochladen
Anstatt alle Datensätze in einer separaten Zeile zu haben, können Sie sie auch in der gleichen Zeile haben.
Im folgenden Beispiel wird jeder einzelne Datensatz durch das Symbol | getrennt.
Um die obige Datei hochzuladen, verwenden Sie die Option lines terminated by, wie unten gezeigt.
LOAD DATA INFILE 'employee5.txt' INTO TABLE employee FIELDS TERMINATED BY ',' LINES TERMINATED BY '|';
Der obige Befehl lädt die Datensätze aus employee5.txt hoch, wie unten gezeigt.
Es gibt einige Punkte, die Sie beachten sollten:
- Wenn die Eingabedatei von einem Windows-Rechner stammt, dann sollten Sie dies verwenden: LINES TERMINATED BY ‚\r\n‘
- Wenn Sie eine CSV-Datei verwenden, um Daten in die Tabelle zu laden, dann versuchen Sie eine der folgenden Möglichkeiten: 1) LINES TERMINATED BY ‚\r‘ 2) LINES TERMINATED BY ‚\r\n‘
Zeilenpräfix in Upload-Datei ignorieren mit „Starting By“-Option
Sie können den Datensätzen in Ihrer Eingabetextdatei auch ein Präfix voranstellen, das beim Upload ignoriert werden kann.
In der folgenden Datei „employee6.txt“ steht zum Beispiel für den 1., 2. und 5. Datensatz „Data:“ am Anfang der Zeile. Sie können nur diese Datensätze hochladen, indem Sie das Zeilenpräfix ignorieren.
Um das Zeilenpräfix zu ignorieren und diese Datensätze hochzuladen (z. B. „Data:“ in der obigen Datei), verwenden Sie die Option „Zeilen beginnen mit“ wie unten gezeigt.
LOAD DATA INFILE 'employee6.txt' INTO TABLE employee FIELDS TERMINATED BY ',' LINES STARTING BY 'Data:';
Das Folgende ist die Ausgabe des obigen Befehls:
Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
Wie Sie unten sehen, hat der obige Befehl nur die Datensätze hochgeladen, die mit dem Präfix „Data:“ beginnen. Dies ist hilfreich, um selektiv nur die Datensätze hochzuladen, die ein bestimmtes Präfix haben.
Kopfzeile aus der Upload-Datei ignorieren
In der folgenden Eingabetextdatei ist die erste Zeile die Kopfzeile, die den Namen der Spalten enthält.
Während des Uploads wollen wir die erste Kopfzeile aus der Datei employee7.txt ignorieren. Dazu verwenden Sie die Option IGNORE 1 lines wie unten gezeigt.
LOAD DATA INFILE 'employee7.txt' INTO TABLE employee FIELDS TERMINATED BY ',' IGNORE 1 LINES;
Wie Sie in der folgenden Ausgabe sehen, hat die Eingabedatei zwar 6 Zeilen, aber die 1. Zeile (die Kopfzeile) wird ignoriert und die restlichen 5 Zeilen werden hochgeladen.
Nur eine bestimmte Spalte aus der Upload-Datei hochladen (und andere Spalten ignorieren)
Im folgenden Beispiel haben wir nur Werte für drei Felder. Die Spalte „Abteilung“ haben wir in dieser Beispieldatei nicht.
# cat employee8.txt 100,Thomas,5000200,Jason,5500300,Mayla,7000400,Nisha,9500500,Randy,6000
Um Werte aus dem Eingabedatensatz in eine bestimmte Spalte in der Tabelle hochzuladen, geben Sie die Spaltennamen während des Ladens der Daten in der Datei an, wie unten gezeigt. Die letzte Zeile im folgenden Befehl enthält die Spaltennamen, die zum Hochladen der Datensätze aus der Eingabetextdatei verwendet werden sollen.
LOAD DATA INFILE 'employee8.txt' INTO TABLE employee FIELDS TERMINATED BY ',' (id, name, salary);
Da wir die Spalte „dept“ im obigen Befehl nicht angegeben haben, sehen wir, dass diese Spalte NULL ist, wie unten gezeigt.
Auch hier gilt: Wenn Sie die Liste der Spalten nicht angeben, erwartet der Befehl, dass alle Spalten in der Eingabedatei vorhanden sind.
Auch wenn Sie die Spaltenliste in der letzten Zeile nicht angeben, erhalten Sie einen Syntaxfehler, wie unten gezeigt.
Variablen während des Hochladens mit der Option „Setzen“ verwenden
Für dieses Beispiel verwenden wir die folgende Datei employee2.txt.
In diesem Beispiel wollen wir das Gehalt um 500 erhöhen, bevor wir es in die Tabelle hochladen. Zum Beispiel ist das Gehalt für Thomas (der der 1. Datensatz ist) 5000. Aber während des Hochladens wollen wir es um 500 auf 5500 erhöhen und diesen erhöhten Wert in der Tabelle aktualisieren.
Zu diesem Zweck verwenden Sie den SET-Befehl und verwenden Gehalt als Variable und führen die Erhöhung wie unten gezeigt durch.
LOAD DATA INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED BY ',' (id, name, dept, @salary) SET salary = @salary+500;
Wie Sie in der folgenden Ausgabe sehen, wurde die Spalte „Gehalt“ während des Hochladens der Daten aus der Textdatei für alle Datensätze um 500 erhöht.
Shell-Skript zum Laden von Daten aus einer Textdatei schreiben
Manchmal möchten Sie die Daten automatisch aus einer Textdatei hochladen, ohne sich jedes Mal bei der mysql-Eingabeaufforderung anmelden zu müssen.
Sagen wir, wir wollen den folgenden Befehl in ein Shell-Skript einfügen und dieses automatisch auf der thegeekstuff-Datenbank ausführen.
LOAD DATA INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED BY ','
Um den Load-Befehl von der Kommandozeile aus auszuführen, verwenden Sie die Option -e im mysql-Befehl und führen ihn von der Linux-Eingabeaufforderung aus, wie unten gezeigt.
# mysql -e "LOAD DATA INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED BY ','" \ -u root -pMySQLPassword thegeekstuff
Oder Sie können ihn in ein Shell-Skript einfügen, wie unten gezeigt. In diesem Beispiel enthält das Shell-Skript load-data.sh den obigen mysql-Befehl.
Erteilen Sie diesem Skript load-data.sh die Ausführungsberechtigung und führen Sie es von der Kommandozeile aus, wodurch die Daten automatisch in die Tabelle geladen werden. Sie können dies auch als Cronjob einplanen, um die Daten aus der Datei in einem geplanten Intervall automatisch in die Tabelle zu laden.
# chmod u+x load-data.sh# ./load-data.sh
Wenn Ihnen dieser Artikel gefallen hat, könnte er Ihnen auch gefallen..
|
|
 |
 |
 |
 |