webentwicklung-frage-antwort-db.com.de

Importieren Sie CSV in die MySQL-Tabelle

Was ist der beste/schnellste Weg, um eine CSV-Datei in eine MySQL-Tabelle hochzuladen? Ich möchte für die erste Datenzeile die Spaltennamen verwenden.

Ich habe es gefunden:

So importieren Sie eine CSV-Datei in eine MySQL-Tabelle

Aber die einzige Antwort war, eine GUI und keine Shell zu verwenden?

94
lcm

Anstatt ein Skript zum Abrufen von Informationen aus einer CSV-Datei zu schreiben, können Sie MYSQL direkt damit verknüpfen und die Informationen mithilfe der folgenden SQL-Syntax hochladen.

Um eine Excel-Datei in MySQL zu importieren, exportieren Sie sie zunächst als CSV-Datei. Entfernen Sie die CSV-Header aus der generierten CSV-Datei sowie leere Daten, die Excel möglicherweise am Ende der CSV-Datei eingefügt hat.

Sie können es dann in eine MySQL-Tabelle importieren, indem Sie Folgendes ausführen:

load data local infile 'uniq.csv' into table tblUniq fields terminated by ','
  enclosed by '"'
  lines terminated by '\n'
    (uniqName, uniqCity, uniqComments)

wie weiter gelesen: CSV-Datei direkt in MySQL importieren

BEARBEITEN

In Ihrem Fall müssen Sie zuerst einen Interpreter schreiben, um die erste Zeile zu finden und sie als Spaltennamen zuzuweisen.


EDIT-2

In MySQL-Dokumenten in der Syntax LOAD DATA :

Mit der Option IGNORE number LINES Können Zeilen am Dateianfang ignoriert werden. Beispielsweise können Sie mit IGNORE 1 LINES Eine anfängliche Kopfzeile mit Spaltennamen überspringen:

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;

Daher können Sie die folgende Anweisung verwenden:

LOAD DATA LOCAL INFILE 'uniq.csv'
INTO TABLE tblUniq
FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(uniqName, uniqCity, uniqComments)
147
hjpotter92

Hier ist ein einfaches PHP Befehlszeilenskript, das genau das tut, was Sie brauchen:

<?php

$Host = 'localhost';
$user = 'root';
$pass = '';
$database = 'database';

$db = mysql_connect($Host, $user, $pass);
mysql_query("use $database", $db);

/********************************************************************************/
// Parameters: filename.csv table_name

$argv = $_SERVER[argv];

if($argv[1]) { $file = $argv[1]; }
else {
    echo "Please provide a file name\n"; exit; 
}
if($argv[2]) { $table = $argv[2]; }
else {
    $table = pathinfo($file);
    $table = $table['filename'];
}

/********************************************************************************/
// Get the first row to create the column headings

$fp = fopen($file, 'r');
$frow = fgetcsv($fp);

foreach($frow as $column) {
    if($columns) $columns .= ', ';
    $columns .= "`$column` varchar(250)";
}

$create = "create table if not exists $table ($columns);";
mysql_query($create, $db);

/********************************************************************************/
// Import the data into the newly created table.

$file = $_SERVER['PWD'].'/'.$file;
$q = "load data infile '$file' into table $table fields terminated by ',' ignore 1 lines";
mysql_query($q, $db);

?>

Es wird eine Tabelle basierend auf der ersten Zeile erstellt und die verbleibenden Zeilen in diese Zeile importiert. Hier ist die Befehlszeilensyntax:

php csv_import.php csv_file.csv table_name
23
Hawkee

Erstellen Sie zunächst eine Tabelle in der Datenbank mit der gleichen Anzahl von Spalten, die sich in der CSV-Datei befinden.

Dann benutze folgende Abfrage

LOAD DATA INFILE 'D:/Projects/testImport.csv' INTO TABLE cardinfo
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
4
Mukesh

