Как из sql выгрузить в excel

Export Data to Excel from Master Data Services

В надстройке Master Data Services для Excel необходимо экспортировать данные из репозитория MDS, чтобы работать с ним.

Если вы хотите отфильтровать набор данных перед загрузкой, ознакомьтесь с разделом «Фильтрация данных перед экспортом» (надстройка MDS для Excel).

Предварительные условия

Для выполнения этой процедуры:

Необходимо иметь разрешение на доступ к функциональной области Обозреватель .

Экспорт данных из MDS в Excel

Откройте Excel и на вкладке Основные данные установите соединение с репозиторием MDS. Дополнительные сведения см. в разделе «Подключение к репозиторию MDS» (надстройка MDS для Excel).
На панели Обозреватель основных данных выберите модель и версию. Заполняется список сущностей.
- Если панель Обозреватель основных данных не видна, в группе Соединение и загрузка нажмите Показать обозреватель.
- Если панель Обозреватель основных данных отключена, то причина этого заключается в том, что существующий лист уже содержит данные, управляемые MDS. Чтобы включить эту панель, откройте новый лист.
В области Обозреватель основных данных в списке сущностей дважды щелкните сущность, которую нужно загрузить.

В Excel загружается только первый миллион элементов. Чтобы отфильтровать список перед загрузкой, на ленте в группе Подключение и загрузка нажмите кнопку Фильтр.
В столбцах, которые являются ограниченными списками (атрибутами на основе домена), по умолчанию загружаются только первые 25 000 значений. Это число можно изменить в свойстве MaximumDbaEntitySize в файле excelusersettings.config, расположенном на компьютере, на котором установлена программа Excel. Этот файл находится в папке C:\Users\\AppData\Local\Microsoft\SQL Server\130\MasterDataServices\. Если количество значений для атрибута на основе домена превышает настройку свойства MaximumDbEntitySize, список значений не загружается.

Если при загрузке разделенных текстом данных с помощью надстройки для Microsoft Excel в 32-разрядную версию Excel свойствам Число ячеек для загрузки и Число ячеек для публикации присвоено максимальное значение 1000, возникнет ошибка нехватки памяти. Для использования максимальных значений свойств Число ячеек для загрузки и Число ячеек для публикациинеобходимо использовать 64-разрядную версию Excel.

Импорт данных в SQL Server или базу данных Azure из Excel

Импортировать данные из файлов 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), который можно настроить и повторно использовать позже.

Start wizard SSMS

В SQL Server Management Studio подключитесь к экземпляру ядро СУБД SQL Server.
Разверните узел Базы данных.
Щелкните базу данных правой кнопкой мыши.
Выберите «Задачи«.
Выберите Импортировать данные или Экспортировать данные:

Connect to an Excel data source

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

Запуск мастера импорта и экспорта SQL Server
Приступая к работе с простым примером мастера импорта и экспорта

Службы Integration Services (SSIS)

Если вы работали с SQL Server Integration Services (SSIS) и не хотите запускать мастер импорта и экспорта SQL Server, создайте пакет SSIS, который использует в потоке данных источник «Excel» и назначение «SQL Server».

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

Источник Excel
Назначение SQL Server

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

Components in the data flow

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»).

sp_configure 'show advanced options', 1; RECONFIGURE; GO sp_configure 'ad hoc distributed queries', 1; RECONFIGURE; GO

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

USE ImportFromExcel; GO SELECT * INTO Data_dq FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=C:\Temp\Data.xlsx', [Sheet1$]); GO

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

USE ImportFromExcel; GO SELECT * INTO Data_dq FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:\Temp\Data.xlsx;Extended Properties=Excel 12.0'). [Sheet1$]; GO

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

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

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

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

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

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

USE ImportFromExcel; GO SELECT * INTO Data_ls FROM EXCELLINK. [Data$]; GO

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

DECLARE @RC INT; DECLARE @server NVARCHAR(128); DECLARE @srvproduct NVARCHAR(128); DECLARE @provider NVARCHAR(128); DECLARE @datasrc NVARCHAR(4000); DECLARE @location NVARCHAR(4000); DECLARE @provstr NVARCHAR(4000); DECLARE @catalog NVARCHAR(128); -- Set parameter values SET @server = 'EXCELLINK'; SET @srvproduct = 'Excel'; SET @provider = 'Microsoft.ACE.OLEDB.12.0'; SET @datasrc = 'C:\Temp\Data.xlsx'; SET @provstr = 'Excel 12.0'; EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider, @datasrc, @location, @provstr, @catalog;

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

Создание связанных серверов
OPENQUERY

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

Предварительное требование — сохранение данных 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.

USE ImportFromExcel; GO BULK INSERT Data_bi FROM 'C:\Temp\data.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ); GO

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

Массовый импорт данных с помощью инструкции BULK INSERT или OPENROWSET(BULK. )
BULK INSERT

Средство BCP

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

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

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

bcp.exe ImportFromExcel..Data_bcp in "C:\Temp\data.csv" -T -c -t ,

Дополнительные сведения о BCP см. в следующих статьях:

Массовый импорт и экспорт данных с помощью программы bcp
bcp Utility
Подготовка данных к массовому экспорту или импорту

Мастер копирования (ADF)

Импортируйте данные, сохраненные как текстовые файлы, с помощью пошаговой инструкции мастера копирования Фабрики данных Azure (ADF).

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

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

Мастер копирования фабрики данных
Руководство. Создание конвейера с действием копирования с помощью мастера копирования фабрики данных.

Azure Data Factory

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

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

Файловая система
SQL Server
База данных SQL Azure

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

Перемещение данных с помощью действия копирования
Руководство. Копирование данных из хранилища BLOB-объектов Azure в базу данных SQL с помощью фабрики данных

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

Microsoft.ACE.OLEDB.12.0″ не зарегистрировано

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

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

Msg 7403, Level 16, State 1, Line 3 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.

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

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

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1; EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1;

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

Msg 7302, Level 16, State 1, Line 3 Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

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

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

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

Msg 7438, Level 16, State 1, Line 3 The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server.

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

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

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

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

Msg 7399, Level 16, State 1, Line 3 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 3 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Следующие шаги

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