para - importar csv a mysql por consola
Cómo importar un archivo CSV a la tabla MySQL (16)
Tengo un CSV de eventos no normalizado de un cliente que estoy tratando de cargar en una tabla MySQL para poder refactorizar un formato sano. Creé una tabla llamada ''CSVImport'' que tiene un campo para cada columna del archivo CSV. El CSV contiene 99 columnas, por lo que esta fue una tarea bastante difícil en sí misma:
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);
No hay restricciones en la tabla, y todos los campos contienen valores VARCHAR (256), excepto las columnas que contienen recuentos (representados por INT), sí / no (representados por BIT), precios (representados por DECIMAL) y anuncios de texto ( representado por TEXTO).
Intenté cargar datos en el archivo:
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 |
...
Toda la tabla se llena con NULL .
Creo que el problema es que las burbujas de texto contienen más de una línea, y MySQL está analizando el archivo como si cada nueva línea corresponda a una fila de databazse. Puedo cargar el archivo en OpenOffice sin ningún problema.
El archivo clientdata.csv contiene 2593 líneas y 570 registros. La primera línea contiene nombres de columnas. Creo que está delimitado por comas, y el texto aparentemente está delimitado con comillas dobles.
ACTUALIZAR:
En caso de duda, lea el manual: http://dev.mysql.com/doc/refman/5.0/en/load-data.html
Agregué información a la declaración LOAD DATA que OpenOffice era lo suficientemente inteligente como para inferir, y ahora carga la cantidad correcta de registros:
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;
Pero todavía hay muchos registros completamente NULL , y ninguno de los datos que se cargaron parece estar en el lugar correcto.
Cambie el nombre del servidor, el nombre de usuario, la contraseña, el nombre de usuario, la ruta de acceso de su archivo, el nombre de la tabla y el campo que se encuentra en su base de datos que desea insertar
<?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>''
}
?>
Sé que la pregunta es antigua , pero me gustaría compartir esto
Usé este método para importar más de 100K registros ( ~ 5MB ) en 0.046sec
Así es como lo haces:
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 muy importante incluir la última línea, si tiene más de un campo, es decir, normalmente omite el último campo (MySQL 5.6.17)
LINES TERMINATED BY ''/n''
(field_1,field_2 , field_3);
Luego, asumiendo que tiene la primera fila como título para sus campos, es posible que desee incluir esta línea también
IGNORE 1 ROWS
Esto es lo que parece si su archivo tiene una fila de encabezado.
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);
Aquí está la muestra de la captura de pantalla del archivo de Excel:
Guardar como y elija .csv.
Y tendrá como se muestra a continuación la captura de pantalla de datos .csv si abre con notepad ++ o cualquier otro bloc de notas.
Asegúrese de eliminar el encabezado y la alineación de la columna en .csv como en la tabla mysql. Reemplace folder_name por el nombre de su carpeta
CARGAR DATOS LOCALES INFILE
''D: /folder_name/myfilename.csv'' EN la TABLA de correo CAMPOS TERMINADOS POR '','' (fname, lname, email, phone);
Si es big data, puedes tomar café y tenerlo cargado !.
Eso es todo lo que necesitas.
El núcleo de su problema parece ser hacer coincidir las columnas del archivo CSV con las de la tabla.
Muchos clientes gráficos de mySQL tienen diálogos de importación muy buenos para este tipo de cosas.
Mi favorito para el trabajo es HeidiSQL basado en Windows. Te da una interfaz gráfica para construir el comando LOAD DATA ; Puedes reutilizarlo programáticamente más tarde.
Captura de pantalla: cuadro de diálogo "Importar archivo de texto"
Para abrir el cuadro de diálogo " Tools > Import CSV file ", vaya a Tools > Import CSV file :
En el caso de que utilice Intellij https://www.jetbrains.com/datagrip/features/importexport.html
Intenta esto, funciono para mi
LOAD DATA LOCAL INFILE ''filename.csv'' INTO TABLE table_name FIELDS TERMINATED BY '','' ENCLOSED BY ''"'' IGNORE 1 ROWS;
IGNORAR 1 FILA aquí ignora la primera fila que contiene los nombres de campo. Tenga en cuenta que para el nombre de archivo debe escribir la ruta absoluta del archivo.
La forma más sencilla en que he importado más de 200 filas es debajo del comando en la ventana de phpmyadmin sql
Tengo una tabla simple de país con dos columnas CountryId, CountryName
aquí están los datos .csv
Aquí está el comando:
LOAD DATA INFILE ''c:/country.csv''
INTO TABLE country
FIELDS TERMINATED BY '',''
ENCLOSED BY ''"''
LINES TERMINATED BY ''/n''
IGNORE 1 ROWS
Tenga una cosa en mente, nunca aparezca, en la segunda columna, de lo contrario su importación se detendrá
La línea de comandos mysql es propensa a demasiados problemas en la importación. Así es como lo haces:
- Usa Excel para editar los nombres de encabezado para que no tengan espacios
- guardar como .csv
- use el navegador gratuito Navicat Lite Sql para importar y crear automáticamente una nueva tabla (asígnele un nombre)
- abrir la nueva tabla insertar una columna de número de auto primaria para ID
- cambiar el tipo de las columnas como se desee.
- ¡hecho!
Otra solución más es utilizar la herramienta csvsql de la increíble suite csvkit .
Ejemplo de uso:
csvsql --db mysql://$user:$password@localhost/$database --insert --tables $tablename $file
Esta herramienta puede inferir automáticamente los tipos de datos (comportamiento predeterminado), crear una tabla e insertar los datos en la tabla creada. --overwrite opción --overwrite se puede usar para eliminar una tabla si ya existe. --insert opción - para rellenar la tabla desde el archivo.
Para instalar la suite
pip install csvkit
Prerrequisitos: python-dev , libmysqlclient-dev , MySQL-python
apt-get install python-dev libmysqlclient-dev
pip install MySQL-python
Puede solucionar esto enumerando las columnas en su declaración LOAD DATA. Del manual :
LOAD DATA INFILE ''persondata.txt'' INTO TABLE persondata (col1,col2,...);
... así que en su caso necesita listar las 99 columnas en el orden en que aparecen en el archivo csv.
Si está utilizando MySQL Workbench (actualmente versión 6.3) puede hacerlo de la siguiente manera:
- Haga clic derecho en "Tablas";
- Elija el Asistente de importación de datos de tabla;
- Elija su archivo csv y siga las instrucciones (también se puede usar JSON); Lo bueno es que puede crear una nueva tabla basada en el archivo csv que desea importar o cargar datos en una tabla existente
Si está utilizando una máquina Windows con la hoja de cálculo de Excel cargada, el nuevo complemento mySql para Excel es fenomenal. La gente de Oracle realmente hizo un buen trabajo con ese software. Puede hacer la conexión de base de datos directamente desde Excel. Ese complemento analizará sus datos y configurará las tablas para usted en un formato consistente con los datos. Tenía algunos archivos csv de datos de monstruos grandes para convertir. Esta herramienta fue un gran ahorro de tiempo.
http://dev.mysql.com/downloads/windows/excel/
Puede realizar actualizaciones desde Excel que se completarán en la base de datos en línea. Esto funcionó extremadamente bien con los archivos mySql creados en el alojamiento compartido GoDaddy de bajo costo. (Tenga en cuenta que cuando cree la tabla en GoDaddy, debe seleccionar algunas configuraciones fuera de estándar para habilitar el acceso a la base de datos fuera del sitio ...)
Con este complemento, tendrá una interactividad pura entre su hoja de cálculo XL y el almacenamiento de datos en línea mySql.
También puede probar esta herramienta en línea para generar sentencias de creación / inserción de SQL basadas en su CSV. http://www.convertcsvtomysql.com/
Lo que me gusta de esta herramienta:
- Sencillo
- No solo genera instrucciones INSERT, sino también instrucciones CREATE para generar una tabla
- Al generar sentencias CREATE, esta herramienta no solo realiza todos los campos VARCHAR, sino que analiza los datos en su CSV y, basándose en ese análisis, elige el tipo de datos adecuado.
Desventajas
- El tamaño del archivo de entrada está limitado a 3 mb
- Es posible que no esté dispuesto a ceder sus datos a terceros.
Use mysqlimport para cargar una tabla en la base de datos:
mysqlimport --ignore-lines=1 /
--fields-terminated-by=, /
--local -u root /
-p Database /
TableName.csv
Lo encontré en http://chriseiffel.com/everything-linux/how-to-import-a-large-csv-file-to-mysql/
Para hacer del delimitador una pestaña, use --fields-terminated-by=''/t''
Consulta de PHP para importar el archivo csv a la base de datos mysql
$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));
}
** Datos de archivo CSV de muestra **
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]
phpMyAdmin puede manejar la importación CSV. Aquí están los pasos:
Prepare el archivo CSV para tener los campos en el mismo orden que los campos de la tabla MySQL.
Elimine la fila del encabezado del CSV (si existe), de modo que solo los datos estén en el archivo.
Ir a la interfaz de phpMyAdmin.
Seleccione la tabla en el menú de la izquierda.
Haga clic en el botón de importación en la parte superior.
Busque el archivo CSV.
Seleccione la opción "CSV utilizando datos de carga".
Introduzca "," en los "campos terminados por".
Ingrese los nombres de las columnas en el mismo orden en que aparecen en la tabla de la base de datos.
Haga clic en el botón de ir y ya está.
Esta es una nota que preparé para mi uso futuro y que comparto aquí si alguien más puede beneficiarse.