Подключение файлов MS Excel — различия между версиями

Материал из Oktell
Перейти к: навигация, поиск
 
(не показаны 32 промежуточные версии этого же участника)
Строка 1: Строка 1:
−
[[Дополнительно|Наверх]]
+
[[Работа с БД Oktell|Наверх]]
  
−
В данной статье описывается пример подключения Excel-файла sample.xlsx на примере сервера MSSQL 2008 SP3 x64.
+
__TOC__
  
−
'''Шаг 1.''' Для начала работы скачайте провайдера '''Microsoft.ACE.OLEDB.12.0''' по ссылке http://www.microsoft.com/en-us/download/details.aspx?id=13255.  
+
===Подключение с помощью Microsoft.ACE.OLEDB.12.0===
 +
 
 +
Провайдер данных Microsoft.ACE.OLEDB.12.0 подходит как для 32-битных, так и для 64 -битных версий SQL-сервера.
 +
 
 +
В данном параграфе описывается пример подключения Excel-файла '''sample.xlsx''' как связанного сервера на примере '''MSSQL 2008 SP3 x64'''.
 +
 
 +
'''Шаг 1.''' Для начала работы скачайте установочные файлы провайдера '''Microsoft.ACE.OLEDB.12.0''' по ссылке http://www.microsoft.com/en-us/download/details.aspx?id=13255.  
  
 
Если у вас 64-битная версия SQL Server используйте установочный файл ''AccessDatabaseEngine_x64.exe''.  
 
Если у вас 64-битная версия SQL Server используйте установочный файл ''AccessDatabaseEngine_x64.exe''.  
 +
 +
 +
[[Файл:Excel 001.png|center]]
  
  
Строка 12: Строка 21:
 
  C:\AccessDatabaseEngine_x64.exe /passive  
 
  C:\AccessDatabaseEngine_x64.exe /passive  
  
 +
<center>[[Файл:Excel 004.png|400px]] [[Файл:Excel 002.png]]</center>
  
−
'''Шаг 3.''' Чтобы прилинковать Excel файл выполните следующие запросы в ''SQL Server Management Studio''
+
 
 +
'''Шаг 3.''' Получить данные напрямую из файла можно с помощью команды OPENROWSET
 +
 
 +
<pre>
 +
select * from OPENROWSET('Microsoft.ACE.OLEDB.12.0',
 +
          'Excel 8.0; HDR=Yes; Database=C:\Sample.xlsx',
 +
                'Select  * from [Sheet1$]')
 +
</pre>
 +
Изменить данные можно с помощью запроса:
 +
<pre>
 +
UPDATE OPENROWSET('Microsoft.ACE.OLEDB.12.0',
 +
  'Excel 8.0; HDR=Yes; Database=C:\Sample.xlsx',
 +
  'Select  * from [Sheet1$]')
 +
SET id='123456'
 +
WHERE phone='84952294989'
 +
</pre>
 +
Убедитесь, что в Excel есть указанное поле [id]. Часто бывает, что столбцы называются [F15] 
 +
 
 +
Ввести строку можно с помощью запроса:
 +
<pre>
 +
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
 +
'Excel 12.0;Database=C:\Sample.xlsx',
 +
'Select * from [Sheet1$]')
 +
SELECT id, phone, comment from sqltable
 +
</pre>
 +
Количество столбцов в Excel и в возвращаемом запросе select должно совпадать.
 +
 
 +
 
 +
 
 +
<span style="color:red">ВНИМАНИЕ:</span> При возникновении ошибки ''Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"'' выполните следующий запрос:
 +
 
 +
USE [master]
 +
GO
 +
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
 +
GO
 +
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
 +
GO
 +
 
 +
<span style="color:red">ВНИМАНИЕ:</span> При возникновении ошибки ''SQL Server заблокировал доступ к STATEMENT "OpenRowset/OpenDatasource" компонента "Ad Hoc Distributed Queries", поскольку он отключен в результате настройки конфигурации безопасности сервера'' выполните поочередно два запроса.
 +
 
 +
sp_configure 'show advanced options', 1;
 +
GO
 +
reconfigure
 +
GO
 +
sp_configure 'Ad Hoc Distributed Queries', 1;
 +
GO
 +
reconfigure
 +
GO
 +
 
 +
Используемые материалы: http://habrahabr.ru/post/219415/ и [http://social.msdn.microsoft.com/Forums/sqlserver/en-US/bb2dc720-f8f9-4b93-b5d1-cfb4f8a8b1cb/the-ole-db-provider-microsoftaceoledb120-for-linked-server-null-reported-an-error-access?forum=sqldataaccess http://social.msdn.microsoft.com]
 +
 
 +
 
 +
===Линковка Excel к MSSQL===
 +
 
 +
Чтобы прилинковать Excel файл выполните следующие запросы в ''SQL Server Management Studio''
  
 
  exec sp_addlinkedserver @server = 'XlsLnkSrv',  
 
  exec sp_addlinkedserver @server = 'XlsLnkSrv',  
Строка 26: Строка 90:
  
  
−
Считать данные можно с помощью команды  
+
[[Файл:Excel 003.png|center|800px]]
 +
 
 +
 
 +
Если все выполнено правильно, то после обновления обозревателя объектов вы увидите название установленного поставщика Microsoft.ACE.OLEDB.12.0 в Объекты сервера / Связанные серверы / Поставщики
 +
 
 +
 
 +
[[Файл:Excel 005.png|center|600px]]
 +
 
 +
 
 +
Считать данные можно с помощью команды openquery:
  
 
  select * from openquery (XlsLnkSrv, 'Select * from [Sheet1$]')
 
  select * from openquery (XlsLnkSrv, 'Select * from [Sheet1$]')
  
  
