Страницы: 1
RSS
SQL запрос внутри книги не выполняется, если нижняя строка диапазона ниже 65536
 
Всем хорошего самочувствия и продуктивного бытия!
В продолжение SQL эпопеи решил, что раз динамический диапазон SQL не "любит", то передам ка я в диапазон статичный (в файле-примере, его зовут "таб_дан") всю область от и до 1048576 строки. А не тут то было! :) Как только нижняя строка диапазона опускается ниже 65536 строчки, то именованный диапазон перестаёт быть видимым для запроса. Приведу параметры запроса:
строка подключения
текст команды
Уважаемые знатоки прекрасной штуки по имени SQL , подскажите пожалуйста, какой драйвер может "скушать" весь лист до самого конца и не "подавиться"? =)) (и если Вас не затруднит, то подскажите где узнать/почитать про то, какие драйвера БД прописаны в системе, какие строки подключения к ним и каковы особенности работы с ними, в частности, особенности синтаксиса, ограничения по объёму обрабатываемых данных)
Файл прилагаю.
На Вашу мудрость, терпимость и снисходительность уповаю.
Спасибо.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Такой подойдет?
Код
Provider=MSDASQL.1;Persist Security Info=True;Data Source=Excel Files;Mode=Read;Extended Properties="DSN=Excel Files;DBQ=C:\1\sql_by_range-rows_limit.xlsx;DefaultDir=D:\Загрузки;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;";Initial Catalog=C:\1\sql_by_range-rows_limit.xlsx
 
justirus, спасибо за Ваше участие в данной теме. Заменил на Вашу строку подключения. Выскочило окно:

Какие мои действия дальше?
Спасибо.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Я сейчас проверил, без фильтров вашего SQL тоже только ~65000 строк, вот пока ищу другие методы.
А вылезло то, что я забыл про файл подключения. Он создается когда мастером подключаешься:
Данные->Из других источников->Мастер подключения к данным->Дополнительно->...OLEB ... for ODBC...
 
justirus, т.е. я не могу протестировать Ваш вариант? Эхехех... :) Но всё равно, я благодарствую Вам за Ваше участие и что тратите на мой вопрос своё время. _/\_

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Вы можете самостоятельно создать подключение через мастер, путь до него я написал (Вкладка Excel Данные, там группа Получение внешних данных).
Изменено: justirus - 04.11.2015 18:29:37
 
justirus, а дальше что делать?

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Здравствуйте JayBhagavan
Эта функция Дмитрия  работает без проблем,проверено
 
про движок Андрей писал здесь (#73)
поэтому, предположение:
прежде надо разобраться стоит ли у вас  Access (2010 - раз вы пользуетесь этой версией)... если нет - то, либо установить его (либо 2007), либо отдельно его движок (либо 2007) - там есть линки, объяснения и особенности Office*64 - я не вникала, но думаю, всё зависит от этих нюансов...
потому что если движка нет (то в системе, видимо, будет только старинный, как писал Андрей, 2000 - вот, видимо, и обрабатывает кол-во строк, ограниченное той старинной версией офиса)... надо или Access установить (движок встанет автоматом) или его отдельно (там линк)... по Office*64 не знаю (не имела дел с ним)... успехов
Изменено: JeyCi - 04.11.2015 19:07:54
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Doober, спасибо за Ваш интерес к данной теме. Буду изучать материал по любезно предоставленной Вами ссылке. Благодарствую. _/\_

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Доброе время суток
JayBhagavan, у меня с обоими вариантами ODBC, OLEDB отработало и на Excel 2010 и на 2013 (оба 32 бит). С именем диапазона как у вас "таб_дан", 4 столбца: три - числовых, один текстовый. Строк 200 000 (так как версия 32 битная, то на 1 000 000 - Excel "повесился" :( ).
Строки подключения ODBC
Код
DBQ=c:\1\Checks.xlsx;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DriverId=1046;MaxBufferSize=2048;PageTimeout=10;ReadOnly=1;

и OLEDB
Код
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\1\Checks.xlsx;Extended Properties='Excel 12.0 XML;HDR=YES';


Успехов.

P. S. Сайт, посвящённых строкам подключения к различным источникам данных, The Connection Strings Reference
Изменено: Андрей VG - 05.11.2015 03:09:40
 
JeyCi, здравствуйте. Спасибо за уделённое Вами внимание к данной теме. Как только доберусь до рабочего места, так и сразу разберусь со всеми описанными Вами нюансами относительно драйвера БД.
Андрей VG, спасибо что помогаете разобраться с данным вопросом. Буду разбираться с Вашим предложением с учётом замечаний JeyCi. За ссылку отдельное спасибо. :)

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Андрей VG, при использовании строки подключения как в Вашем примере OLEDB выскакивает окно как в сообщении №3. Пока не понимаю что с этим делать. Пожалуйста, объясните, если будет желание и возможность. Спасибо.
Попытки экспериментировать со строками из примеров по ссылке, что Вы любезно предоставили, приводит к появлению того же окна. (англ. слабо знаю, потому только методом применения подходящих к моей версии экселя строк пытаюсь подобрать)
JeyCi, установлен Access 2010 х64.
В общем у меня нет на данный момент, ясности в следующем:
1) какие драйвера в системе установлены?
2) как понять какой нужно (и где его взять) драйвер для строки подключения, которые советуют или дают в примерах?
Грустно от чувства собственной тупизны и безпомощности в данном вопросе.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Цитата
JayBhagavan написал:
а дальше что делать?
А дальше имя источника -Excel, потом ОК, потом выбор файла из которого нужны данные. После этого и создается файл подключения к данным на вашем ПК в папке по умолчанию, который требуется в посте №3
 
