Здравствуйте!

В excel-ом файле на каждом листе "Лист1" и 'Лист2" есть по одной таблице с подключением с MDX-запросом. На "Лист3" нужно вставить таблицу с подключением с SQL-запросом к этим двум листам. Нужно сделать внешнее соединение из этих двух таблиц.

Логичнее было сделать так: обойтись без первых двух листов и вставить на лист таблицу с подключением с непосредственным SQL-запросом на сервер. Но такой возможности нет.

Делаю так. Данные - Из других источников- Из Microsoft Query. Но фишка в том, что синтаксис Microsoft Query не совсем такой как SQL. Более капризный. Запутался там как делать внешнее соединение Для данной задачи нужно подключаться через Microsoft Query или можно подключаться по другому (через другой провайдер)? ADO не вариант, там ограниченный SQL.

В общем, сделал так

В меню "Из других источников" - "С сервера SQL сервер" - выбираю любую базу данных, потом меняю свойства подключении и текст запроса.

Вариант 1
В строке подключения ввел так Provider=Microsoft.ACE.OLEDB.12.0;Excel 12.0;Database=C:\Users\User\Desktop\СМ2.xlsm. Тип команды - SQL. Текст запроса
Выгрузились данные нормально.

Захожу в свойства подключения и в строке подключения теперь другая запись
Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source="";Mode=Share Deny None;Extended Properties="Excel 12.0;Database=C:\Users\User\Desktop\СМ2.xlsm";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False

Теперь вставляю такой текст простого запроса с объединением

Пишет, что ошибка в From. Вроде все по синтаксису написал.


Вариант 2

Если в строке подключения написать Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\User\Desktop\СМ2.xlsm;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"" .Тип команды - SQL. И текст запроса

Данные выгружаются нормально.

Захожу в свойства подключения и в строке подключения теперь также другая запись как в первом варианте.

Если вставить такой текст запроса

Пишет также, что ошибка в From.

Что не так делаю? Может надо что в строке подключения изменить?

Какой вариант подключения лучше использовать?

anvg,

вместо JOIN написал INNER JOIN - заработал.

Потом FULL JOIN не работал. Использовал UNION - заработало. Похоже SQL к excel листам ограниченный.

anvgferzmikkКакой вариант подключения лучше использовать?, а вот подключение лучше использовать ODBC .
Тут я не совсем понял.
Это писать в строке подключения?

anvgСмотря как вы создавали подключение. В уже существующем, если не ошибаюсь, перейти с OleDb на ODBC не получится (не эксперементировал). А так да, по минимальному тексту подключения всё правильно
Получилось. Делал так: в меню "Из других источников" - "С сервера SQL сервер" - выбираю любую базу данных, потом меняю свойства подключении и текст запроса.
Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\Users\User\Desktop\СМ2.xlsm;
Только потом excel изменил запись в подключении
Provider=MSDASQL.1;Persist Security Info=True;Extended Properties="DBQ=C:\Users\User\Desktop\СМ2.xlsm;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"

Тут фишка в том, что если этот файл будет открывать другой пользователь, то ему надо будет изменить строку подключения, а именно, изменить путь к файлу. Такая проблема только макросом решается, который в строке подключения меняет путь к файлу, или есть другие варианты решения?

Обратил внимание. Если в свойствах подключении ставить тип команды "По умолчанию" или "SQL", то таблица формируется. А если ставить "Таблица", то выходит такое сообщение

anvgProvider=MSDASQL.1;Увы, это слой с ODBC в OLEDB. Чтобы чистый ODBC получить делайте через MS Query, а потом точно также редактируете строку подключения.
DSN=Excel Files;DBQ=C:\Users\User\Desktop\СМ3.xlsm;DefaultDir=C:\Users\User\Desktop;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

Тип команды - SQL, но тут неактивно поле, то есть нельзя изменить.

Если в свойствах подключении нажать на "Изменить запрос", то вылезает такое сообщение. Как можно изменить запрос в конструкторе?

Почему кнопка "параметры" не активна? Даже бывает не активна, когда подключаешься к кубу. Как еe сделать активной?

Какую роль играет файл подключения?

В интернете мало информации на тему Создание подключений к excel листам, где прописаны такие тонкие логические нюансы

anvg,

Обнаружил такую фишку. В запросе присутствует 2 параметра и записываем их как знаками "?".


Какой именно знак "?" соответствует для заданного параметра, определяется последовательность заданных параметров Set Параметр1 и Set Параметр2.

Записывал в тексте параметры не как "?", а как "@Параметр1" и "@Параметр2". Но так почему то не работает.

Как надо правильно записывать в тексте запроса разные параметры (без знаков вопросов)?

