How to import data from excel file, csv into the database and export back

As you know, database is a very important component in every website, information is stored and manipulated on it. Assuming you need to change or upgrade the database management system, physical memory or data from a 3rd party that wants to import into your database, we have many ways to do, in this article, I will introduce to you. How to import data from excel file, csv into the database and export back.

The following example uses PHP language, MySQL administration system, PHPExcel library. I also attached the sql file for you to easily test, just create a database test, import data structure table and use it immediately.

Requirements: Import the data file and export the master logs table file in MySQL

The summary information is available:

Database name: test
Username: root
Password: ‘123456’
Table name: master_logs
The sql file: master.sql
Test file excel: import.csv, import.xlsx with sheet name is table name (master_logs)

Make sure that you have started the server service, the MySQL database. I assume you have installed and run through XAMPP software, MAMP, etc …

Step 1. We create a connection file to connect to Database

connection.php

It is very simple to understand, with the above connection information, I simply write the value directly like that.

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

Step 2. Create your database and table

Import file master_logs.sql tôi đã đính kèm phía dưới, hoặc bạn chỉ cần tạo bảng master_logs như sau:

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;

We have 4 fields: id, time, operator_id, content. Your excel or csv import file needs a corresponding number of columns to be able to insert into your table.

Step 3. The data file to import is xlsx or csv, PHPExcel library

– PHPExcel library in the directory Libs: require(‘Libs/PHPExcel.php’);

– File import.xlsx or import.csv, PHPExcel library that I have prepared (attached below), have the following format:

File import.xlsx or import.csv format

1. Import excel, csv files into MySQL

PHP code for data import. The steps are as follows:

– Require the PHPExcel library
– Require file connection
– Validate must import the file to import
– Validate imported files as excel, csv
– Read imported file data. We will read 4 columns data (A, B, C, D) corresponding to 4 data fields in the master_logs table.
– Since the file can have many records, we need to process the data before using an INSERT statement
– After selecting the file and clicking [Import Excel file], it will be successful if the above conditions are met. Please access your database to check.

<?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 !’;
}
}

2. MySQL export to csv file, excel file

The steps are as follows:

– Query data from the master_logs table, of course you can add arbitrary conditions here
– Assign the data field name to the corresponding excel columns (A – ID, B – Time, C – Operator, D – Logs Content), using the setCellValue function
– Continue to use the function setCellValue and while function to set data into the file, in addition to the while function you can use the loop function you like.

// 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. HTML

The HTML code is as simple as below (I have attached the HTML code in the file below)

HTML tags

4. CSS

The CSS code is as simple as below.

Conclusion

On the main screen, after importing, you will immediately see a list of records retrieved from the Database by combining PHP and MySQL.

– Before import file:

Before import file

– List of data after importing

List of data after importing

Download full code: Download Size: 602 KB

Thank you for reading this article.