Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 OPENDATASOURCE ... (Импорт из Excel в SQL)  [new]
NickBell
Member

Откуда: Tbilisi
Сообщений: 2863
Мне нужен импорт данных из Excel в SQL.

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

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=D:\DDD\123.xlsx;Extended Properties=EXCEL 5.0')...[Sheet1$] ;
GO


Выдает ошибку:

автор
Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


Может какие параметры указаны неверно? Как узнать, что необходимо сделать?
31 авг 17, 11:54    [20761276]     Ответить | Цитировать Сообщить модератору
 Re: OPENDATASOURCE ... (Импорт из Excel в SQL)  [new]
alex_p_n
Member [заблокирован]

Откуда: Дніпро
Сообщений: 145
NickBell,
А прилинковать ехель как linked server разве не надо снала?
31 авг 17, 12:01    [20761292]     Ответить | Цитировать Сообщить модератору
 Re: OPENDATASOURCE ... (Импорт из Excel в SQL)  [new]
NickBell
Member

Откуда: Tbilisi
Сообщений: 2863
alex_p_n,

Если надо, то прилинкую, только как надо это сделать, пока не знаю.
31 авг 17, 12:13    [20761331]     Ответить | Цитировать Сообщить модератору
 Re: OPENDATASOURCE ... (Импорт из Excel в SQL)  [new]
Alozar
Member

Откуда:
Сообщений: 141
1.Файл находится на том же сервере, где стоит sql?
2. Пользователь, от которого запущен sql-server имеет доступ к папке и файлу?
3. в момент загрузки из файла, он не должен использоваться чем-то другим, например не быть открытым в Excel
31 авг 17, 12:23    [20761379]     Ответить | Цитировать Сообщить модератору
 Re: OPENDATASOURCE ... (Импорт из Excel в SQL)  [new]
NickBell
Member

Откуда: Tbilisi
Сообщений: 2863
Alozar,

1.Файл находится на том же сервере, где стоит sql?

Да, в данный момент на том же сервере, где стоит sql

2. Пользователь, от которого запущен sql-server имеет доступ к папке и файлу?

По идее, да, лично я захожу на сервер и оттуда пока что работаю.

3. в момент загрузки из файла, он не должен использоваться чем-то другим, например не быть открытым в Excel.

Excel не был открыт.


Сейчас нашла статью, пока пытаюсь разобраться.

https://www.excel-sql-server.com/excel-import-to-sql-server-using-linked-servers.htm

Пока что пустила:

EXEC sp_addlinkedserver
    @server = 'ExcelServer1',
    @srvproduct = 'Excel',
    @provider = 'Microsoft.Jet.OLEDB.4.0',
    @datasrc = 'D:\DDD\123.xlsx',
    @provstr = 'Excel 8.0;IMEX=1;HDR=YES;'


Он создал мне "ExcelServer1"


SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=D:\DDD\123.xlsx;Extended Properties=EXCEL 5.0')...[Sheet1$] ;


Пока не заработал, видимо еще что-то нужно.
31 авг 17, 13:14    [20761534]     Ответить | Цитировать Сообщить модератору
 Re: OPENDATASOURCE ... (Импорт из Excel в SQL)  [new]
инот
Guest
NickBell, а просто визардом данные взять не вар-т?
31 авг 17, 13:51    [20761695]     Ответить | Цитировать Сообщить модератору
 Re: OPENDATASOURCE ... (Импорт из Excel в SQL)  [new]
NickBell
Member

Откуда: Tbilisi
Сообщений: 2863
инот,

Нет, нужно программно
31 авг 17, 14:01    [20761727]     Ответить | Цитировать Сообщить модератору
 Re: OPENDATASOURCE ... (Импорт из Excel в SQL)  [new]
Guf
Member

Откуда: Новосибирск
Сообщений: 641
Какая-то каша
NickBell
2. Пользователь, от которого запущен sql-server имеет доступ к папке и файлу?
По идее, да, лично я захожу на сервер и оттуда пока что работаю.

Надо не "по идее", а результат
xp_cmdshell 'DIR "D:\DDD\123.xlsx"'

NickBell
Пока что пустила:
Он создал мне "ExcelServer1"

https://www.excel-sql-server.com/excel-import-to-sql-server-using-linked-servers.htm
Отличная ссылка, только читать надо внимательно
автор
There are two ways to use linked server data. The first way is like this:
SELECT * FROM ExcelServer1...[Sheet1$]


and the second one is the use of the OPENQUERY function:
SELECT * FROM OPENQUERY(ExcelServer1, 'SELECT * FROM [Sheet1$]')


Что вернет
SELECT @@VERSION

Microsoft.Jet.OLEDB.4.0 - для x32
Microsoft.ACE.OLEDB.12.0 - для x64
Об этом статье тоже написано
31 авг 17, 14:10    [20761789]     Ответить | Цитировать Сообщить модератору
 Re: OPENDATASOURCE ... (Импорт из Excel в SQL)  [new]
инот
Guest
NickBell
инот,
Нет, нужно программно

Такую штуку не смотрел http://epplus.codeplex.com/ ?
Я для выгрузки селектов в эксель это юзал - кода строк 10 было на C#.
C# до этого знал на уровне того, что знал, что коллега программирует на C#.
31 авг 17, 15:11    [20762187]     Ответить | Цитировать Сообщить модератору
 Re: OPENDATASOURCE ... (Импорт из Excel в SQL)  [new]