wenn Sie die Möglichkeit haben, phpadmin zu installieren, gibt es einen Importbereich, in dem Sie CSV-Dateien in Ihre Datenbank importieren können. Es gibt sogar ein Kontrollkästchen, mit dem Sie den Header in die erste Zeile der Datei setzen können Die erste Zeile wird Teil der Daten

4
Jose Ortiz

Zum Laden von Daten aus einer Textdatei oder einer CSV-Datei lautet der Befehl

load data local infile 'file-name.csv'
into table table-name
fields terminated by '' enclosed by '' lines terminated by '\n' (column-name);

Im obigen Befehl muss in meinem Fall nur eine Spalte geladen werden, sodass kein "Abgeschlossen von" und "Eingeschlossen von" vorhanden ist, sodass ich es leer halte, da der Programmierer sonst das Trennzeichen eingeben kann. für z.B. , (Komma) oder "oder; oder irgendetwas.

** für Leute, die mysql Version 5 und höher benutzen **

Bevor Sie die Datei in mysql laden, müssen Sie sicherstellen, dass in side etc/mysql/my.cnf

zum Bearbeiten von my.cnf Befehl ist

Sudo vi /etc/mysql/my.cnf

[mysqld]  
local-infile

[mysql]  
local-infile  
3
Rakesh

Wenn Sie mysql als "mysql -u -p --local-infile" starten, funktioniert es einwandfrei

3
marciomolusco

Ich habe dazu einen Code geschrieben und werde ein paar Schnipsel einfügen:

$dir = getcwd(); // Get current working directory where this .php script lives
$fileList = scandir($dir); // scan the directory where this .php lives and make array of file names

Holen Sie sich dann die CSV-Header, damit Sie mysql mitteilen können, wie es importiert werden soll (Hinweis: Stellen Sie sicher, dass Ihre mysql-Spalten genau mit den csv-Spalten übereinstimmen):

//extract headers from .csv for use in import command
$headers = str_replace("\"", "`", array_shift(file($path)));
$headers = str_replace("\n", "", $headers);

Dann senden Sie Ihre Anfrage an den MySQL-Server:

