Перенос данных из excel файла в базу данных

Перенос данных из Excel в базу данных. Переносим данные из csv файла в БД

Всем привет. Иногда, для работы с БД, требуется перенос данных из файла в базу данных. Я столкнулся с такой проблемой, когда мне нужно было перенести данные из 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) – эта строка производит передачу данных в базу данных.

На этом всё!

Если у вас есть вопросы, пишите их в нашей группе – https://vk.com/progtime

Вы так же можете разместить свой вопрос на нашем форуме, где другие программисты смогут вам помочь в решение вашей задачи – https://vk.com/prog_time

В ней мы делимся своим опытом с другими начинающими программистами, поэтому обязательно ответим на ваш вопрос.

Так же прокачивайте свои навыки на нашем канале – https://www.youtube.com/c/ProgTime

Рекомендованные

 

Вам также будет интересно

 

 

Оставить комментарий

avatar
  Подписаться  
Уведомление о