Как загрузить данные из excel в mysql

Обновлено: 03.07.2024

Бывает так, что вам нужно импортировать файл 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 в SQL Server или базу данных SQL Azure можно несколькими способами. Некоторые методы позволяют импортировать данные за один шаг непосредственно из файлов Excel. Для других методов необходимо экспортировать данные Excel в виде текста (CSV-файла), прежде чем их можно будет импортировать. В этой статье перечислены часто используемые методы и содержатся ссылки для получения дополнительных сведений.

Список методов

Для импорта данных из Excel можно использовать следующие средства:

Сначала экспортировать в текст (SQL Server и база данных SQL)	Непосредственно из Excel (только в локальной среде SQL Server)
Мастер импорта неструктурированных файлов	мастер импорта и экспорта SQL Server
Инструкция BULK INSERT	Службы SQL Server Integration Services
BCP	Функция OPENROWSET
Мастер копирования (Фабрика данных Azure)
Фабрика данных Azure.

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

Этот список не дает полного описания таких сложных инструментов и служб, как SSIS или фабрика данных Azure. Дополнительные сведения об интересующем вас решении доступны по ссылкам ниже.

Дополнительные сведения о подключении к файлам Excel, а также об ограничениях и известных проблемах, связанных с загрузкой данных в файлы этого приложения и из них, см. в разделе Загрузка данных в приложение Excel или из него с помощью служб SQL Server Integration Services (SSIS).

Если у вас не установлен SQL Server или SQL Server есть, но нет SQL Server Management Studio, см. статью Скачивание SQL Server Management Studio (SSMS).

Мастер импорта и экспорта SQL Server

Импортируйте данные напрямую из файлов Excel, выполнив инструкции на страницах мастера импорта и экспорта SQL Server. При необходимости сохраните параметры в виде пакета служб SQL Server Integration Services (SSIS), доступного для настройки и многократного применения в будущем.

В SQL Server Management Studio подключитесь к экземпляру SQL Server Компонент Database Engine.

Разверните узел Базы данных.

Щелкните базу данных правой кнопкой мыши.

Наведите указатель мыши на пункт Задачи.

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

Импорт данных

Экспорт данных

Пример использования мастера для импорта из Excel в SQL Server см. в разделе Get started with this simple example of the Import and Export Wizard (Начало работы с помощью простого примера использования мастера импорта и экспорта).

Сведения о других способах запустить мастер импорта и экспорта см. в разделе Запуск мастера импорта и экспорта SQL Server.

Службы SQL Server Integration Services

Если вы работали со службами SSIS и не хотите запускать мастер экспорта и импорта SQL Server, создайте пакет SSIS, который использует для потока данных источник Excel и назначение SQL Server.

Дополнительные сведения о компонентах SSIS см. в указанных ниже статьях.

Чтобы научиться создавать пакеты SSIS, см. руководство How to Create an ETL Package (Как создать пакет ETL).

OPENROWSET и связанные серверы

В базе данных SQL Azure невозможно импортировать данные непосредственно из Excel. Сначала необходимо экспортировать данные в текстовый файл (CSV). Примеры см. в разделе Пример.

Поставщик ACE (прежнее название — поставщик Jet), который подключается к источникам данных Excel, предназначен для интерактивного клиентского использования. Если поставщик ACE используется на сервере SQL Server, особенно в автоматизированных процессах или процессах, выполняющихся параллельно, вы можете получить непредвиденные результаты.

Распределенные запросы

Импортируйте данные напрямую из файлов Excel в SQL Server с помощью функции Transact-SQL OPENROWSET или OPENDATASOURCE . Такая операция называется распределенный запрос.

Перед выполнением распределенного запроса необходимо включить параметр ad hoc distributed queries в конфигурации сервера, как показано в примере ниже. Дополнительные сведения см. в статье ad hoc distributed queries Server Configuration Option (Параметр конфигурации сервера "ad hoc distributed queries").

В приведенном ниже примере кода данные импортируются из листа Excel Sheet1 в новую таблицу базы данных с помощью OPENROWSET .

Ниже приведен тот же пример с OPENDATASOURCE .

Чтобы добавить импортированные данные в существующую таблицу, а не создавать новую, используйте синтаксис INSERT INTO . SELECT . FROM . вместо синтаксиса SELECT . INTO . FROM . из предыдущих примеров.

Для обращения к данным Excel без импорта используйте стандартный синтаксис SELECT . FROM . .

Дополнительные сведения о распределенных запросах см. в указанных ниже разделах.

(Распределенные запросы по-прежнему поддерживаются в SQL Server 2016, но документация по этой функции не обновлена.)

Связанные серверы

Кроме того, можно настроить постоянное подключение от SQL Server к файлу Excel как к связанному серверу. В примере ниже данные импортируются из листа Excel Data на существующем связанном сервере EXCELLINK в новую таблицу базы данных SQL Server с именем Data_ls .

Вы можете создать связанный сервер в SQL Server Management Studio или запустить системную хранимую процедуру sp_addlinkedserver , как показано в примере ниже.

