Как из excel сделать базу данных и залить на phpmyadmin?

Главная — MySQL — Экспорт и импорт базы данных в PHPMyAdmin

Добрый день друзья, сегодня мы с Вами научимся делать экспорт и импорт базы данных в PHPMyAdmin. Для чего это нужно, можете задать вопрос Вы. Прежде всего экспорт базы данных необходимо делать периодически для того чтобы в экстренных ситуациях Вы не потеряли важную для Вас информацию.

Экспорт будет представлять собой небольшой файл, в котором будет храниться вся информация о БД. Для экспорта БД необходимо зайти в PHPMyAdmin и нажать на интересующую Вас БД. После этого вы увидите все находящиеся в ней таблицы и, не заходя в них, кликните на кнопку меню под названием экспорт.

Перед Вами появится следующая страница:

Я советую Вам выбрать быстрый способ экспорта, а также в формате указать SQL. После этого вы можете нажать ок. Перед Вами появится окно, в котором Вам предложат сохранить файл.

Вы сохраняете файл в нужном для Вас месте, главное помните, куда Вы его сохранили, ведь он очень важен для нас. Что касается обычного способа экспорта. Вы можете также его использовать, если Вам это необходимо, там присутствует множество дополнительных настроек, которые Вы можете задать при экспорте.

Например, выбрать нужные таблицы из БД, указать кодировку, и многое другое. Если Вам интересна данная настройка, Вы можете ее посмотреть. Но мы с Вами не будем углубляться в данную настройку. После того как вы сохраните файл у себя на компьютере я попрошу Вас удалить БД. Как это сделать я не буду Вам объяснять, т.к., мы уже это проходили.

Не бойтесь удалять, мы все вернем с Вами на свои места.

Настало время заняться импортом базы данных. Переходим в меню импорт.

Выбираем обзор вашего компьютера, и указываем путь к нашему файлу. И нажимаем ок. Как видите, перед Вами появилась ошибка.

Не пугайтесь, все дело в том, что мы с Вами импортировали не саму базу данных, а лишь все ее таблицы. Поэтому создайте вначале БД, зайдите в нее и нажмите кнопку импорт, проделав все указанное выше.

Нажав кнопку ок, у Вас все получится, и если Вы все сделали правильно, ошибки появиться не должно.

Как видите, наша таблица вновь появилась на своем месте, а также все данные в ней были сохранены. Теперь Вы понимаете, какая замечательная возможность экспорт и импорт базы данных в PHPMyAdmin. Ведь если Вы потеряете в один день все ваши наработки за долгие годы, благодаря данному файлу Вы сможете все вернуть. На этом я с Вами прощаюсь до скорого.

И снова я приветствую Вас в очередной теме посвященной языку JavaScript, в которой мы разберем методы alert, prompt, confrim.

Всем привет, сегодня мы рассмотрим с Вами конструкцию switch-case в языке JavaScript.

Здравствуйте дорогие друзья, сегодня мы с Вами изучим еще одну тему посвященную языку CSS. И научимся реализовывать всплывающие подсказки на CSS.

Сегодня мы рассмотрим еще одну возможность, которую предоставляет нам CSS3, а именно поговорим о псевдоклассе target, для чего он нам может быть нужен, и рассмотрим один из самых популярных способов его применения.

Довольно часто Вы видите на различных ресурсах видео, которое хранится на сервисе youtube, но каким-то образом его можно воспроизвести на данном сайте. Об этом сегодня пойдет речь, и я расскажу Вам, как вставить видео с YouTube.

Источник: http://dwebbers.ru/article?id=103

Экспорт из Excel в MySQL

От автора: в этом уроке мы рассмотрим экспорт из Excel в MySQL на PHP. В одном из уроков нашего сайта, мы с Вами изучали библиотеку PHPExcel, которая используется для работы с таблицами Microsoft Excel, используя язык PHP.

При этом на примере создания прайс-листа для сайта, мы научились переносить данные из СУБД MySql в данные таблицы. Но очень часто при разработке сайтов, необходимо выполнить обратную задачу – выгрузить данные из таблиц Microsoft Excel в базу данных СУБД MySql.

Поэтому в данном уроке мы займемся решением данной задачи.

Установка библиотеки PHPExcel

Для начала хотел бы обратить Ваше внимание на то, что данный урок – это своего рода продолжение урока Генерация прайс-листа в формате Excel при помощи PHP. Phpexcel, который публиковался ранее.

При этом если Вы не знакомы с библиотекой PHPExcel и с основами работы с ней, то настоятельно рекомендую просмотреть вышеуказанный урок, так как сегодня мы не будем тратить время на изучение основ. Для данного урока мы будем использовать тестовый сайт, который написан с использованием объектно-ориентированного подхода и шаблона проектирования MVC.

В функционале данного сайта описан механизм загрузки на сервер файлов формата Excel и соответственно, данный механизм нам необходимо доработать, а именно, после успешной загрузки файла на сервер, необходимо прочитать содержимое данного файла и перенести данные из необходимых столбцов таблицы Excel, в базу данных MySql.

• Бесплатный курс по PHP программированию
• Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC
• В курсе 39 уроков | 15 часов видео | исходники для каждого урока

Получить курс сейчас!

