Portada » Lenguaje PHP » Importar datos de Excel a MySQL con php

Importar datos de Excel a MySQL con php

Importar datos de Excel a MySQL con php. Cuando realizamos algún sistema web debemos importar archivos Excel a Mysql en sus respectivos registros. Hace algunos meses, por motivo de trabajo estuve desarrollando un sistema para una institución educativa donde sus notas eran almacenadas en ficheros de Excel (Misnotas.xlsx), observé el obstáculo de almacenar cientos de registros desde Excel a una base de datos MySQL, en su respectiva tabla.

Analizando la cantidad de registros que superaba los mil registros, descarté la opción de hacerlo en forma manual y tuve que buscar otra alternativa que me ahorrara tiempo para agilizar el proceso.

Por mi mente pasaron varias alternativas, algunas en formato CSV o XLS, analicé varias las soluciones que pude observar, unas más fáciles que otras, pero la idea era trabajar con el formato actual del Excel sin tener que hacer ninguna modificación, me refiero a la extensión XLSX. Ahora en este pequeño articulo les brindo la solución que le di a este sistema.

Importar datos de Excel a MySQL con php
Importar datos de Excel a MySQL con php

Importar datos de Excel a MySQL con php

¿Cómo importar a MySQL desde Excel?

Como les comentaba había varias alternativas, unas en extensión CSV y luego importarlo a MySQL. Luego de comprobar la eficacia me incline por esta solución.

Quizá le interese los siguientes artículos.

[pt_view id=»fc09d98ps2″]

Recursos necesarios para la solución:

  1. El gestor PHPMyAdmin para crear la tabla y base de datos.
  2. Un archivo de Excel con datos, ordenados por columna y con una cabecera.
  3. EL lenguaje PHP como interprete para la vinculación con MySQL.

Ya tenemos casi todo, vamos a describir los pasos

A continuación detallaremos una serie de pasos para conseguir el objetivo.

1. Implementando formulario

El formulario HTML encargado de recoger la informacion del fichero de excel.

Guardar excel en Mysql
Guardar excel en Mysql
<div class="container">
<h2>Cargar e importar archivo excel a MySQL</h2>
<form name="importa" method="post" action="" enctype="multipart/form-data" >
  <div class="col-xs-4">
    <div class="form-group">
      <input type="file" class="filestyle" data-buttonText="Seleccione archivo" name="excel">
    </div>
  </div>
  <div class="col-xs-2">
    <input class="btn btn-default btn-file" type='submit' name='enviar'  value="Importar"  />
  </div>
  <input type="hidden" value="upload" name="action" />
  <input type="hidden" value="usuarios" name="mod">
  <input type="hidden" value="masiva" name="acc">
</form>
</div>

2. Procesamos el fichero Excel

Las siguientes lineas de código será el encargado de recibir el fichero Excel recogido desde el formulario HTML

//cargamos el fichero
$archivo = $_FILES['excel']['name'];
$tipo = $_FILES['excel']['type'];
$destino = "cop_".$archivo;//Le agregamos un prefijo para identificarlo el archivo cargado
if (copy($_FILES['excel']['tmp_name'],$destino)) echo "Archivo Cargado Con Éxito";
else echo "Error Al Cargar el Archivo";
        
