- PHP Import Excel File into MySQL Database Tutorial
- Реализация быстрого импорта из Excel на PHP
- Что использовать в качестве инструмента?
- Наша боль, как разработчиков
- И тут нас отпустило.
- Полученные результаты производительности
- Import Excel File into MySQL Database using PHP
- What is inside?
- About this Excel import example
- File Structure
- Database script
- Creating UI to upload import template
- Parse Excel data using PHPSpreadsheet
PHP Import Excel File into MySQL Database Tutorial
In this post, i going to explain how to import excel file into mysql database using PHP. In this post i will give you full example of csv file, ods file, xls file, xlsx file import into database using phpexcel package.
We always require to add data from admin panel like products, items, users, emails etc. If we have few records and require to add manually then it is ok, But if we have more data on excel file or csv file then it take long time to store data, At that time we can use direct import xls file or csv file into mysql database.
So, In this tutorial i am using Spreadsheet_Excel_Reader class for import csv file into php database. So you can do it by following few step. In this tutorial you have to do just as bellow file:
1.Download Package
2.Create db_config.php file
3.Create index.php file
4.Create excelUpload.php
5.Create Upload Folder
So, you have to just follow few step, So let’s follow:
s
Step 1: Download Package
In first step we have to download PHP Excel Reader library from GitHub, So first let’s download from here :
Click Here to download PHP Excel
After download extract it to your root folder and rename it to «library».
Step 2: Create db_config.php file
In this step, we have to create db_config.php file for database configuration, In this file you have to set database host, database username, database password, database name. This file will use for store data into database.
So, let’s create db_config.php file and put bellow code:
db_config.php
$dbHost = «localhost»;
$dbDatabase = «h_php»;
$dbPasswrod = «root»;
$dbUser = «root»;
$mysqli = new mysqli($dbHost, $dbUser, $dbPasswrod, $dbDatabase);
?>
Step 3: Create index.php file
In this step i am going to create index.php file in your root directory, in this file i created simply form using bootstrap, this way you have to just feel this form and click to «Submit» button. You can import excel file from file input button.
So let’s copy bellow code and put it into index.php file.
Upload
Download Demo File from here : Demo.ods
Step 4: Create excelUpload.php file
You have to create excelUpload.php file that will manage for import data into database, In this step we have to create uploads folder for store excel file into this file and then read that file.
So, let’s create excelUpload.php file and put bellow code:
excelUpload.php
require(‘library/php-excel-reader/excel_reader2.php’);
require(‘library/SpreadsheetReader.php’);
require(‘db_config.php’);
if(isset($_POST[‘Submit’]))
$mimes = [‘application/vnd.ms-excel’,’text/xls’,’text/xlsx’,’application/vnd.oasis.opendocument.spreadsheet’];
if(in_array($_FILES[«file»][«type»],$mimes))
$uploadFilePath = ‘uploads/’.basename($_FILES[‘file’][‘name’]);
move_uploaded_file($_FILES[‘file’][‘tmp_name’], $uploadFilePath);
$Reader = new SpreadsheetReader($uploadFilePath);
$totalSheet = count($Reader->sheets());
echo «You have total «.$totalSheet.» sheets».
$html=»
| Title | Description |
|---|---|
| «.$title.» | «.$description.» |
«;
echo $html;
echo «
Data Inserted in dababase»;
>else <
die(«
Sorry, File type is not allowed. Only Excel file.»);
>
>
?>
Run PHP App:
All the required steps have been done, now you have to type the given below command and hit enter to run the PHP app:
Now, Go to your web browser, type the given URL and view the app output:
Реализация быстрого импорта из Excel на PHP
Мы продолжаем рассказывать о технологиях, используемых на нашем сервисе email-маркетинга Pechkin-mail.ru. Одной из ключевых задач любого сервиса, связанного с данными клиентов, является загрузка этих данных на сервис. Для Печкина очень важно быстро и без проблем для пользователя загружать адресные базы, содержащие email-адреса, имена, фамилии и другие дополнительные данные.
Что использовать в качестве инструмента?
- это стандартный инструмент, которым на базовом уровне владеют 100% пользователей компьютеров. Более того, в бизнесе — это де-факто корпоративный стандарт и используется даже, если на рабочих компьютерах Mac или Linux.
- Практически все CRM-, CMS-, облачные или десктопные системы имеют экспорт в Excel или CSV, который простым пересохранением приводится к формату XLS или XLSX.
- Известно также, что “90% ошибок ПО сидит в полуметре от монитора”. Не в обиду будет сказано рядовым пользователям, но мы должны учитывать самый базовый уровень подготовки и тех. поддержке для объяснения достаточно сказать “Загрузите Excel-файл”, а не объяснять процедуру подготовки файла в нужном формате.
Проблему пользователей при импорте адресных баз сняли. Но тут возникает уже проблема непосредственно разработки.
Наша боль, как разработчиков
- php-spreadsheetreader reads a variety of formats (.xls, .ods AND .csv)
- PHP-ExcelReader (xls only)
- PHP_Excel_Reader (xls only)
- PHP_Excel_Reader2 (xls only)
- XLS File Reader Коммерческая и xls only
- SimpleXLSX Из описания способен читать xlsx, однако, автор ссылается только на xls
- PHP Excel Explorer Коммерческая и xls only
Обратила на себя наше внимание библиотека PHPExcel. Ее мы использовали еще несколько лет назад в сервисе sms-рассылок SMS24X7.ru. Петя Соколов (Petr_Sokolov), наш талантливый разработчик, написал обертку для этой библиотеки, исправляющую ряд ее недостатков и багов.
Библиотека, безусловно, интересная и развитая. Но для Печкина ее использовать стало невозможно уже через пару лет, когда выросли и мы и наши клиенты — ее катастрофическая требовательность к ресурсам и огромное время парсинга файлов. Например, нередки случаи загрузки на сервис адресных баз > 100 000 строк со сложной структурой. А если файл уже 500 000 строк и “весит” больше 30Мб?
И тут нас отпустило.
В процессе поисков мы наткнулись на коммерческую библиотеку libxl, увидев результаты “кустарного benchmark” на Stackoverflow.
Библиотека написана на C++, а благодаря великолепному объектно-ориентированному расширению для PHP от Ilia Alshanetsky, легка в освоении и интеграции (например, переписать наше текущее решение с PHPExcel на LibXL заняло около 3 часов). Что очень классно, учитывая, что, к сожалению, документации от разработчика расширения нет и необходимо пользоваться расширением Reflection.
Процесс установки очень прост.
cd /usr/local/src/ wget http://libxl.com/download/libxl.tar.gz tar zxfv libxl.tar.gz cd libxl-3.5.4.1/ ln -s include_c include cd ../ wget https://github.com/iliaal/php_excel/archive/master.zip unzip master.zip cd php_excel-master/ ./configure --with-excel --with-libxl-libdir=../libxl-3.5.4.1/lib64 --with-libxl-incdir=../libxl-3.5.4.1/include_c make make test make installВ результате компиляции вы получите файл excel.so в папке /usr/lib/php5/20090626/. Теперь достаточно создать файл /etc/php5/conf.d/excel.ini с содержимым.
Проверим установился ли модуль и перезагрузим веб-сервер.
php -m | grep excel service lighttpd restart В коде все тоже очень просто. Подгружаете файл и читаете необходимые ячейки. Например, вот так:
$doc = new ExcelBook(); $doc->loadFile(‘example.xls’); for($r=$sheet->firstRow();$rlastRow();$r++) < for($c=$sheet->firstCol();$c lastCol();$c++)< echo ‘Строка: ’.$r.’ Столбец: ’.$c.’ Значение: ’.$sheet->read($r,$c).’
’; > > Полученные результаты производительности
Отсутствие потребности в оперативной памяти (в процессе загрузки файла и его чтения) приятно порадовало.
А вот и прирост скорости загрузки excel-файла и его чтения на различных размерах адресных баз.
Данные тесты проводились на xlsx-файлах с N подписчиками в один стоблец с email. Реальные же адресные базы еще больше и сложнее и преимущество в скорости и потреблении памяти выглядит еще значительнее.
Стоимость библиотеки 199$ за девелоперскую лицензию, но, поверьте, это того стоит. Безусловно рекомендуем всем, кто сталкивается с проблемой импорта Excel-файлов на свой сервис.
Import Excel File into MySQL Database using PHP
Database backup and restore is the most important part of maintaining software. Taking automatic periodical backup is a must for any project.
In case of unknown security flaws which may collapse your system. In such an unfortunate situation, it will be the holy grail to save you if you have a backup.
It is not only enough to take a backup by exporting your database. Also, we need to experiment with importing backups to ensure the restoration.
Backup files can be in any format. Many of the database clients support SQL, Excel or CSV format files to import external data.
We will create our own PHP database client only for importing Excel data into a database.
Restoring Excel backup into a database via programming will save us time. Let us study how to implement a quick restore by importing bulk data from Excel files. You may visit my earlier export data in CSV format if you want an export example.
This screenshot shows the output by displaying the list of imported rows from the database.
What is inside?
About this Excel import example
This example allows users to choose the import template via HTML form input. A sample Excel template is added with this downloadable resource.
With a valid file, the HTML form submitted will call PHP to process Excel parsing.
In PHP, the PHPSpreadsheet library is used to read the Excel file data and convert it into an array.
The code will execute the database insert row by row by iterating the array data.
File Structure
See the Excel import example’s file structure shown below. The vendor folder has the PHPSpreadsheet library and its dependencies.
I have given a sample import template with these files to experiment with the import operation.
The schema.sql has the script to create the target database. You can find the SQL script in the next section.
Database script
Import the following SQL script and create a database environment. Also, configure your database details in DataSource.php to make this example work.
-- -- Database: `import_excel` -- -- -------------------------------------------------------- -- -- Table structure for table `tbl_info` -- CREATE TABLE `tbl_info` ( `id` int(11) NOT NULL, `name` varchar(50) NOT NULL, `description` varchar(50) NOT NULL, `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); -- -- Indexes for table `tbl_info` -- ALTER TABLE `tbl_info` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT for table `tbl_info` -- ALTER TABLE `tbl_info` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;; Creating UI to upload import template
This HTML form with the file upload option is used to choose the Excel source. On submitting this form, the Excel file will be sent to the PHP to parse the data source. This file upload option will only allow the Excel files to choose using the accept attribute.
This code also contains the response HTML for displaying the message returned from PHP. This message is shown based on the type of response sent from PHP after Excel import.
Import Excel File into MySQL Database using PHP
?> select($sqlSelect); if (! empty($result)) < ?> Name Description ?>
?> Parse Excel data using PHPSpreadsheet
In this PHP code, I specified the array of allowed file types for the file-type validation in PHP. I validated the uploaded file type along with the array allowed type.
If a match is found, the code will execute other logic. Otherwise, it will return the error message to the user.
With successful validation, excel file parsing read and convert the data into an array. Each array index has an array of row data. After reading the non-empty row data, I ran the database insert and showed the response.
getConnection(); require_once ('vendor/autoload.php'); if (isset($_POST["import"])) < $allowedFileType = [ 'application/vnd.ms-excel', 'text/xls', 'text/xlsx', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ]; if (in_array($_FILES["file"]["type"], $allowedFileType)) < $targetPath = 'uploads/' . $_FILES['file']['name']; move_uploaded_file($_FILES['file']['tmp_name'], $targetPath); $Reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx(); $spreadSheet = $Reader->load($targetPath); $excelSheet = $spreadSheet->getActiveSheet(); $spreadSheetAry = $excelSheet->toArray(); $sheetCount = count($spreadSheetAry); for ($i = 1; $i $description = ""; if (isset($spreadSheetAry[$i][1])) < $description = mysqli_real_escape_string($conn, $spreadSheetAry[$i][1]); >if (! empty($name) || ! empty($description)) < $query = "insert into tbl_info(name,description) values(. )"; $paramType = "ss"; $paramArray = array( $name, $description ); $insertId = $db->insert($query, $paramType, $paramArray); // $query = "insert into tbl_info(name,description) values('" . $name . "','" . $description . "')"; // $result = mysqli_query($conn, $query); if (! empty($insertId)) < $type = "success"; $message = "Excel Data Imported into the Database"; >else < $type = "error"; $message = "Problem in Importing Excel Data"; >> > > else < $type = "error"; $message = "Invalid File Type. Upload Excel File."; >> ?>
Note: Download the PHPSpreadsheet and include it in the vendor directory.