anvgесть ли приоритет параметра по положению в основном запросе ли или в подзапросе или главное порядковый номер ?
Это пример простого запроса, на самом деле будет сложным. Разные таблицы на разных листах, в запросе присутствуют подзапросы, несколько параметров. Со временем будут изменяться столбцы, добавляться еще таблицы. Хотелось бы сделать запрос мобильным, чтобы потом легко дорабатывать. Конечно здесь не такого удобного конструктора как в 1С - СКД.

Реально ли с помощью программы SQL Server Management Studio подключиться к excel-листу?

Вот на двух первых листах присутствуют таблицы с подключением с MDX-запроса. На третьем листе делаю SQL-запрос к этим двум листам. Первые два листа обновляю, потом обновляю третий. После после обновления двух листов данные свежие. Потом обновляю третий лист, но данные берутся из двух листов, но которые были до обновления. Почему так происходит? Как исправить?

Даже если менять SQL-запрос, то все равно обращается к данным до обновления

Как сделать корректное обновление?

Может надо параметры подключения изменить?

DBQ=C:\Users\User\Desktop\СМ3.xlsm;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DriverId=1046;ExtendedAnsiSQL=1;MaxBufferSize=2048;PageTimeout=5;ReadOnly=1;

Обратил внимание на следующее.

Если обновить таблицы на первых двух листах с подключением OLAP с помощью MDX-запросов, то данные обновляются корректно. Если обновить лист с SQL-запросом к этим двум листам, то отображаются данные с листов до обновления.

Если обновить таблицы на первых двух листах и программно создать лист с SQL-запросом к этим двум листам, то такая же проблема.

Если добавить новый лист с таблицей с подключением OLAP и также программно создать лист с SQL-запросом, то также все.

Если создать новый файл с такими же таблицами, то данные свежие. Но если в этом новом файле опять обновить данные на первых двух листах, то опять такая же проблема.

Почему так происходит? Как сделать так, чтобы данные можно корректно обновлять без создании новых файлов?

anvgДля обращения к листам Excel используется движок Access, а он года с 97, в плане SQL, не развивается. Там есть только Left Join, Right Join, Inner Join
Может эту тему перенести в "Microsoft Access"?

anvgСкорее всего в настройках подключения mdx запросов стоит включить синхронное обновление, иначе сохранение может прервать их обновление
Такая строка подключения
Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=profit;Data Source=...;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error
Что тут нужно дописать?

anvgДа не здесь. Диалог "Свойства подключения", вкладка "Использование" сбросить галку "Фоновое обновление". Или если программно обновляете, то QueryTable.Refresh False
Ура! Наконец то получилось. Надо было снять Фоновое обновление, обновить, сохранить и только потом обновить подключение с SQL-запросом к листам.)

ferzmikkanvgПотом FULL JOIN не работаДля обращения к листам Excel используется движок Access, а он года с 97, в плане SQL, не развивается. Там есть только Left Join, Right Join, Inner Join
Пытаюсь использовать функцию ROW_NUMBER, не работает чего то. Может быть ограничение из за этого. Есть где нибудь список функции и возможностей , которые можно применять, а также которые не будут работать для обращении к excel листам, но предлагаются альтернативы ?

anvgКто бы его составил, раз сразу поиском не находится. По зарезервированным словам . По функциям можно посмотреть в самом Access, а можно и поискать давайте сделаю это за вас ;)
Спасибо!)

anvgДля обращения к листам Excel используется движок Access, а он года с 97, в плане SQL, не развивается.
Мне вот интересно узнать. Если это так, то на сегодняшний день наверняка есть другая наиболее оптимальная технология выгрузки данных.

У меня ситуация такая. Безусловно, самый лучший вариант это выгружать данные из сервера с помощью SQL-запроса. Но не разрешают пользоваться. Есть OLAP. Если делать MDX-запросы, то запросы получаются очень большими и запутанными. А также MDX не соединяет таблицы как SQL, имеет другую логику. Поэтому делаю так: на каждый excel-лист выгружаю данные с OLAP и потом запускаю SQL-запрос с ограниченным функционалом к этим листам, соединяются таблицы как надо, расчеты разные и выводит результат на другой лист. Сам SQL-запрос очень большой. Я трачу много времени на экспериментирование, изучение тонкостей, написание запроса учетом ограничении функционала. Может для данной ситуации есть наиболее оптимальное решение? Учитывая такой логический нюанс, что разрабатываю инструмент, который формирует нужный отчет нажатием одной кнопки с помощью макросов, и запускать будут другие пользователи.