justirus, спасибо за объяснение. Данный вариант у меня видит только именованные диапазоны, которые заканчиваются не ниже 65536 строки. (злополучное число...)

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Похоже, что это - глюк ODBC драйвера и он не лечится.
Надо искать другое решение. Можно попробовать ADO-соединение-Recordset-Диапазон (кусками по 65635), но это почти тоже самое, что и копирование, только немного быстрее на среднем объеме. Можно удалить строки до строки-заголовка и сослаться на лист в операторе FROM.
Изменено: TheBestOfTheBest - 06.11.2015 18:04:24
Неизлечимых болезней нет, есть неизлечимые люди.
 
Во здесь пишут, что при ссылке на диапазоны с номерами строк появляется это ограничение, а если ссылаться на колонки, то берутся все строки!
Но у вас то диапазон начинается не с 1- строки в этом-то и засада.
А почему именно SQL запрос нужен?
 
justirus, спасибо за Ваше участие. Вас понял. Значит это фича такая у экселя, с которой надо смириться. _/\_
Почему SQL и почему диапазон не с первой строки, а потому что делаю для пользователя, который вряд ли что-то будет понимать в ВБА, да, и исходных данных в оригинале будет более 40к, а на сколько больше я не знаю. Данные надо обработать и думал ограничиться функционалом SQL  внутри листа. Да, я могу пойти другим путём, а теперь просто вынужден буду, т.к., благодаря всем помогающим и Вам в том числе, я увидел ограничение, которое не преодолимо для выбранного мной пути.
TheBestOfTheBest, спасибо за Ваш комментарий. Да, буду искать другой путь решения. _/\_
Спасибо всем. Вопрос, думаю исчерпан. За дополнительные нюансы буду весьма благодарен.
Изменено: JayBhagavan - 06.11.2015 18:08:57

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Если интересно, то можно задачу решить в Excel 2016 средствами PowerQuery, предварительно я диапазон таб_дан увеличил.
Получилось сделать связь с нужным диапазоном без ограничения в 65к строк.
 
justirus, спасибо за Ваши подсказки. 2016 вряд ли буду использовать, а насчёт PowerQuery буду вострить лыжи намерения изучить. _/\_

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
JayBhagavan, да похоже я поспешил с обнадёживанием - тестировал на закрытой книге - работало. Стоит открыть книгу и упс, что на ODBC, что на OLEDB. Так что уважаемый TheBestOfTheBest, всецело прав... Это глюк "движка" Access при работе с данными открытой книги Excel. Приношу свои извинения.
 
Андрей VG, всё в порядке. Не за что извиняться. :)

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
Страницы: 1
Наверх