Выполнять SQL-запросы к файлам Excel
Хотя действия Excel могут обрабатывать большинство сценариев автоматизации Excel, запросы SQL могут более эффективно извлекать значительные объемы данных Excel и работать с ними.
Предположим, поток должен изменить только те реестры Excel, которые содержат определенное значение. Чтобы реализовать эту функциональность без SQL-запросов, вам потребуются циклы, условные выражения и несколько действий Excel.
Вы также можете реализовать эту функциональность с помощью SQL-запросов, используя только два действия: Открыть SQL-подключение и Выполнять инструкции SQL.
Откройте SQL-подключение к файлу Excel
Перед запуском SQL-запроса вы должны открыть подключение с файлом Excel, к которому вы хотите получить доступ.
Чтобы установить подключение, создайте новую переменную с именем %Excel_File_Path% и инициализируйте его, указав путь к файлу Excel. При желании вы можете пропустить этот шаг и использовать жестко заданный путь к файлу позже в потоке.
Теперь разверните действие Открыть SQL-подключение и заполните следующую строку подключения в его свойствах.
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%Excel_File_Path%;Extended Properties=»Excel 12.0 Xml;HDR=YES»;
Для успешного использования представленной строки подключения вам необходимо скачать и установить Распространяемый пакет ядра СУБД Microsoft Access 2010.
Откройте SQL-подключение к файлу Excel, защищенному паролем
Другой подход требуется в сценариях, где вы запускаете SQL-запросы к файлам Excel, защищенным паролем. Действие Открыть SQL-подключение не может подключиться к файлам Excel, защищенным паролем, поэтому вам необходимо снять защиту.
Для этого запустите файл Excel с помощью действие Запустить Excel. Файл защищен паролем, поэтому введите соответствующий пароль в поле Пароль.
Затем разверните соответствующие действия автоматизации пользовательского интерфейса и перейдите к Файл>Информация>Защита книги>Зашифровать паролем. Дополнительные сведения об автоматизации пользовательского интерфейса и о том, как использовать соответствующие действия можно найти в Автоматизировать классические приложения.
После выбора Зашифровать паролем заполните пустую строку во всплывающем диалоговом окне, используя действие Заполнить текстовое поле в окне. Чтобы заполнить пустую строку, используйте следующее выражение: %»»%.
Чтобы нажать на ОК в диалоговом окне и применить изменения, разверните действие Нажать кнопку в окне.
Наконец, разверните действие Закрыть Excel, чтобы сохранить незащищенную книгу как новый файл Excel.
После сохранения файла следуйте инструкциям в Открытие SQL-подключения к файлу Excel, чтобы открыть к нему подключение.
Когда работа с файлом Excel будут завершена, используйте действие Удалить файлы для удаления незащищенной копии файла Excel.
Чтение содержимого электронной таблицы Excel
Хотя действие Считать с листа Excel может считывать содержимое листа Excel, циклы могут занять значительное время для итерации полученных данных.
Более эффективный способ получения определенных значений из электронных таблиц — это рассматривать файлы Excel как базы данных и выполнять на них SQL-запросы. Этот подход быстрее и увеличивает производительность потока.
Чтобы получить все содержимое электронной таблицы, вы можете использовать следующий SQL-запрос в действие Выполнить инструкцию SQL.
SELECT * FROM [SHEET$] 
Чтобы применить этот SQL-запрос в ваших потоках, замените заполнитель SHEET именем электронной таблицы, к которой вы хотите получить доступ.
Чтобы получить строки, содержащие определенное значение в определенном столбце, используйте следующий запрос SQL:
SELECT * FROM [SHEET$] WHERE [COLUMN NAME] = 'VALUE' Чтобы применить этот SQL-запрос в ваших потоках, замените:
- SHEET с именем электронной таблицы, к которой вы хотите получить доступ.
- COLUMN NAME столбцом, содержащим значение, которое вы хотите найти. Столбцы в первой строке листа Excel идентифицируются как имена столбцов таблицы.
- VALUE со значением, которое вы хотите найти.
Удалить данные из строки Excel
Хотя Excel не поддерживает SQL-запрос DELETE, вы можете использовать запрос UPDATE, чтобы установить для всех ячеек определенной строки значение NULL.
Точнее, вы можете использовать следующий SQL-запрос:
UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE' 
При разработке потока вы должны заменить заполнитель SHEET именем электронной таблицы, к которой вы хотите получить доступ.
Заполнители COLUMN1 а также COLUMN2 представляют имена всех столбцов для обработки. В этом примере два столбца, но в реальном сценарии количество столбцов может быть другим. Столбцы в первой строке листа Excel идентифицируются как имена столбцов таблицы.
Часть запроса [COLUMN1]=’VALUE’определяет строку, которую вы хотите обновить. В вашем потоке используйте имя столбца и значение в зависимости от того, какая комбинация однозначно описывает строки.
Получить данные Excel, кроме определенной строки
В некоторых сценариях может потребоваться получить все содержимое электронной таблицы Excel, кроме определенной строки.
Удобный способ добиться этого результата — установить для значений нежелательной строки значение NULL, а затем получить все значения, кроме нулевых.
Чтобы изменить значения определенной строки в электронной таблице, вы можете использовать SQL-запрос UPDATE, представленный в Удалить данные из строки Excel:
UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'
Затем выполните следующий SQL-запрос, чтобы получить все строки электронной таблицы, не содержащие значений NULL:
SELECT * FROM [SHEET$] WHERE [COLUMN1] IS NOT NULL OR [COLUMN2] IS NOT NULL Заполнители COLUMN1 а также COLUMN2 представляют имена всех столбцов для обработки. В этом примере два столбца, но в реальной таблице количество столбцов может быть другим. Все столбцы в первой строке листа Excel идентифицируются как имена столбцов таблицы.
Как из sql выгрузить в excel
Есть несколько способов выгрузки данных из SQL в Excel. Рассмотрим несколько из них:
- Используйте функцию экспорта в Excel в среде управления базами данных. Некоторые программы, такие как Microsoft SQL Server Management Studio, MySQL Workbench или phpMyAdmin, позволяют экспортировать результаты SQL-запросов в формате Excel. Для этого необходимо выполнить SQL-запрос, затем выбрать опцию экспорта в Excel в меню программы.
- Используйте SQL-запрос, который выводит результаты в формате CSV, а затем откройте CSV-файл в Excel. Для этого выполните SQL-запрос с использованием ключевого слова INTO OUTFILE , чтобы сохранить результаты запроса в CSV-файл. Например:
SELECT * FROM таблица INTO OUTFILE 'путь_к_файлу.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; После выполнения этого запроса CSV-файл будет сохранен в указанном каталоге. Откройте Excel и выберите опцию «Открыть файл», затем выберите CSV-файл и откройте его в Excel.
Подключение Excel к SQL Server
Для обработки данных в Excel требуется сначала получить сами данные. Часто такая информация находится во внешних источниках, а именно базах данных. В этой статье описывается подключение электронных таблиц к БД MS SQL Server.
На вкладке «Данные» имеется область «Получение внешних данных» на которой необходимо кликнуть по кнопке «Из других источников». Раскроется список источников, к которым можно осуществить подключение. В этом списке выберите пункт «С сервера SQL Server».
Теперь на экране появилось окно мастера подключения к данным. Изначально требуется указать имя сервера и учетные сведения для подключения (если Вы их не знаете, то обратитесь к администратору БД).
После заполнения формы нажмите кнопку «Далее», чтобы перейти к выбору базы данных.
Здесь же можно выбрать таблицы, из которых будут грузиться данные, или же снять галочку с пункта «Подключение к определенной таблице».
Нажмите кнопку «Далее», чтобы задать имя файла подключения и описание, чтобы в дальнейшем было проще найти данный файл.
Нажав кнопку «Готово», Вам будет предложено произвести импорт данных с сервера (если в мастере подключений Вами не была выбрана таблица, то сначала ее придется выбрать).
В текущем окне можно выбрать место и способ представления импортируемых данных: таблица, сводная таблица, сводная диаграмма или пропустить импорт, выбрав пункт «Только создать подключение».
SQL-запрос
По умолчанию, при импорте из SQL Server, Excel выгружает всю таблицу. Можно изменить выборку, указав SQL-запрос.
Для этого во время импорта данных (предыдущий рисунок) необходимо кликнуть по кнопке «Свойства», если подключение создается впервые, либо, если файл подключения уже был добавлен к книге, на вкладке «Данные» в области «Получение внешних данных» кликнуть по кнопке «Подключения», выбрать нужное из списка и открыть его свойства.
В свойствах подключения на вкладке «Определение» в поле «Тип команды» поменяйте значение на «SQL», а в поле «Текст команды» введите SQL-код и нажмите «ОК».
Теперь, чтобы получить данные с помощью нового подключения, достаточно вызвать его из существующих подключений на вкладке «Данные» в области «Получение внешних данных». Дальнейшая процедура импорта аналогична рассмотренному выше.
- Подключение MySQL в Excel
- Критерий Манна-Уитни
- Подключение MySQL в Excel
- Подключение Excel к SQL Server
Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы я мог развивать его дальше.
Формируем 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 Source= Storoge.company.ru\ Storoge;APP=SFM"Но в этом случае при использовании макроса возникает риск компрометации Ваших учетных данных. Поэтому лучше программно удалять учетные данные после выполнения макроса. Разместим поля для ввода пароля и логина на листе и изменим макрос следующим образом:
sql = "Provider= SQLOLEDB.1;Password=" & Sheets("Лист аутентификации").TextBox1.Value & ";User Лист аутентификации ").TextBox2.Value & ";Data Source= Storoge.company.ru\ Storoge;APP=SFM"Место для расположения текстовых полей не принципиально, можно расположить их на листе с таблицей в первых строках, но мне удобней размещать поля на отдельном листе. Чтобы введенные учетные данные не сохранялись вместе с результатом выполнения макроса в конце исполняемого кода дописываем:
Sheets("Выгрузка").TextBox1.Value = "" Sheets("Выгрузка").TextBox2.Value = ""То есть просто присваиваем текстовым полям пустые значения, таким образом после выполнения макроса поля для ввода пароля и логина окажутся пустыми.
Вот такое вполне жизнеспособное решение, позволяющее сократить трудозатраты при получении и обработке данных, я использую. Надеюсь мой опыт применения SQL запросов в Excel будет полезен и вам в решении текущих задач.