Cómo importar datos desde un archivo excel, csv a la base de datos y exportarlos nuevamente

Como sabes, la base de datos es un componente muy importante en cada sitio web, en ella se almacena y manipula información. Suponiendo que necesites cambiar o actualizar el sistema de gestión de la base de datos, la memoria física o datos de un tercero que quiera importar a tu base de datos, tenemos muchas formas de hacerlo, y en este artículo te las presentaré. Cómo importar datos desde archivos de Excel, CSV a la base de datos y exportar de vuelta.

El siguiente ejemplo utiliza el lenguaje PHP, el sistema de administración MySQL y la biblioteca PHPExcel. También adjunté el archivo SQL para que puedas probarlo fácilmente, simplemente crea una base de datos llamada ‘test’, importa la estructura de la tabla de datos y úsala inmediatamente.

Requisitos: Importar el archivo de datos y exportar el archivo de tabla de registros maestros en MySQL

La información resumida está disponible:

Nombre de la base de datos: test Nombre de usuario: root Contraseña: ‘123456’ Nombre de la tabla: master_logs Archivo SQL: master.sql Archivo de prueba de Excel: import.csv, import.xlsx con el nombre de la hoja igual al nombre de la tabla (master_logs)

Asegúrate de haber iniciado el servicio del servidor, la base de datos MySQL. Supongo que has instalado y ejecutado software como XAMPP, MAMP, etc.

Paso 1. Creamos un archivo de conexión para conectarnos a la base de datos connection.php

Es muy simple de entender, con la información de conexión anterior, simplemente escribo los valores directamente de esta manera.

$mysqli = mysqli_connect(‘localhost’, ‘root’, ‘123456’ ,’test’);
$mysqli->set_charset(‘utf8’);
if (mysqli_connect_errno()) {
echo ‘Connect Failed: ‘ . mysqli_connect_error();
exit;
}

Paso 2. Crea tu base de datos y tabla


Importa el archivo master_logs.sql que adjunté a continuación, o simplemente crea la tabla master_logs de la siguiente manera:

