IT-инфраструктура для бизнеса и творчества
Разработка
NTA

Как сформировать SQL запросы в Excel?

Представьте себе ситуацию, Вы получили целевую выборку из одной базы данных, но для полноты картины, как всегда, нужны дополнительные данные. Проблема может быть в том, что нужная информация хранится в другой базе данных и возможности создать на ней свою таблицу нет, подключиться используя link тоже нельзя, да и количество элементов, по которым нужно получить данные, несколько больше, чем допустимое на данном источнике. Вот и получается, что возможность написать SQL запрос и получить нужные данные есть, но написать придется не один запрос, а потом потратить время на объединение полученных данных.

Выйти из подобной ситуации поможет Excel.

Уверен, что ни для кого не секрет, что MS Excel имеет встроенный модуль VBA и надстройки, позволяющие подключаться к внешним источникам данных, то есть по сути является мощным инструментом для аналитики, а значит идеально подходит для решения подобных задач.

Для того чтобы обойти проблему, нам потребуется таблица с целевой выборкой, в которой содержатся идентификаторы, по которым можно достаточно корректно получить недостающую информацию (это может быть уникальный идентификатор, назовем его ID, или набор из данных, находящихся в разных столбцах), ПК с установленным MS Excel, и доступом к БД с недостающей информацией и, конечно, желание получить ту самую информацию.

Создаем в MS Excel книгу, на листе которой размещаем таблицу с идентификаторами, по которым будем в дальнейшем формировать запрос (если у нас есть уникальный идентификатор, для обеспечения максимальной скорости обработки таблицу лучше представить в виде одного столбца), сохраняем книгу в формате *.xlsm, после чего приступаем к созданию макроса.

Через меню «Разработчик» открываем встроенный VBA редактор и начинаем творить.

Sub job_sql() — Пусть наш макрос называется job_sql.

Пропишем переменные для подключения к БД, записи данных и запроса:

Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim sql As String

Опишем параметры подключения:

sql = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=Storoge.company.ru\ Storoge."

Объявим процедуру свойства, для присвоения значения:

Set cn = New ADODB.Connection cn.Provider = " SQLOLEDB.1" cn.ConnectionString = sql cn.ConnectionTimeout = 0 cn.Open

Вот теперь можно приступать непосредственно к делу.

Организуем цикл:

For i = 2 To 1000

Как вы уже поняли конечное значение i=1000 здесь только для примера, а в реальности конечное значение соответствует количеству строк в Вашей таблице. В целях унификации можно использовать автоматический способ подсчета количества строк, например, вот такую конструкцию:

Dim LastRow As Long LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count

Тогда открытие цикла будет выглядеть так:

For i = 2 To LastRow

Как я уже говорил выше MS Excel является мощным инструментом для аналитики, и возможности Excel VBA не заканчиваются на простом переборе значений или комбинаций значений. При наличии известных Вам закономерностей можно ограничить объем выгружаемой из БД информации путем добавления в макрос простых условий, например:

If Cells(i, 2) = "Ваше условие" Then

Итак, мы определились с объемом и условиями выборки, организовали подключение к БД и готовы формировать запрос. Предположим, что нам нужно получить информацию о размере ежемесячного платежа [Ежемесячный платеж] из таблицы [payments].[refinans_credit], но только по тем случаям, когда размер ежемесячного платежа больше 0

sql = "select [Ежемесячный платеж] from [PAYMENTS].[refinans_credit] " & _ "where [Ежемесячный платеж]>0 and [Номер заявки] ='" & Cells(i, 1) & "' "

Если значений для формирования запроса несколько, соответственно прописываем их в запросе:

"where [Ежемесячный платеж]>0 and [Номер заявки] = '" & Cells(i, 1) & "' " & _ " and [Дата платежа]='" & Cells(i, 2) & "'"

В целях самоконтроля я обычно записываю сформированный макросом запрос, чтобы иметь возможность проверить его корректность и работоспособность, для этого добавим вот такую строчку:

Cells(i, 3) = sql

в третьем столбце записываются запросы.

Выполняем SQL запрос:

Set rs = cn.Execute(sql)

А чтобы хоть как-то наблюдать за выполнением макроса выведем изменение i в статус-бар

Application.StatusBar = "Execute script ..." & i Application.ScreenUpdating = False

Теперь нам нужно записать полученные результаты. Для этого будем использовать оператор Do While:

j = 0 Do While Not rs.EOF For ii = 0 To rs.Fields.Count - 1 Cells(i, 4 + j + ii) = rs.Fields(0 + ii) '& ";"

Указываем ячейки для вставки полученных данных (4 в примере это номер столбца с которого начинаем запись результатов)

Next ii j = j + rs.Fields.Count s.MoveNext Loop rs.Close End If