if (file_exists ("cop_".$archivo)){ 
/** Llamamos las clases necesarias PHPEcel */
require_once('Classes/PHPExcel.php');
require_once('Classes/PHPExcel/Reader/Excel2007.php');                  
// Cargando la hoja de excel
$objReader = new PHPExcel_Reader_Excel2007();
$objPHPExcel = $objReader->load("cop_".$archivo);
$objFecha = new PHPExcel_Shared_Date();       
// Asignamon la hoja de excel activa
$objPHPExcel->setActiveSheetIndex(0);

3. Hacemos la conexión con la base de datos

El lenguaje PHP es muy dinámico y por ende se puede conectar con MySQL con gran facilidad. Por lo tanto, aquí les mostrare el código de conexión.

// Importante - conexión con la base de datos 
$cn = mysql_connect ("localhost","root","") or die ("ERROR EN LA CONEXION CON LA BD");
$db = mysql_select_db ("php_excel",$cn) or die ("ERROR AL CONECTAR A LA BD");

4. Creación de la tabla Mysql

Para que nuestro ejemplo funcione tenemos que tener una tabla con sus respectivas columnas definidas para albergar la información del fichero Excel.

-- Estructura de tabla para la tabla `excel`

CREATE TABLE `excel` (
  `id` int(11) NOT NULL,
  `nombres` varchar(255) NOT NULL,
  `apellidos` varchar(255) NOT NULL,
  `genero` varchar(255) NOT NULL,
  `carrera` varchar(150) NOT NULL,
  `edad` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `activo` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;


INSERT INTO `excel` (`id`, `nombres`, `apellidos`, `genero`, `carrera`, `edad`, `email`, `activo`) VALUES
(1, 'Juan Carlos', 'Flores', 'Masculino', 'Computacion', '21', 'juan@micorreo.com', 1),
(2, 'Maria', 'Ugarte', 'Femenino', 'Contabilidad', '19', 'maria@micorreo.com', 1),
(3, 'Vanessa', 'Torres', 'Femenino', 'Computacion', '17', 'Vanessa@micorreo.com', 1),
(4, 'Juan Carlos', 'Flores', 'Masculino', 'Computacion', '21', 'juan@micorreo.com', 1),
(5, 'Maria', 'Ugarte', 'Femenino', 'Contabilidad', '19', 'maria@micorreo.com', 1),
(6, 'Vanessa', 'Torres', 'Femenino', 'Computacion', '17', 'Vanessa@micorreo.com', 1);

ALTER TABLE `excel`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `excel`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

5. Insertando registros a la base de datos

Este ejemplo se basa en la inserción de un fichero de Excel a la base de datos. Sin embargo, estos ficheros pueden contener cientos de registros que pueden ser, lista de alumnos, clientes, productos, etc. Para poder insertar nos apoyaremos en el ciclo foreach.

foreach($_DATOS_EXCEL as $campo => $valor){
$sql = "INSERT INTO excel  (nombres,apellidos,genero,carrera,edad,email,activo)  VALUES ('";
foreach ($valor as $campo2 => $valor2){
$campo2 == "activo" ? $sql.= $valor2."');" : $sql.= $valor2."','";
}

$result = mysql_query($sql);
if (!$result){ echo"Error al insertar registro ".$campo;$errores+=1;}
}   
/////////////////////////////////////////////////////////////////////////   
echo"<hr> <div class='col-xs-12'>
        <div class='form-group'>";
          echo "<strong><center>ARCHIVO IMPORTADO CON EXITO, EN TOTAL $campo REGISTROS Y $errores ERRORES</center></strong>";
echo"</div>
</div>  ";

6. Mostrando resultados insertados

Datos guardados en Mysql con Excel y PHP
Datos guardados en Mysql con Excel y PHP
if (isset($action)== "upload"){
echo '<table border="1" class="table">';
    echo '<thead>';
        echo '<tr>
            <th>Nombres</th> 
            <th>Apellidos</th>
            <th>Genero</th>
            <th>Edad</th>
            <th>Carrera</th>
            <th>E-Mail</th>
            </tr> 
        </thead> 
        <tbody> ';

$count=0;
foreach ($objPHPExcel->setActiveSheetIndex(0)->getRowIterator() as $row) {
    $count++;
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false);
    echo '<tr>';
    foreach ($cellIterator as $cell) {
        if (!is_null($cell)) {
            $value = $cell->getCalculatedValue();
            echo '<td>".$value."</td>';
           
        }
    }
    echo '</tr>';
}?>
  </tbody>
  </table>
<?php }?>

Mostramos los archivos completos: Importar datos de Excel a MySQL con php

Siguiendo con el artículo, mostraremos los elementos de nuestro sistema de importación

Index.php

<?php include("header.php");?>
<!-- FORMULARIO PARA ESTE EJERCICIO -->
<div class="container">
<h2>Cargar e importar archivo excel a MySQL</h2>
<form name="importa" method="post" action="" enctype="multipart/form-data" >
  <div class="col-xs-4">
    <div class="form-group">
      <input type="file" class="filestyle" data-buttonText="Seleccione archivo" name="excel">
    </div>
  </div>
  <div class="col-xs-2">
    <input class="btn btn-default btn-file" type='submit' name='enviar'  value="Importar"  />
  </div>
  <input type="hidden" value="upload" name="action" />
  <input type="hidden" value="usuarios" name="mod">
  <input type="hidden" value="masiva" name="acc">
</form>

<!-- PROCESO DE CARGA Y PROCESAMIENTO DEL EXCEL-->
<?php 
extract($_POST);
if (isset($_POST['action'])) {
$action=$_POST['action'];
}

if (isset($action)== "upload"){
//cargamos el fichero
$archivo = $_FILES['excel']['name'];
$tipo = $_FILES['excel']['type'];
$destino = "cop_".$archivo;//Le agregamos un prefijo para identificarlo el archivo cargado
if (copy($_FILES['excel']['tmp_name'],$destino)) echo "Archivo Cargado Con Éxito";
else echo "Error Al Cargar el Archivo";
        
if (file_exists ("cop_".$archivo)){ 
/** Llamamos las clases necesarias PHPEcel */
require_once('Classes/PHPExcel.php');
require_once('Classes/PHPExcel/Reader/Excel2007.php');                  
// Cargando la hoja de excel
$objReader = new PHPExcel_Reader_Excel2007();
$objPHPExcel = $objReader->load("cop_".$archivo);
$objFecha = new PHPExcel_Shared_Date();       
// Asignamon la hoja de excel activa
$objPHPExcel->setActiveSheetIndex(0);

// Importante - conexión con la base de datos 
$cn = mysql_connect ("localhost","root","") or die ("ERROR EN LA CONEXION CON LA BD");
$db = mysql_select_db ("php_excel",$cn) or die ("ERROR AL CONECTAR A LA BD");

// Rellenamos el arreglo con los datos  del archivo xlsx que ha sido subido

$columnas = $objPHPExcel->setActiveSheetIndex(0)->getHighestColumn();
$filas = $objPHPExcel->setActiveSheetIndex(0)->getHighestRow();

//Creamos un array con todos los datos del Excel importado
for ($i=2;$i<=$filas;$i++){
                        $_DATOS_EXCEL[$i]['nombres'] = $objPHPExcel->getActiveSheet()->getCell('A'.$i)->getCalculatedValue();
                        $_DATOS_EXCEL[$i]['apellidos'] = $objPHPExcel->getActiveSheet()->getCell('B'.$i)->getCalculatedValue();
                        $_DATOS_EXCEL[$i]['genero']= $objPHPExcel->getActiveSheet()->getCell('C'.$i)->getCalculatedValue();
                        $_DATOS_EXCEL[$i]['carrera']= $objPHPExcel->getActiveSheet()->getCell('D'.$i)->getCalculatedValue();
                        $_DATOS_EXCEL[$i]['edad'] = $objPHPExcel->getActiveSheet()->getCell('E'.$i)->getCalculatedValue();
                        $_DATOS_EXCEL[$i]['email'] = $objPHPExcel->getActiveSheet()->getCell('F'.$i)->getCalculatedValue();
                        $_DATOS_EXCEL[$i]['activo'] = 1;
                    }       
                    $errores=0;


foreach($_DATOS_EXCEL as $campo => $valor){
                        $sql = "INSERT INTO excel  (nombres,apellidos,genero,carrera,edad,email,activo)  VALUES ('";
                        foreach ($valor as $campo2 => $valor2){
                            $campo2 == "activo" ? $sql.= $valor2."');" : $sql.= $valor2."','";
                        }

                        $result = mysql_query($sql);
                        if (!$result){ echo "Error al insertar registro ".$campo;$errores+=1;}
                    }   
                    /////////////////////////////////////////////////////////////////////////   
echo "<hr> <div class='col-xs-12'>
        <div class='form-group'>
          <strong><center>ARCHIVO IMPORTADO CON EXITO, EN TOTAL $campo REGISTROS Y $errores ERRORES</center></strong>
</div>
</div>  ";
                            //Borramos el archivo que esta en el servidor con el prefijo cop_
                    unlink($destino);
                    
                }
                    //si por algun motivo no cargo el archivo cop_ 
                else{
                    echo "Primero debes cargar el archivo con extencion .xlsx";
                }
            }
        ?>
<?php 
            if (isset($action)) {
$filas = $objPHPExcel->setActiveSheetIndex(0)->getHighestRow();
                }
            if (isset($filas)) {
$columnas = $objPHPExcel->setActiveSheetIndex(0)->getHighestColumn();
                }
            if (isset($filas)) {
$filas = $objPHPExcel->setActiveSheetIndex(0)->getHighestRow();
                }


if (isset($action)== "upload"){
echo '<table border="1" class="table">';
    echo '<thead>
        <tr>
            <th>Nombres</th> 
            <th>Apellidos</th>
            <th>Genero</th>
            <th>Edad</th>
            <th>Carrera</th>
            <th>E-Mail</th>
            </tr> 
        </thead> 
        <tbody> ';

$count=0;
foreach ($objPHPExcel->setActiveSheetIndex(0)->getRowIterator() as $row) {
    $count++;
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false);
    echo '<tr>';
    foreach ($cellIterator as $cell) {
        if (!is_null($cell)) {
            $value = $cell->getCalculatedValue();
            echo '<td>".$value."</td>';
           
        }
    }
    echo '</tr>';
}
  echo '</tbody></table>';
}?>
</div>
<?php include ("footer.php");
?>

header.php

<!DOCTYPE html>
<html>
<head>
    <title>Sistema Guardar Excel en MySQL</title>
    <!-- Latest compiled and minified CSS -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" >
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" >
<link rel="stylesheet" href="css/styles.css" >

 <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<!-- Latest compiled and minified JavaScript -->
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
 <script type="text/javascript" src="js/filestyle.min.js"> </script>

</head>
<body>

<div class="container">
    <div class="row">
        <div style="border: 1px solid #ccc; height: 42px; background:#f1f3f5 ">
        <nav class="dropdownmenu">
  <ul>
    <li><a href="index.php">Inicio</a></li>
    <li><a href="Mantenimiento.php">Ver Registros</a></li>
    <li></li>
    <li></li>
  </ul>

</nav></div>

Mantenimiento.php

<?php

require_once('connect.php');
$ReadSql = "SELECT * FROM `excel`";
$res = mysqli_query($connection, $ReadSql);
include("header.php");
?>
<div style="width: 100%; height: 10px; clear: both;"></div>
    <h2>Mantenimiento de registros insertados con PHP Excel</h2>
        <table class="table"> 
        <thead> 
            <tr> 
                <th>#</th> 
                <th>Nombres</th> 
                <th>Apellidos</th> 
                <th>E-Mail</th> 
                <th>Genero</th> 
                <th>Edad</th> 
                <th>Carrera</th> 
            </tr> 
        </thead> 
        <tbody> 
        <?php 
        $i=0;
        while($r = mysqli_fetch_assoc($res)){$i++;
        ?>
            <tr> 
                <th scope="row"><?php echo $i; ?></th> 
                <td><?php echo $r['nombres']; ?></td> 
                <td><?php echo $r['apellidos']; ?></td> 
                <td><?php echo $r['genero']; ?></td> 
                <td><?php echo $r['edad']; ?></td> 
                <td><?php echo $r['carrera']; ?></td> 
                <td><?php echo $r['email']; ?></td> 

    
            </tr> 
        <?php } ?>
        </tbody> 
        </table>
    </div>
