Загрузка из Excel в MS SQL Server (несколько способов)

Несколько примеров работы из MS SQL Server  с таблицами формата Excel(.xls,.xlsx):

1)Сиспользованием функции OPENROWSET или с OPENDATASOURCE:

SELECT * FROM OPENROWSET('MSDASQL',

'Driver={Microsoft Excel Driver (*.xls)};

DBQ=[C:\gr_otchet.xls]', 'SELECT * FROM [Sheet1$A8:D10000]'

Пример для OPENDATASOURCEиз BOL:

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'DataSource=C:\DataFolder\Documents\TestExcel.xls;Extended Properties=EXCEL 5.0')...[Sheet1$] ;

Одна израспространенных  проблем, это отсутствиедрайверов под х64 платформу, или установка х32 битных под х64 систему. Например,драйверов Microsoft.Jet.OleDB нет 64 битных,в этом случае  можно использовать другие драйвера, к примеру Microsoft.ACE.OLEDB.12.0 .

Не забудьте только проПримечание из  BOL:

ФункцияOPENROWSET  или  OPENDATASOURCE может быть использованадля доступа к удаленным данным из источников данных OLE DB только в том случае,если для заданного поставщика параметр реестра DisallowAdhocAccess явноустановлен в 0 и включен параметр Ad Hoc Distributed Queries расширеннойнастройки. Если эти параметры не установлены, поведение по умолчанию запрещаетнерегламентированный доступ.

Если параметрAd Hoc Distributed Queries выключен, то  об будет информационное сообщение. Включениепараметра осуществляется через хранимую процедуру sp_configure.

sp_configure 'Ad Hoc Distributed Queries', 1;

RECONFIGURE;

GO

2) Второй спосб через Linkedserverи ODBC драйвер.

1-ый способхорошо, когда необходимо использовать разово, для частого и широкоиспользования лучше использовать технологию связанного сервера(Linked Server)

Для этогонеобходимо установить на сервере MS SQL Server ODBC драйвер а для Excel, затем создать источникданных( Администрирование ->Источники данных)

Указывает имя источника данных,версию Excel и самфайл.

Сохраняем источник.

После этого создаем связанныйсервер LinkedServed (связанныйсервер):

Указываем имя нашего  связанного сервера и имя созданного намираннего ODBC источника.

Сохраняем.

Теперь можно выполнять запросы кнашему связанному серверу, к примеру:

select * from openquery(excel,'select * from [sheet1$]') – получение всех данныз из экселя

select * from openquery(excel,'select * from[Sheet1$A10:D2]') – получение данных диапозона $A10:D2

select * from openquery(excel,'select * from[Sheet1$A10:D]') – получение данных диапозона с A10:D доконца файла.

3) Еще одни,способ, когда необходим импорт разово, то можно использовать «SQL Server Import and Export Wizard»:

Выделяем БД,  правая кнопка, Задачи, Выбираем пункт Импортили Экспорт:

Выбираем источник данных, нашфайл Excel, версию Excel-я. :

Выбираем кудакопировать данные, указываем таблицу назначение .

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

4) Кстати, 4 способ, это как раз создание пакета SSIS в Microsoft Visual Studio, результатом котороготак же будет пакет, похожий на то, что было создано в варианте 3

Делается онпросто

Выбирается Элемент потокауправления

Затем выбирается источник исервер назначения:

В источнике соединений создаетсяновое соединение с нашим файлом Excel,в  Назначение указываем наш MS SQL Server, указываем таблицу,сопоставляем столбцы:

После этого сохраняем пакет, иего запускаем.

Пакет создали  и должен работать.

Удачи .

Автор:Вячеслав на22:49

Ярлыки:Excel Linked Server OPENDATASOURCE OPENROWSET