Страницы: 1 2 След.
RSS
Избавиться от цикла в запросе
 
Коллеги, с Наступающим всех!!

А теперь к делу  :)  )))
Есть данные из SAP, которые нужно обработать.
В Excel данные обрабатываются, НО много различных исключений по сцепкам (около 4 вкладок с исключениями по разным сцепкам - от 100 до 70 000 строк).
Шаблон уже пытался по-всякому оптимизировать, уменьшал формулы, менял их местами, считал с помощью макроса, сколько каждая формула занимает времени при пересчете), но данных много (обрабатывается в Excel иногда до 130 000 строк - и это кол-во будет увеличиваться) и шаблон от этого тормозит (много ЕСЛИ и ВПР).
Возникла идея перекинуть хотя бы эти таблицы с исключениями в Access и пользоваться только запросами к ним.
Куски разных кодов брал с форумов и блогов, получилось так:
Код
Sub кнопка()

Dim sCon As String, FieldName As String, sSql As String
Dim FilePath As String, conn  As Object
Set conn = CreateObject("ADODB.Connection"

conn = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=g:\2.accdb;Uid=Admin;Pwd=;"
conn.Open 'подключаемся к БД

For i = 1 To 5
sSql = "SELECT ПОкр FROM Таблица1 WHERE Товар=" & Cells(i, 3).Value & ""
Sheets("Лист2".Cells(i, 6).CopyFromRecordset conn.Execute(sSql)
Next

conn.Close
Set conn = Nothing

End Sub
Естественно, запрос отправляется только от одной ячейки, что меня не устраивает. Пытался зациклить процесс, но тогда считает оочень долго даже 2000 строк. Пытался также помещать данные в массив, но там тоже не лучше ситуация.

Возможно ли как-то сделать запрос массовым для ячеек ОТ и ДО ?
Или, возможно, у вас появятся еще мысли, как уменьшить время расчета?
Изменено: modcom - 01.01.2015 06:27:38
 
А нельзя все нужные обработки сделать в Access? А в Excel уже загружать итог.

Не совсем понятен механизм сцепок. Может можно выгрузить все данные в массив и уже его обрабатывать макросами?
Изменено: ber$erk - 31.12.2014 08:49:49
Учимся сами и помогаем другим...
 
задавался я как-то подобным вопросом.
оказалось - можно и без цикла.
http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=56395&TITLE_SEO=56395&MID=470463#message470463
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
Цитата
Не совсем понятен механизм сцепок.
ber$erk,ну например магазин №1 Товар "1000" возится в коробках, а на супермакрет №2 тот же товар возится в поддонах (примерный смысл сцепок). Т.е. к сцепке  №1&"1000" подтягиваются "коробки".

Насчет обрабатывать в Access - вот фиг знает, как мне показалось при беглом взгляде на программу, не очень удобно менять формулы (если изменился алгоритм) + вроде есть какая-то заморочка с вставлением данных для оперативной обработки (за 1 день может потребоваться вставить данные раз 30-60 - вставить от 10 строчек до 130 000 - нужно быстро сделать копи-паст).  
Цитата
ber$erk пишет: Может можно выгрузить все данные в массив и уже его обрабатывать макросами?
Примерно так делал с 80 000 строчек обрабатываемых данных - 1 массив + 80 000 строчек исключений - 2 массив, сравнивал их, времени что-то много уходит на это (не дождался конца). Т.е. перебирал каждый элемент массива №1 в массиве №2.
 
вот это мне пока не понятно - как может быть 80 тысяч исключений?
Может быть получиться через словарь сделать. в два прохода:
1) загоняем в словарь исключения
2) проходим по данным и исключаем те, которые есть в словаре.

З.Ы.: выкладывали здесь вот такую справочку - до сих пор ею пользуюсь
Изменено: ber$erk - 31.12.2014 09:11:30
Учимся сами и помогаем другим...
 
modcom, код следует оформлять тегом. Исправляйте.
 