Дополнительные сведения о связанных серверах см. в указанных ниже разделах.

Примеры и дополнительные сведения о связанных серверах и распределенных запросах см. указанных ниже разделах.

Предварительное требование — сохранение данных Excel как текста

Чтобы использовать другие методы, описанные на этой странице (инструкцию BULK INSERT, средство BCP или фабрику данных Azure), сначала экспортируйте данные Excel в текстовый файл.

В Excel последовательно выберите Файл | Сохранить как и выберите как целевой тип файла Текст (разделитель — табуляция) (*.txt) или CSV (разделитель — запятая) (*.csv) .

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

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

Мастер импорта неструктурированных файлов

Импортируйте данные, сохраненные как текстовые файлы, выполнив инструкции на страницах мастера импорта неструктурированных файлов.

Как было описано выше в разделе Предварительное требование, необходимо экспортировать данные Excel в виде текста, прежде чем вы сможете импортировать их с помощью мастера импорта неструктурированных файлов.

Дополнительные сведения о мастере импорта неструктурированных файлов см. в разделе Мастер импорта неструктурированных файлов в SQL.

Команда BULK INSERT

BULK INSERT — это команда Transact-SQL, которую можно выполнить в SQL Server Management Studio. В приведенном ниже примере данные загружаются из файла Data.csv с разделителями-запятыми в существующую таблицу базы данных.

Как было описано выше в разделе Предварительное требование, необходимо экспортировать данные Excel в виде текста, прежде чем вы сможете использовать BULK INSERT для их импорта. BULK INSERT не может считывать файлы Excel напрямую. С помощью команды BULK INSERT можно импортировать CSV-файл, который хранится локально или в хранилище BLOB-объектов Azure.

Дополнительные сведения и примеры для SQL Server и базы данных SQL см. в следующих разделах:

Средство BCP

BCP — это программа, которая запускается из командной строки. В приведенном ниже примере данные загружаются из файла Data.csv с разделителями-запятыми в существующую таблицу базы данных Data_bcp .

Как было описано выше в разделе Предварительное требование, необходимо экспортировать данные Excel в виде текста, прежде чем вы сможете использовать BCP для их импорта. BCP не может считывать файлы Excel напрямую. Используется для импорта в SQL Server или базу данных SQL из текстового файла (CSV), сохраненного в локальном хранилище.

Для текстового файла (CSV), хранящегося в хранилище BLOB-объектов Azure, используйте BULK INSERT или OPENROWSET. Примеры см. в разделе Пример.

Дополнительные сведения о программе BCP см. в указанных ниже разделах.

Мастер копирования (Фабрика данных Azure)

Импортируйте данные, сохраненные как текстовые файлы, выполнив инструкции на страницах мастера копирования Фабрики данных Azure.

Как было описано выше в разделе Предварительное требование, необходимо экспортировать данные Excel в виде текста, прежде чем вы сможете использовать фабрику данных Azure для их импорта. Фабрика данных не может считывать файлы Excel напрямую.

Дополнительные сведения о мастере копирования см. в указанных ниже разделах.

Фабрика данных Azure

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

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

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

Распространенные ошибки

Эта ошибка возникает, так как не установлен поставщик OLE DB. Установите его через Распространяемый пакет ядра СУБД Microsoft Access 2010. Не забудьте установить 64-разрядную версию, если Windows и SQL Server — 64-разрядные.

Полный текст ошибки.

Не удалось создать экземпляр поставщика OLE DB "Microsoft.ACE.OLEDB.12.0" для связанного сервера "(null)".

Это означает, что Microsoft OLEDB не был настроен должным образом. Чтобы устранить проблему, выполните приведенный ниже код Transact-SQL.

Полный текст ошибки.

32-разрядный поставщик OLE DB "Microsoft.ACE.OLEDB.12.0" не может быть загружен в процессе на 64-разрядной версии SQL Server.

Это происходит, когда 32-разрядная версия поставщика OLD DB устанавливается вместе с 64-разрядной версией SQL Server. Чтобы устранить эту проблему, удалите 32-разрядную версию и вместо нее установите 64-разрядную версию поставщика OLE DB.

Полный текст ошибки.

Поставщик OLE DB "Microsoft.ACE.OLEDB.12.0" для связанного сервера "(null)" сообщил об ошибке. Поставщик не предоставил данных об ошибке.

Не удалось проинициализировать объект источника данных поставщика OLE DB "Microsoft.ACE.OLEDB.12.0" для связанного сервера "(null)".

Обе эти ошибки обычно указывают на ошибку разрешений между процессом SQL Server и файлом. Убедитесь, что учетная запись, с которой выполняется служба SQL Server, имеет разрешение на полный доступ к файлу. Мы не рекомендуем импортировать файлы с настольного компьютера.

От автора: в этом уроке мы рассмотрим экспорт из 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, поэтому открываем командную строку, переходим в каталог с тестовым сайтом и выполняем следующую команду:

может кто-нибудь объяснить, как импортировать файл Excel в базу данных MySQL?

