Импорт данных из Excel в SQL Server или базу данных Azure
Применимо к: SQL Server (все поддерживаемые версии) База данных SQL Azure
Импортировать данные из файлов 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 . Такая операция называется распределенный запрос.
В базе данных SQL Azure невозможно импортировать данные непосредственно из Excel. Сначала необходимо экспортировать данные в текстовый файл (CSV). Примеры см. в разделе Пример.
Перед выполнением распределенного запроса необходимо включить параметр 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, но документация по этой функции не обновлена.)
- OPENROWSET
- OPENDATASOURCE
Связанные серверы
Кроме того, можно настроить постоянное подключение от 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.
Как было описано выше в разделе Предварительное требование, необходимо экспортировать данные Excel в виде текста, прежде чем вы сможете использовать фабрику данных Azure для их импорта. Фабрика данных не может считывать файлы Excel напрямую.
Дополнительные сведения об использовании этих источников и приемников фабрики данных см. в указанных ниже разделах.
Чтобы научиться копировать данные с помощью фабрики данных Azure, см. указанные ниже разделы.
Распространенные ошибки
"Microsoft.ACE.OLEDB.12.0" не зарегистрирован
Эта ошибка возникает, так как не установлен поставщик 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
Я пытаюсь импортировать данные из Excel в MySQl. Ниже приведен мой код, проблема в том, что он записывает только последнюю строку из моего листа Excel в MySQl DB, и я хочу, чтобы он импортировал все строки из моего листа Excel.
5 ответов
Ваш код в настоящее время хранит только последнюю пару и записывает это в базу данных. Вам нужно вызвать fname и lname внутри цикла и записать каждую пару отдельно в базу данных.
Вы можете изменить свой код к этому:
Примечание. Вы можете установить autocommit=True на этапе подключения. PyMySQL отключает автокоммит по умолчанию. Это означает, что вам не нужно звонить cursor.commit() после запроса.
Ваши значения переменных должны быть внутри инструкции for следующим образом:
Это то, что вы будете делать на регулярной основе? Я вижу сценарий, который вы пишете, но я не уверен, нужно ли вам запускать его снова и снова или вы просто импортируете данные в MySQL один раз.
Если это сделка с одним выстрелом, вы можете попробовать это.
Откройте электронную таблицу и ВЫБЕРИТЕ ВСЕ, затем Скопируйте все свои данные. Вставьте его в текстовый документ и сохраните текстовый документ (скажем, текстовый документ будет в c: \ temp \ exceldata.txt). Затем вы можете загрузить все это в таблицу с помощью одной команды:
Я делаю несколько предположений здесь:
- В электронной таблице есть только два столбца, и они находятся в том же порядке, что и поля в вашей таблице.
- Вам НЕ нужно очищать стол перед загрузкой. Если вы это сделаете, введите команду TRUNCATE TABLE report_table; до загрузки.
Обратите внимание, я выбрал формат с разделителями табуляции, потому что я предпочитаю его. Вы можете сохранить файл как файл .CSV и настроить команду следующим образом:
«Необязательно заключено в» есть, потому что Excel будет заключать в кавычки текстовые данные с запятой.
Если вам нужно делать это на регулярной основе, вы все равно можете использовать метод CSV, написав скрипт Excel, который сохраняет файл в копию .CSV при каждом сохранении электронной таблицы. Я тоже это сделал.
Я никогда не писал Python, но именно так я и делаю в PHP.
Извините, я не знаю много о базах данных, так же как и о pymysql. Но предполагается, что все остальное правильно, я думаю, это может работать так:
Этот код работал для меня после получения помощи от вышеупомянутого предложения, ошибка была отступа, теперь это работает 🙂