Проект «SnakeProject» Михаила КозловаРегистрация

Навигация
⇒SQL\T-SQL⇒

⇐CISCO
⇐Voice(Asterisk\Cisco)
⇐Microsoft
⇐Powershell
⇐Python
⇐FreeBSD and Nix
⇐1С
⇐Общая
⇐WEB Разработка
⇐ORACLE SQL \ JAVA

Импорт\Экспорт в или из Excel в MS SQL Server


--В примерах использовался MS SQL Server 2012 и Excel 2007

--В этом примере сделаем импортэкспорт из Excel в MS SQL и обратно

--Служба ms sql server была запущена не под системной учеткой
--Ставил учетку пользователя, имеющего доступ к файлу и папке

--Установим драйвер Microsoft.ACE.OLEDB.12.0
--http://www.microsoft.com/en-us/download/details.aspx?id=13255
--После установки для 86x платформ

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

--Создаем linked server для работы с Excel файлом
--Более полная докуметация 
--http://msdn.microsoft.com/en-us/library/aa259589(v=sql.80).aspx

EXEC sp_addlinkedserver
    @server = 'ExcelServer',
    @srvproduct = 'Excel', 
    @provider = 'Microsoft.ACE.OLEDB.12.0',
    @datasrc = 'C:1231.xlsx',
    @provstr = 'Excel 12.0;IMEX=1;HDR=YES;'

--Выборка из Excel
--l1 - такое название я дал листу в excel файле

SELECT * FROM ExcelServer...[l1$]
SELECT * FROM OPENQUERY(ExcelServer, 'SELECT * FROM [l1$]')

--Вставка данных в EXCEL
INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:1231.xlsx;','SELECT * FROM [l1$]')
SELECT 1, 2, 3

--Столкнулся с проблемой:SQL Server заблокировал доступ к STATEMENT "OpenRowset/OpenDatasource" компонента "Ad Hoc Distributed Queries", поскольку он отключен в результате настройки конфигурации безопасности сервера. Использование "Ad Hoc Distributed Queries" может быть разрешено администратором при помощи хранимой процедуры sp_configure
--Решил так:

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

--Более полная документация есть в этой статье
----http://www.excel-sql-server.com/excel-import-to-sql-server-using-linked-servers.htm

 

На этом все, видео можно увидеть на моем канале YouTube

P.S. BONUS :))
Прочтой пример выборки из excel файла:
 
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', N'Excel 8.0;Database=C:detailстрой_ОООdet_day_1_part_1.xlsx', 'SELECT * FROM [detail$]')

<font face="&quot;courier" new,="" courier,="" monospace"="">


Комментарии пользователей

Оставленных комментариев: 2Добавить комментарий
Анонимам нельзя оставоять комментарии, зарегистрируйтесь!
Berezkin (05.10.2017 в 14:01)ответить
У меня не получилось добавление в файл Excel (INSERT INTO OPENQUERY(ExcelServer,'SELECT 1,2,3 FROM [DD$A14:U50000]') VALUES(1,2,3)). Ошибка Cannot update. Database or object is read-only.Видимо потому что Служба ms sql server была запущена под системной учеткой (nt service\mssqlserver). Обойти это никак нельзя?
MSnake (10.10.2017 в 16:30)ответить
К сожалению нет возможности в данный момент проверить

© Snakeproject.ru создан в 2013 году. При копировании материала с сайта - оставьте ссылку.


Яндекс.Метрика

Goon Каталог сайтов Рейтинг@Mail.ru