−
<span style="color:red">ВНИМАНИЕ:</span> При возникновении ошибки ''Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"'' выполните следующий запрос:
+
===Подключение с помощью Microsoft Jet 4.0 Ole DB Provider ===
  
−
USE [master]
+
Скачайте установочные файлы провайдера по ссылке http://www.microsoft.com/en-us/download/details.aspx?id=23734.  
−
GO
+
−
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
+
−
GO
+
−
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
+
−
GO
+
  
 +
<span style="color:red;">Технология Jet 4.0 работает только на 32-битных версиях MSSQL.
  
−
Используемые материалы: http://habrahabr.ru/post/219415/ и [http://social.msdn.microsoft.com/Forums/sqlserver/en-US/bb2dc720-f8f9-4b93-b5d1-cfb4f8a8b1cb/the-ole-db-provider-microsoftaceoledb120-for-linked-server-null-reported-an-error-access?forum=sqldataaccess http://social.msdn.microsoft.com]
+
Для создания связанного сервера используйте следующий SQL-запрос:
 +
 
 +
EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
 +
'c:\MyData\DistExcl.xls', NULL, 'Excel 8.0' 
 +
 +
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', NULL, NULL </pre>
 +
 
 +
где
 +
*'''ExcelSource''' - название связанного сервера
 +
*'''c:\MyData\DistExcl.xls''' - абсолютный путь к Excel-файлу
 +
 
 +
 
 +
Для получения данных из Excel файла используется запрос:  
 +
 
 +
Select * From [ExcelSource]...[Лист1$]

Текущая версия на 10:46, 24 апреля 2015

Наверх

Подключение с помощью Microsoft.ACE.OLEDB.12.0

Провайдер данных Microsoft.ACE.OLEDB.12.0 подходит как для 32-битных, так и для 64 -битных версий SQL-сервера.

В данном параграфе описывается пример подключения Excel-файла sample.xlsx как связанного сервера на примере MSSQL 2008 SP3 x64.

Шаг 1. Для начала работы скачайте установочные файлы провайдера Microsoft.ACE.OLEDB.12.0 по ссылке http://www.microsoft.com/en-us/download/details.aspx?id=13255.

Если у вас 64-битная версия SQL Server используйте установочный файл AccessDatabaseEngine_x64.exe.


Excel 001.png


Шаг 2. Для установки провайдера используйте запуск файла через командную строку с ключом /passive. 

C:\AccessDatabaseEngine_x64.exe /passive
Excel 004.png Excel 002.png


Шаг 3. Получить данные напрямую из файла можно с помощью команды OPENROWSET 

 select * from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
		          'Excel 8.0; HDR=Yes; Database=C:\Sample.xlsx', 
       		          'Select  * from [Sheet1$]')

Изменить данные можно с помощью запроса: 

 UPDATE OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
		   'Excel 8.0; HDR=Yes; Database=C:\Sample.xlsx', 
		   'Select  * from [Sheet1$]')
 SET id='123456'
 WHERE phone='84952294989'

Убедитесь, что в Excel есть указанное поле [id]. Часто бывает, что столбцы называются [F15]

Ввести строку можно с помощью запроса: 

 INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
			'Excel 12.0;Database=C:\Sample.xlsx', 
			'Select * from [Sheet1$]')
 SELECT id, phone, comment from sqltable

Количество столбцов в Excel и в возвращаемом запросе select должно совпадать.


ВНИМАНИЕ: При возникновении ошибки Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" выполните следующий запрос: 

USE [master]
GO
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO

ВНИМАНИЕ: При возникновении ошибки SQL Server заблокировал доступ к STATEMENT "OpenRowset/OpenDatasource" компонента "Ad Hoc Distributed Queries", поскольку он отключен в результате настройки конфигурации безопасности сервера выполните поочередно два запроса. 

sp_configure 'show advanced options', 1;
GO
reconfigure
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
reconfigure
GO

Используемые материалы: http://habrahabr.ru/post/219415/ и http://social.msdn.microsoft.com


Линковка Excel к MSSQL

Чтобы прилинковать Excel файл выполните следующие запросы в SQL Server Management Studio 

exec sp_addlinkedserver @server = 'XlsLnkSrv', 
@srvproduct = 'ACE 12.0', 
@provider = 'Microsoft.ACE.OLEDB.12.0', 
@datasrc = 'C:\Sample.xlsx', 
@provstr = 'Excel 12.0; HDR=Yes'

где

  • XlsLnkSrv - название связанного сервера
  • C:\Sample.xlsx - абсолютный путь к требуемому Excel-файлу


Excel 003.png


Если все выполнено правильно, то после обновления обозревателя объектов вы увидите название установленного поставщика Microsoft.ACE.OLEDB.12.0 в Объекты сервера / Связанные серверы / Поставщики


Excel 005.png


Считать данные можно с помощью команды openquery: 

select * from openquery (XlsLnkSrv, 'Select * from [Sheet1$]')


Подключение с помощью Microsoft Jet 4.0 Ole DB Provider

Скачайте установочные файлы провайдера по ссылке http://www.microsoft.com/en-us/download/details.aspx?id=23734.

Технология Jet 4.0 работает только на 32-битных версиях MSSQL.

Для создания связанного сервера используйте следующий SQL-запрос: 

EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 
'c:\MyData\DistExcl.xls', NULL, 'Excel 8.0'  

EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', NULL, NULL </pre>

где

  • ExcelSource - название связанного сервера
  • c:\MyData\DistExcl.xls - абсолютный путь к Excel-файлу


Для получения данных из Excel файла используется запрос: 

Select * From [ExcelSource]...[Лист1$]
Источник — «https://wiki.oktell.ru/index.php?title=Подключение_файлов_MS_Excel&oldid=12134»