Връзка на Excel (таблица от xlsx файл) към база на SQL Server
Здравейте!
От известно време се боря с този проблем, на който решаването му с Access е детска игра.
За какво става дума.
Имам данни от таблица в EXCEL, които искам да вкарам в база данни, да ги обработя там с наличната от базата информация и да върна резултата отново в Ексел. Ако говорим за връзка Excel - Access - Excel, това е повече от лесно. Създава се линк в аксеската база към екселската таблица и след като тя се позиционира като част от базата с данни, обработва се с комбинирана заявка и резултатът се връща в същия екселски файл, но в друга таблица.
До момента не съм открил аналогичен вариант с Excel - SQL Server - Excel. Има вариант за създаване на селект заявка или VIEW в SQL Server. Макар на пръв поглед елементарно, това въобще не е просто, с оглед на купчината настройки, задължителното отваряне на SQL Server Management Studio като админ и кръпката AccessDatabaseEngine, която трябва да се инсталира. Човек би подскочил кеф, когато види, че това е сработило. Обаче не! Ако екселският файл е отворен, заявката или View-то връщат грешка:
Msg 7399, Level 16, State 1, Procedure (Име на View-то), 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, Procedure (Име на View-то), Line 3
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
Подобна заявка губи смисъла си, дори ако се потърси вариант за обработка в SQL Server след затваряне на Ексел. Когато отново отвориш Ексел, заявката или view-то гърмят. Когато вместо SQL Server седи Аксес, достатъчно е да поискаш от екселския файл да се ъпдейтне, без дори да се налага да се отваря Аксес. Всичко работи.
Някой да се е сблъсквал с подобен проблем? Ще съм благодарен на всеки, който подскаже някаква идея.
Заявката (view-то), която ползвам в SQL Server са от тип:
SELECT *
FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;
Database=D:\xxx\SQL\Моят файл.xlsx',
'SELECT * FROM [Моята таблица$]')
или
SELECT <имената на колоните>
FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0',
'Data Source="D:\xxx\SQL\Моят файл.xlsx";
Extended properties=Excel 12.0' )...[Моята таблица$]
Точно. През Data tab-а. Стандартно вмъкване на външна таблица с посочване на сървъра и самото VIEW.
Малко по-подробно за начина, по който е решено чрез Access.
Access позволява да се създаде линк през EXTERNAL DATA менюто към таблица от Excel. След което към нея може да се обръщаш като към таблица от самата база, да обработиш данните от нея с някаква заявка, която разбира се ползва данни и от други таблици в базата. Това се записва като QUERY и след това може спокойно да бъде достъпено отново през Data tab-а в Excel. Разбира се има вариант да си викнеш данните от базата направо в Excel и там да си ги ръчкаш, но когато става дума за десетки хиляди записи и то в няколко различни таблици на базата, хич не е елегантно, а и Excel почва да се пита какво му се случва. Мястото на обработката е в базата, но възможностите на Access са доста ограничени и ако човек потърси вариант за мигриране към SQL Server изведнъж се сблъсква с проблемът, за който писах в първия пост.