Нормальные люди уже квасят вовсю, а я пока пытался тут часов 5 построить грамотный запрос - не выходит за неимением опыта.  :)  ))
Цитата
ber$erk пишет: вот это мне пока не понятно - как может быть 80 тысяч исключений?
Просто всего значений Супермаркет-Товар оооочень много, ну вот 80 000 всяких исключений... Причем это не единственная сцепка...
Цитата
З.Ы.: выкладывали здесь вот такую справочку - до сих пор ею пользуюсь
Спасибо большое, проанализирую файлик, найду макрос-словарь и попробую применить!
Цитата
ikki пишет: оказалось - можно и без цикла.
Спасибо! Сегодня около 5 часов, сидя на работе, пробовал как-то под себя подстроить строку из вашей ссылки:
Код
a.Execute "Update [tn$] As t1 Inner Join [Excel 8.0;Database=d:\path\mmm_sheet_file.xls;HDR=YES].[mmm$] As t2 On ((t1.idMat=t2.idMat) And (t1.idIzd=t2.idIzd) And (t1.normSZ=t2.normSZ)) Set t1.normDel=1"
Я так понимаю, мне тут нужно применять не Update, а простой SELECT FROM ? Т.к. заменять данные не надо, а просто подтягивать.
Также не понял, что означает это t1.idMat=t2.idMat и это t1.normDel=1 в этом примере...

Подскажите, применительно к моему коду ниже, как можно его поправить?
Код
sSql = "SELECT ПОкр FROM Таблица1 WHERE Товар=" & Cells(i, 3).Value & ""
Sheets("Лист2".Cells(i, 6).CopyFromRecordset conn.Execute(sSql)
Цитата
Юрий М пишет: modcom, код следует оформлять тегом. Исправляйте.
Прошу прощения, поправил!
 
Цитата
modcom пишет: Нормальные люди уже квасят вовсю
Мнение большинства не есть истинна.
Цитата
modcom пишет: Супермаркет-Товар оооочень много, ну вот 80 000 всяких исключений
Сгруппируйте товар по группам (простите за тавтологию) и исключайте по ним - исключений должно, навскидку, поубавиться. Либо исключайте по ключевым словам, которые не встречаются в целевых позициях.
Изменено: JayBhagavan - 01.01.2015 00:00:13

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
да.
я вначале вопрос неточно понял.
думал, Вам нужно обновление.

основной смысл альтернативного (не по одной записи) SELECT'а - это избежание накладных расходов на соединение и т.п.
устанавливаем соединение таблиц - той, из которой нужно получить данные и нужного нам подмножества ключей.
допустим, нам нужно поле field2 из таблицы main для каких-то записей.
перечень этих записей находится в таблице tab
само собой, таблицы main и tab как-то связаны, например - по полю key

тогда получим:
Код
SELECT main.field2 FROM (main INNER JOIN tab ON main.key = tab.key)

не проверял.

я пока основные сложности вижу в правильном построении запроса вместе с соединениями таблиц из разных источников - Access и Excel (опять же, если я правильно понял Вашу задачу).
гуглить надо.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
Сидел сегодня еще несколько часов по форумам. Вообщем решил задачу пока так - связал анализируемую таблицу Excel с Access. В Access создал запрос, который формирует отчет из данных Excel. А в Excel создал запрос, подтягивающий уже готовые данные из запроса Access)))
К сожалению, не нашел способа, как в запросе Excel сравнить сразу диапазон ячеек со столбцом в Access, поэтому пришлось так крутить...
 
А в самом Excel нельзя этот запрос создать?
Учимся сами и помогаем другим...
 
Думаю, что это возможно (даже для меня было бы удобней), но пока так, с помощью костылей.)))
Попробую еще объяснить задание (скорее всего непонятно описал его в начале).

Есть, например, Супермаркет (С) - Товар (Т). 95% товаров возятся кратно коробкам. НО, есть С-Т, которые должны возится кратно штукам или поддонам (неважно).  Это и есть исключения, на данный момент которые ВПРятся по сцепке С-Т из листа "Исключения". (прикрепил пример в файле). Т.к. исключений очень много + сцепок для исключений тоже, хочу ускорить это дело запросами+убрать всякие сцепки и ВПРы.
Пробовал запрос поячеечно - это очень долго. Можно ли взять весь столбец для запроса, не прибегая к массиву? (надеюсь, понятно выразился))).
 
Можно и без линка таблицы в access. Можно и в одном запросе с джоинить таблицу excelи access
 
Прошу подсказать, как это сделать?
 
Вот пример
(архив нужно распаковать на физический диск)
Изменено: B.Key - 02.01.2015 23:37:41
 
Подскажите пожалуйста, по какому признаку вытащились именно записи (где это указано в коде):
txt2txt5txt6 ?
Работать надо не 12 часов, а головой.
 
where b.id=a.test
 
Цитата
B.Key пишет: Вот пример
Спасибо большое, теперь понятно, что это возможно!!

Переделал немного под свою задачу - иногда неправильно тянет (выделил в примере красным), иногда не с первого раза сам макрос срабатывает (?), также почему-то подтягивает данные "-1" строка...