— закрываем цикл If, если вводили дополнительные условия

Next i cn.Close Application.StatusBar = "Готово" End Sub

— закрываем макрос.

В дополнение хочу отметить, что данный макрос позволяет обращаться как к БД на MS SQL так и к БД Oracle, разница будет только в параметрах подключения и собственно в синтаксисе SQL запроса.

В приведенном примере для авторизации при подключении к БД используется доменная аутентификация.

А как быть если для аутентификации необходимо ввести логин и пароль? Ничего невозможного нет. Изменим часть макроса, которая отвечает за подключение к БД следующим образом:

sql = "Provider= SQLOLEDB.1;Password=********;User ID=********;Data Source= Storoge.company.ru\ Storoge;APP=SFM"

Но в этом случае при использовании макроса возникает риск компрометации Ваших учетных данных. Поэтому лучше программно удалять учетные данные после выполнения макроса. Разместим поля для ввода пароля и логина на листе и изменим макрос следующим образом:

sql = "Provider= SQLOLEDB.1;Password=" & Sheets("Лист аутентификации").TextBox1.Value & ";User ID=" & Sheets("Лист аутентификации ").TextBox2.Value & ";Data Source= Storoge.company.ru\ Storoge;APP=SFM"

Место для расположения текстовых полей не принципиально, можно расположить их на листе с таблицей в первых строках, но мне удобней размещать поля на отдельном листе. Чтобы введенные учетные данные не сохранялись вместе с результатом выполнения макроса в конце исполняемого кода дописываем:

Sheets(«Выгрузка»).TextBox1.Value = «« Sheets(»Выгрузка«).TextBox2.Value = »»

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

Вот такое вполне жизнеспособное решение, позволяющее сократить трудозатраты при получении и обработке данных, я использую. Надеюсь мой опыт применения SQL запросов в Excel будет полезен и вам в решении текущих задач.