CREATE TABLE `master_logs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`time` datetime DEFAULT NULL,
`operator_id` int(4) DEFAULT NULL,
`content` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Tenemos 4 campos: id, tiempo, operator_id, contenido. Tu archivo de importación en Excel o CSV necesita tener un número correspondiente de columnas para poder insertarse en tu tabla.

Paso 3. El archivo de datos para importar puede ser xlsx o csv, utilizando la biblioteca PHPExcel:

  • La biblioteca PHPExcel en el directorio Libs: require(‘Libs/PHPExcel.php’);
  • Archivo import.xlsx o import.csv, la biblioteca PHPExcel que he preparado (adjunta más abajo), tiene el siguiente formato:
Archivo en formato import.xlsx o import.csv

3.1. Importar archivos de Excel, CSV en MySQL Código PHP para la importación de datos. Los pasos son los siguientes:

  • Requerir la biblioteca PHPExcel
  • Requerir el archivo de conexión
  • Validar la obligatoriedad de importar el archivo a importar
  • Validar que los archivos importados sean de tipo Excel o CSV
  • Leer los datos del archivo importado. Leeremos datos de 4 columnas (A, B, C, D) correspondientes a 4 campos de datos en la tabla master_logs.
  • Dado que el archivo puede contener muchos registros, necesitamos procesar los datos antes de utilizar una declaración INSERT
  • Después de seleccionar el archivo y hacer clic en [Importar archivo de Excel], será exitoso si se cumplen las condiciones anteriores. Por favor, accede a tu base de datos para verificar.

<?php
require ‘Libs/PHPExcel.php’;
require ‘connection.php’;

// Import
if (isset($_POST[‘btnImport’])) {
$file = $_FILES[‘file’][‘tmp_name’];

if (!empty($file)) {
$csvMimes = array(‘application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’, ‘application/vnd.ms-excel’, ‘application/x-csv’, ‘application/excel’, ‘application/vnd.msexcel’);

if (in_array($_FILES[‘file’][‘type’], $csvMimes)) {
$objReader = PHPExcel_IOFactory::createReaderForFile($file);
$objReader->setLoadSheetsOnly(‘master_logs’);

$objReader->setReadDataOnly(true); // remove empty row

$objExcel = $objReader->load($file);
$sheetData = $objExcel->getActiveSheet()->toArray(‘null’,true,true,true);

$highestRow = $objExcel->setActiveSheetIndex()->getHighestRow();
$value = ”;
$comma = ‘,’;

$i = 1;
for ($row = 2; $row <= $highestRow; $row ++) {
$i ++;
if ($i == $highestRow) { $comma = ”;}
$id = $sheetData[$row][‘A’];
$time = “‘” . $sheetData[$row][‘B’] . “‘”;
$operator_id = $sheetData[$row][‘C’];
$content = “‘”. $sheetData[$row][‘D’] . “‘”;

$value .= “(” . $id . “,” . $time . “,” . $operator_id . “,” . $content . “)” . $comma;
}

// Multi insert query
if (!empty($value)) {
$sql = “INSERT INTO master_logs(id, time, operator_id, content) VALUES $value “;
if ($mysqli->query($sql)) {
echo ‘Inserted!’;
} else {
echo ‘Insert failed!’;
}
}
} else {
echo ‘Please select file excel!’;
}
} else {
echo ‘Please select file !’;
}
}

3.2. Exportar MySQL a archivo CSV, archivo de Excel Los pasos son los siguientes:

  • Consultar datos de la tabla master_logs, por supuesto, puedes agregar condiciones arbitrarias aquí
  • Asignar el nombre del campo de datos a las columnas de Excel correspondientes (A – ID, B – Tiempo, C – Operador, D – Contenido de los registros), utilizando la función setCellValue
  • Continuar utilizando la función setCellValue y la función while para establecer los datos en el archivo, además de la función while, puedes usar el bucle que prefieras.

// Export
$result = $mysqli->query(“select * from master_logs”);
if (isset($_POST[“exportToExcel”])) {
$objExcel = new PHPExcel;
$objExcel->setActiveSheetIndex(0);
$sheet = $objExcel->getActiveSheet()->setTitle(‘master_logs’);

$rowCount = 1;
$sheet->setCellValue(‘A’.$rowCount,’ID’);
$sheet->setCellValue(‘B’.$rowCount,’Time’);
$sheet->setCellValue(‘C’.$rowCount,’Operator’);
$sheet->setCellValue(‘D’.$rowCount,’Logs Content’);

while ($row = mysqli_fetch_array($result)){
$rowCount++;
$sheet->setCellValue(‘A’.$rowCount,$row[‘id’]);
$sheet->setCellValue(‘B’.$rowCount,$row[‘time’]);
$sheet->setCellValue(‘C’.$rowCount, ($row[‘operator_id’] == 1) ? ‘admin’ : ‘editor’);
$sheet->setCellValue(‘D’.$rowCount,$row[‘content’]);
}

ob_clean(); // clear three rows blank

// Export Data to CSV with PHP, when open use format UTF-8
$filename = “fileName” . date(“Y-m-d-H-i-s”) . “.csv”;
header(‘Content-Encoding: UTF-8’);
header(‘Content-type: text/csv; charset=UTF-8’);
header(‘Content-Disposition: attachment;filename=”‘ . $filename . ‘”‘);
header(“Pragma: no-cache”);
header(“Expires: 0”);
header(‘Content-Transfer-Encoding: binary’);

header(‘Content-Type: application/vnd.ms-excel’);
header(‘Cache-Control: max-age=0’);
$objWriter = PHPExcel_IOFactory::createWriter($objExcel, ‘CSV’);
$objWriter->save(‘php://output’);

// Export Data to Excel with PHP
$objWriter = new PHPExcel_Writer_Excel2007($objExcel);
$filename = ‘export.xlsx’;
$objWriter->save($filename);

header(‘Content-Disposition: attachment; filename=”‘ . $filename . ‘”‘);
header(‘Content-Type: application/vnd.openxmlformatsofficedocument.spreadsheetml.sheet’);
header(‘Content-Length: ‘ . filesize($filename));
header(‘Content-Transfer-Encoding: binary’);
header(‘Cache-Control: must-revalidate’);
header(‘Pragma: no-cache’);
readfile($filename);
return;
}

3.3. HTML


El código HTML es tan simple como se muestra a continuación (he adjuntado el código HTML en el archivo a continuación)

etiquetas HTML

3.4. CSS

The CSS code is as simple as below.

Conclusión

En la pantalla principal, después de importar, verás de inmediato una lista de registros recuperados de la base de datos mediante la combinación de PHP y MySQL.

  • Antes de importar el archivo:
Antes de importar el archivo

– Lista de datos después de importar

Lista de datos después de importar

Descargar código completo

 Size: 602 KB

Deja un comentario