Буду очень благодарен, если посмотрите (путь для базы данных прописан как диск G)
 
Извиняюсь за дотошность,
b.id это наверное первая колонка id, а a.test - это где?
в коде есть про "а" [tt$a1:a6]-где это находится? в access же только одна таблица tbl.
Спасибо.
Работать надо не 12 часов, а головой.
 
Цитата
Leanna пишет: в коде есть про "а" [tt$a1:a6]-где это находится? в access же только одна таблица tbl.
насколько я понял, tt$ - это имя листа Excel, а1-а6 - диапазон, откуда берутся данные с этого листа.
Цитата
а a.test - это где?
test - это имя столбца в Excel
 
Код
Workbooks("xls").Sheets("tt").Range("A1:A6")

Так понятнее? :D
 
modcom, используйте left join
Leanna, это находится в excel  
 
Извиняюсь, не заметила в excel листа tt.
Всем спасибо! Теперь всё ясно.
Работать надо не 12 часов, а головой.
 
Цитата
B.Key пишет: modcom , используйте left join
1. Понял, пробую использовать, читаю всякие форумы, но выходит ошибка. Последняя моя версия, как ЭТО должно быть:
Код
sSql = "SELECT b.ПОкр FROM [Excel 12.0;DATABASE=" & ThisWorkbook.FullName & ";HDR=YES;IMEX=1;].[tt$a1:b20] as a,Таблица1 as b" _
& " LEFT OUTER JOIN b ON b.тк=a.тк AND ON b.товар=a.товар"
Подскажите, в чем ошибка?

2. Интересно, почему в вашем запросе:
Код
sSql = "SELECT b.namestr FROM [Excel 8.0;DATABASE=" & ThisWorkbook.FullName & ";HDR=YES;IMEX=1;].[tt$a1:a6] as a, tbl as b" _
& " where b.id=a.test"
в выражении FROM присутствуют 2 таблицы, а не одна?
 
Цитата
ikki пишет: гуглить надо
Цитата
B.Key пишет: Вот пример
вывод - если Вы не идёте в гугл, гугл сам приходит к Вам :)

B.Key, +1
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
Вы лучше скажите, что Вы хотите получить в итоге?
----
Если просто подтянуть некие данные из справочника, тогда сформируйте полный набор данных и вставьте его на лист.
Если необходимо вставить в эти конкретные ячейки, тогда надо извращаться. Сначала пронумеровать записи в excel затем провести отбор с сортировкой.
 
Цитата
B.Key пишет:
Если просто подтянуть некие данные из справочника, тогда сформируйте полный набор данных и вставьте его на лист.
Подтянуть данные из справочника, чтобы эти данные подтягивались на ту строку, какая соответствуют по выборке (раньше это делал ВПР):
Код
ON b.тк=a.тк AND ON b.товар=a.товар

Непонятно, у рекордсета какой порядок записи? Раньше думал, что он записывает по порядку. Или это не в рекордсете дело, а SELECT так отбирает?


Например, как идет таблица, так и пишет их:
ТКТовар
00033000  
00022000
00033000
00044000
А у меня получается, что не все строки в данной таблице EXCEL совпадают со справочником, и еще 1 строку пропускает...

Прошу прощения, если непонятно выразился!
 
Jet сам (исходя из вашего запроса) определят какой столбец индексировать для поиска  данных. Естественно он его и сортирует и затем выводит информацию в отсортированном виде.
 
Да и вообще не видя схемы данных, мне тяжело Вам предложить оптимальный запрос.
В некоторых случаях достаточно where, в других надо использовать join.
 
Цитата
B.Key пишет:
Jet сам (исходя из вашего запроса) определят какой столбец индексировать для поискаданных. Естественно он его и сортирует и затем выводит информацию в отсортированном виде.
Спасибо Вам большое! Всё получилось теперь таким образом:
Код
sSql = "SELECT ПОкр FROM Таблица1 as b" _
& " RIGHT OUTER JOIN [Excel 12.0;DATABASE=" & ThisWorkbook.FullName & ";HDR=YES;IMEX=1;].[tt$b1:c20] as a  ON b.тк=a.тк AND b.Товар=a.Товар"
Если данные есть в справочнике - они подтягиваются к соответствующим строкам, если нет, то просто стоит "Пусто".

B.Key, считаю, что вы дали достаточно эксклюзивную информацию, т.к. в гугле несколько часов поисков не дали нормального результата, как присобачить справочник Access в таблицу Excel.


P.S. Кому требуется сделать также, можете обращаться ко мне, помогу и расскажу, что знаю!
Страницы: 1 2 След.
Наверх