{ "author_name": "NTA", "author_type": "editor", "tags": ["selectel_\u0438\u043d\u0441\u0442\u0440\u0443\u043a\u0446\u0438\u044f"], "comments": 4, "likes": 0, "favorites": 16, "is_advertisement": false, "subsite_label": "dev", "id": 158807, "is_wide": true, "is_ugc": false, "date": "Wed, 16 Sep 2020 21:16:38 +0300", "is_special": false }
(function () { let cdnUrl = `https://specialsf378ef5-a.akamaihd.net/SelectelBranding/images/` let previousArticleNumber = null let currentArticleNumber = 0 let platform = 'Desktop' let articles = [ // { // name: 'camera', // url: `${cdnUrl}CameraCat`, // text: 'умную камеру для\u00A0наблюдения за\u00A0котиками', // link: '1', // }, { name: 'chill', url: `${cdnUrl}ChillCat`, text: 'трекер, который подскажет, когда пора отдохнуть', link: 'https://vc.ru/promo/288561-eye-tracker', }, // { // name: 'cloud', // url: `${cdnUrl}CloudCat`, // text: 'котика: даёшь ему «пять», а\u00A0он делает бэкап в облако', // link: '3', // } ] let buttonCycle = document.querySelector('.button--cycle') let textField = document.querySelector('.selectel-footer-subtitle') let imageAgent = document.querySelector('.image--agent') let banner = document.querySelector('.selectel-footer') buttonCycle.addEventListener('click', cycleClick) let media = window.matchMedia("(max-width: 570px)") media.addEventListener('change', matchMedia) function matchMedia() { if (media.matches) { platform = 'Mobile' } else { platform = 'Desktop' } update() } matchMedia() function cycleClick(event) { if (event) { event.preventDefault() event.stopPropagation() } window.open('https://vc.ru/tag/selectelDIY', '_blank') //cycle(event) } function cycle(event) { // incrementArticleNumber() textField.innerHTML = generatedText() imageAgent.src = articles[currentArticleNumber].url + platform + '.svg?5' imageAgent.setAttribute("class", "") imageAgent.classList.add('image--agent', articles[currentArticleNumber].name) banner.href = articles[currentArticleNumber].link } function update() { banner.href = articles[currentArticleNumber].link imageAgent.src = articles[currentArticleNumber].url + platform + '.svg?5' textField.innerHTML = generatedText() } function incrementArticleNumber() { previousArticleNumber = currentArticleNumber if (currentArticleNumber >= articles.length - 1) { currentArticleNumber = 0 } else { currentArticleNumber++ } } function generatedText() { let defaultText if (platform === 'Desktop') { defaultText = `Мы тут собрали %text%. Хотите почитать?` } else { defaultText = `Мы тут собрали %text%.` } return defaultText.replace('%text%', articles[currentArticleNumber].text) } function getRandom(min, max) { min = Math.ceil(min) max = Math.floor(max) return Math.floor(Math.random() * (max - min + 1)) + min } (function create() { currentArticleNumber = getRandom(0, articles.length - 1) cycle() let page = document.querySelector('.page--entry') if (page) { function insertAfter() { let parents = page.querySelectorAll('[data-id="7"]') let referenceNode = parents[0] referenceNode.parentNode.insertBefore(banner, referenceNode.nextSibling); loaded() } setTimeout(() => insertAfter(), 0) } }()) function loaded() { banner.classList.add('loaded') } loadImages([ `${cdnUrl}CameraCatDesktop.svg`, `${cdnUrl}ChillCatDesktop.svg`, `${cdnUrl}CloudCatDesktop.svg`, `${cdnUrl}CameraCatMobile.svg`, `${cdnUrl}ChillCatMobile.svg`, `${cdnUrl}CloudCatMobile.svg`, ]) function loadImages(urls) { return Promise.all(urls.map(function (url) { return new Promise(function (resolve) { var img = document.createElement('img'); img.onload = resolve; img.onerror = resolve; img.src = url; }); })); } }())
0
4 комментария
Популярные
По порядку

Дополню: в конце статьи по ссылке ниже - несколько макросов, которые позволяют "переводить" запрос sql в переменную VBA и обратно

https://ytikhonov.wordpress.com/2015/10/12/excel-vba-sql/ ред.

2
Дальний клуб

Это 2000 год? Зачем макросы , если Давно уже есть ms query , который превратился в powerquery. С 2007 excel все это делается без кода

1

Зачем вообще подключаться к удалённому экселю и пытаться его использовать как БД, используя для этого SQL? Я, может быть, выскажу непопулярное мнение, но имея данные в excel таблице куда проще их слить в БД и работать нативными инструментами аналитики, нежели городить костыли с макросами. Времени опять же меньше уйдет. 

0

Использование PQ не освобождает от использования SQL, равно как в некоторых случаях связка VBA + SQL является оптимальным решением.

Знаю одну компанию, где сделали простенькую CRM на базе Excel с этой связкой. На PQ такое не провернуть - им нужно редактирование и занесение записей в БД через привычный интерфейс

2
Читать все 4 комментария
Нужны ли в России сити-фермы

И появятся ли грядки на крышах пятиэтажек.

re-thinkingthefuture.com
Создал ледовый комбайн в 50, а вместе с ним и рынок таких машин, который сразу захватил — это изобретатель Фрэнк Замбони Статьи редакции

Иногда ледозаливочные машины других производителей по ошибке называют «Замбони», пишет The Hustle.

Ледовый комбайн Замбони Time
Хомяк-криптотрейдер заработал 30% за три месяца в «клетке для торговли» — обойдя Уоррена Баффета и S&P 500 Статьи редакции

Мистер Гокс занимается торговлей криптовалютой с 12 июня.

Эксперты Httpool выступят на конференции по глобальному маркетингу Globalize! 2021

Событие соберет самых продвинутых специалистов в сфере трафика и аналитики из Google, Httpool, AppsFlyer, Aitarget, Angle Connect, TikTok, OWOX, SHAREit, Tribuna, SportQuake, LCFC, FBS.

«Яндекс» отказывается решать проблему с сетью Тele2 на «Яндекс.Телефон». Проблема вызвана отсутствием поддержки

Так случилось, что стал обладателем «Яндекс.Телефона» в ноябре прошлого года. Аппарат был куплен через площадку «Яндекс.Маркет» (Заказ № 28218750). Практически через неделю вместо оператора появилась уже ставшая знаменитой в узких кругах (среди владельцев «Яндекс.Телефонов») ошибка с крестами.

Quick FAQ: Осенняя хандра и то, как её победить

Привет! Это Дмитрий, автор канала «Ноотропы и разгон мозга» и сегодня я хочу поговорить с вами о том, что называют «осенняя хандра» или по-научному — Seasonal Affective Disorder (сезонное аффективное расстройство).

Я скрестил «Трибуну» и Product Hunt

Теперь вы никогда не пропустите самые перспективные русскоязычные стартапы.

Как мы проводили командную ретроспективу в Minecraft

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

Как испортить лучшую систему комментариев в рунете на примере vc.ru

Иногда мне кажется, на vc.ru идёт прогресс дизайна ради дизайна, а не ради прогресса. Вот и до комментариев добрались.

Готовы выбрать победителя премии «Экспортер года eBay — 2021»?
Как традиционному малому бизнесу превратиться в стартап: план действий

Сейчас в России предприниматели переходят из традиционного малого бизнеса в стартапы очень редко — меньше чем в 0,02% случаев. Это не больше 1 000 стартапов из около 6 млн предприятий малого бизнеса. Поговорим о том, что мешает предпринимателям и как действовать, если есть желание создать стартап.

null