Как импортировать данные из excel в sql
В Microsoft SQL Server существует возможность в SQL запросах, на языке T-SQL, запрашивать данные из внешних источников, сегодня мы рассмотрим несколько способов обращения к файлу Excel из запроса с целью получения данных.
В языке T-SQL это можно реализовать с помощью так называемых «Распределенных запросов», которые предполагают использование следующих конструкций:
- OPENDATASOURCE
- OPENROWSET
- OPENQUERY
Сейчас мы рассмотрим все эти конструкции, и напишем несколько примеров.
Примечание! Загрузить данные из Excel можно и без использования языка T-SQL, в Microsoft SQL Server разработаны специальные службы SSIS (SQL Server Integration Services), в частности реализован инструмент по загрузке данных из разных источников «Импорт и экспорт данных», который позволяет загружать данные, уже не прибегая к использованию языка T-SQL.
Исходные данные и требования
В качестве примера давайте представим, что нам нужно обратиться к файлу «TestExcel.xls», который расположен на диске D. Сами данные расположены на листе со стандартным названием «Лист1», они имеют следующую структуру.
ProductId | CategoryId | ProductName | Price |
1 | 1 | Клавиатура | 100 |
2 | 1 | Мышь | 50 |
3 | 2 | Телефон | 300 |
Для того чтобы начать пользоваться распределенными запросами, нам сначала нужно включить данную возможность на SQL сервере, так как по умолчанию она выключена.
Включается возможность использования распределённых запросов с помощью системной хранимой процедуры sp_configure, которая отвечает за системные параметры сервера. Нам необходимо параметру Ad Hoc Distributed Queries присвоить значение 1, для этого выполните следующую SQL инструкцию.
Параметром show advanced options мы отображаем дополнительные параметры системной хранимой процедуры sp_configure, а командой RECONFIGURE мы применяем новые параметры.
Функция OPENDATASOURCE в T-SQL
OPENDATASOURCE – функция возвращает ссылку на источник данных, который может использоваться как часть четырехсоставного имени объекта.
Данная функция принимает два параметра, первый (provider_name) — это имя поставщика OLE DB, второй (init_string) – строка подключения.
Как видите, в функцию мы передали имя поставщика и строку подключения, а потом обратились к источнику, используя четырехсоставное имя объекта, т.е. к таблице на листе 1.
Функция OPENROWSET в T-SQL
OPENROWSET – функция подключается к источнику данных и выполняет необходимый запрос.
У данной функции уже много параметров, с помощью которых мы указываем все необходимые сведения о подключении, которые требуются для доступа к источнику данных OLE DB.
Для того чтобы обратиться к Excel, можно написать следующий запрос.
Вместо [Лист1$] можно в апострофах указать необходимый SQL запрос, например:
Функция OPENQUERY в T-SQL
OPENDATASOURCE и OPENROWSET удобно использовать, если Вам требуется разово получить данные из определённых внешних источников, в нашем случае из Excel, но если Вам требуется постоянно обращаться к таким источникам, то имеет смысл создать специальный объект в SQL Server – он называется «Связанный сервер» (Linked Server). Данный объект будет настроен на конкретный источник, и к этому объекту Вы сможете обращаться практически как к обычной базе данных, не указывая при этом строку подключения.
Связанный сервер настраивается один раз, иными словами, при его создании Вы указываете все необходимые настройки, а затем просто обращаетесь к нему по имени, которое Вы укажете.
Для обращения к связанным серверам в SQL сервере как раз и используется функция OPENQUERY.
OPENQUERY – функция обращается к связанному серверу и выполняет указанный запрос. На эту функцию можно даже ссылаться в инструкциях по модификации данных, т.е. мы можем изменять данные на связанном сервере.
Связанный сервер сначала нужно создать, это можно сделать с помощью системной процедуры sp_addlinkedserver, доступ к данному серверу настраивается процедурой sp_addlinkedsrvlogin. Связанные серверы также можно создавать и с помощью среды SQL Server Management Studio (Обозреватель объектов-> Объекты сервера-> Связанные серверы).
Более подробно про связанные серверы можете почитать в материале «Связанные серверы в MS SQL Server – создание и примеры использования».
После того как связанный сервер создан, в моем примере я его назвал TEST_EXCEL, мы можем к нему обратиться с помощью функции OPENQUERY.
Также к связанному серверу можно обратиться и без использования функции OPENQUERY, например:
Но рекомендовано к связанным серверам обращаться через функцию OPENQUERY.
Как я уже отмечал, функцию OPENQUERY можно использовать и для модификации данных на связанном сервере в инструкциях INSERT, UPDATE или DELETE, например, для обновления данных, в нашем случае инструкция будет выглядеть следующем образом.
Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.
Импортировать данные из файлов Excel в SQL Server или базу данных SQL Azure можно несколькими способами. Некоторые методы позволяют импортировать данные за один шаг непосредственно из файлов Excel. Для других методов необходимо экспортировать данные Excel в виде текста (CSV-файла), прежде чем их можно будет импортировать.
В этой статье перечислены часто используемые методы и содержатся ссылки для получения дополнительных сведений. Однако в ней не указано полное описание таких сложных инструментов и служб, как SSIS или Фабрика данных Azure. Дополнительные сведения об интересующем вас решении доступны по ссылкам ниже.
Список методов
Существует несколько способов импортировать данные из Excel. Для использования некоторых из этих инструментов может понадобиться установка SQL Server Management Studio (SSMS).
Для импорта данных из Excel можно использовать следующие средства:
Сначала экспортировать в текст (SQL Server и база данных SQL) | Непосредственно из Excel (только в локальной среде SQL Server) |
---|---|
Мастер импорта неструктурированных файлов | мастер импорта и экспорта SQL Server |
Инструкция BULK INSERT | Службы SQL Server Integration Services |
BCP | Функция OPENROWSET |
Мастер копирования (Фабрика данных Azure) | |
Фабрика данных Azure. |
Если вы хотите импортировать несколько листов из книги Excel, обычно нужно запускать каждое из этих средств отдельно для каждого листа.
Дополнительные сведения см. в разделе Ограничения и известные проблемы загрузки данных в файлы Excel или из них.
Мастер импорта и экспорта
Импортируйте данные напрямую из файлов Excel с помощью мастера импорта и экспорта SQL Server. Также можно сохранить параметры в виде пакета SQL Server Integration Services (SSIS), который можно настроить и использовать повторно в будущем.
В SQL Server Management Studio подключитесь к экземпляру SQL Server Компонент Database Engine.
Разверните узел Базы данных.
Щелкните базу данных правой кнопкой мыши.
Наведите указатель мыши на пункт Задачи.
Выберите Импортировать данные или Экспортировать данные:
Дополнительные сведения см. в следующих статьях:
Integration Services (SSIS)
Если вы работали с SQL Server Integration Services (SSIS) и не хотите запускать мастер импорта и экспорта SQL Server, создайте пакет SSIS, который использует в потоке данных источник "Excel" и назначение "SQL Server".
Дополнительные сведения см. в следующих статьях:
Чтобы научиться создавать пакеты 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 2019, но документация по этой функции не обновляется.)
Связанные серверы
Кроме того, можно настроить постоянное подключение от 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 см. в указанных ниже разделах.
Мастер копирования (ADF)
Импортируйте данные, сохраненные как текстовые файлы, с помощью пошаговой инструкции мастера копирования Фабрики данных Azure (ADF).
Как было описано выше в разделе Предварительное требование, необходимо экспортировать данные Excel в виде текста, прежде чем вы сможете использовать фабрику данных Azure для их импорта. Фабрика данных не может считывать файлы Excel напрямую.
Дополнительные сведения о мастере копирования см. в указанных ниже разделах.
Фабрика данных Azure
Если вы уже работали с фабрикой данных Azure и не хотите запускать мастер копирования, создайте конвейер с действием копирования из текстового файла в SQL Server или Базу данных SQL Azure.
Как было описано выше в разделе Предварительное требование, необходимо экспортировать данные Excel в виде текста, прежде чем вы сможете использовать фабрику данных Azure для их импорта. Фабрика данных не может считывать файлы Excel напрямую.
Дополнительные сведения об использовании этих источников и приемников фабрики данных см. в указанных ниже разделах.
Чтобы научиться копировать данные с помощью фабрики данных 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 и экспорта данных в Excel с помощью SQL Server Integration Services (SSIS).
В следующих разделах содержится вся информация, которая потребуется для успешной работы с приложением Excel из служб SSIS, а также для понимания и устранения распространенных проблем:
Средства, которые можно использовать.
Файлы, которые вам потребуются.
Сведения о подключении, которые необходимо предоставить, и параметры, которые необходимо настроить для загрузки данных в/из Excel с использованием SSIS.
-
в качестве источника данных.
- Укажите имя файла Excel и путь к нему.
- Выберите версию Excel.
- Укажите, содержит ли первая строка имена столбцов.
- Укажите лист или диапазон, который содержит данные.
Известные проблемы и ограничения.
- Проблемы с типами данных.
- Проблемы с импортом.
- Проблемы с экспортом.
Средства, которые можно использовать
Вы можете импортировать и экспортировать данные в Excel с использованием SSIS при помощи любого из следующих инструментов.
SQL Server Integration Services (SSIS) . Создайте пакет SSIS, настроив для него Excel в качестве источника или назначения с помощью диспетчера подключений Excel. (В этой статье не рассматривается создание пакетов SSIS.)
Получение файлов, необходимых для подключения к Excel
Прежде чем импортировать или экспортировать данные в Excel с использованием SSIS, вам может потребоваться скачать компоненты подключения для Excel, если они еще не установлены. По умолчанию компоненты подключений для Excel не устанавливаются.
Используйте таблицу из статьи Не удалось использовать интерфейсы Access ODBC, OLEDB или DAO за пределами Office Click-to-Run, чтобы понять, нужны ли дополнительные компоненты для вашей среды.
Примечание. Системные драйверы Office поддерживаются только в определенных случаях, конкретные руководства см. в статье Рекомендации по автоматизации Office на стороне сервера.
Назначение Excel в качестве источника данных
Сначала необходимо указать, что вам требуется установить подключение к Excel.
В службах SSIS
В службах SSIS создайте диспетчер соединений Excel для подключения к файлу источника или назначения Excel. Создать диспетчер соединений можно несколькими способами:
Щелкните правой кнопкой в области Диспетчеры соединений и выберите команду Создать соединение. В диалоговом окне Добавление диспетчера соединений со службами SSIS выберите EXCEL и затем Добавить.
В меню Службы SSIS выберите Создать соединение. В диалоговом окне Добавление диспетчера соединений со службами SSIS выберите EXCEL и затем Добавить.
Диспетчер соединений необходимо создавать в то время, как вы настраиваете источник Excel или назначение Excel на странице Диспетчер соединений в редакторе источника Excel или в редакторе назначения Excel.
В мастере импорта и экспорта SQL Server
В мастере импорта и экспорта на странице Выбор источника данных или Выбор назначения выберите Microsoft Excel в списке Источник данных.
Если вы не видите Excel в списке источников данных, убедитесь, что используется 32-разрядная версия мастера. Компоненты подключений для Excel обычно представляют собой 32-разрядные файлы и не отображаются в 64-разрядной версии мастера.
Файл Excel и путь к нему
В первую очередь необходимо предоставить имя файла Excel и путь к нему. Это можно сделать в редакторе диспетчера соединений Excel в пакете служб SSIS либо на странице Выбор источника данных или Выбор назначения в мастере импорта и экспорта.
Введите имя файла и путь к нему в следующем формате:
Для файла на локальном компьютере: C:\TestData.xlsx.
Для файла в общей сетевой папке: \\Sales\Data\TestData.xlsx.
Также можно нажать кнопку Обзор и выбрать электронную таблицу в диалоговом окне Открыть.
Подключить защищенный паролем файл Excel нельзя.
Версия Excel
Далее следует предоставить сведения о версии файла Excel. Это можно сделать в редакторе диспетчера соединений Excel в пакете служб SSIS либо на странице Выбор источника данных или Выбор назначения в мастере импорта и экспорта.
Выберите версию Microsoft Excel, в которой был создан файл, или другую совместимую версию. Например, если вам не удалось установить компоненты подключений для версии 2016, вы можете установить компоненты для версии 2010 и выбрать в этом списке вариант Microsoft Excel 2007-2010.
Если у вас установлены старые версии компонентов подключений, вы не сможете выбрать более новые версии Excel в этом списке. Список Версия Excel содержит все версии Excel, поддерживаемые службами SSIS. Наличие элементов в этом списке не означает, что необходимые компоненты подключений были установлены. Например, вариант Microsoft Excel 2016 будет присутствовать в этом списке даже в том случае, если у вас не установлены компоненты подключений для версии 2016.
Первая строка содержит имена столбцов
Если вы импортируете данные из Excel, далее необходимо указать, содержит ли первая строка данных имена столбцов. Это можно сделать в редакторе диспетчера соединений Excel в пакете служб SSIS или на странице Выбор источника данных в мастере импорта и экспорта.
- Если этот параметр отключен, поскольку в данных источника отсутствуют имена столбцов, мастер будет использовать в качестве заголовков столбцов значения F1, F2 и т. д.
- Если данные содержат имена столбцов, но этот параметр отключен, мастер импортирует строку имен столбцов как первую строку данных.
- Если данные не содержат имена столбцов, но этот параметр включен, мастер использует первую строку данных источника как имена столбцов. В этом случае первая строка в данных источника не включается в сами данные.
Если вы экспортируете данные из Excel и включили этот параметр, первая строка экспортированных данных будет включать имена столбцов.
Листы и диапазоны
В качестве источника или назначения для данных можно использовать три типа объектов Excel: лист, именованный диапазон или неименованный диапазон ячеек, который задается с помощью адреса.
Лист Чтобы указать лист, добавьте в конец имени листа символ $ и окружите строку разделителями, например [Листt1$] . Также можно выполнить поиск имени, заканчивающегося символом $ , в списке существующих таблиц и представлений.
Именованный диапазон Чтобы указать именованный диапазон, используйте имя диапазона, например Мой_диапазон. Также можно выполнить поиск имени, не заканчивающегося символом $ , в списке существующих таблиц и представлений.
Неименованный диапазон Чтобы указать диапазон ячеек, которым не были заданы имена, добавьте символ $ после имени листа, добавьте спецификацию диапазона и окружите строку разделителями, например [Лист1$A1:B4] .
Чтобы выбрать или указать тип объекта Excel, который вы хотите использовать в качестве источника или назначения для данных, выполните одно из следующих действий:
В службах SSIS
В службах SSIS на странице Диспетчер соединений в редакторе источника Excel или редакторе назначения Excel выполните одно из следующих действий:
Чтобы использовать лист или именованный диапазон, выберите Таблица или представление в разделе Режим доступа к данным. Затем выберите лист или именованный диапазон в списке Имя листа Excel.
Чтобы использовать неименованный диапазон, который задается по адресу, выберите Команда SQL в разделе Режим доступа к данным. Затем в поле Текст команды SQL введите запрос, аналогичный представленному ниже примеру:
В мастере импорта и экспорта SQL Server
В мастере импорта и экспорта выполните одно из следующих действий:
При импорте из Excel выполните одно из следующих действий:
Чтобы использовать лист или именованный диапазон, на странице Выбор копирования таблицы или запроса выберите Скопировать данные из одной или нескольких таблиц или представлений. Затем на странице Выбор исходных таблиц и представлений в столбце Источник укажите исходные листы и именованные диапазоны.
Чтобы использовать неименованный диапазон, который задается по адресу, на странице Выбор копирования таблицы или запроса выберите Написать запрос, указывающий данные для передачи. Затем на странице Определение исходного запроса введите запрос, аналогичный показанному ниже:
При экспорте в Excel выполните одно из следующих действий:
Чтобы использовать лист или именованный диапазон, на странице Выбор исходных таблиц и представлений в столбце Назначение выберите целевые листы и именованные диапазоны.
Чтобы использовать неименованный диапазон, который задается по адресу, на странице Выбор исходных таблиц и представлений в столбце Назначение укажите диапазон в следующем формате без использования разделителей: Sheet1$A1:B5 . Мастер автоматически добавит разделители.
После выбора или ввода объектов Excel для импорта или экспорта вы также можете выполнить следующие действия на странице Выбор исходных таблиц и представлений мастера:
Проверить сопоставления столбцов между источником и назначением, выбрав команду Изменить сопоставления.
Выполнить предварительный просмотр данных, чтобы убедиться в правильности выбора, с помощью команды Предварительный просмотр.
Проблемы с типами данных
Типы данных
Драйвер Excel распознает только ограниченный набор типов данных. Например, все числовые столбцы воспринимаются как тип double (DT_R8), а все строковые столбцы (кроме столбцов типа memo) воспринимаются как строки в Юникоде длиной 255 символов (DT_WSTR). Службы SSIS сопоставляют типы данных Excel следующим образом:
Числовой — с плавающей запятой двойной точности (DT_R8)
Денежный — денежный (DT_CY)
Логический — логический (DT_BOOL)
Дата и время — datetime (DT_DATE)
Строковый — строка в Юникоде длиной в 255 символов (DT_WSTR)
Memo — текстовый поток в Юникоде (DT_NTEXT)
Преобразования типов данных и длины по умолчанию
В службах SSIS неявное преобразование типов данных не выполняется. В результате, возможно, потребуется использовать преобразование "Производный столбец" или "Преобразование данных" для явного преобразования данных Excel до их загрузки в назначение, отличное от Excel, либо для преобразования данных из источника, отличного от Excel, до их загрузки в назначение Excel.
Ниже приведены некоторые примеры преобразований, которые могут потребоваться:
Преобразование между строковыми столбцами Excel в Юникоде и строковыми столбцами в формате с конкретными кодовыми страницами, отличными от Юникода.
Преобразование между строковыми столбцами Excel длиной в 255 символов и строковыми столбцами другой длины.
Преобразование между числовыми столбцами Excel с плавающей запятой двойной точности и числовыми столбцами других типов.
Если вы используете мастер импорта и экспорта и вам требуется выполнить некоторые из этих преобразований данных, мастер выполнит настройку необходимых преобразований автоматически. В результате, даже если вам требуется использовать пакет служб SSIS, часто бывает полезно создать первичный пакет с помощью мастера импорта и экспорта. В таком случае мастер создаст и настроит диспетчеры соединений, источники, преобразования и назначения автоматически.
Проблемы с импортом
Пустые строки
Если в качестве источника указан лист или диапазон, драйвер считывает непрерывный блок ячеек, начиная с первой непустой ячейки в верхнем левом углу листа или диапазона. В результате данные могут начинаться не со строки 1, однако в данных источника не должны присутствовать пустые строки. Например, пустые строки не допускаются между заголовками столбцов и строками данных, а также после названия в верхней части листа.
Если над данными располагаются пустые строки, вы не сможете выполнить их запрос в качестве листа. В Excel необходимо выбрать диапазон данных, присвоить ему имя и выполнить запрос к именованному диапазону, а не к листу.
Отсутствующие значения
Драйвер Excel считывает определенное количество строк (по умолчанию 8 строк) в указанном источнике для определения типа данных каждого столбца. Если столбец содержит смешанные типы данных, особенно если числовые данные смешаны с текстовыми данными, драйвер принимает решение в пользу того типа данных, которого больше, и возвращает значения NULL в ячейки, содержащие данные другого типа. (В случае равенства преимущество получает числовой тип.) Большинство параметров форматирования ячеек в листе Excel не затрагивает это определение типа данных.
Можно изменить поведение драйвера Excel, указав режим импорта для импорта всех значений в виде текста. Чтобы указать режим импорта, добавьте IMEX=1 к значению расширенных свойств в строке соединения диспетчера соединений с Excel в окне "Свойства".
Усеченный текст
Когда драйвер определяет, что столбец Excel содержит текстовые данные, он выбирает тип данных (строковый или memo) на основании самого длинного значения. Если драйвер не обнаруживает значений длиннее 255 символов в выбираемых строках, он считает, что столбец является строковым с длиной 255 символов, а не столбцом типа memo. Поэтому значения длиннее 255 символов могут быть усечены.
Чтобы импортировать данные из столбца типа memo без усечения, можно воспользоваться любым из двух способов:
Убедитесь, что столбец типа memo как минимум в одной из выбранных строк содержит значение длиной более 255 символов.
Увеличьте число строк в выборке драйвера, чтобы включить такую строку. Чтобы увеличить количество строк, включаемых в выборку, достаточно увеличить значение TypeGuessRows в следующем разделе реестра:
Проблемы с экспортом
Создание нового файла назначения
В службах SSIS
Создайте диспетчер соединений Excel с именем создаваемого файла Excel и путем к нему. Затем в редакторе назначения Excel в разделе Имя листа Excel выберите Создать, чтобы создать лист назначения. В этот момент службы SSIS создают новый файл Excel с указанным листом.
В мастере импорта и экспорта SQL Server
На странице Выбор назначения нажмите кнопку Обзор. В диалоговом окне Открыть перейдите в папку, в которой требуется создать новый файл Excel, укажите имя этого файла и выберите Открыть.
Экспорт в достаточно большой диапазон
Экспорт длинных текстовых значений
Чтобы успешно сохранять в столбцы Excel строки, имеющие длину более 255 символов, драйвер должен распознать тип данных целевого столбца как memo , а не как string.
- Если в существующей целевой таблице уже содержатся строки данных, то в столбце типа memo в первых нескольких строках, которые проверит драйвер, должен содержаться, по крайней мере, один экземпляр значения, имеющего длину более 255 символов.
См. также
Дополнительные сведения о компонентах и процедурах, описываемых в этой статье, см. в следующих статьях:
Сведения о том, что следует ожидать от мастера экспорта и импорта SQL Server, на примере типичного сценария — импорта данных из электронной таблицы Excel в базу данных SQL Server. Даже если вы планируете использовать другой источник и другое назначение, в этом разделе вы найдете основную часть сведений о работе с мастером.
Предварительное требование — у вас на компьютере установлен этот мастер?
Если вы хотите запустить мастер, но на вашем компьютере не установлен Microsoft SQL Server, мастер импорта и экспорта SQL Server можно установить с помощью SQL Server Data Tools (SSDT). Дополнительные сведения см. в разделе Скачивание SQL Server Data Tools (SSDT).
Исходные данные для этого примера Excel
Это исходные данные, которые вы собираетесь скопировать, — небольшая таблица из двух столбцов на листе WizardWalkthrough в книге WizardWalkthrough.xlsx Excel.
Целевая база данных SQL Server для этого примера
Это целевая база данных SQL Server (в SQL Server Management Studio), куда вы собираетесь скопировать исходные данные. Целевая таблица не существует, поэтому вы позволите мастеру создать ее для вас.
Шаг 1. Запуск мастера
Запустите мастер из группы "Microsoft SQL Server 2016" в меню "Пуск" Windows.
Для этого примера выберите 32-разрядный мастер, так как у вас установлена 32-разрядная версия Microsoft Office. Поэтому нужно использовать 32-разрядный поставщик данных для подключения к Excel. Для многих других источников данных в большинстве случаев можно выбрать 64-разрядный мастер.
Чтобы использовать 64-разрядную версию мастера экспорта и импорта SQL Server, нужно установить SQL Server. SQL Server Data Tools (SSDT) и SQL Server Management Studio (SSMS) являются 32-разрядными приложениями и устанавливают только 32-разрядные файлы, включая 32-разрядную версию мастера.
Шаг 2. Просмотр страницы приветствия
Первая страница мастера — это страница приветствия.
Если вы не хотите, чтобы она открывалась снова, установите флажок Больше не показывать это окно.
Шаг 3. Выбор Excel в качестве источника данных
На следующей странице Выбор источника данных выберите Microsoft Excel в качестве источника данных. Затем перейдите к файлу Excel и выберите его. Наконец, укажите версию Excel, которую вы использовали для создания файла.
Дополнительные сведения о подключении к файлам Excel, а также об ограничениях и известных проблемах, связанных с загрузкой данных в файлы этого приложения и из них, см. в разделе Загрузка данных в приложение Excel или из него с помощью служб SQL Server Integration Services (SSIS).
Дополнительные сведения об этой странице мастера см. в разделе Выбор источника данных.
Шаг 4. Выбор SQL Server в качестве назначения
На странице отображается список свойств поставщика. Многие из них могут быть вам незнакомы или иметь непонятные имена. К счастью, для подключения к любой корпоративной базе данных, как правило, достаточно указать лишь три параметра. Остальные параметры можно пропустить.
Необходимые сведения | Свойство "Поставщик данных .NET Framework для SQL Server" |
---|---|
Имя сервера | Источник данных |
Сведения для проверки подлинности (имя входа) | Встроенная система безопасности или Идентификатор пользователя и Пароль Чтобы открыть раскрывающийся список баз данных на сервере, сначала нужно указать действительные данные для входа. |
Имя базы данных | Исходный каталог |
Дополнительные сведения о подключении к SQL Server см. в разделе Подключение к источнику данных SQL Server. Дополнительные сведения об этой странице мастера см. в разделе Выбор назначения.
Шаг 5. Копирование таблицы вместо написания запроса
На следующей странице Выбор копирования таблицы или запроса укажите, что нужно скопировать всю таблицу исходных данных. Вам не нужно создавать запрос на языке SQL, чтобы выбрать данные для копирования.
Дополнительные сведения об этой странице мастера см. в разделе Выбор копирования таблицы или запроса.
Шаг 6. Выбор копируемой таблицы
На следующей странице Выбор исходных таблиц и представлений выберите таблицу или таблицы, которые необходимо копировать из источника данных. Затем следует сопоставить каждую из выбранных исходных таблиц с новой или существующей целевой таблицей.
В этом примере по умолчанию мастер сопоставил лист WizardWalkthrough$ в столбце Source с новой таблицей с таким же именем в назначении SQL Server. (Книга Excel содержит всего один рабочий лист.)
- Знак доллара ($) на имени исходной таблицы обозначает лист Excel. (Именованный диапазон в Excel представлен лишь своим именем.)
- Звезда на значке целевой таблицы указывает, что мастер создаст новую целевую таблицу.
Может потребоваться удалить знак доллара ($) из имени новой целевой таблицы.
Дополнительные сведения об этой странице мастера см. в разделе Выбор исходных таблиц и представлений.
Необязательный шаг 7. Проверка сопоставлений столбцов
Прежде чем закрыть страницу Выбор исходных таблиц и представлений, вы можете нажать кнопку Изменить сопоставления, чтобы открыть диалоговое окно Сопоставления столбцов. Здесь в таблице Сопоставления видно, как мастер будет сопоставлять столбцы на исходном листе со столбцами в целевой таблице.
Дополнительные сведения об этой странице мастера см. в разделе Сопоставления столбцов.
Необязательный шаг 8. Просмотр инструкции CREATE TABLE
Хотя диалоговое окно Сопоставления столбцов открыто, вы можете нажать кнопку Изменить SQL, чтобы открыть диалоговое окно Инструкция SQL Create Table. Вы увидите инструкцию CREATE TABLE, созданную мастером для создания целевой таблицы. Обычно менять эту инструкцию не требуется.
Дополнительные сведения об этой странице мастера см. в разделе Инструкция SQL Create Table.
Необязательный шаг 9. Просмотр данных для копирования
Нажав кнопку ОК для закрытия диалогового окна Инструкция SQL Create Table и кнопку ОК для закрытия диалогового окна Сопоставления столбцов, вы вернетесь на страницу Выбор исходных таблиц и представлений. При необходимости нажмите кнопку Предварительный просмотр, чтобы просмотреть образец данных, которые скопирует мастер. В этом примере он выглядит правильно.
Дополнительные сведения об этой странице мастера см. в разделе Предварительный просмотр данных.
Шаг 10. Требуется запустить операцию импорта-экспорта
На следующей странице Сохранение и запуск пакета оставьте параметр Запустить немедленно включенным, чтоб скопировать данные сразу после нажатия кнопки Готово на следующей странице. Либо вы можете пропустить следующую страницу, нажав кнопку Готово на странице Сохранение и запуск пакета.
Дополнительные сведения об этой странице мастера см. в разделе Сохранение и запуск пакета.
Шаг 11. Завершение работы с мастером и запуск операции импорта-экспорта
Дополнительные сведения об этой странице мастера см. в разделе Завершение работы мастера.
Шаг 12. Просмотр результатов работы мастера
На последней странице наблюдайте, как мастер выполняет каждую задачу, а затем просмотрите результаты. Выделенная строка указывает, что мастер успешно скопировал ваши данные. Готово!
Дополнительные сведения об этой странице мастера см. в разделе Выполнение операции.
Новая таблица данных, скопированная в SQL Server
Здесь видна новая целевая таблица (В SQL Server Management Studio), созданная мастером в SQL Server.
Здесь (опять в SSMS) видны данные, которые мастер скопировал в SQL Server.
Дополнительные сведения
Дополнительные сведения о работе мастера.
Дополнительные сведения о мастере. Обзор мастера см. в статье Импорт и экспорт данных с помощью мастера импорта и экспорта SQL Server.
Дополнительные сведения о шагах в мастере. Если вам нужна информация о процедурах, выполняемых в мастере, выберите нужную страницу в списке в разделе Шаги в мастере импорта и экспорта SQL Server. Каждой странице мастера соответствует отдельная страница документации.
Сведения о подключении к источникам данных и назначениям. Сведения о подключении к данным см. на соответствующей странице, выбрав ее в списке в разделе Подключение к источникам данных с помощью мастера импорта и экспорта SQL Server. Для каждого распространенного источника данных имеется отдельная страница документации.
Дополнительные сведения о загрузке файлов в приложение Excel и из него. Сведения о подключении к файлам Excel, а также об ограничениях и известных проблемах, связанных с загрузкой данных в файлы этого приложения и из них, см. в разделе Загрузка данных в приложение Excel или из него с помощью служб SQL Server Integration Services (SSIS).
Microsoft SQL Server позволяет встроенными средствами языка T-SQL в SQL запросе импортировать данные из файла Excel в базу данных. Сегодня я подробно расскажу, как это делается, какие условия необходимо выполнить, чтобы эта операция проходила успешно, расскажу про особенности импорта для самых распространённых случаев конфигураций SQL сервера и приведу конкретный порядок действий и практические примеры.
Начну я с того, что импортировать данные из Excel в Microsoft SQL Server можно с помощью «Распределенных запросов» и с помощью «Связанных серверов». Это, скорей всего, Вы уже знаете, так как я уже не раз писал об этом (ссылки на соответствующие материалы указаны чуть выше).
Обратиться к файлу Excel и импортировать данные в Microsoft SQL Server можно с помощью T-SQL инструкций OPENDATASOURCE, OPENROWSET или OPENQUERY.
Однако в вышеупомянутых статьях я упустил несколько важных моментов, одним из которых является то, что у всех конфигурация SQL сервера разная, за счет чего у многих возникают различные проблемы и появляются ошибки во время выполнения распределенных запросов и обращений к связанным серверам. Также я описывал способ загрузки данных из Excel, который на сегодняшний день уже устарел, поэтому сегодня я постараюсь дать Вам немного больше информации о том, как импортировать данные из файла Excel в Microsoft SQL Server на языке T-SQL.
Введение
Итак, как я уже сказал, очень важную роль здесь играет конфигурация SQL сервера, в частности, какая версия сервера установлена, x86 или x64.
Если говорить о последних версиях Microsoft SQL Server 2016-2019, то они только x64 и устанавливаются на 64-разрядные версии Windows.
Исходя из этого я разделю статью на несколько частей, в каждой из которых я расскажу про особенности импорта данных из Excel для самых распространённых случаев конфигураций и приведу конкретный порядок действий.
Для того чтобы быстро узнать, какая версия SQL Server установлена у Вас на компьютере, можете выполнить простой SQL запрос
Обращение к файлу Excel и, соответственно, импорт данных в Microsoft SQL Server происходит с помощью специальных провайдеров (поставщиков). Для работы с Excel в Microsoft SQL Server обычно используются:
Во всех примерах ниже я буду посылать простой запрос SELECT на выборку данных из Excel файла, для того чтобы проверить доступ к данным в файле Excel. Чтобы осуществить импорт данных (загрузить данные в БД), Вы можете использовать любой удобный для Вас способ, например, конструкцию SELECT INTO или INSERT INTO.
Дополнительно рекомендовано закрывать файл Excel во время обращения к нему в распределенных запросах, а также указывать путь к файлу без пробелов (хотя современный SQL сервер умеет работать с пробелами).
Импорт данных из Excel 2003 (файл xls) в Microsoft SQL Server x86
Шаг 1 – Проверяем наличие провайдера Microsoft.Jet.OLEDB.4.0 на SQL Server
В результирующем наборе данных должна присутствовать строка с Microsoft.Jet.OLEDB.4.0. Если такого провайдера нет, то скорей всего в системе нет установленного Excel 2003 и, соответственно, его нужно установить.
Шаг 2 – Предоставление прав пользователю на временный каталог
Особенностью распределённых запросов и работы со связанным серверами Excel в x86 версиях SQL Server является то, что независимо от имени какой учетной записи посылается SQL запрос к Excel, эта учетная запись должна иметь права на запись во временный каталог той учетной записи, под которой работает сама служба SQL Server.Так как поставщик OLE DB создает временный файл во время запроса во временном каталоге SQL Server, используя учетные данные пользователя, выполняющего запрос.
Таким образом, если служба SQL Server работает от имени или локальной, или сетевой службы, необходимо дать соответствующие права на временный каталог этих служб всем пользователям, которые будут посылать распределенные запросы и обращаться к связанному серверу Excel (если сервер работает от имени пользователя, который посылает SQL запросы, то такие права давать не требуется, они у него уже есть).
Это можно сделать с помощью встроенной утилиты командной строки icacls.
Например, для локальной службы команда будет выглядеть следующим образом.
Для сетевой службы
Вместо UserName укажите имя пользователя, который посылает запрос.
Шаг 3 – Включаем распределенные запросы на SQL Server
По умолчанию возможность использования распределённых запросов, в частности функций OPENDATASOURCE и OPENROWSET, в Microsoft SQL Server запрещена, поэтому данную возможность нужно сначала включить.
Она включается с помощью системной хранимой процедуры sp_configure, которая отвечает за системные параметры сервера. Нам необходимо параметру Ad Hoc Distributed Queries присвоить значение 1, для этого выполняем следующую SQL инструкцию.
Шаг 4 – Выполняем SQL запрос, обращение к файлу Excel
Ниже я приведу несколько вариантов обращения к файлу Excel (TestExcel.xls).
OPENROWSET
OPENDATASOURCE
Linked Server
Импорт данных из Excel 2007 и выше (файл xlsx) в Microsoft SQL Server x86
Шаг 1 – Проверяем наличие провайдера Microsoft.ACE.OLEDB.12.0 на SQL Server
Точно так же, как и в предыдущем примере, сначала проверяем, установлен ли у нас необходимый нам провайдер, в данном случае нам нужен Microsoft.ACE.OLEDB.12.0.
Шаг 2 – Установка провайдера Microsoft.ACE.OLEDB.12.0 (32-bit)
Если провайдера нет, то его необходимо установить.
Вот ссылка на скачивание провайдера
Выберите и скачайте файл, соответствующий архитектуре x86 (т.е. в названии без x64).
Шаг 3 – Предоставление прав пользователю на временный каталог
В данном случае также даем права на временный каталог локальной или сетевой службы всем пользователям, которые будут посылать SQL запросы к файлу Excel.
Используем все ту же утилиту командной строки icacls.
Для локальной службы
Вместо UserName укажите имя пользователя, который посылает запрос.
Шаг 4 – Включаем распределенные запросы на SQL Server
Включаем возможность использования OPENDATASOURCE и OPENROWSET на Microsoft SQL Server, повторюсь, что по умолчанию данная возможность отключена.
Шаг 5 – Настройка провайдера Microsoft.ACE.OLEDB.12.0
В данном случае дополнительно потребуется настроить провайдер Microsoft.ACE.OLEDB.12.0. Для этого включим следующие параметры провайдера (для отключения укажите 0 вместо 1).
Если не включать данные параметры, то, скорей всего, появится ошибка примерно следующего содержания
Шаг 6 – Выполняем SQL запрос, обращение к файлу Excel
Примеры обращения к файлу Excel (TestExcel.xlsx).
OPENROWSET
OPENDATASOURCE
Linked Server
Импорт данных из Excel (любые файлы) в Microsoft SQL Server x64
Шаг 1 – Проверяем наличие провайдера Microsoft.ACE.OLEDB.12.0 на SQL Server
Шаг 2 – Установка провайдера Microsoft.ACE.OLEDB.12.0 (64-bit)
В случае, если провайдер не установлен, его необходимо скачать и установить.
Скачиваем файл x64.
Шаг 3 – Включаем распределенные запросы на SQL Server
Необходимость включения возможности использования распределенных запросов (OPENDATASOURCE и OPENROWSET) на Microsoft SQL Server x64 также есть, поэтому сначала включаем ее, выполнив точно такую же инструкцию.
Шаг 4 – Настройка провайдера Microsoft.ACE.OLEDB.12.0
Шаг 5 – Выполняем SQL запрос, обращение к файлу Excel
Здесь используются точно такие же параметры в SQL запросах, что и в предыдущем примере. Для удобства продублирую их еще раз.
Примеры обращения к файлу Excel (TestExcel.xlsx).
OPENROWSET
OPENDATASOURCE
Linked Server
Подведение итогов
Ну и в заключение я сгруппирую действия, которые необходимо выполнять в зависимости от выпуска SQL Server (x68 или x64) и версии файла Excel (xls или xlsx), в одну таблицу, для Вашего удобства.
Действие / Настройка | Импорт Excel 2003 (файл xls) в SQL Server x86 | Импорт Excel 2007 (файл xlsx) в SQL Server x86 | Импорт Excel (любые файлы) в SQL Server x64 |
Установка Excel 2003 | Да | Нет | Нет |
Установка провайдера Microsoft.ACE.OLEDB.12.0 | Нет | Да (x86) | Да (x64) |
Предоставление прав на временный каталог служб (если SQL сервер работает от имени служб) | Да | Да | Нет |
Настройка провайдера Microsoft.ACE.OLEDB.12.0 | Нет | Да | Нет (по необходимости) |
Параметры подключения в SQL запросах | Microsoft.Jet. OLEDB.4.0 и Excel 8.0 | Microsoft.ACE. OLEDB.12.0 и Excel 12.0 | Microsoft.ACE. OLEDB.12.0 и Excel 12.0 |
Включение распределённых запросов на SQL Server | Да | Да | Да |
Заметка! Для комплексного изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения и рассматриваются все конструкции языка SQL и T-SQL.
Читайте также: