Как загрузить данные из excel в mysql
Спасибо большое Вам за уроки по PHPExcel помогли во многом разобраться по экспорту из Excel в MySQL.
подскажите пож. как реализовать обработку только нужных столбцов так как файл который я подключаю 50 тыс. строк
в мануале написано:
но реализовать в вашем примере не получается )
на сколько я понял нужно создать функцию перед 40 строчкой в файле controller.php
и еще хочу реализовать чтение только даных .
написав такой код
видим что фильтр работает и не подгружает в dump не нужные данные.
По столбцам необходимо поставить В, G и дт. очень хотелось бы реализовать через масив а не выходит (( как у Вас в примере
я только учусь не судите строго, подскажите или покажите куда копать )
кто-то спросит почему бы не использовать метод импорта нужных столбцов как в примере Экспорт из Excel в MySQL ?
обратите внимане, если использовать даный пример и в файле controller.php
мы видим что PHPExcel обрабатывает все ячейки и те что нам надо и другие записывая их в оперативку и только в на этапе переноса в MySQL отсекает лишнее , при работе с большими файлами это огромная проблема так как времени уходит много на выполнение скрипта да и пемяти нужно много. Хостер просто неможет выполнить данний скрипт.
если используем
мы берем с файла в оперативку только то что нужно а с помощью мы не читаем форматирование а только данные, скорость обработки только из-за использования увеличивается в разы
у меня большие файлы по 50 тыс строк и там куча колонок, а нужны только несколько
Еще раз, здравствуйте!
А можно Ваши исходники посмотреть, возможно посмотрев код, что то смогу подсказать(прикрепите архив и дамп базы данных).
Еще раз, здравствуйте!
А можно Ваши исходники посмотреть, возможно посмотрев код, что то смогу подсказать(прикрепите архив и дамп базы данных).
В архиве нет самой библиотеки (форум по размерам не пропустил)
Дамп базы не даю так сейчас не получается вот на этом участке
сделать так что бы названия столбцов тянуло с масива (еще раз повторюсь, я только учусь
а вообще есть желание разобратся как внести необходимые изменения в Ваш controller.php но там я вообще застрял )
Прикрепленные файлы
доделал что впринципе хотелось, а это -- фильтр перед загрузкой данных и чтение только даных. Производительность улучшилась в разы
Если вруг кому надо yadi.sk/d/wC4-D4eEo5E6t
для создания пустой БД есть скрипт
НО для огромных файлов метот не решает проблему я сумел максимум загрузить файл в 20 тыс строк и 9 колонок
если есть идеи поделитесь плиз
решить нужно без использования JS
Друзья! Может кто сталкивался или знает в чем дело:
1. Экспортирую из БД в xls файл данные. Даты при этом нормального формата - Дата , формат ячейки - *14.03.2001. И в ячейках вид - 14.03.2001
2. При этом ничего в нем не трогаю.
3. При восстановлении базы через экспорт этого xls, то есть экспортирую его как в этом уроке, форматы ячеек прописаны по соответствующим столбцам записывается только день даты правильный, остальное 01.1900
В чем может быть загвоздка.
Спасибо.
P.S. в csv все норм идет, но нужно именно без лишней возни - пересохранения в csv и обратно xls. Скачал дамп в виде xls. И восстановил если что.
P.S.P.S. и Экспорт в базу в csv тоже не пойдет. Нужен только xls.
P.S.P.S.P.S. Спасибо!
Доброго времени суток.
Получил ответ:
1. task в массиве $_GET[`task`], то есть нужно передавать через адресную строку
Это понятно по определению самого метода _GET. Не понял другое: на видео видно, что в адресной строке ни какого запроса не предусмотрено типа localhost/any_page/?query=any_request_from_client . Зато, имеется некая стартовая страница с элементами управления. Иначе говоря, нам предоставляется самим создать подобную страничку и из нее передавать запрос посредством метода _GET? Если так, то в силу своей неосведомленности в сайтостроении я хотел бы на форуме обсудить технические аспекты этой страницы.
Доброго времени суток.
Получил ответ:
1. task в массиве $_GET[`task`], то есть нужно передавать через адресную строку
Это понятно по определению самого метода _GET. Не понял другое: на видео видно, что в адресной строке ни какого запроса не предусмотрено типа localhost/any_page/?query=any_request_from_client . Зато, имеется некая стартовая страница с элементами управления. Иначе говоря, нам предоставляется самим создать подобную страничку и из нее передавать запрос посредством метода _GET? Если так, то в силу своей неосведомленности в сайтостроении я хотел бы на форуме обсудить технические аспекты этой страницы.
Здравствуйте!
По сути Вы можете создать тестовую страницу так как Вам нужно, ее код вообще можно переписать. В уроке это только пример и код файла index.php к основной теме - Экспорт из excel в mysql, не относится. index.php нужен только для отображения данных на экран и все - это точка входа.
Здравствуйте!
По сути Вы можете создать тестовую страницу так как Вам нужно, ее код вообще можно переписать. В уроке это только пример и код файла index.php к основной теме - Экспорт из excel в mysql, не относится. index.php нужен только для отображения данных на экран и все - это точка входа.
Здравствуйте. Заранее извиняюсь за прошлый пост. Не разобрался в структуре файлов в предоставленном архиве, поэтому скоропостижно и необдуманно отправил пост. Просто попытался адаптировать ваш код под свою БД и допустил ряд досадных синтаксических ошибок. После отладки все работает отменно. А вот если например столбцы таблицы SPE в бд Import (у меня) представлены символами кириллицы, проверяю запрос через переменную echo $fields в модуле model.php в методе insertExcel(). В браузере вижу кракозябры. И также в модуле config.php пишу названия полей на русском:
Поэтому конечный запрос не проходит - echo $str возвращает 'NOT $this->model->insertExcel($arr)' в модуле controller.php
Посоветуйте пожалуйста решить этот баг
Здравствуйте. Заранее извиняюсь за прошлый пост. Не разобрался в структуре файлов в предоставленном архиве, поэтому скоропостижно и необдуманно отправил пост. Просто попытался адаптировать ваш код под свою БД и допустил ряд досадных синтаксических ошибок. После отладки все работает отменно. А вот если например столбцы таблицы SPE в бд Import (у меня) представлены символами кириллицы, проверяю запрос через переменную echo $fields в модуле model.php в методе insertExcel(). В браузере вижу кракозябры. И также в модуле config.php пишу названия полей на русском:
Поэтому конечный запрос не проходит - echo $str возвращает 'NOT $this->model->insertExcel($arr)' в модуле controller.php
Посоветуйте пожалуйста решить этот баг
Здравствуйте!
Возможно стоит изменить кодировку. Насколько я помню Excel работает в windows-1251.А что Вы имеете ввиду под столбцы таблицы SPE в бд Import (у меня) представлены символами кириллицы,?
Здравствуйте!
Возможно стоит изменить кодировку. Насколько я помню Excel работает в windows-1251.А что Вы имеете ввиду под столбцы таблицы SPE в бд Import (у меня) представлены символами кириллицы,?
Доброго времени суток,
Имя базы данных SPE. Таблица Import. Названия столбцов введены на кириллице см. выше (например Дата_СПЕ (вместо Date_SPE), Подэкспертный (вместо SubExpert) etc.
Доброго времени суток,
Имя базы данных SPE. Таблица Import. Названия столбцов введены на кириллице см. выше (например Дата_СПЕ (вместо Date_SPE), Подэкспертный (вместо SubExpert) etc.
На работе встретился с такой задачей «Надо сделать отчет, который брал бы данные из MySQL и закидывал его в таблицу Excel».
Ну вот я сейчас и опишу процесс конекта.
Есть:
1. Сервер MySQL
2. База данных на MySQL
3. Microsoft Excel 2010
Начнем:
1. Самое первое что нужно сделать, это установить MySQL Connector, актуальную версию можно скачать здесь
2. Нужно создать источник данных, идем вот сюда Панель управления — Администрирование — Источники данных (ODBC)
2.1. Вкладка Пользовательский DSN — Добавить — MySQL ODBC 5.1 Driver (должен появиться после манипуляций с п.1)
2.2. Откроется окно настройки источника, там в поле Data Source Name нужно написать имя сервера MySQL, порт оставляем стандартным (если вы при установке сервера не указали иной), User — имя пользователя, скорее всего root, Password — пароль, Database — тут можно указать базу, к которой подключаться, если все правильно на начальных этапах, то в выпадающем списке уже появятся существующие БД на сервере.
2.3. Ок. Источник данных создан.
3. Теперь необходимо создать строку подключения.
3.1. В любом месте компьютера создайте текстовый файл и переименуйте его в тип файла .udl
3.2. В открывшемся окне Использовать источник данных — выбрать ранее созданный источник данных (п. 2), ввести Пользователь и Пароль, жмем Ок.
3.3. Теперь созданный файл надо открыть с помощью Блокнот'а и там будет что-то подобное:
[oledb]
; Everything after this line is an OLE DB initstring
Provider=MSDASQL.1;Persist Security Info=False;User Source=MySQL
то, что начинается с Provider и до MySQL и есть строка подключения, т.е. Provider=MSDASQL.1;Persist Security Info=False;User Source=MySQL она нам понадобиться при настройке Excel.
4. Теперь скачиваем специально подготовленный мной документ Excel
5. Открываем, возможно будет ругаться из-за того, что у вас сервер отличный от моего.
6. Идем на вкладку Данные — Параметры — Источник данных — Свойства подключения — Вкладка Определения.
6.1. Вводим Строка подключения, которая у нас появилась ранее (п. 3.3.), вводим текст SQL команды и жмем Ок.
Все, если все правильно было сделано, то в правой части должны появиться поля, которые вы хотели выбрать. Советую на сервере протестировать синтаксис и правильность своего SQL запроса, чтобы не появлялись дополнительные вопросы. Лично я работаю с MySQL через HeidiSQL, там сделал запрос, а потом просто скопировал его в Excel.
Бывает так, что вам нужно импортировать файл Excel в базу MySQL, но готового решения нигде нет. Вот и я, когда меня попросил друг поискать легкий способ импорта, сперва решил загуглить поискать решение. Увы, запрос php excel to mysql не дал ничего конкретного, или же описанные способы были довольно таки не удобны. Тогда же я решил найти библиотеку для работы с Excel на PHP, и мне попалась PHPExcel. Но опять же меня ждало разочарование, запрос phpexcel to mysql не дал ничего путного (я ленивый пользователь и дальше 1й страницы не хожу). В итоге я решил создать свой велосипед скрипт, которым и хочу поделиться с вами.
Начало
Итак, библиотеку я нашел, скачал и начал разбираться. Для начала нужно было подключить библиотеку и создать подключение к базе, что совсем не сложно:
Далее нужно открыть файл Excel для чтения:
После открытия файла, нам нужно перебрать все листы в нем и каждый добавить в базу MySQL (можно и 1 конкретный, но об этом позже):
Ну а теперь самое интересное…
Перебор и добавление
Мы будем исходить из того, что таблицы у нас нет (или есть, но с другими данными) и ее нужно создать. Для этого нам нужно получить имена для столбцов (в соответствии с просьбой друга, имена могут находиться в 1 строчке таблицы):
Далее удаляем таблицу из базы, если она существовала, и создаем новую:
Как видно из кода, значения будут иметь тип TEXT. Теперь приступаем собственно к перебору ячеек и добавления их в базу. Конечно, такой алгоритм не сложно найти на просторах Stack Overflow, однако было замечено, что происходить ошибка при попытки чтения объединенных ячеек (точнее несоответствие количества столбцов и значений в запросе). Это я и решил учесть:
Все дело в функцию!
Конечно, данный скрипт был бы гораздо удобнее, если бы все объединить в функцию. Поэтому итоговый результат получается такой:
Заключение
Что ж, надеюсь данная статья поможет вам. Ну, или, если вы захотите изобрести свой велосипед, но только с моторчиком написать свой скрипт, эта статья поможет вам начать.
Это моя первая, и думаю, не последняя статья. Поэтому жду ваших советов и поправок, как тут принято, в комментариях.
Update
Вижу, все-таки, мне удалось создать небольшую дискуссию, но не все понимают, почему было сделано именно так. Постараюсь объяснить.
Во-первых: с этим должен был работать пожилой человек, которому будет трудновато объяснить как сохранить файл в CSV, при этом не потеряв данные (а такое исключать нельзя, к тому же у них свой формат на файл XLS, который приходит сверху) и, тем более, как это импортировать через phpMyAdmin (который, кстати, с версии 3.4.5 не поддерживает XLS/XLSX, советую посмотреть почему) или подобное. Так что это не подходит.
Во-вторых: все это должно быть расположено на хостинге, и установка модулей как на сервер, так и для локальных программ не подходит (к тому же там Linux, а не Windows, как некоторые подумали).
В-третьих: это дело проводится раз в полгода, однако от безделья я решил написать такую функцию, способную обобщить импорт (вдруг кому нужно).
Теперь о хорошем: переписал данную функцию в класс, исправил кое-что и добавил возможность экспорта из MySQL в Excel. Забрать можно отсюда.
Извините, что не ответил в комментариях, решил что в самой статье будет уместние.
Иногда бывает проще работать с некоторыми данными в Excel, прежде чем импортировать их в базу данных MySQL.
Импорт Excel в MySQL phpMyAdmin
Иногда бывает проще работать с некоторыми данными в Excel, прежде чем импортировать их в базу данных MySQL.
Для этого сделайте несколько простых шагов.
Короче: экспортируйте файл Excel в CSV и импортируйте CSV в новую таблицу с помощью PHPMyAdmininterface.
Начиная с Excel, в примере ниже один лист, содержащий некоторые данные:
Выберите «Сохранить как», чтобы выбрать другой формат.
Прокрутите вниз до формата CSV (с разделителями-запятыми) (* .csv).
Убедитесь, что некоторые функции будут потеряны - CSV является текстовым форматом, никакие особенности Excel не будут экспортироваться (функции, программы и т. Д.).
Как импортировать Excel в PHPMyAdmin
В PHPMyAdmin перейдите в меню «Импорт».
Найдите файл CSV, который был недавно сохранен, и выберите соответствующий формат:
Посмотрите на другие параметры, которые могут применяться, например, если столбцы имеют имена, проверьте соответствующий параметр «Первая строка файла содержит имена столбцов таблицы» и нажмите «Перейти»
Должно появиться подтверждение импорта, и будут показаны ссылки на полученную базу данных и таблицу.
Теперь можно просмотреть таблицу!
Используя различные параметры, можно также работать со структурой таблицы, ее содержимым или перемещать ее в другую базу данных:
PHPMyAdmin импортировать CSV в MySQL
В PHPMyAdmin импортировать CSV-файл в MySQL довольно просто.
После того, как данные подготовлены в CSV, следуйте структуре таблицы, откройте таблицу в PHPMyAdmin, перейдите на вкладку import, найдите файл CSV для импорта на ваш компьютер, убедитесь, что формат CSV был выбран в параметре формата файла. и импортируйте файл CSV в MySQL в PHPMyAdmin.
Как импортировать Excel в MySQL PHP MyAdmin
Чтобы импортировать Excel в MySQL PHP MyAdmin, экспортируйте файл Excel в файл CSV. Затем импортируйте его в PHP MyAdmin, используя опцию файла данных CSV.
При импорте файла в MySQL из Excel прокрутите вниз до параметров «Формат» и выберите файл CSV. Это позволит преобразовать Excel в MySQL в PHP MyAdmin без необходимости что-либо программировать и просто с помощью графического интерфейса пользователя.
Создание базы данных в DirectAdmin
Если на вашем веб-сервере еще нет базы данных, независимо от используемого программного обеспечения для управления сервером, такого как cPanel или DirectAdmin, вам сначала необходимо создать базу данных, прежде чем вы сможете импортировать в нее какие-либо данные.
Просто перейдите на панель управления веб-пользователем и найдите экран управления MySQL. Там вы должны иметь возможность создать как базу данных, так и пользователя, который может получить доступ к этой базе данных - и то, и другое необходимо для того, чтобы иметь функциональную базу данных для вашего веб-сайта! Просто создайте его и используйте имя пользователя и пароль, выберите для подключения к базе данных и импорта экспорта, CSV или Excel в свою базу данных.
От автора: в этом уроке мы рассмотрим экспорт из Excel в MySQL на PHP. В одном из уроков нашего сайта, мы с Вами изучали библиотеку PHPExcel, которая используется для работы с таблицами Microsoft Excel, используя язык PHP. При этом на примере создания прайс-листа для сайта, мы научились переносить данные из СУБД MySql в данные таблицы. Но очень часто при разработке сайтов, необходимо выполнить обратную задачу – выгрузить данные из таблиц Microsoft Excel в базу данных СУБД MySql. Поэтому в данном уроке мы займемся решением данной задачи.
Установка библиотеки PHPExcel
Для начала хотел бы обратить Ваше внимание на то, что данный урок – это своего рода продолжение урока Генерация прайс-листа в формате Excel при помощи PHP. Phpexcel, который публиковался ранее.
При этом если Вы не знакомы с библиотекой PHPExcel и с основами работы с ней, то настоятельно рекомендую просмотреть вышеуказанный урок, так как сегодня мы не будем тратить время на изучение основ. Для данного урока мы будем использовать тестовый сайт, который написан с использованием объектно-ориентированного подхода и шаблона проектирования MVC. Сейчас я не буду приводить его код, так как он будет доступен Вам в дополнительных материалах к уроку. В функционале данного сайта описан механизм загрузки на сервер файлов формата Excel и соответственно, данный механизм нам необходимо доработать, а именно, после успешной загрузки файла на сервер, необходимо прочитать содержимое данного файла и перенести данные из необходимых столбцов таблицы Excel, в базу данных MySql.
Бесплатный курс по PHP программированию
Освойте курс и узнайте, как создать веб-приложение на PHP с полного нуля
Теперь давайте установим библиотеку PHPExcel. Для этого мы воспользуемся инструментом Composer, (для тех кто не знаком с данным инструментов, рекомендую посмотреть урок Введение в Composer, поэтому открываем командную строку, переходим в каталог с тестовым сайтом и выполняем следующую команду:
Читайте также: