Всем привет. Иногда, для работы с БД, требуется перенос данных из файла в базу данных. Я столкнулся с такой проблемой, когда мне нужно было перенести данные из Excel файла в базу данных. Есть несколько способов решения этой проблемы, сегодня мы с вами решим ее с помощью библиотеки PHPExcel. Я в качестве примера буду использовать PostgreSQL, но здесь так же можно использовать и другие СУБД. Я вам покажу код который можно отредактировать, чтобы подключиться к MySQL, MariaDB и так далее.
И так, суть работы такая. Есть Excel файл, который нужно перевести в формат csv(это можно сделать в настройках сохранение файла). После этого мы достаем данные из этого файла, обрабатываем их и вставляем в базу данных.
Для вашего случая скрипт, может отличаться, поэтому вам нужно будет его изменить самостоятельно. Так же вы можете написать в нашей группе https://vk.com/prog_time на стене и вам обязательно помогут.
Скажу сразу, данный код не подойдет для вас, если:
- У вас очень большая база, больше 10 000 записей. Если у вас большая база, то процесс будет очень долгим и может выдать ошибку.
- Если вам необходима постоянная загрузка строк в реальном времени. Скорость работы этого скрипта, хромает и большая нагрузка может его тормозить.
Почему тогда я его показываю:
- Это готовый код, который вы можете просто скопировать и вставить к себе.
- Здесь необходимо только изменить путь к вашему файлу в параметре функции.
- С помощью этого кода, вы можете потренироваться в работе с библиотекой PHPExcel)))
Для того чтобы все работало, необходимо скачать библиотеку PHPExcel — https://github.com/PHPOffice/PHPExcel
Вот так выглядит скрипт. Данный скрипт написан на PHP. Сейчас мы с вами его построчно разберем.
//СКРИПТ КОТОРЫЙ ПЕРЕВОДИТ ЗНАЧЕНИЯ ИЗ EXCEL В МАССИВ
set_time_limit(7200);
ini_set("memory_limit", "3000M");
function parse_excel_file( $filename ){
// путь к библиотеки от корня сайта
require_once $_SERVER['DOCUMENT_ROOT'].'/PHPExcel-1.8/Classes/PHPExcel.php';
$result = array();
// получаем тип файла (xls, xlsx), чтобы правильно его обработать
$file_type = PHPExcel_IOFactory::identify( $filename );
// создаем объект для чтения
$objReader = PHPExcel_IOFactory::createReader( $file_type );
$objPHPExcel = $objReader->load( $filename ); // загружаем данные файла
$result = $objPHPExcel->getActiveSheet()->toArray(); // выгружаем данные
return $result;
}
$res = parse_excel_file($_SERVER['DOCUMENT_ROOT'].'\files\list_of_expired_passports.csv' );
$arr_result = count($res); //ПОДСЧИТЫВАЕТ СКОЛЬКО ЗНАЧЕНИЙ В МАССИВЕ
for ($i=0; $i<$arr_result; $i++) {
$arr = [implode(',', $res[$i])];
//print_r($arr);
$arr_str = implode('', $arr);
$connect_string = "host=localhost port=5432 dbname=postgres user=postgres password=";
$dbconnect = pg_connect($connect_string);
$query = "INSERT INTO test_db (id, pasport) VALUES ($i, '$arr_str') ";
$result = pg_query($dbconnect, $query);
pg_close($dbconnect);
}
set_time_limit(7200); — эта строка задает время, после которого скрипт перестанет работать. Это сделано для того чтобы скрипт в случае зависания смог самостоятельно окончить свою работу, по истечению указанного времени. Здесь я указываю число, равное 2 часам.
ini_set("memory_limit", "3000M"); — эта строка задет лимит оперативной памяти, который может быть задействован на работу скрипта.
Эти две функции не обязательны, но и не мешаю работе компьютера. Вы можете не бояться за оперативную память, ведь если файл не большого размера, то работа скрипта будет быстрой и не нагрузит систему.
Далее идет функция parse_excel_file, которая подключает библиотеку PHPExcel и обрабатывает файл который указан в ее параметре $filename.
В этой функции в первой строке находится следующий код
require_once $_SERVER['DOCUMENT_ROOT'].'/PHPExcel-1.8/Classes/PHPExcel.php';
Этот код указывает путь к папке в которой находятся файлы библиотеки. $_SERVER['DOCUMENT_ROOT']. — эта часть строки передает данные пути до файла домена, то есть в моем случае(я использую локальный хостинг OpenServer) это D:\OSPanel\domains\.
$result = array(); — здесь создается переменная и в неё записывается массив.
Здесь $file_type = PHPExcel_IOFactory::identify( $filename ); функция указывает тип файла, который передается. То есть если вы распечатаете эту функцию через var_dump(), то вы увидите строку CSV, это наш формат.
Далее идут строки, в которых подключается код из класса указанного на стороне библиотеки. Это нам не особо интересно, так как мы тут не можем не чего изменить.
А вот последняя строка в этой функции куда интереснее, здесь с помощью встроенных, в библиотеку методов класса, мы преобразуем нашу информацию в массив.
После создания функции, нам необходимо ее передать и записать результат ее выполнения в переменную, которую я назвал $res. Это мы делаем с помощью этой строки$res = parse_excel_file($_SERVER['DOCUMENT_ROOT'].'\files\list_of_expired_passports.csv' );. В качестве параметра мы передаем путь к нашему Excel файлу ($_SERVER[‘DOCUMENT_ROOT’].’\files\list_of_expired_passports.csv’ ).
Здесь мы сделаем небольшую паузу и посмотрим что у нас получилось. Если мы попробуем просмотреть, с помощью var_dump переменную $res, то мы увидим массив в котором находится много других массивов.
Я столкнулся с этим кодом, когда у меня был запрос на перенос в базу паспортов. Через запятую были записаны серия и номер (1234,174517). И когда я прогнал файл через этот код, то у меня на этой стадии получился вот такой массив — Array{Array{[0]=>»1234″[1]=>»174517″},Array{и так далее}}.
И так теперь давайте пойдем далее.
Следующая строка $arr_result = count($res); подсчитывает сколько элементов в $res.
И вот мы подошли к самому интересному… цикл for.
Здесь, в скобках мы передаем следующие условия работы цикла $i=0; $i<$arr_result; $i++.
$arr = [implode(',', $res[$i])] — здесь я создал переменную в которой преобразовал массив, а точнее значения в нем. До этого момента массив выглядел вот так Array{Array{[0]=>»1234″[1]=>»174517″}…}, а теперь массив выглядит так Array{Array{[0]=>»1234,174517″}.
$arr_str = implode('', $arr); — эта строка преобразует массив в строку. Теперь у нас на этом моменте получается, что есть строка вот такого вида 1234,174517. Я бы мог занести данные из массива в базу данных, как они есть, но мне нужно было чтобы они имели тип строки, чтобы в дальнейшем было удобнее их обрезать.
Следующие строки могут отличаться от ваших, в зависимости от того, какой базой данных вы пользуетесь. В данном случае здесь используется база PostgreSQL. По сути здесь простые функции подключения и работы с базой данных, которые вы легко сможете найти для своей базы данных.
Если у вас появились трудности с этой задачей, вы можете задать свой вопрос в нашей группе https://vk.com/prog_time , где вам обязательно ответят.
Если использовать, как и я PostgreSQL, то эта строчка $connect_string = "host=localhost port=5432 dbname=postgres user=postgres password="передает параметры для подключения к базе. Здесь вписываются следующие данные: сервер для подключения — localhost, порт — 5432, имя базы, которое вы предварительно создали — postgres, имя пользователя, у меня оно такое же как и у базы — postgres, пароль, у меня без пароля.
$dbconnect = pg_connect($connect_string) — здесь происходит подключение к базе, по параметрам которые были указанны выше.
$query = "INSERT INTO test_db (id, pasport) VALUES ($i, '$arr_str')" — в переменную query я записываю, запрос о внесение изменений в базу. Данные будут вноситься в таблицу test_db. В столбцы id и pasport будут вноситься данные из переменных i и arr_str.
$result = pg_query($dbconnect, $query) — эта строка производит передачу данных в базу данных.