NickBell
Member

Откуда: Tbilisi
Сообщений: 2863
Guf
xp_cmdshell 'DIR "D:\DDD\123.xlsx"'


Запустила и дал такое сообщение:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.
31 авг 17, 15:12    [20762193]     Ответить | Цитировать Сообщить модератору
 Re: OPENDATASOURCE ... (Импорт из Excel в SQL)  [new]
NickBell
Member

Откуда: Tbilisi
Сообщений: 2863
EXEC sp_configure 'xp_cmdshell', 1;  
GO  
-- To update the currently configured value for this feature.  
RECONFIGURE;  


Теперь уже дал такое сообщение:

автор
Volume in drive D is Data
NULL
Directory of D:\DDD
NULL
06/30/2017 01:18 PM 12,558 123.xlsx
1 File(s) 12,558 bytes
0 Dir(s) 96,075,943,936 bytes free
NULL
31 авг 17, 15:18    [20762217]     Ответить | Цитировать Сообщить модератору
 Re: OPENDATASOURCE ... (Импорт из Excel в SQL)  [new]
NickBell
Member

Откуда: Tbilisi
Сообщений: 2863
EXEC sp_addlinkedserver
    @server = 'ExcelServer2',
    @srvproduct = 'Excel',
    @provider = 'Microsoft.ACE.OLEDB.12.0',
    @datasrc = 'D:\DDD\123.xlsx',
    @provstr = 'Excel 12.0;IMEX=1;HDR=YES;'


Создал успешно ExcelServer2.

SELECT * FROM ExcelServer2...[Sheet1$]

SELECT * FROM OPENQUERY(ExcelServer2, 'SELECT * FROM [Sheet1$]')

Выдало следующее сообщение:

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


Затем решила установить:

AccessDatabaseEngine_X64.exe


Но не удалось, так как вывел это сообщение. Насколько я понимаю, следует удалить Office (по-вилимому он 32-bit) и установить Office (64-bit) и для этого мне придется попросить системного администратора установить его. Надеюсь, после этого не будет проблем с импортом, хотелось бы уточнить, так ли надо действовать или нет.

К сообщению приложен файл. Размер - 80Kb
1 сен 17, 11:21    [20764007]     Ответить | Цитировать Сообщить модератору
 Re: OPENDATASOURCE ... (Импорт из Excel в SQL)  [new]
ывпавыа
Guest
попробуйте поставить AccessDatabaseEngine_X64.exe из

Microsoft Access Database Engine 2016 Redistributable
1 сен 17, 11:54    [20764098]     Ответить | Цитировать Сообщить модератору
 Re: OPENDATASOURCE ... (Импорт из Excel в SQL)  [new]
svpk
Member

Откуда:
Сообщений: 21
Разрядность офиса по идее не при чем, не надо переустанавливать на 64-х разрядный.
Надо удалить 32-х разрядный AccessDatabaseEngine и установить 64-х разрядный.

select * from openrowset('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=D:\DDD\123.xlsx;HDR=YES;IMEX=1', 'select * from [Sheet1$]')
1 сен 17, 12:12    [20764164]     Ответить | Цитировать Сообщить модератору
 Re: OPENDATASOURCE ... (Импорт из Excel в SQL)  [new]
ывпавыа
Guest
svpk,

предполагаю, что имеет место быть Workstation с установленным MS Office.

И разрядность Office конфликтует с разрядностью SQL Server.
1 сен 17, 12:33    [20764263]     Ответить | Цитировать Сообщить модератору
 Re: OPENDATASOURCE ... (Импорт из Excel в SQL)  [new]
ывпавыа
Guest
хотя

вот пишут
In other words, designers need 32-bit database drivers, while Automation service needs 64-bit database drivers.

In order to have both database drivers installed on the same system, you must have Microsoft Office installed. It doesn't matter which version of Microsoft Office you have, it can be 32-bit or 64-bit. The key point is that you install Microsoft Access Database Engine 2010 Redistributable of the opposite "bitness". If you have 32-bit Microsoft Office, then install Microsoft Access Database Engine 2010 Redistributable 64-bit and vice-versa.
и

SQL Server 2016 Import and Export Wizard and Excel
1 сен 17, 12:56    [20764362]     Ответить | Цитировать Сообщить модератору
 Re: OPENDATASOURCE ... (Импорт из Excel в SQL)  [new]
NickBell
Member

Откуда: Tbilisi
Сообщений: 2863
Старый удалила, новый вариант пока не удалось инсталлировать, почему-то то же самое сообщение, непонятно только почему. Потом свяжусь с системным администратором, чтобы помог разобраться с установкой.
1 сен 17, 14:28    [20764710]     Ответить | Цитировать Сообщить модератору
 Re: OPENDATASOURCE ... (Импорт из Excel в SQL)  [new]
NickBell
Member

Откуда: Tbilisi
Сообщений: 2863
А у меня оказывается Import and Export Wizard and Excel не работает ... :)
Надо было оказывается здесь вначале проверить, а потом уже искать программный путь.
1 сен 17, 14:40    [20764767]     Ответить | Цитировать Сообщить модератору
 Re: OPENDATASOURCE ... (Импорт из Excel в SQL)  [new]
ывпавыа
Guest
Там ещё разрядность ssms вмешивается.
1 сен 17, 19:40    [20765624]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить