Importar datos de hoja de cálculo de Excel o CVS en MySQL

Eche un vistazo a la statement LOAD DATA INFILE . Le ayudará a importar datos del archivo CSV en la tabla.

Trabajo en un producto llamado SQLizer https://sqlizer.io que convierte archivos de Excel (y CSV) en instrucciones de inserción de MySQL que se pueden ejecutar para insertar los datos, o pegarlos en varios sistemas de administración de bases de datos.

Es gratis para archivos de hasta 5000 filas de datos

Esta es una pregunta recurrente en stackoverflow. Aquí hay una respuesta actualizada.

En realidad, hay varias maneras de importar un archivo de Excel a una base de datos MySQL con diversos grados de complejidad y éxito.

1. Utilidades Excel2MySQL o Navicat . Divulgación completa, soy el autor de Excel2MySQL. Estas 2 utilidades no son gratuitas, pero son la opción más fácil y tienen menos limitaciones. También incluyen características adicionales para ayudar a importar datos de Excel en MySQL. Por ejemplo, Excel2MySQL crea automáticamente su tabla y automáticamente optimiza los tipos de datos de campo como fechas, horas, carrozas, etc. Si tiene prisa o no puede obtener otras opciones para trabajar con sus datos, estas herramientas pueden satisfacer sus necesidades.

   

2. LOAD DATA INFILE : esta opción popular es quizás la más técnica y requiere cierta comprensión de la ejecución de comandos MySQL. Debe crear manualmente su tabla antes de cargarla y usar los tipos de campo VARCHAR de tamaño apropiado. Por lo tanto, sus tipos de datos de campo no están optimizados. LOAD DATA INFILE tiene problemas para importar archivos de gran tamaño que superan el tamaño de ‘max_allowed_packet’. Se requiere atención especial para evitar problemas al importar caracteres especiales y caracteres Unicode foráneos. Aquí hay un ejemplo reciente que utilicé para importar un archivo csv llamado test.csv.

   

3. phpMyAdmin : Seleccione primero su base de datos, luego seleccione la pestaña Importar. phpMyAdmin creará automáticamente su tabla y dimensionará sus campos VARCHAR, pero no optimizará los tipos de campo. phpMyAdmin tiene problemas para importar archivos de gran tamaño que exceden el tamaño de ‘max_allowed_packet’.

   

4. MySQL para Excel : este es un complemento de Excel gratuito de Oracle. Esta opción es un poco tediosa porque utiliza un asistente y la importación es lenta y con errores con archivos grandes, pero esta puede ser una buena opción para archivos pequeños con datos VARCHAR. Los campos no están optimizados.

   

Hace un tiempo, respondí una pregunta muy similar en el sitio de EE , y ofrecí el siguiente bloque de Perl, como un ejemplo rápido y sucio de cómo se puede cargar directamente una hoja de Excel en MySQL. Pasando por alto la necesidad de exportar / importar a través de CSV y así poder preservar más de esos caracteres especiales, y eliminando la necesidad de preocuparse por escapar del contenido.

#!/usr/bin/perl -w # Purpose: Insert each Worksheet, in an Excel Workbook, into an existing MySQL DB, of the same name as the Excel(.xls). # The worksheet names are mapped to the table names, and the column names to column names. # Assumes each sheet is named and that the first ROW on each sheet contains the column(field) names. # use strict; use Spreadsheet::ParseExcel; use DBI; use Tie::IxHash; die "You must provide a filename to $0 to be parsed as an Excel file" unless @ARGV; my $sDbName = $ARGV[0]; $sDbName =~ s/\.xls//i; my $oExcel = new Spreadsheet::ParseExcel; my $oBook = $oExcel->Parse($ARGV[0]); my $dbh = DBI->connect("DBI:mysql:database=$sDbName;host=192.168.123.123","root", "xxxxxx", {'RaiseError' => 1,AutoCommit => 1}); my ($sTableName, %hNewDoc, $sFieldName, $iR, $iC, $oWkS, $oWkC, $sSql); print "FILE: ", $oBook->{File} , "\n"; print "DB: $sDbName\n"; print "Collection Count: ", $oBook->{SheetCount} , "\n"; for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++) { $oWkS = $oBook->{Worksheet}[$iSheet]; $sTableName = $oWkS->{Name}; print "Table(WorkSheet name):", $sTableName, "\n"; for(my $iR = $oWkS->{MinRow} ; defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ; $iR++) { tie ( %hNewDoc, "Tie::IxHash"); for(my $iC = $oWkS->{MinCol} ; defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ; $iC++) { $sFieldName = $oWkS->{Cells}[$oWkS->{MinRow}][$iC]->Value; $sFieldName =~ s/[^A-Z0-9]//gi; #Strip non alpha-numerics from the Column name $oWkC = $oWkS->{Cells}[$iR][$iC]; $hNewDoc{$sFieldName} = $dbh->quote($oWkC->Value) if($oWkC && $sFieldName); } if ($iR == $oWkS->{MinRow}){ #eval { $dbh->do("DROP TABLE $sTableName") }; $sSql = "CREATE TABLE IF NOT EXISTS $sTableName (".(join " VARCHAR(512), ", keys (%hNewDoc))." VARCHAR(255))"; #print "$sSql \n\n"; $dbh->do("$sSql"); } else { $sSql = "INSERT INTO $sTableName (".(join ", ",keys (%hNewDoc)).") VALUES (".(join ", ",values (%hNewDoc)).")\n"; #print "$sSql \n\n"; eval { $dbh->do("$sSql") }; } } print "Rows inserted(Rows):", ($oWkS->{MaxRow} - $oWkS->{MinRow}), "\n"; } # Disconnect from the database. $dbh->disconnect(); 

Nota:

1. Cambie la cadena de conexión ($ oConn) para que se ajuste, y si es necesario, agregue una identificación de usuario y contraseña a los argumentos.
2. Si necesita soporte para XLSX, un cambio rápido a Spreadsheet :: XLSX es todo lo que necesita. Alternativamente, solo se necesitan unas pocas líneas de código para detectar el tipo de archivo y llamar a la biblioteca adecuada.
3. Lo anterior es un simple truco, supone que todo en una celda es una cadena / escalar, si preservar el tipo es importante, una pequeña función con algunas expresiones regulares se puede usar junto con algunas declaraciones if para asegurar que los números / fechas permanezcan en la aplicación formato cuando se escribe en la base de datos

4. El código anterior depende de una cantidad de módulos de CPAN, que puede instalar, suponiendo que se permita el acceso a ftp saliente, a través de:

   cpan YAML Data :: Dumper Spreadsheet :: ParseExcel Tie :: IxHash Encode Scalar :: Util File :: Basename DBD :: mysql

Debería devolver algo en las siguientes líneas (es bastante lento, debido a la confirmación automática):

 # ./Excel2mysql.pl test.xls FILE: test.xls DB: test Collection Count: 1 Table(WorkSheet name):Sheet1 Rows inserted(Rows):9892 

Para archivos de valores separados por comas (CSV), el panel de vista de resultados en Workbench tiene una opción “Importar registros desde un archivo externo” que importa datos CSV directamente en el conjunto de resultados. Ejecute eso y haga clic en “Aplicar” para confirmar los cambios.

Para archivos de Excel, considere usar el complemento oficial de MySQL for Excel .