</div>

<?php include ("footer.php"); ?>

connect.php

<?php
$connection = mysqli_connect('localhost', 'root', '');
if (!$connection){
    die("Database conexion fallida" . mysqli_error($connection));
}
$select_db = mysqli_select_db($connection, 'php_excel');
if (!$select_db){
    die("Ha fallado la conexion" . mysqli_error($connection));
}

?>

CONCLUSIÓN: Importar datos de Excel a MySQL con php

Espero que esta breve explicación les sirva en abundancia, dejare los ficheros para que descarguen y lo implementen en sus sistemas.

CÓDIGO CORREGIDO POR ERRORES DE ACTUALIZACIÓN DEL PHP

 

 

 

¿De cuánta utilidad te ha parecido este contenido?

¡Haz clic en una estrella para puntuarlo!

Promedio de puntuación 5 / 5. Recuento de votos: 1

Hasta ahora, ¡no hay votos!. Sé el primero en puntuar este contenido.

Scroll al inicio
Esta web utiliza cookies propias para su correcto funcionamiento. Contiene enlaces a sitios web de terceros con políticas de privacidad ajenas que podrás aceptar o no cuando accedas a ellos. Al hacer clic en el botón Aceptar, acepta el uso de estas tecnologías y el procesamiento de tus datos para estos propósitos.
Privacidad