into - Importar CSV a la tabla mysql

insert into table from csv mysql

Question

¿Cuál es la mejor / más rápida forma de cargar un archivo csv en una tabla mysql? Me gustaría utilizar la primera fila de datos como nombres de columna.

Encontró esto:

Cómo importar un archivo CSV a la tabla MySQL

¿Pero la única respuesta fue usar una GUI y no shell?

Answer 1

Aquí hay una secuencia de comandos de línea de comandos PHP simple que hará lo que necesita:

<?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);

?>

Creará una tabla basada en la primera fila e importará las filas restantes en ella. Aquí está la sintaxis de la línea de comando:

php csv_import.php csv_file.csv table_name

Answer 2

Así es como lo hice en Python usando csv y el conector MySQL :

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()

Puntos clave

• Use declaraciones preparadas para el INSERT
• Abra el archivo.csv en ''rb'' binario ''rb''
• Algunos archivos CSV pueden necesitar tweaking , como la opción skipinitialspace .
• Si 255 no es lo suficientemente ancho, obtendrá errores en INSERT y tendrá que comenzar de nuevo.
• Ajuste los tipos de columnas, p. Ej. ALTER TABLE t MODIFY `Amount` DECIMAL(11,2);
• Agregue una clave principal , por ejemplo ALTER TABLE t ADD `id` INT PRIMARY KEY AUTO_INCREMENT;

Answer 3

Como otros han mencionado, el infile local de datos de carga funciona bien. Probé el script php que Hawkee publicó, pero no funcionó para mí. En lugar de depurarlo, esto es lo que hice:

1) copie / pegue la fila del encabezado del archivo CSV en un archivo txt y edítelo con emacs. agregue una coma y CR entre cada campo para obtener cada uno en su propia línea.
2) Guarde ese archivo como FieldList.txt
3) edite el archivo para incluir defns para cada campo (la mayoría eran varchar, pero algunos eran int (x). Agregue create table tablename (al principio del archivo y) al final del archivo. Guárdelo como CreateTable .sql
4) iniciar el cliente mysql con la entrada del archivo Createtable.sql para crear la tabla
5) iniciar el cliente mysql, copiar / pegar en la mayor parte del comando ''LOAD DATA INFILE'', subsumiendo el nombre de mi tabla y el nombre del archivo csv. Pegue en el archivo FieldList.txt. Asegúrese de incluir ''IGNORE 1 LINES'' antes de pegar en la lista de campos

Suena como un montón de trabajo, pero fácil con emacs .....

Answer 4

En lugar de escribir un script para obtener información de un archivo CSV, puede vincular MYSQL directamente y cargar la información con la siguiente sintaxis SQL.

Para importar un archivo de Excel en MySQL, primero exportelo como un archivo CSV. Elimine los encabezados CSV del archivo CSV generado junto con los datos vacíos que Excel puede haber puesto al final del archivo CSV.

Luego puede importarlo en una tabla MySQL ejecutando:

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

como leído: Importar archivo CSV directamente a MySQL

EDITAR

Para su caso, primero tendrá que escribir un intérprete para encontrar la primera fila y asignarlos como nombres de columna.

EDIT-2

Desde documentos MySQL en la sintaxis LOAD DATA :

La opción IGNORE number LINES se puede utilizar para ignorar las líneas al inicio del archivo. Por ejemplo, puede usar IGNORE 1 LINES para omitir una línea de encabezado inicial que contiene nombres de columna:

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

Por lo tanto, puede usar la siguiente declaración:

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

Answer 5

Escribí un código para hacer esto, voy a poner algunos fragmentos:

$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

A continuación, obtenga los encabezados de CSV para que pueda decirle a mysql cómo importar (nota: asegúrese de que sus columnas de mysql coincidan exactamente con las columnas de csv):

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

Luego envíe su consulta al servidor mysql:

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());

Answer 6

Importar archivos CSV en la tabla mysql

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

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

Answer 7

Luché con esto por un tiempo. El problema no radica en cómo cargar los datos, sino en cómo construir la tabla para contenerlos. Debe generar una declaración DDL para compilar la tabla antes de importar los datos.

Particularmente difícil si la tabla tiene una gran cantidad de columnas.

Aquí hay un script de Python que (casi) hace el trabajo:

#!/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''

El problema que deja resolver es que el nombre final del campo y la declaración del tipo de datos finalizan con una coma, y ​​el analizador mySQL no lo tolerará.

Por supuesto, también tiene el problema de que usa el tipo de datos TEXT para cada campo. Si la tabla tiene varios cientos de columnas, entonces VARCHAR (64) hará que la tabla sea demasiado grande.

Esto también parece romperse en el recuento máximo de columnas para mySQL. Es entonces cuando es hora de pasar a Hive o HBase si puedes.

Answer 8

Para cargar datos del archivo de texto o archivo csv, el comando es

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

En el comando anterior, en mi caso solo hay que cargar una columna para que no haya "terminado por" y "encerrado por", así que lo dejo vacío, de lo contrario el programador puede ingresar el carácter separador. por ej. , (coma) o "o; o cualquier cosa.

** para las personas que usan la versión 5 de mysql o superior **

Antes de cargar el archivo en mysql debe asegurarse de que debajo de la línea de remolque se agreguen en el lado etc/mysql/my.cnf

para editar el comando my.cnf es

sudo vi /etc/mysql/my.cnf

[mysqld]  
local-infile

[mysql]  
local-infile  

Answer 9

Primero crea una tabla en la base de datos con los mismos números de columnas que están en el archivo csv.

Luego usa la siguiente consulta

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

Answer 10

Si inicia mysql como "mysql -u -p --local-infile", funcionará bien

Answer 11

Si tiene la capacidad de instalar phpadmin hay una sección de importación donde puede importar archivos csv a su base de datos. Incluso hay una casilla de verificación para configurar el encabezado en la primera línea del archivo que contiene los nombres de columna de la tabla (si no está marcada, la la primera línea se convertirá en parte de los datos

Answer 12

Tengo la búsqueda de Google de muchas maneras para importar csv a mysql, incluir "cargar archivo de datos", usar mysql workbench, etc.

Cuando utilizo el botón de importación de mysql workbench, primero debe crear la tabla vacía por su cuenta, establezca cada tipo de columna por su cuenta. Nota: debe agregar la columna ID al final como clave principal y no nulo y auto_increment; de lo contrario, el botón importar no se verá más adelante. Sin embargo, cuando empiezo a cargar el archivo CSV, nada cargado, parece un error. Me doy por vencido.

Afortunadamente, la mejor manera fácil que he encontrado hasta ahora es usar el mysql de Oracle para Excel. puedes descargarlo desde aquí mysql para excel

Esto es lo que vas a hacer: abrir el archivo csv en Excel, en la pestaña de Datos, buscar mysql para el botón de Excel

seleccione todos los datos, haga clic en exportar a mysql. Nota para establecer una columna de ID como clave principal.

Cuando haya terminado, vaya a mysql workbench para modificar la tabla, como el tipo de moneda debe ser decimal (19,4) para una cantidad grande de decimal (10,2) para uso regular. otro tipo de campo puede establecerse en varchar (255).