Еще учитывая такой момент, что SQL-запрос к листам сложный, там много полей, вложенности, соединений. Если вдруг заказчик скажет добавить еще несколько полей, то доработка будет сложной, запутанной и займет много времени на поиск ошибок и тестирование. Есть какая нибудь возможность или технология, которая позволяет облегчить эту процедуру?

У меня Microsoft Office 2007. Windows 7. Тип системы - 64. Сегодня установили Бизнес скайп. В пуске появилась папка "Средства Microsoft Office 2016". Пишет что не может загрузить драйвер. Потом восстановили. Как то странно работает. Теперь вылезает такая ошибка в экселевском файле при создании подключения к листу. Что делать?

Пытаюсь в запросе использовать временную таблицу. Пишу запрос с использованием INTO. Такая функция должна работать в Microsoft Access SQL.


Запускаю. Выдает сообщение об ошибке. Здесь возможно использовать временные таблицы путем INTO или надо по другому делать? Если разрешить доступ к базе данных, в даннном случае к листу, то логически как временную таблицу можно поместить на лист, если там находятся исходные данные?

Если написать такой запрос

то выдает сообщение об ошибке.

Похоже WITH для сохранения промежуточного результата запроса не поддерживается

На моем локальном компьютере обновление происходит нормально. Но на другом компьютере - вылезает сообщение.

Свободных гигабайтов на диске много. Возможно установлена квота (2 гигабайта). Где найти и увеличить эту квоту? И возможно ли определить размер необходимой квоты?

Может быть есть смысл изменить строку подключения и указать максимальным размер базы?

GaryaОбойти его можно, храня разные таблицы в разных базах данных.
Есть несколько листов, где на каждом листе одна таблица и запускается локальный SQL-запрос к этим листам и выводит результат на отдельный лист. Я правильно понимаю, что в данном случае, только разбить локальный SQL-запрос на два запроса с промежуточным результирующим листом?

GaryaferzmikkСвободных гигабайтов на диске много. Возможно установлена квота (2 гигабайта). Где найти и увеличить эту квоту? И возможно ли определить размер необходимой квоты?Это ограничение ядра MS JET, посредством которого происходит работа с данными БД MS Access и/или MS Excel.
Обойти его можно, храня разные таблицы в разных базах данных. Либо используя MS SQL Server - у него ограничение размера БД до 10 Тб.
Если передать все расчеты на Access, то там тоже будет такое ограничение?

Фишка в том, что данные выгружались по одному городу и такое ограничение вылезает. Но надо будем по всем городам. Access сможет как то обработать?

Bobgosferzmikk,
Вам действительно нужен Access или просто ломает от мысли перевода уже сделанного на MS SQL или Oracle ?
У нас есть OLAP, оттуда выгружаются разные таблицы и заталкиваются в каждый лист. Потом сложный SQL запускает. Приходится промежуточные таблицы вставлять в дополнительных листах (это случаи, когда подзапрос повторяется). Потом макросом добавлять дополнительные поля. Для такого случая excel не идеально подходит.

MS SQL не продоставляют, так как будет влить на основные процессы, поэтому ищу возможные пути реализации.

Чтобы все города выгружать в Access я вижу такое решение: с помощью цикла выгружать по отдельному городу обрабатывать с помощью SQL. И выводит в конечную таблицу. Аналогично для остальных городов, и полученные строки добавляет в эту конечную таблицу. В итоге получится обработанная конечная таблица по всем городам. Понятно, что эту таблицу нельзя поместить в Excel целиком, тут либо по городам, либо без разбивки по SKU. Тут другой вопрос. Вот Access сможет ли выдержать такую конечную таблицу просто для хранения обработанных данных?

vikkivкстати в твоём случае для работы на SQL достаточно минимальных прав хотя-бы на TempDB, главное чтобы там дрова нужные стояли (вроде ACE) и OpenQuery/OpenRowSet были разрешены.. тогда сможешь использовать обычный T-SQL в Temp пространстве для работы со всеми внешними запросами, ну и Join-ить как вздумается SQL с MDX результатами..
Там есть такая возможность настроить, чтобы запущенные нужные запросы на сервере не сильно тормозили основные главные процессы?

Получается надо будет предоставить некоторым пользователям эти права, кто будет запускать такой запрос.

vikkivкстати в твоём случае для работы на SQL достаточно минимальных прав хотя-бы на TempDB, главное чтобы там дрова нужные стояли (вроде ACE) и OpenQuery/OpenRowSet были разрешены.. тогда сможешь использовать обычный T-SQL в Temp пространстве для работы со всеми внешними запросами, ну и Join-ить как вздумается SQL с MDX результатами..
Тут вместо TempDB можно использовать Табличную переменную? Или у TempDB больше возможностей?