Теперь давайте установим библиотеку PHPExcel. Для этого мы воспользуемся инструментом Composer, (для тех кто не знаком с данным инструментов, рекомендую посмотреть урок Введение в Composer, поэтому открываем командную строку, переходим в каталог с тестовым сайтом и выполняем следующую команду:

composer require phpoffice/phpexcel

composer require phpoffice/phpexcel

После установки библиотеки, в корневом каталоге тестового сайта, была создана папка vendor. В данной папке расположен файл autoload.php, который необходимо обязательно подключить в файле index.php (файл autoload.php содержит инструкции по подключению необходимых классов библиотеки).

require_once «vendor/autoload.php»;

require_once «vendor/autoload.php»;

Подготовка к работе

Для данного урока нам потребуется база данных MySql, в которую мы будем осуществлять выгрузку данных. Структура таблицы main (в данную таблицу мы будем выгружать данные), тестовой базы данных:

При этом имена полей таблицы main, совпадают с заголовками столбцов документа Excel. Так как формировать SQl запрос по вставке данных, мы будем динамически и при этом, из таблицы Excel мы будем выгружать данные только из определенных столбцов, имена которых представлены в конфигурационном файле.

class Config {

public $cells = array(
'D'=>'order',
'F'=>'date port',
'B'=>'customer',
'G'=>'country',
'J'=>'products',
'K'=>'grade',
'U'=>'date',
'M'=>'size',
'P'=>'length',
'Q'=>'quantity',
'U'=>'date',
'V'=>'gruzo',

);
}

То есть, как Вы видите в свойстве $cells класса Config, содержится массив с именами столбцов, таблицы Excel, данные которых подлежат выгрузке в базу данных.

При этом в качестве ключей используются имена столбцов, а в качестве значений — заголовки каждого столбца и соответственно заголовки столбцов одноименны с полями таблицы main, базы данных.

Таким образом, добавляя или удаляя ячейки из данного массива, мы указываем какие данные необходимо загрузить из таблицы Excel.

Перенос данных из Excel в MySql

В тестовом сайте, за загрузку файла на сервер отвечает метод export(), класса Controller, при этом на данном этапе код данного метода, следующий:

public function export() {
if(!empty($_FILES['xls']['tmp_name'])) {
$file = $this->uploadFile($_FILES);

}
}

public function export() {    if(!empty($_FILES['xls']['tmp_name'])) {      $file = $this->uploadFile($_FILES);

Обратите внимание, что после успешной загрузки файла на сервер – возвращается имя загруженного файла, для дальнейшей работы. Поэтому вызовем на исполнение метод xlsToMysql($file), который выполнит перенос данных из таблицы Excel в базу данных MySql:

public function export() {
if(!empty($_FILES['xls']['tmp_name'])) {
$file = $this->uploadFile($_FILES);

if($this->xlsToMysql($file)) {
header('Location:index.php');
}
}
}

public function export() {    if(!empty($_FILES['xls']['tmp_name'])) {      $file = $this->uploadFile($_FILES);      if($this->xlsToMysql($file)) {        header('Location:index.php');

1. Бесплатный курс по PHP программированию
2. Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC
3. В курсе 39 уроков | 15 часов видео | исходники для каждого урока

Получить курс сейчас!

Код метода xlsToMysql($file), для начала, разберем по порядку, а затем я приведу его в полном виде. То есть дальнейшие пояснения относятся к коду будущего метода. Первым делом получаем объект модели и объект класса PHPExcel:

$this->model = $this->getModel();
$this->xls = $this->getPhpExcel($file);

$this->model = $this->getModel();$this->xls = $this->getPhpExcel($file);

Метод getModel() – не представляет интереса так как возвращает объект модели тестового сайта.

Метод getPhpExcel($file), возвращает объект класса PHPExcel, для файла, имя которого содержится в переменной $file – то есть это имя — только что загруженного файла в систему.

Другими словами мы загружаем файл с таблицей Excel, для дальнейших манипуляций, при этом возвращается объект, который мы будем использовать. Код метода getPhpExcel($file):

public function getPhpExcel($file) {
return PHPExcel_IOFactory::load($file);
}

public function getPhpExcel($file) {    return PHPExcel_IOFactory::load($file);

Далее продолжаем описывать код метода xlsToMysql($file) и устанавливаем активный лист таблицы Excel с индексом 0 (напомню, что индексация листов Excel начинается с нуля) и получаем объект активного листа с данными:

$this->xls->setActiveSheetIndex(0);
$sheet = $this->xls->getActiveSheet();

$this->xls->setActiveSheetIndex(0);$sheet = $this->xls->getActiveSheet();

Далее используя метод getRowIterator(), объекта активного листа – рекурсивно обходим все строки таблицы Excel и формируем объект с полученными данными:

$rowIterator = $sheet->getRowIterator();

$rowIterator = $sheet->getRowIterator();

Используя цикл foreach() мы можем пройтись по объекту $rowIterator и получить доступ к каждой строке таблицы в отдельности:

foreach($rowIterator as $row) {
if($row->getRowIndex() != 1) {
$cellIterator = $row->getCellIterator();
foreach($cellIterator as $cell) {
$cellPath = $cell->getColumn();
if(isset($this->config->cells[$cellPath])) {
$arr[$row->getRowIndex()][$this->config->cells[$cellPath]] = $cell->getCalculatedValue();
}
}
}
}

foreach($rowIterator as $row) {      if($row->getRowIndex() != 1) {        $cellIterator = $row->getCellIterator();        foreach($cellIterator as $cell) {          $cellPath = $cell->getColumn();          if(isset($this->config->cells[$cellPath])) {            $arr[$row->getRowIndex()][$this->config->cells[$cellPath]] = $cell->getCalculatedValue();

При этом на каждой итерации цикла в переменную $row, попадает объект одной строки таблицы Excel. Для определения индекса каждой строки, используем метод getRowIndex(). Далее, если на текущей итерации индекс строки не равен 1, так как в первой строке располагается шапка таблицы, значит, рекурсивно обходим все ячейки текущей строки, используя метод getCellIterator().

А затем, аналогично проходимся в цикле foreach(), по полученному объекту, получая, тем самым, доступ к каждой ячейке в отдельности. Для получения имени столбца, для текущей ячейки – используем метод getColumn(). При этом, если имя ячейки, присутствует в массиве свойства cells, объекта Config, значит, получаем данные текущей ячейки, используя метод getCalculatedValue().

Хотел бы обратить Ваше внимание, на следующее – если в некой ячейке таблицы содержится дата, то для преобразования данной даты в формат языка PHP, необходимо использовать статический метод ExcelToPHP($cell->getCalculatedValue()) класса PHPExcel_Shared_Date, который вернет дату из ячейки таблицы Excel, в формате timestamp. Полный код метода xlsToMysql():

protected function xlsToMysql ($file) {

$this->model = $this->getModel();
$this->xls = $this->getPhpExcel($file);

$this->xls->setActiveSheetIndex(0);
$sheet = $this->xls->getActiveSheet();

$rowIterator = $sheet->getRowIterator();

$arr = array();
foreach ($rowIterator as $row) {

if($row->getRowIndex() != 1) {

$cellIterator = $row->getCellIterator();
foreach ($cellIterator as $cell) {
$cellPath = $cell->getColumn();
if(isset($this->config->cells[$cellPath])) {

if($cellPath == 'U') {
if($cell->getCalculatedValue() == '00.00.0000' || $cell->getCalculatedValue() == '') {
$t = '0000-00-00';
}
else {
$t = date( 'Y-m-d', PHPExcel_Shared_Date::ExcelToPHP( $cell->getCalculatedValue() ) );
}

$arr[$row->getRowIndex()][$this->config->cells[$cellPath]] =$t;
continue;
}

if($cellPath == 'F') {
if($cell->getCalculatedValue() == '00.00.0000' || $cell->getCalculatedValue() == '') {
$t = '0000-00-00';
}
else {
$t = date( 'Y-m-d', PHPExcel_Shared_Date::ExcelToPHP( $cell->getCalculatedValue() ) );
}

$arr[$row->getRowIndex()][$this->config->cells[$cellPath]] =$t;
continue;
}

$arr[$row->getRowIndex()][$this->config->cells[$cellPath]] = $cell->getCalculatedValue();
}
}

}

}

$res = $this->model->inserExcel($arr);
if(!$res) {
exit();
}
return TRUE;
}

protected function xlsToMysql ($file) {    $this->model = $this->getModel();    $this->xls = $this->getPhpExcel($file);    $this->xls->setActiveSheetIndex(0);    $sheet = $this->xls->getActiveSheet();    $rowIterator = $sheet->getRowIterator();    foreach ($rowIterator as $row) {      if($row->getRowIndex() != 1) {        $cellIterator = $row->getCellIterator();        foreach ($cellIterator as $cell) {          $cellPath = $cell->getColumn();          if(isset($this->config->cells[$cellPath])) {              if($cell->getCalculatedValue() == '00.00.0000' || $cell->getCalculatedValue() == '') {              $t = date( 'Y-m-d', PHPExcel_Shared_Date::ExcelToPHP( $cell->getCalculatedValue() ) );              $arr[$row->getRowIndex()][$this->config->cells[$cellPath]] =$t;              if($cell->getCalculatedValue() == '00.00.0000' || $cell->getCalculatedValue() == '') {              $t = date( 'Y-m-d', PHPExcel_Shared_Date::ExcelToPHP( $cell->getCalculatedValue() ) );              $arr[$row->getRowIndex()][$this->config->cells[$cellPath]] =$t;            $arr[$row->getRowIndex()][$this->config->cells[$cellPath]] = $cell->getCalculatedValue();    $res = $this->model->inserExcel($arr);

В итоге, работы данного метода, мы получаем следующий массив данных:

То есть в каждой ячейке данного массива, содержится массив с данными по отдельной строке таблицы Excel. Соответственно данный массив передаем методу модели insertExcel(), который сформирует SQL запрос для вставки данных в таблицу main базы данных MySql. Код метода модели insertExcel():

public function insertExcel($arr) {

$fields = '';

foreach($arr[2] as $key => $cell) {
$fields .= '`'.$key.'`'.',';
}
$fields = trim($fields,',');

$str = '';
// INSERT INTO `main` («,«,«..) VALUES ('','','',),(),(),();
foreach($arr as $item) {
$str .= «(«;
foreach($item as $cell) {
$str .= «'».$this->db->real_escape_string($cell).»',»;
}
$str = trim($str,»,»);
$str .= «),»;
}
$str = trim($str,»,»);
$query = «INSERT INTO `main` («.$fields.») VALUES «.$str;

$result = $this->db->query($query);
if($result) {
return TRUE;
}

}

public function insertExcel($arr) {    foreach($arr[2] as $key => $cell) {      $fields .= '`'.$key.'`'.',';    $fields = trim($fields,',');    // INSERT INTO `main` («,«,«..) VALUES ('','','',),(),(),();      foreach($item as $cell) {         $str .= «'».$this->db->real_escape_string($cell).»',»;    $query = «INSERT INTO `main` («.$fields.») VALUES «.$str;    $result = $this->db->query($query);

Данный метод формирует SQL запрос вида INSERT INTO main (field1,field2,field3..) VALUES (‘data1′,’data2′,’data3′,),(),(),(); — для добавления всех данных из переданного массива в таблицу main базы данных. Соответственно после выполнения данного SQL запроса – информация из таблиц Microsoft Excel будет перенесена в базу данных MySql.

На этом данный урок завершен. Всего Вам доброго и удачного кодирования!

• Бесплатный курс по PHP программированию
• Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC
• В курсе 39 уроков | 15 часов видео | исходники для каждого урока

Получить курс сейчас!

Источник: https://webformyself.com/eksport-iz-excel-v-mysql/

Импорт базы данных в PHPMyAdmin

Вы здесь: Главная — MySQL — MySQL Основы — Импорт базы данных в PHPMyAdmin

Когда Вы только начинаете создавать сайт, Вы его делаете, как правило, на локальном сервере. Когда он будет готов, его нужно будет перемещать на удалённый сервер. Скопировать файлы — это несложно, а вот как импортировать базу данных на удалённый сервер? Как раз о том, как сделать импорт базы данных в PHPMyAdmin, я Вам и объясню в этой статье.

Существует много способов импорта базы данных, однако, я Вам расскажу самый, на мой взглд, простой, и которым пользуюсь сам.

Шаг 1

Первым делом нужно экспортировать базу данных с текущего местонахождения (в частности, локального сервера). Наша цель получить SQL-запрос нашей базы данных. Для этого надо сделать следующее:

1. Зайти на главную страницу PHPMyAdmin.
2. Выбрать базу данных, которую Вы хотите экспортировать:
3. В верхнем меню выбрать пункт «Экспорт«:
4. На открывшейся странице поставьте радио-переключатель на «SQL«, затем нажмите на кнопку «Пошёл«:
5. На следующей странице Вам надо выделить весь SQL-код, который будет сгенерирован, и скопировать его в буфер обмена:

Шаг 2

Вторым и последним шагом является выполнение SQL-запроса, который Вы скопировали, в PHPMyAdmin, который находится на сервере, куда Вам надо импортировать базу данных. Для этого выполните следующие действия:

1. Зайти на главную страницу PHPMyAdmin.
2. Выбрать базу данных, в которую Вы хотите импортировать Ваш SQL-запрос. Обратите внимание, что базу данных предварительно необходимо создать:
3. В верхнем меню выбрать пункт «SQL«:
4. Вставить SQL-запрос из буфера обмена в текстовое поле и нажать на кнопку «Пошёл«:

В результате, все Ваши таблицы со всеми записями будут созданы на новом сервере.

Как видите, процесс экспортирования и импортирования базы данных в PHPMyAdmin упрощён до минимума, поэтому проблем с этим не будет.

Напоследок, хочется дать Вам ещё один совет. Дело в том, что очень часто бывает ситуация, когда нужно импортировать не всю базу данных, а, например, только одну таблицу.

Тогда принцип абсолютно тот же самый, только при экспортировании Вам надо выбрать не только базу данных, но и таблицу для экспорта. А затем вновь в верхнем меню нажать на «Экспорт«.

Дальше всё аналогично импорту баз данных.

Подробная работа с phpMyAdmin в новой версии разобрана в курсе «PHP и MySQL с Нуля до Гуру«: http://srs.myrusakov.ru/php

Предыдущая статья Следующая статья

Копирование материалов разрешается только с указанием автора (Михаил Русаков) и индексируемой прямой ссылкой на сайт (http://myrusakov.ru)!

Добавляйтесь ко мне в друзья ВКонтакте: http://vk.com/myrusakov.Если Вы хотите дать оценку мне и моей работе, то напишите её в моей группе: http://vk.com/rusakovmy.

Если Вы не хотите пропустить новые материалы на сайте,то Вы можете подписаться на обновления: Подписаться на обновления

Если у Вас остались какие-либо вопросы, либо у Вас есть желание высказаться по поводу этой статьи, то Вы можете оставить свой комментарий внизу страницы.

Если Вам понравился сайт, то разместите ссылку на него (у себя на сайте, на форуме, в контакте):

1. Кнопка:

   Она выглядит вот так:

2. Текстовая ссылка:Как создать свой сайт

   Она выглядит вот так: Как создать свой сайт

3. BB-код ссылки для форумов (например, можете поставить её в подписи): [URL=»https://myrusakov.ru»]Как создать свой сайт[/URL]

Источник: https://MyRusakov.ru/phpmyadmin-importexport.html

Из Excel в MySQL. Небольшая функция на PHP (fixed)

Здравствуй, $habrauser!

Бывает так, что вам нужно импортировать файл Excel в базу MySQL, но готового решения нигде нет. Вот и я, когда меня попросил друг поискать легкий способ импорта, сперва решил загуглить поискать решение.

Увы, запрос php excel to mysql не дал ничего конкретного, или же описанные способы были довольно таки не удобны. Тогда же я решил найти библиотеку для работы с Excel на PHP, и мне попалась PHPExcel.

Но опять же меня ждало разочарование, запрос phpexcel to mysql не дал ничего путного (я ленивый пользователь и дальше 1й страницы не хожу). В итоге я решил создать свой велосипед скрипт, которым и хочу поделиться с вами.

Начало

Итак, библиотеку я нашел, скачал и начал разбираться. Для начала нужно было подключить библиотеку и создать подключение к базе, что совсем не сложно: require_once «PHPExcel.

php»;

$connection = new mysqli(«localhost», «user», «pass», «base»);
$connection->set_charset(«utf8»);
Далее нужно открыть файл Excel для чтения:$PHPExcel_file = PHPExcel_IOFactory::load(«./file.

xlsx»);
После открытия файла, нам нужно перебрать все листы в нем и каждый добавить в базу MySQL (можно и 1 конкретный, но об этом позже):foreach ($PHPExcel_file->getWorksheetIterator() as $worksheet) {
// …
}
Ну а теперь самое интересное…

Перебор и добавление

Мы будем исходить из того, что таблицы у нас нет (или есть, но с другими данными) и ее нужно создать.

Для этого нам нужно получить имена для столбцов (в соответствии с просьбой друга, имена могут находиться в 1 строчке таблицы):// Строка для названий столбцов таблицы MySQL
$columns_str = «»;
// Количество столбцов на листе Excel
$columns_count = PHPExcel_Cell::columnIndexFromString($worksheet->getHighestColumn());

// Перебираем столбцы листа Excel и генерируем строку с именами через запятую
for ($column = 0; $column < $columns_count; $column++) { $columns_str .= ($columns_name_on1line ? "column" . $column : $worksheet->getCellByColumnAndRow($column, 1)->getCalculatedValue()) . «,»;
}

// Обрезаем строку, убирая запятую в конце
$columns_str = substr($columns_str, 0, -1);
Далее удаляем таблицу из базы, если она существовала, и создаем новую:$connection->query(«DROP TABLE IF EXISTS exceltable»);
$connection->query(«CREATE TABLE exceltable (» . str_replace(«,», » TEXT NOT NULL,», $columns_str) . » TEXT NOT NULL)»);
Как видно из кода, значения будут иметь тип TEXT. Теперь приступаем собственно к перебору ячеек и добавления их в базу. Конечно, такой алгоритм не сложно найти на просторах Stack Overflow, однако было замечено, что происходить ошибка при попытки чтения объединенных ячеек (точнее несоответствие количества столбцов и значений в запросе). Это я и решил учесть: // Количество строк на листе Excel
$rows_count = $worksheet->getHighestRow();

// Перебираем строки листа Excel
for ($row = 1; $row getCellByColumnAndRow($column, $row);

// Перебираем массив объединенных ячеек листа Excel
foreach ($worksheet->getMergeCells() as $mergedCells) {
// Если текущая ячейка — объединенная,
if ($cell->isInRange($mergedCells)) {
// то вычисляем значение первой объединенной ячейки, и используем её в качестве значения
// текущей ячейки
$merged_value = $worksheet->getCell(explode(«:», $mergedCells)[0])->getCalculatedValue();
break;
}
}

// Проверяем, что ячейка не объединенная: если нет, то берем ее значение, иначе значение первой
// объединенной ячейки
$value_str .= «'» . (strlen($merged_value) == 0 ? $cell->getCalculatedValue() : $merged_value) . «',»;
}

// Обрезаем строку, убирая запятую в конце
$value_str = substr($value_str, 0, -1);

// Добавляем строку в таблицу MySQL
$connection->query(«INSERT INTO exceltable (» . $columns_str . «) VALUES (» . $value_str . «)»);
}

Все дело в функцию!

Конечно, данный скрипт был бы гораздо удобнее, если бы все объединить в функцию. Поэтому итоговый результат получается такой:Функция excel2mysql// Подключаем библиотеку
require_once «PHPExcel.php»;

// Функция преобразования листа Excel в таблицу MySQL, с учетом объединенных строк и столбцов.
// Значения берутся уже вычисленными.

Параметры:
// $worksheet — лист Excel
// $connection — соединение с MySQL (mysqli)
// $table_name — имя таблицы MySQL
// $columns_name_line — строка с именами столбцов таблицы MySQL (0 — имена типа column + n)
function excel2mysql($worksheet, $connection, $table_name, $columns_name_line = 0) {
// Проверяем соединение с MySQL
if (!$connection->connect_error) {
// Строка для названий столбцов таблицы MySQL
$columns_str = «»;
// Количество столбцов на листе Excel
$columns_count = PHPExcel_Cell::columnIndexFromString($worksheet->getHighestColumn());

// Перебираем столбцы листа Excel и генерируем строку с именами через запятую
for ($column = 0; $column < $columns_count; $column++) { $columns_str .= ($columns_name_line == 0 ? "column" . $column : $worksheet->getCellByColumnAndRow($column, $columns_name_line)->getCalculatedValue()) . «,»;
}

// Обрезаем строку, убирая запятую в конце
$columns_str = substr($columns_str, 0, -1);

// Удаляем таблицу MySQL, если она существовала
if ($connection->query(«DROP TABLE IF EXISTS » . $table_name)) {
// Создаем таблицу MySQL
if ($connection->query(«CREATE TABLE » . $table_name . » (» . str_replace(«,», » TEXT NOT NULL,», $columns_str) . » TEXT NOT NULL)»)) {
// Количество строк на листе Excel
$rows_count = $worksheet->getHighestRow();

// Перебираем строки листа Excel
for ($row = $columns_name_line + 1; $row getCellByColumnAndRow($column, $row);

// Перебираем массив объединенных ячеек листа Excel
foreach ($worksheet->getMergeCells() as $mergedCells) {
// Если текущая ячейка — объединенная,
if ($cell->isInRange($mergedCells)) {
// то вычисляем значение первой объединенной ячейки, и используем её в качестве значения
// текущей ячейки
$merged_value = $worksheet->getCell(explode(«:», $mergedCells)[0])->getCalculatedValue();
break;
}
}

// Проверяем, что ячейка не объединенная: если нет, то берем ее значение, иначе значение первой
// объединенной ячейки
$value_str .= «'» . (strlen($merged_value) == 0 ? $cell->getCalculatedValue() : $merged_value) . «',»;
}

// Обрезаем строку, убирая запятую в конце
$value_str = substr($value_str, 0, -1);

// Добавляем строку в таблицу MySQL
$connection->query(«INSERT INTO » . $table_name . » (» . $columns_str . «) VALUES (» . $value_str . «)»);
}
} else {
return false;
}
} else {
return false;
}
} else {
return false;
}

return true;
}

// Соединение с базой MySQL
$connection = new mysqli(«localhost», «user», «pass», «base»);
// Выбираем кодировку UTF-8
$connection->set_charset(«utf8»);

// Загружаем файл Excel
$PHPExcel_file = PHPExcel_IOFactory::load(«./file.xlsx»);

// Преобразуем первый лист Excel в таблицу MySQL
$PHPExcel_file->setActiveSheetIndex(0);
echo excel2mysql($PHPExcel_file->getActiveSheet(), $connection, «excel2mysql0», 1) ? «OK
» : «FAIL
«;

// Перебираем все листы Excel и преобразуем в таблицу MySQL
foreach ($PHPExcel_file->getWorksheetIterator() as $index => $worksheet) {
echo excel2mysql($worksheet, $connection, «excel2mysql» . ($index != 0 ? $index : «»), 1) ? «OK
» : «FAIL
«;
}

Заключение

Что ж, надеюсь данная статья поможет вам. Ну, или, если вы захотите изобрести свой велосипед, но только с моторчиком написать свой скрипт, эта статья поможет вам начать.

P.S

Это моя первая, и думаю, не последняя статья. Поэтому жду ваших советов и поправок, как тут принято, в х.

Update

Вижу, все-таки, мне удалось создать небольшую дискуссию, но не все понимают, почему было сделано именно так. Постараюсь объяснить.

Во-первых: с этим должен был работать пожилой человек, которому будет трудновато объяснить как сохранить файл в CSV, при этом не потеряв данные (а такое исключать нельзя, к тому же у них свой формат на файл XLS, который приходит сверху) и, тем более, как это импортировать через phpMyAdmin (который, кстати, с версии 3.4.5 не поддерживает XLS/XLSX, советую посмотреть почему) или подобное. Так что это не подходит.

Во-вторых: все это должно быть расположено на хостинге, и установка модулей как на сервер, так и для локальных программ не подходит (к тому же там Linux, а не Windows, как некоторые подумали). В-третьих: это дело проводится раз в полгода, однако от безделья я решил написать такую функцию, способную обобщить импорт (вдруг кому нужно).

Теперь о хорошем: переписал данную функцию в класс, исправил кое-что и добавил возможность экспорта из MySQL в Excel. Забрать можно отсюда.

Извините, что не ответил в х, решил что в самой статье будет уместние.

Источник: https://habr.com/post/178089/

Импорт базы данных MySQL в консоли

• Доброго времени суток, коллеги ????
• Сегодня я продолжу знакомить вас с работой с MySQL в консоли и командной строкой MySQL.
• Я уже написал статьи о том, как производить основные действия с данными MySQL через консоль иm делать бэкап базы MySQL, а также экспорт хранимой в ней информации.

Логическим продолжением этого повествования будет восстановление БД и хранящейся в ней информации с помощью операций импорта базы данных MySQL. И, что немаловажно, мы продолжим делать это с помощью инструмента всех трушных разработчиков — через консоль.

Если же вам нужна инструкция по импорту базы через phpMyAdmin, то вы можете найти её в статье о переносе WordPress на хостинг. В текущей статье я не горю желанием описывать её ещё раз, тем более, что сегодняшний материал будет посвящён исключительно импорту базы MySQL через консоль.

Но, перед тем, как мы приступим к обзору способов и инструментов, пару слов о том, что такое импорт базы данных MySQL, каким он бывает и как его лучше всего делать?

Импорт базы данных MySQL: что и зачем?

Импорт базы данных MySQL — это операция, при которой происходит наполнение базы информацией. При этом источником данных является файл дампа — слепок другой базы, автоматически созданный при операции экспорта, или специально подготовленный SQL скрипт.

Импорт, как и экспорт БД MySQL, бывает двух видов информации, хранящейся в базе:

1. структуры базы, её таблиц и хранимых в них данных (в простонародье именуемых дампом БД);
2. просто данных, хранящихся в таблице либо собранных с помощью SELECT запросов.

1. В данной статье будут рассмотрены оба варианта.
2. Для восстановления из дампа MySQL базы данных с её структурой и всей хранимой информацией, как уже было сказано, нужен файл дампа БД, который представляет из себя текстовый файл с любым расширением (предварительно может быть запакован в архив для уменьшения размера), содержащий SQL команды для создания самой базы и таблиц, а также наполнения их информацией.
3. Следовательно, для того, чтобы восстановить MySQL базу из дампа, требуется выполнение содержащихся в файле команд.

Для обычного восстановления данных такие сложности не обязательны. Достаточно иметь в наличии тестовый файлик, информация в котором будет структурирована тем же образом, что и в таблице БД: количество колонок с информацией соответствует числу атрибутов записи таблицы.

Для этих целей подойдёт и обычный txt файл, данные в котором будут разделены, либо файлы, создаваемые в специальных табличных редакторах (Microsoft Office Excel, OpenOffice и т.д.), имеющих отличное расширение: xls, csv, odt и др.

Данные форматы даже предпочтительнее, т.к. при их создании ограничители данных добавляются редакторами автоматически, и нет нужды вносить их отдельно, как в случае обычного текстового файла.

Добавление данных в MySQL: инструменты

По поводу инструментов осуществления импорта базы данных MySQL могу сказать, что их на сегодняшний день существует целых три.

Перечислю их, начиная с самых низкоуровневых, заканчивая высокоуровневыми (с точки зрения применения всяческих оболочек и надстроек):

1. Консоль сервера и командная строка MySQL;
2. Скрипты, написанные на языках программирования, позволяющие делать запись данных в MySQL с помощью языковых средств;
3. Готовые программы, предоставляющие визуальный интерфейс для работы с БД (тот же самый phpMyAdmin, MySQL WorkBench, MySQL Manager и др.).

Думаю, что порядок расположения инструментов не вызовет ни у кого вопросов, т.к. средства языков программирования, как правило, работают на базе команд консоли MySQL, а программы базируются на скриптах или же работают с MySQL командной строкой напрямую.

Так или иначе, во главе всего лежит консоль, а остальные инструменты, по сути, являются её эмуляторами.

За счёт этого залить базу данных MySQL через консоль можно не только быстрее, но и сделать эту операцию возможной в принципе, т.к. скрипты и программы склонны прерывать импорт при достижении максимального времени выполнения скрипта или вообще не стартовать его из-за размера загружаемого файла.

• Думаю, все, кто хоть раз пытался загрузить дамп в MySQL БД большого размера через phpMyAdmin, понимают, о чём я говорю.
• Зачастую именно эти лимиты являются причинами ошибок при импорте базы данных MySQL, которые при использовании консоли вы никогда не увидите.
• Они, конечно, не константны, и их можно изменить, но это дополнительная головная боль, которая для рядовых пользователей, кстати, может оказаться нерешаемой.
• Надеюсь, что я замотивировал вас делать импорт базы данных MySQL через консоль (причём, как её структуры, так и отдельно данных).
• И на этой позитивной ноте мы переходим к долгожданной практике и рассмотрим способы и команды для консольного переноса данных в базу.

Как восстановить MySQL базу из дампа через консоль?

Итак, для того, чтобы развернуть дамп MySQL из консоли есть два пути:

1. с помощью команды в командной строке MySQL;
2. в самой консоли сервера.

Начнём по порядку.

Итак, чтобы импортировать дамп базы данных MySQL в существующее хранилище через командную строку MySQL, нам сначала нужно её запустить и выбрать нужную базу данных, в которую мы будем заливать наш дамп.

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

После того, как вы сделаете указанное, вводим в MySQL Shell следующую команду:

source путь_и_имя_файла_дампа;

Всё, что нам останется — это изучать сообщения в консоли о ходе выполнения операций, содержащихся в дампе.

Без предварительного переключения на нужную базу данных после соединения с сервером MySQL в консоли дамп можно импортировать следующей командой:

mysql -u имя_пользователя -p имя_базы_данных < путь_и_имя_файла_дампа

Вот и всё. Главное — дождаться окончания импорта, если файл очень большой. Об окончании заливки дампа можно судить по тому, когда консоль сервера будет снова доступна.

Собственно говоря, в этом и заключается недостаток данного способа по сравнению с предыдущим, т.к. в первом есть возможность наблюдать за операциями, производимыми с БД во время импорта, а во втором — нет.

Если файл дампа будет запакован в архив, то при загрузке нужно будет его попутно распаковывать.

В Linux это можно сделать следующим образом:

gunzip > [имя_файла_архива.sql.gz] | mysql -u [user] -p[password] [databasename]

В Windows стандартной утилиты для распаковки архива в консоли нет, поэтому её нужно будет установить дополнительно.

Если вдруг вы не знаете, как запустить консоль сервера, то можете найти эту информацию в статье о командной строке MySQL, ссылку на которую я уже располагал ранее.

Кстати, описанными способами возможен также и импорт таблицы MySQL, а не целой базы. В этом случае в заливаемом вами дампе должны присутствовать операции её создания и наполнения данными.

Загрузка данных в MySQL базу из файла в консоли

О восстановлении БД MySQL из дампа в консоли мы поговорили. Теперь самое время разобраться с тем, как аналогичным образом можно импортировать данные из файлов, в том числе из xls и csv в MySQL базу.

1. Для данной задачи у нас снова есть те же два инструмента, что и в предыдущем случае: командная строка MySQL и консоль сервера.
2. Снова начнём обзор по порядку.
3. Итак, для импорта файла в MySQL командной строке мы снова запускаем её и переходим на БД, в которую будут загружаться данные.
4. А далее прописываем в консоли следующую SQL команду:

LOAD DATA INFILE 'путь_и_имя_файла_дампа'
INTO TABLE `таблица_базы_данных`
COLUMNS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '
';

• Не забудьте, что, если сервер MySQL был запущен с опцией —secure-file-priv (что часто бывает при использовании MySQL дистрибутивов, входящих в WAMP/MAMP сборки), то имя файла нужно указывать с учётом системной переменной secure_file_priv.
• О том, как узнать её значение и изменить его, подробно написано в статье об экспорте базы данных MySQL.
• Для того, чтобы сделать импорт базы данных MySQL в консоли сервера, не заходя в MySQL Shell, нам пригодится утилита mysqlimport, входящая в состав дистрибутива MySQL, и следующий её вызов:

mysqlimport –u имя_пользователя –p имя_базы_данных имя_и_путь_к_файлу_импорта

Данная утилита является аналогом SQL команды LOAD DATA INFILE, только для командной строки. Но, спрашивается, почему тогда среди параметров её вызова не указана таблица, в которую будут загружаться данные из файла?

Дело в том, что у mysqlimport просто физически нет данного параметра. Вместо этого имя таблицы, в которую будут загружаться данные, должно присутствовать в имени импортируемого файла.

Т.е. если вы захотите сделать импорт из Excel таблицы в MySQL таблицу users, то ваш файл должен называться users.xls.

Расширение у импортируемого файла, как уже говорилось, может быть любым.

С помощью mysqlimport также можно загружать сразу несколько файлов xls или csv в MySQL. Чтобы данные попали по назначению, названия файлов и таблиц БД, как и в предыдущем примере, также должны совпадать.

Если вдруг в импортируемом файле столбцы идут не в той же последовательности, что и колонки таблицы БД, то для уточнения их порядка нужно использовать опцию —columns в следующем виде:

mysqlimport –u имя_пользователя –p имя_базы_данных —columns столбец1, столбец2, … имя_и_путь_к_файлу_импорта

Естественно, что в своих примерах я не рассмотрел полный список параметров mysqlimport, т.к. некоторые из них весьма специфичны и на практике используются крайне редко.

Если захотите ознакомиться с ними самостоятельно, то полный их список доступен здесь — https://dev.mysql.com/doc/refman/5.7/en/mysqlimport.html

Особенности загрузки данных в MySQL базу из дампа

1. Если хотите, чтобы процесс импорта большой базы MySQL проходил быстрее, то необходимо создавать дамп БД с использованием специальных опций команды mysqldump, о которых я писал в своей предыдущей статье об экспорте БД MySQL, ссылку на которую размещал в тексте ранее.

2. У самих команд импорта баз данных MySQL таких опций, к сожалению, нет.
3. Единственное, для увеличения скорости при загрузке большого дампа БД можете использовать следующую фишку.

1. Открываем файл дампа (желательно в файловых менеджерах, т.к.

обычные редакторы могут просто загнуться от больших файлов).

2. Прописываем в начале файла следующие строки:

SET foreign_key_checks = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;

Обратите внимание! Может быть они уже есть или закомментированы (многие программы, с помощью которых делают дампы, могут добавлять их автоматически)

3. В конце файла прописываем обратные действия:

SET foreign_key_checks = 1;
SET UNIQUE_CHECKS = 1;
SET AUTOCOMMIT = 1;

Кстати, данные команды помогут не только ускорить процесс импорта, но и сделать его возможным.

Дело в том, что, если вы хоть раз заглядывали в файл дампа для импорта БД MySQL, то могли заметить, что операция задания структуры загружаемых таблиц выглядит так:

DROP TABLE IF EXISTS `clients`;
CREATE TABLE `clients` (
…
);

И если вдруг существующая таблица будет связана внешними ключами с другими, то вся загрузка провалится.

Поэтому отключение проверки существования внешних ключей и прочих – это ещё и отличная гарантия успешного выполнения процесса импорта базы данных MySQL.

Особенности импорта csv в MySQL БД и других файлов

При загрузке данных в MySQL БД из текстовых файлов может также потребоваться отключение внешних ключей.

Причём, в отличие от предыдущей ситуации, в данном случае прописать директивы в файл не получится, т.к. SQL команды в нём не будут восприниматься и выполняться.

В предыдущей статье об экспорте базы MySQL я уже упоминал о том, как это сделать с помощью следующей операции в командной строке MySQL:

SET FOREIGN_KEY_CHECKS=0;

Однако, там я не упомянул, что системная переменная MySQL FOREIGN_KEY_CHECKS имеет два значение: глобальное и сессионное (для текущей сессии).

Глобальное значение переменных MySQL действует при выполнении любых действий на сервере MySQL вплоть до его перезапуска. Тогда значение переменных будут сброшены и им будет присвоено значения по умолчанию.

Сессионное значение системной переменной MySQL устанавливается только на время сеанса работы пользователя с сервером MySQL. Сеанс или сессия начинается при подключении клиента к серверу, при котором ему присваивается уникальный connection id, и заканчивается при отключении от сервера, которое может произойти в любой момент (например, по таймауту).

• Почему я об этом решил вспомнить?
• Потому что при выполнении команд загрузки файла в MySQL БД через консоль сервера, без захода в MySQL Shell, я обнаружил, что отключение проверки внешних ключей приведённым ранее способом не работает.
• В консоли всё так же выдавалось сообщение об ошибке, вызываемой наличием в таблице внешних ключей.
• А возникало оно по той причине, что приведённой командой отключалась проверка существования внешних ключей в рамках сессии, а не глобально, которая, помимо указанного способа, может быть выполнена ещё и следующим образом:

SET SESSION имя_переменной = значение_переменной;
SET @@session.имя_переменной = значение_переменной;
SET @@имя_переменной = значение_переменной;

1. В приведённых командах переменная явно помечается как сессионная.
2. А, поскольку, я выполнял загрузку csv файла в MySQL таблицу через консоль сервера, без прямого подключения к серверу MySQL, то сеанс и не был создан, в рамках которого работало бы моё сессионное значение переменной.
3. В итоге я установил глобальное значение FOREIGN_KEY_CHECKS, и импорт успешно выполнился.
4. Сделать это можно одним из перечисленных способов:

SET GLOBAL имя_переменной = значение_переменной;
SET @@global.имя_переменной = значение_переменной;

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

SELECT @@GLOBAL.foreign_key_checks, @@SESSION.foreign_key_checks;

На этом сегодняшняя статья, посвящённая импорту базы данных MySQL, подошла к концу. Делитесь своими впечатлениями и собственными наработками в х. Думаю, что многим будет интересен ваш опыт.

Если публикация вам понравилась — вы всегда можете поделиться ею со своими друзьями в социальных сетях и сказать спасибо автору с помощью формы под статьёй.

Источник: http://cccp-blog.com/razrabotchiku/import-bazy-dannyh-mysql