Excel-Datei in eine MySQL-Datenbank importieren

Um eine Excel-Arbeitsmappe in MySQL zu importieren, speichern Sie entweder ein Arbeitsblatt als CSV und laden es mit LOAD DATA oder phpMyAdmin, oder verwenden Sie Sqlizer.io, um die Arbeitsmappe in SQL umzuwandeln. Für die CSV-Wege ist eine kompatible Zieltabelle erforderlich. Beim Weg über Sqlizer.io wird die Arbeitsmappe direkt verarbeitet; prüfen Sie das erzeugte SQL, bevor Sie es ausführen. Die folgenden Schritte verwenden eine Tabelle students mit den Spalten id, firstname, lastname, gender und city.

Mit der Anweisung LOAD DATA Daten importieren

Speichern Sie das Arbeitsblatt als CSV-Datei mit Komma als Trennzeichen. Prüfen Sie, ob Reihenfolge und Bedeutung der Spalten mit der Zieltabelle übereinstimmen. Das Beispiel setzt eine Kopfzeile, durch Kommas getrennte Felder, Text in doppelten Anführungszeichen und Unix-Zeilenumbrüche voraus. Für den ersten Import wird außerdem angenommen, dass die Zieltabelle leer ist. Bei einem erneuten Import können jedoch Duplikate entstehen, sofern Sie die vorhandenen Zeilen nicht vorher entfernen oder eine geeignete Behandlung doppelter Einträge konfigurieren. Passen Sie die Formatoptionen an die tatsächlich gespeicherte Datei an.

Bevor Sie die Daten laden, können Sie mit der Python-Standardbibliothek eine repräsentative CSV-Zeile prüfen. Dieses kleine Beispiel verwendet eine Zeile mit denselben Spaltennamen wie die Beispieltabelle:

import csv
from io import StringIO

sample = StringIO("id,firstname,lastname,gender,city\n1,Ada,Lovelace,F,London\n")
row = next(csv.DictReader(sample))
print(f"{row['firstname']} {row['lastname']} — {row['city']}")

Ada Lovelace — London

Mit dieser Vorschau prüfen Sie, ob Kopfzeilennamen und Werte wie vorgesehen eingelesen werden. Sie stellt keine Verbindung zu MySQL her und ersetzt nicht die nachfolgende Importanweisung.

Das Beispiel für MySQL 8.0.27 verwendet ein Upload-Verzeichnis auf einem Windows-Server. LOAD DATA INFILE liest den angegebenen Pfad aus dem Dateisystem des MySQL-Servers. Die Datei muss daher für den Server zugänglich und durch die Einstellung secure_file_priv zugelassen sein. Führen Sie die Anweisung in MySQL Workbench in einem Abfragefenster aus, das mit diesem Server verbunden ist. Workbench macht einen Pfad auf Ihrem Arbeitsplatzrechner nicht automatisch für den Server zugänglich. Dieselbe Regel für serverseitige Pfade gilt auch für den Kommandozeilenclient. Bei LOAD DATA LOCAL INFILE liest der Client die Datei und sendet sie an den Server; dafür müssen die entsprechenden Einstellungen sowohl auf Client- als auch auf Serverseite das lokale Laden erlauben. Wenn der Zugriff verweigert wird, finden Sie weitere Informationen in der MySQL-Sicherheitsdokumentation zu LOAD DATA.

Für die Beispieldatei lautet die Anweisung LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/students.csv' INTO TABLE tb_students FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;. Ersetzen Sie Pfad, Tabelle, Trennzeichen, Textbegrenzung und Zeilenabschluss durch die Werte Ihrer Umgebung. Für tabulatorgetrennte Daten verwenden Sie FIELDS TERMINATED BY '\t', für ein einzelnes Leerzeichen als Trennzeichen FIELDS TERMINATED BY ' '. Windows-CSV-Dateien verwenden häufig \r\n als Zeilenabschluss. Wenn die Zeilen nicht wie erwartet getrennt werden, verwenden Sie den tatsächlichen Zeilenabschluss der Datei. IGNORE 1 ROWS überspringt die Kopfzeile, ordnet die Spalten aber nicht anhand ihrer Namen zu. Fehlt id in der Datei oder weicht ihre Spaltenreihenfolge ab, geben Sie nach dem Tabellennamen die passenden Zielspalten an und prüfen Sie, ob automatisch erzeugte Werte wie vorgesehen behandelt werden.

