Ich habe ein nicht normalisiertes Ereignis-Tagebuch-CSV von einem Client, den ich in eine MySQL-Tabelle laden möchte, damit ich in ein gesundes Format umwandeln kann. Ich habe eine Tabelle mit dem Namen 'CSVImport' erstellt, die ein Feld für jede Spalte der CSV-Datei enthält. Die CSV enthält 99 Spalten. Dies war an sich schon eine schwierige Aufgabe:
CREATE TABLE 'CSVImport' (id INT);
ALTER TABLE CSVImport ADD COLUMN Title VARCHAR(256);
ALTER TABLE CSVImport ADD COLUMN Company VARCHAR(256);
ALTER TABLE CSVImport ADD COLUMN NumTickets VARCHAR(256);
...
ALTER TABLE CSVImport Date49 ADD COLUMN Date49 VARCHAR(256);
ALTER TABLE CSVImport Date50 ADD COLUMN Date50 VARCHAR(256);
In der Tabelle gibt es keine Einschränkungen, und alle Felder enthalten VARCHAR-Werte (256), mit Ausnahme der Spalten, die Zählungen enthalten (dargestellt durch INT), Ja/Nein (dargestellt durch BIT), Preise (dargestellt durch DECIMAL) und Textklappen ( vertreten durch TEXT).
Ich habe versucht, Daten in die Datei zu laden:
LOAD DATA INFILE '/home/paul/clientdata.csv' INTO TABLE CSVImport;
Query OK, 2023 rows affected, 65535 warnings (0.08 sec)
Records: 2023 Deleted: 0 Skipped: 0 Warnings: 198256
SELECT * FROM CSVImport;
| NULL | NULL | NULL | NULL | NULL |
...
Die gesamte Tabelle ist mit NULL
gefüllt.
Ich denke, das Problem ist, dass die Text-Blurbs mehr als eine Zeile enthalten und MySQL die Datei so analysiert, als würde jede neue Zeile einer Datenzeilenzeile entsprechen. Ich kann die Datei problemlos in OpenOffice laden.
Die Datei clientdata.csv enthält 2593 Zeilen und 570 Datensätze. Die erste Zeile enthält Spaltennamen. Ich denke, es ist durch Kommas getrennt, und der Text ist anscheinend durch Doppelzitate begrenzt.
AKTUALISIEREN:
Lesen Sie im Zweifelsfall das Handbuch: http://dev.mysql.com/doc/refman/5.0/de/load-data.html
Ich fügte der LOAD DATA
-Anweisung einige Informationen hinzu, die OpenOffice klug genug war, um daraus abzuleiten, und lädt jetzt die korrekte Anzahl von Datensätzen:
LOAD DATA INFILE "/home/paul/clientdata.csv"
INTO TABLE CSVImport
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
Es gibt jedoch immer noch viele NULL
-Datensätze, und keine der geladenen Daten scheint am richtigen Ort zu sein.
Der Kern Ihres Problems scheint darin zu bestehen, dass die Spalten in der CSV-Datei mit denen in der Tabelle übereinstimmen.
Viele grafische MySQL-Clients haben sehr schöne Importdialoge für diese Art von Dingen.
Mein Favorit für diesen Job ist Windows-basiert HeidiSQL . Sie erhalten eine grafische Oberfläche, um den Befehl LOAD DATA
zu erstellen. Sie können es später programmgesteuert wiederverwenden.
Screenshot: Dialog "Textdatei importieren"
Um den Dialog "Textdatei importieren" zu öffnen, gehen Sie zu Tools > Import CSV file
:
Verwenden Sie mysqlimport , um eine Tabelle in die Datenbank zu laden:
mysqlimport --ignore-lines=1 \
--fields-terminated-by=, \
--local -u root \
-p Database \
TableName.csv
Ich fand es unter http://chriseiffel.com/everything-linux/how-to-import-a-large-csv-file-to-mysql/
Um das Trennzeichen zu einer Registerkarte zu machen, verwenden Sie --fields-terminated-by='\t'
Der einfachste Weg, den ich über 200 Zeilen importiert habe, befindet sich unterhalb des Befehls im phpmyadmin-SQL-Fenster
Ich habe eine einfache Landtabelle mit zwei Spalten CountryId, CountryName
hier sind .csv-Daten
hier ist Befehl:
LOAD DATA INFILE 'c:/country.csv'
INTO TABLE country
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
Denken Sie daran, erscheinen Sie nie in der zweiten Spalte, sonst wird Ihr Import angehalten
Ich weiß, dass die Frage ist alt, aber ich möchte teilen dies
Ich habe diese Methode verwendet, um mehr als 100.000 Datensätze ( ~ 5MB ) in 0.046sec zu importieren.
So machen Sie es:
LOAD DATA LOCAL INFILE
'c:/temp/some-file.csv'
INTO TABLE your_awesome_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(field_1,field_2 , field_3);
Es ist sehr wichtig, die letzte Zeile einzufügen, wenn Sie mehr als ein Feld haben. Normalerweise wird das letzte Feld übersprungen (MySQL 5.6.17).
LINES TERMINATED BY '\n'
(field_1,field_2 , field_3);
Vorausgesetzt, Sie haben die erste Zeile als Titel für Ihre Felder, möchten Sie möglicherweise auch diese Zeile einfügen
IGNORE 1 ROWS
So sieht es aus, wenn Ihre Datei eine Kopfzeile hat.
LOAD DATA LOCAL INFILE
'c:/temp/some-file.csv'
INTO TABLE your_awesome_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(field_1,field_2 , field_3);
phpMyAdmin kann den CSV-Import verarbeiten. Hier sind die Schritte:
Bereiten Sie die CSV-Datei so vor, dass die Felder in derselben Reihenfolge wie die MySQL-Tabellenfelder stehen.
Entfernen Sie die Kopfzeile aus der CSV-Datei (sofern vorhanden), sodass nur die Daten in der Datei enthalten sind.
Wechseln Sie zur phpMyAdmin-Schnittstelle.
Wählen Sie die Tabelle im linken Menü aus.
Klicken Sie oben auf die Schaltfläche "Importieren".
Navigieren Sie zur CSV-Datei.
Wählen Sie die Option "CSV mit LOAD DATA".
Geben Sie "," in die "Felder mit" ein.
Geben Sie die Spaltennamen in derselben Reihenfolge wie in der Datenbanktabelle ein.
Klicken Sie auf die Schaltfläche "Los" und Sie sind fertig.
Dies ist eine Notiz, die ich für meine zukünftige Verwendung vorbereitet habe, und hier teilen, wenn jemand anderes davon profitieren kann.
Sie können dies beheben, indem Sie die Spalten in Ihrer LOAD DATA-Anweisung auflisten. Aus dem Handbuch :
LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);
... in Ihrem Fall müssen Sie also die 99 Spalten in der Reihenfolge auflisten, in der sie in der CSV-Datei erscheinen.
Die mysql-Befehlszeile kann beim Import zu viele Probleme verursachen. So machen Sie es:
Versuchen Sie das, es hat bei mir funktioniert
LOAD DATA LOCAL INFILE 'filename.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 ROWS;
IGNORE 1 ROWS ignoriert hier die erste Zeile, die die Feldnamen enthält. Beachten Sie, dass Sie für den Dateinamen den absoluten Pfad der Datei eingeben müssen.
Wenn Sie MySQL Workbench (derzeit Version 6.3) verwenden, können Sie Folgendes tun:
Sie können dieses Online-Tool auch zum Generieren von SQL-Anweisungen zum Erstellen/Einfügen basierend auf Ihrer CSV verwenden. /. http://www.convertcsvtomysql.com/
Was ich an diesem Tool mag:
Nachteile
Ich sehe etwas seltsames. Sie verwenden für ESCAPING das gleiche Zeichen, das Sie für ENCLOSING verwenden. Die Engine weiß also nicht, was zu tun ist, wenn sie ein "" "gefunden hat, und ich denke, deshalb scheint nichts an der richtigen Stelle zu sein. Ich denke, wenn man die Zeile von ESCAPING entfernt, sollte es toll laufen :
LOAD DATA INFILE "/home/paul/clientdata.csv"
INTO TABLE CSVImport
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
Es sei denn, Sie analysieren (manuell, visuell, ...) Ihre CSV und finden heraus, welche Figur für die Flucht verwendet wird. Manchmal ist '\'. Wenn Sie es nicht haben, verwenden Sie es nicht.
Ändern Sie den Servernamen, den Benutzernamen, das Kennwort, den Datenbanknamen, den Pfad Ihrer Datei, den Tabellennamen und das Feld, das Sie in Ihrer Datenbank einfügen möchten
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "bd_dashboard";
//For create connection
$conn = new mysqli($servername, $username, $password, $dbname);
$query = "LOAD DATA LOCAL INFILE
'C:/Users/lenovo/Desktop/my_data.csv'
INTO TABLE test_tab
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(name,mob)";
if (!$result = mysqli_query($conn, $query)){
echo '<script>alert("Oops... Some Error occured.");</script>';
exit();
//exit(mysqli_error());
}else{
echo '<script>alert("Data Inserted Successfully.");</script>'
}
?>
Wenn Sie einen Windows-Computer mit geladener Excel-Tabelle verwenden, ist das neue mySql-Plugin für Excel phänomenal. Die Leute bei Oracle haben wirklich einen netten Job mit dieser Software gemacht. Sie können die Datenbankverbindung direkt aus Excel herstellen. Dieses Plugin analysiert Ihre Daten und stellt die Tabellen in einem den Daten entsprechenden Format auf. Ich hatte einige Monster große CSV-Dateien mit Daten zu konvertieren. Dieses Tool hat viel Zeit gespart.
http://dev.mysql.com/downloads/windows/Excel/
Sie können in Excel Aktualisierungen vornehmen, die online in der Datenbank angezeigt werden. Dies funktionierte außerordentlich gut mit mySql-Dateien, die mit dem äußerst kostengünstigen GoDaddy Shared Hosting erstellt wurden. (Beachten Sie beim Erstellen der Tabelle bei GoDaddy, dass Sie einige Standardeinstellungen auswählen müssen, um den externen Zugriff auf die Datenbank zu ermöglichen ...)
Mit diesem Plugin haben Sie pure Interaktivität zwischen Ihrer XL-Tabelle und dem Online-Speicher von mySQL.
Eine andere Lösung ist die Verwendung von csvsql tool von amazing csvkit suite.
Anwendungsbeispiel:
csvsql --db mysql://$user:[email protected]/$database --insert --tables $tablename $file
Dieses Tool kann automatisch auf die Datentypen schließen (Standardverhalten), eine Tabelle erstellen und die Daten in die erstellte Tabelle einfügen. Die --overwrite
-Option kann verwendet werden, um eine Tabelle zu löschen, wenn sie bereits vorhanden ist. --insert
-Option, um die Tabelle aus der Datei auszufüllen.
pip install csvkit
Voraussetzungen:python-dev
, libmysqlclient-dev
, MySQL-python
apt-get install python-dev libmysqlclient-dev
pip install MySQL-python
PHP Abfrage zum Importieren einer CSV-Datei in die Mysql-Datenbank
$query = <<<EOF
LOAD DATA LOCAL INFILE '$file'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(name,mobile,email)
EOF;
if (!$result = mysqli_query($this->db, $query))
{
exit(mysqli_error($this->db));
}
** CSV-Beispieldaten **
name,mobile,email
Christopher Gritton,570-686-3439,[email protected]
Brandon Wilson,541-309-5149,[email protected]
Craig White,516-795-8065,[email protected]
David Whitney,713-214-3966,[email protected]
Hier ist ein Beispiel für eine Excel-Datei:
Speichern Sie unter und wählen Sie .csv.
Wenn Sie mit Notepad ++ oder einem anderen Notizblock öffnen, wird der CSV-Datenbildschirm wie unten gezeigt angezeigt.
Stellen Sie sicher, dass Sie den Header entfernen und die Spaltenausrichtung in .csv wie in mysql Table . Hat. Ersetzen Sie den Ordnernamen durch Ihren Ordnernamen
LOAD DATA LOKALE INFILE
'D: /Ordner_name/meinDateiname.csv' INTO TABLE mail FELDER BEENDET '', '.
Wenn große Daten vorliegen, können Sie Kaffee trinken und laden!.
Das ist alles was du brauchst.
So importieren Sie CSV-Dateien in SQL-Tabellen
Beispieldatei: Overseas_trade_index
data CSV-Datei
Schritte:
Muss eine Tabelle für overseas_trade_index
erstellen.
Es müssen Spalten erstellt werden, die sich auf die CSV-Datei beziehen.
SQL-Abfrage:
( id int not null primary key auto_increment,
series_reference varchar (60),
period varchar (60),
data_value decimal(60,0),
status varchar (60),
units varchar (60),
magnitude int(60),
subject text(60),
group text(60),
series_title_1 varchar (60),
series_title_2 varchar (60),
series_title_3 varchar (60),
series_title_4 varchar (60),
series_title_5 varchar (60),
);
Müssen Sie MySQL-Datenbank im Terminal verbinden.
=>show databases;
=>use database;
=>show tables;
Bitte geben Sie diesen Befehl ein, um die CSV-Daten in MySQL-Tabellen zu importieren.
load data infile '/home/desktop/Documents/overseas.csv' into table trade_index fields terminated by ',' lines terminated by '\n' (series_reference,period,data_value,status,units,magnitude,subject,series_title1,series_title_2,series_title_3,series_title_4,series_title_5);
Finden Sie diese Übersee-Handelsindexdaten auf sqldatabase:
select * from trade_index;
Wenn Sie Intellij verwenden https://www.jetbrains.com/datagrip/features/importexport.html
Ich benutze Mysql Workbench, um die gleiche Arbeit zu erledigen.
Hinweis: Schauen Sie sich die Protokolldatei von mysql workbench auf Fehler an, indem Sie "tail -f [mysqlworkbenchpath] /log/wb*.log" verwenden.