mysqli_query($cons, '
        LOAD DATA LOCAL INFILE "'.$path.'"
            INTO TABLE '.$dbTable.'  
            FIELDS TERMINATED by \',\' ENCLOSED BY \'"\'
            LINES TERMINATED BY \'\n\'
            IGNORE 1 LINES
            ('.$headers.')
            ;
        ')or die(mysql_error());
2
ravenchilde

Ich habe einige Zeit damit gerungen. Das Problem liegt nicht darin, wie die Daten geladen werden, sondern wie die Tabelle aufgebaut wird, um sie zu halten. Sie müssen eine DDL-Anweisung generieren, um die Tabelle zu erstellen, bevor Sie die Daten importieren.

Besonders schwierig, wenn die Tabelle eine große Anzahl von Spalten enthält.

Hier ist ein python Skript, das (fast) den Job erledigt:

#!/usr/bin/python    
import sys
import csv

# get file name (and hence table name) from command line
# exit with usage if no suitable argument   
if len(sys.argv) < 2:
   sys.exit('Usage: ' + sys.argv[0] + ': input CSV filename')
ifile = sys.argv[1]

# emit the standard invocation
print 'create table ' + ifile + ' ('

with open(ifile + '.csv') as inputfile:
   reader = csv.DictReader(inputfile)
   for row in reader:
      k = row.keys()
      for item in k:
         print '`' + item + '` TEXT,'
      break
   print ')\n'

Das Problem, das es zu lösen gilt, ist, dass der endgültige Feldname und die Datentypdeklaration mit einem Komma abgeschlossen werden und der mySQL-Parser dies nicht toleriert.

Natürlich hat es auch das Problem, dass für jedes Feld der Datentyp TEXT verwendet wird. Wenn die Tabelle mehrere hundert Spalten enthält, wird die Tabelle durch VARCHAR (64) zu groß.

Dies scheint auch bei der maximalen Spaltenanzahl für mySQL zu scheitern. Dann ist es an der Zeit, zu Hive oder HBase zu wechseln, wenn Sie dazu in der Lage sind.

2
agentv

Hier ist, wie ich es in Python mit csv und dem MySQL Connector gemacht habe:

import csv
import mysql.connector

credentials = dict(user='...', password='...', database='...', Host='...')
connection = mysql.connector.connect(**credentials)
cursor = connection.cursor(prepared=True)
stream = open('filename.csv', 'rb')
csv_file = csv.DictReader(stream, skipinitialspace=True)

query = 'CREATE TABLE t ('
query += ','.join('`{}` VARCHAR(255)'.format(column) for column in csv_file.fieldnames)
query += ')'
cursor.execute(query)
for row in csv_file:
    query = 'INSERT INTO t SET '
    query += ','.join('`{}` = ?'.format(column) for column in row.keys())
    cursor.execute(query, row.values())

stream.close()
cursor.close()
connection.close()

Wichtige Punkte

  • Verwenden Sie vorbereitete Anweisungen für das INSERT
  • Öffnen Sie die Datei file.csv in der Binärdatei 'rb'
  • Einige CSV-Dateien benötigen möglicherweise Optimierungen , z. B. die Option skipinitialspace.
  • Wenn 255 Nicht breit genug ist, erhalten Sie Fehler bei INSERT und müssen neu beginnen.
  • Passen Sie die Spaltentypen an, z. ALTER TABLE t MODIFY `Amount` DECIMAL(11,2);
  • Fügen Sie einen Primärschlüssel hinzu, z. ALTER TABLE t ADD `id` INT PRIMARY KEY AUTO_INCREMENT;
1
Bob Stein

Importieren Sie CSV-Dateien in die MySQL-Tabelle

LOAD DATA LOCAL INFILE 'd:\\Site.csv' INTO TABLE `siteurl` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

Character   Escape Sequence
\0      An ASCII NUL (0x00) character
\b      A backspace character
\n      A newline (linefeed) character
\r      A carriage return character
\t      A tab character.
\Z      ASCII 26 (Control+Z)
\N      NULL

besuche: http://www.webslessons.com/2014/02/import-csv-files-using-php-and-mysql.html

0
Elangovan

Wie bereits erwähnt, funktioniert die lokale Infile-Datei zum Laden von Daten einwandfrei. Ich habe das PHP-Skript ausprobiert, das Hawkee gepostet hat, aber es hat bei mir nicht funktioniert. Anstatt es zu debuggen, habe ich Folgendes getan:

1) kopiere/füge die Kopfzeile der CSV-Datei in eine txt-Datei ein und bearbeite sie mit Emacs. Fügen Sie zwischen jedem Feld ein Komma und einen Buchstaben ein, um jedes Feld in eine eigene Zeile zu setzen.
2) Speichern Sie diese Datei als FieldList.txt
3) Bearbeiten Sie die Datei so, dass sie Defns für jedes Feld enthält (die meisten waren varchar, aber einige waren int (x). Fügen Sie create table tablename (am Anfang der Datei und ) an das Ende der Datei. Speichern Sie sie als CreateTable.sql
4) Starten Sie den MySQL-Client mit Eingaben aus der Datei Createtable.sql, um die Tabelle zu erstellen
5) starte mysql client, kopiere/füge die meisten 'LOAD DATA INFILE' Befehle ein und ersetze meinen Tabellennamen und den Namen der CSV-Datei. Fügen Sie in der FieldList.txt-Datei ein. Stellen Sie sicher, dass Sie die 'IGNORE 1 LINES' einfügen, bevor Sie sie in die Feldliste einfügen

Klingt nach viel Arbeit, aber mit Emacs einfach .....

0
jim sims