Prüfen Sie nach dem Laden die Tabelle in Workbench oder führen Sie die Abfrage SELECT id, firstname, lastname, gender, city FROM tb_students LIMIT 10; aus. Vergleichen Sie die Zeilen mit der CSV-Datei und kontrollieren Sie die Anzahl der importierten Datensätze, bevor Sie die Daten verwenden.

Methode 2: Daten mit Sqlizer.io importieren

Verwenden Sie Sqlizer.io, wenn Sie eine Arbeitsmappe umwandeln möchten, ohne sie vorher als CSV zu speichern. Wählen Sie die Arbeitsmappe und MySQL als Zieldatenbanktyp aus und geben Sie an, ob die erste Zeile Spaltenüberschriften enthält. Wählen Sie das aktive Arbeitsblatt aus oder geben Sie dessen Namen ein, zum Beispiel students, und wählen Sie entweder das gesamte Arbeitsblatt oder den umzuwandelnden Zellbereich aus. Legen Sie den Namen der Zieltabelle fest und lassen Sie das SQL erzeugen.

Prüfen Sie die erzeugten Anweisungen, bevor Sie sie ausführen: Kontrollieren Sie die Tabellendefinition, Spaltennamen und Werte sowie das Verhalten beim Erstellen der Tabelle oder bei einer Prüfung, ob sie bereits vorhanden ist. Führen Sie das SQL für die vorgesehene Datenbank aus. Prüfen Sie anschließend die Tabelle und vergleichen Sie repräsentative Zeilen mit der Arbeitsmappe. Eine Arbeitsmappe kann vertrauliche Daten enthalten. Berücksichtigen Sie daher deren Sensibilität und die Nutzungsbedingungen des Dienstes, bevor Sie sie zu einem externen Konverter hochladen.

Methode 3: Daten mit phpMyAdmin importieren

Auch bei diesem Weg wird eine CSV-Datei verwendet. Speichern Sie das Arbeitsblatt als CSV, öffnen Sie dann die Zieldatenbank und wählen Sie die Tabelle aus, deren Spalten zur CSV-Datei passen. Die Beispieltabelle tb_students_phpmyadmin enthält die Spalten id, firstname, lastname, gender und city. Öffnen Sie in phpMyAdmin die Registerkarte Import, wählen Sie die CSV-Datei aus und stellen Sie CSV als Format ein. Passen Sie die formatspezifischen Optionen an die Datei an, einschließlich Feldtrennzeichen, Textbegrenzung, Zeilenabschluss und der Einstellung, ob die Kopfzeile übersprungen werden soll. Starten Sie den Import mit Go.

Wenn die CSV-Spalten nicht zur Tabelle passen, korrigieren Sie die Tabelle oder die Datei vor dem Import. Die Auswahl des CSV-Formats ordnet die Spalten nicht automatisch anhand ihrer Überschriften zu. Nach Abschluss des Imports können Sie die Tabelle öffnen oder eine Abfrage für Zeilen ausführen. Vergleichen Sie mehrere Werte und die Zeilenanzahl mit der Quelldatei.

Fazit

Wählen Sie LOAD DATA, wenn Sie ein Arbeitsblatt als CSV speichern können und der MySQL-Server auf die Datei zugreifen kann. Für einen CSV-Import über den Browser eignet sich phpMyAdmin. Sqlizer.io wandelt eine Arbeitsmappe in SQL um, das Sie prüfen und für die vorgesehene Datenbank ausführen können. Stimmen Sie bei jeder Methode Spalten und Dateiformat auf die Zieltabelle ab und überprüfen Sie anschließend die importierten Zeilen.