например, моя таблица Excel выглядит так:

экспортировать его в текстовый формат. Самым простым, вероятно, будет версия с разделителями табуляции, но CSV также может работать.
используйте возможность загрузки данных. См.http://dev.mysql.com/doc/refman/5.1/en/load-data.html
посмотрите на полпути вниз по странице, так как это даст хороший пример для данных, разделенных вкладками:
поля заканчиваются на '\t', заключенные в " Escape BY '\ '
Проверьте свои данные. Иногда цитирование или экранирование имеет проблемы, и вам нужно настроить свой источник, команду импорта-или это может быть просто проще после обработки через SQL.

есть простой онлайн-инструмент, который может сделать это под названием sqlizer.io.

вы загружаете файл XLSX в него, вводите имя листа и диапазон ячеек, и он будет генерировать инструкцию CREATE TABLE и кучу инструкций INSERT для импорта всех ваших данных в базу данных MySQL.

Excel2MySQL или программа коммунальные услуги. Полное раскрытие информации, я являюсь автором Excel2MySQL. Эти 2 утилиты не являются бесплатными, но они являются самым простым вариантом и имеют наименьшее количество ограничений. Они также включают в себя дополнительные функции, чтобы помочь с импортом данных Excel в MySQL. Например, Excel2MySQL автоматически создает таблицу и автоматически оптимизирует типы данных полей, такие как даты, время, поплавки и т. д. Если вы торопитесь или не можете получить другие варианты для работы с вашими данными, то эти утилиты могут удовлетворить ваши потребности.
ЗАГРУЗИТЬ ФАЙЛ ДАННЫХ: этот популярный вариант, пожалуй, самый технический и требует некоторого понимания выполнения команд MySQL. Вы должны вручную создайте таблицу перед загрузкой и используйте типы полей VARCHAR соответствующего размера. Таким образом типы данных поля не оптимизированы. LOAD DATA INFILE имеет проблемы с импортом больших файлов, которые превышают размер "max_allowed_packet". Особое внимание требуется, чтобы избежать проблем с импортом специальных символов и иностранных символов Юникода. Вот недавний пример, который я использовал для импорта csv-файла с именем тест.csv.
phpMyAdmin: Сначала выберите базу данных, затем перейдите на вкладку импорт. phpMyAdmin автоматически создаст вашу таблицу и размер ваших полей VARCHAR, но не будет оптимизировать типы полей. phpMyAdmin имеет проблемы с импортом больших файлов, которые превышают размер "max_allowed_packet".
MySQL для Excel: это a бесплатная надстройка Excel от Oracle. Этот вариант немного утомителен, потому что он использует мастер, а импорт медленный и глючит с большими файлами, но это может быть хорошим вариантом для небольших файлов с данными VARCHAR. Поля не оптимизированы.

выберите соответствующие ячейки:
вставить в Г-Н Конвертер Данных и выберите выход MySQL:
измените имя таблицы и определения столбцов в соответствии с вашими требованиями в сгенерированном выводе:

если вы используете MySQL Workbench или уже вошли в mysql из командной строки, то вы можете выполнить сгенерированные инструкции SQL из Шага 3 непосредственно. В противном случае, вставьте код в текстовый файл (например, import.sql ) и выполните эту команды из оболочки Unix:
mysql mydatabase < import.sql
другие способы импорта из файла SQL можно найти в этот ответ переполнения стека.

Не уверен, что у вас есть все эти настройки, но для меня я использую PHP и MYSQL. Поэтому я использую PHP класс PHPExcel. Это занимает файл практически в любом формате, xls, xlsx, cvs. а затем позволяет читать и / или вставлять.

Так что я заканчиваю тем, что загружаю excel в объект phpexcel, а затем перебираю все строки. Основываясь на том, что я хочу, я пишу простую команду SQL insert для вставки данных в файл excel в мою таблицу.

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

лучший и самый простой способ-использовать приложение "MySQL для Excel", которое является бесплатным приложением от oracle. это приложение добавило плагин для excel для экспорта и импорта данных в mysql. вы можете скачать это с здесь

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

теперь я использую Microsoft открыть 2010, чтобы открыть мою таблицу MySql как связанную таблицу. Там я могу просто скопировать и вставить ячейки из Excel в Access.

для этого сначала установить драйвер ODBC MySql создать ODBC соединение. Затем в access, на вкладке "внешние данные", откройте диалоговое окно" база данных ODBC " и ссылку на любую таблицу, используя соединение ODBC.

вы могли бы использовать DocChow, очень интуитивный GIU для импорта Excel в MySQL, и это бесплатно на большинстве распространенных платформ (включая Linux).

особенно если вас беспокоит дата, типы данных datetime, DocChow легко обрабатывает типы данных. Если вы работаете с несколькими электронными таблицами Excel, которые вы хотите импортировать в одну таблицу MySQL, DocChow выполняет грязную работу.

Шаг 1 Создайте свой CSV-файл

Шаг 2 войдите на свой сервер mysql

Шаг 3 загрузите csv-файл

Читайте также: