Sql-запросы в Excel

Обновлено: 23.11.2024

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

Предположим, поток должен изменить только те реестры Excel, которые содержат определенное значение. Чтобы использовать эту функцию без запросов SQL, вам нужны циклы, условия и несколько действий Excel.

Наоборот, вы можете реализовать эту функцию с запросами SQL, используя только два действия: действие "Открыть соединение SQL" и действие "Выполнить операторы SQL".

Открыть SQL-соединение с файлом Excel

Перед выполнением SQL-запроса необходимо установить соединение с файлом Excel, к которому вы хотите получить доступ.

Чтобы установить подключение, создайте новую переменную с именем %Excel_File_Path% и инициализируйте ее путем к файлу Excel. При желании вы можете пропустить этот шаг и использовать жестко заданный путь к файлу позже.

Теперь разверните действие подключения Open SQL и заполните следующую строку подключения в его свойствах.

Provider=Microsoft.ACE.OLEDB.12.0;Источник данных=%Excel_File_Path%;Расширенные свойства="Excel 12.0 Xml;HDR=YES";

Чтобы успешно использовать представленную строку подключения, необходимо загрузить и установить распространяемый компонент Microsoft Access Database Engine 2010.

Открыть SQL-соединение с защищенным паролем файлом Excel

В сценариях, где вы выполняете SQL-запросы к файлам Excel, защищенным паролем, требуется другой подход. Действие подключения Open SQL не может подключиться к файлам Excel, защищенным паролем, поэтому вам необходимо снять защиту.

Для этого запустите файл Excel с помощью действия Запустить Excel. Файл защищен паролем, поэтому введите соответствующий пароль в поле «Пароль».

Затем разверните соответствующие действия автоматизации пользовательского интерфейса и перейдите в меню «Файл» > «Информация» > «Защитить книгу» > «Зашифровать с помощью пароля». Дополнительную информацию об автоматизации пользовательского интерфейса и о том, как использовать соответствующие действия, можно найти в разделе Автоматизация потоков рабочего стола.

После выбора «Зашифровать с помощью пароля» заполните пустую строку во всплывающем диалоговом окне с помощью действия «Заполнить текстовое поле» в Windows. Чтобы заполнить пустую строку, используйте следующее выражение: %""%.

Чтобы нажать кнопку "ОК" в диалоговом окне и применить изменения, разверните действие "Нажать кнопку в окне".

Наконец, разверните действие «Закрыть Excel», чтобы сохранить незащищенную книгу в виде нового файла Excel.

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

После завершения работы с файлом Excel используйте действие Удалить файл(ы), чтобы удалить незащищенную копию файла Excel.

Чтение содержимого электронной таблицы Excel

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

Более эффективный способ извлечения определенных значений из электронных таблиц — рассматривать файлы Excel как базы данных и выполнять к ним SQL-запросы. Такой подход быстрее и повышает производительность потока.

Чтобы получить все содержимое электронной таблицы, вы можете использовать следующий SQL-запрос в действии «Выполнить операторы SQL».

Чтобы применить этот SQL-запрос в своих потоках, замените заполнитель SHEET на имя электронной таблицы, к которой вы хотите получить доступ.

Чтобы получить строки, содержащие определенное значение в определенном столбце, используйте следующий запрос SQL:

Чтобы применить этот SQL-запрос к потокам, замените:

SHEET с названием электронной таблицы, к которой вы хотите получить доступ.
COLUMN NAME со столбцом, содержащим значение, которое вы хотите найти. Столбцы в первой строке рабочего листа Excel идентифицируются как имена столбцов таблицы.
VALUE со значением, которое вы хотите найти

Удалить данные из строки Excel

Хотя Excel не поддерживает SQL-запрос DELETE, вы можете использовать запрос UPDATE, чтобы установить для всех ячеек определенной строки значение null.

Точнее, вы можете использовать следующий SQL-запрос:

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

Заполнители COLUMN1 и COLUMN2 представляют имена всех существующих столбцов. В этом примере столбцов два, но в реальном сценарии количество столбцов может быть другим. Столбцы в первой строке рабочего листа Excel идентифицируются как имена столбцов таблицы.

Часть [COLUMN1]='VALUE' запроса определяет строку, которую нужно обновить. В своем потоке используйте имя столбца и значение, в зависимости от того, какая комбинация однозначно описывает строки.

Получить данные Excel, кроме определенной строки

В некоторых случаях может потребоваться получить все содержимое электронной таблицы Excel, кроме определенной строки.

Удобный способ добиться этого результата – установить для значений нежелательной строки значение null, а затем получить все значения, кроме нулевых.

Чтобы изменить значения определенной строки в электронной таблице, вы можете использовать SQL-запрос UPDATE, как показано в разделе Удаление данных из строки Excel:

Затем выполните следующий SQL-запрос, чтобы получить все строки электронной таблицы, не содержащие пустых значений:

Заполнители COLUMN1 и COLUMN2 представляют имена всех существующих столбцов. В этом примере столбцов два, но в реальной таблице количество столбцов может быть другим. Все столбцы в первой строке рабочего листа Excel идентифицируются как имена столбцов таблицы.

В большинстве электронных таблиц Excel вы вручную вводите данные в ячейки, а затем используете формулы или другие функции для их анализа или выполнения вычислений. Если у вас есть большой источник данных, например база данных Access, база данных SQL Server или даже большой текстовый файл, вы также можете извлечь данные из него с помощью Excel. Использование операторов SQL в Excel позволяет подключаться к внешнему источнику данных, анализировать содержимое полей или таблиц и импортировать данные — и все это без необходимости вводить данные вручную. После импорта внешних данных с операторами SQL их можно сортировать, анализировать или выполнять любые расчеты, которые могут вам понадобиться.

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

Нажмите «Данные» на ленте Excel. Щелкните значок «Из других источников» в разделе «Получить внешние данные». Нажмите «Из Microsoft Query» в раскрывающемся меню.

Нажмите на тип источника данных в окне «Выбор источника данных». Нажмите и включите параметр «Использовать мастер запросов для создания/редактирования запросов», а затем нажмите «ОК». Сначала появится окно «Подключение к базе данных», а через несколько секунд появится окно браузера «Выбрать файл базы данных».

Перейдите к папке и файлу для вашей базы данных или файла источника данных. Выделите имя файла источника данных и нажмите «ОК». На экране появится окно мастера запросов.

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

При желании выберите параметры фильтра для данных, которые нужно извлечь и отобразить в электронной таблице. Создавая фильтр для данных в одном или нескольких полях, вы указываете Excel извлекать из источника данных только те данные, которые соответствуют определенным условиям или критериям. Например, если ваш источник данных содержит список клиентов и их контактную информацию, у вас может быть поле в таблице для телефонных номеров. Если вы хотите получить из источника данных только клиентов с кодом города (919), вы можете сделать это, применив фильтр. Нажмите «Номер телефона» или другое поле с аналогичным названием на панели «Столбец для фильтра» и выберите «содержит» в списке типов фильтра. Введите "919" в следующее поле и нажмите "Далее".

Выберите порядок сортировки по возрастанию или убыванию для записей, извлекаемых из источника данных. Нажмите кнопку «Далее». Включите параметр «Вернуть данные в Microsoft Excel» и нажмите кнопку «Готово».

Нажмите и включите параметр «Таблица» в окне «Импорт данных». Включите параметр «Существующий рабочий лист» и щелкните значок «красная стрелка» справа от поля ячейки под меткой «Существующий рабочий лист». Нажмите и выберите ячейку, в которую вы хотите поместить правый верхний угол таблицы данных, содержащей записи из внешнего источника данных.

Нажмите "ОК". Excel отображает таблицу с извлеченными записями, которые возвращаются в результате базового SQL-запроса источника данных с помощью мастера запросов.

Просмотрите запрос SQL, чтобы увидеть, как код извлекает данные из внешнего источника данных. Нажмите «Существующие подключения» на вкладке «Данные». Щелкните значок «Запрос из типа источника данных» в разделе «Подключения в этой книге» окна «Существующие подключения». На экране появится окно импорта данных.

Нажмите кнопку "Свойства". В окне «Свойства подключения» перейдите на вкладку «Определение». Найдите текст в поле Текст команды. Там появляется собственный код SQL-запроса. Для SQL-запроса, извлекающего записи из внешней таблицы с пометкой «Личные_контакты», код будет выглядеть примерно так: \Database1.accdb .tbl_Personal_Contacts tbl_Personal_Contacts

Нажмите кнопку «ОК», чтобы закрыть окно «Свойства подключения». При необходимости отредактируйте другие данные в электронной таблице и сохраните книгу.

Я пытаюсь создать подтаблицу из другой таблицы со всеми полями фамилий, отсортированными по алфавиту, в которых есть поле номера телефона, отличное от нуля. Я мог бы сделать это довольно легко с SQL, но я понятия не имею, как запустить SQL-запрос в Excel. У меня возникает соблазн импортировать данные в postgresql и просто запрашивать их там, но это кажется немного чрезмерным.

Для того, что я пытаюсь сделать, SQL-запрос SELECT lastname, firstname, phonenumber WHERE phonenumber IS NOT NULL ORDER BY lastname сделает свое дело. Это кажется слишком простым, чтобы Excel не мог сделать это изначально. Как запустить подобный SQL-запрос из Excel?

10 ответов 10

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

В Excel есть "Мастер подключения данных", который позволяет импортировать или связывать данные из другого источника или даже из того же файла Excel.

Как часть Microsoft Office (и ОС) представляют интерес два поставщика: старый "Microsoft.Jet.OLEDB" и новейший "Microsoft.ACE.OLEDB". Ищите их при настройке подключения (например, с помощью мастера подключения к данным).

После подключения к книге Excel рабочий лист или диапазон становится эквивалентом таблицы или представления. Имя таблицы рабочего листа — это имя рабочего листа со знаком доллара («$»), добавленным к нему и заключенным в квадратные скобки («[» и «]»); диапазона, это просто имя диапазона. Чтобы указать безымянный диапазон ячеек в качестве источника записи, добавьте стандартное обозначение строки/столбца Excel в конец имени листа в квадратных скобках.

Собственный SQL будет (более или менее) SQL Microsoft Access. (Раньше он назывался JET SQL, однако Access SQL эволюционировал, и я считаю, что JET — это устаревшая технология.)

Пример чтения листа: SELECT * FROM [Sheet1$]

Пример чтения диапазона: SELECT * FROM MyRange

Пример чтения безымянного диапазона ячеек: SELECT * FROM [Sheet1$A1:B10]

Существует множество книг и веб-сайтов, которые помогут вам разобраться в деталях.

Дополнительные примечания

По умолчанию предполагается, что первая строка вашего источника данных Excel содержит заголовки столбцов, которые можно использовать в качестве имен полей. Если это не так, вы должны отключить этот параметр, иначе ваша первая строка данных «исчезнет» и будет использоваться в качестве имен полей. Это делается путем добавления необязательного параметра HDR= в расширенные свойства строки подключения. Значение по умолчанию, которое не нужно указывать, — HDR=Yes. Если у вас нет заголовков столбцов, вам нужно указать HDR=No ; провайдер называет ваши поля F1, F2 и т. д.

Предупреждение об указании рабочих листов: провайдер предполагает, что ваша таблица данных начинается с самой верхней, самой левой и непустой ячейки на указанном листе. Другими словами, ваша таблица данных может без проблем начинаться со строки 3 столбца C. Однако вы не можете, например, ввести заголовок листа выше и слева от данных в ячейке A1.

Предупреждение об указании диапазонов: когда вы указываете рабочий лист в качестве источника записей, провайдер добавляет новые записи ниже существующих записей на рабочем листе, если позволяет место. Когда вы указываете диапазон (с именем или без имени), Jet также добавляет новые записи ниже существующих записей в диапазоне, если позволяет пространство. Однако если вы запросите исходный диапазон, результирующий набор записей не будет включать вновь добавленные записи за пределами диапазона.

Типы данных (стоит попробовать) для CREATE TABLE: Short, Long, Single, Double, Currency, DateTime, Bit, Byte, GUID, BigBinary, LongBinary, VarBinary, LongText, VarChar, Decimal .

Часто таблицы Excel постепенно превращаются в большие неудобные книги. Поиск дубликатов, фильтрация, группировка, сложная сортировка, объединение таблиц в одну и т. д. — становятся настоящим вызовом. Потенциально эти задачи можно было бы легко выполнить с помощью SQL... если бы только можно было выполнять SQL-запросы к данным Excel.

Надстройка XLTools SQL Queries расширяет возможности Excel с помощью языка структурированных запросов:

Прежде чем начать, добавьте SQL-запросы в Excel

Запросы SQL — это одна из более чем 20 функций надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, Office 365 для настольных ПК.

Как превратить данные Excel в реляционную базу данных и подготовиться к SQL-запросам

По умолчанию Excel представляет данные в виде простых диапазонов данных. Но SQL работает только с реляционными источниками данных. Поэтому, прежде чем начать, убедитесь, что диапазоны Excel отформатированы как таблицы (именованные диапазоны с примененным стилем таблицы):

Выберите таблицу. Откройте вкладку "Дизайн". Введите имя таблицы.

Повторите эти шаги для каждого диапазона данных, который вы планируете использовать в запросах SQL.

Например. таблицы "RetailPrice", "SalesVolume" и т.д.

Как создать и запустить SQL SELECT для таблиц Excel

Надстройка SQL Queries позволяет выполнять запросы к таблицам Excel, расположенным на разных листах и в разных книгах. Убедитесь, что эти рабочие книги открыты, а необходимые данные отформатированы как именованные таблицы.

Слева найдите древовидное представление всех доступных таблиц.

Нажимайте на узлы, чтобы развернуть/свернуть поля таблицы (столбцы).

Выберите целые таблицы или определенные поля.

По мере того, как вы отмечаете поля, оператор SELECT создается автоматически в правой части редактора.

Операторы Left Join, Order By, Group By, Distinct и другие команды SQLite в Excel

XLTools использует синтаксис SQLite. Пользователи, знакомые с SQLite, могут выполнять широкий спектр запросов:

Как соединить две или более таблиц Excel с помощью надстройки SQL Queries

Вы можете объединить несколько таблиц Excel в одну, если они имеют общее ключевое поле. Предположим, вам нужно объединить несколько таблиц, которые имеют общий столбец «ProductID»:

Нажмите Execute SQL на вкладке XLTools. Выберите поля, которые вы хотите видеть в объединенной таблице.

Когда вы отмечаете поля, SELECT и LEFT JOIN создаются автоматически.

Мария Балобанова Привет, Джон, XLTools использует SQLite и поддерживает операторы IS NULL и IS NOT NULL — вы можете использовать их в своем запросе.

Сир, можно ли здесь использовать функции DATEDIFF? Я пробовал пару разных комбинаций и, похоже, не работает. Благодарим вас за помощь.

Мария Балобанова Здравствуйте, сисир, наша надстройка основана на языке SQLite. Однако SQLite не поддерживает функцию DateDiff. Есть обходные пути, например. вычитание одной даты из другой, чтобы получить разницу.

Мария Балобанова Привет, Эми! Да, эта надстройка использует синтаксис SQLite, а также поддерживаются выражения CASE.

Сэйт Реншоу заметил, что когда я сохранял файл локально, все работало нормально. Если файл хранится в Onedrive, ему не удается найти книгу.

Здравствуйте, Мэтт! Спасибо, что сообщили об ошибке! Мы рассмотрим это.

А пока попробуйте следующее: снова загрузите XLTools > сохраните файл > запустите XLTools.exe и следуйте указаниям мастера установки > если вам будет предложено установить необходимые компоненты Microsoft, сделайте это.

Мария Балобанова Нет, Марсио, рабочий стол XLTools предназначен только для Windows. Вас может заинтересовать наше приложение SQL Queries — оно имеет аналогичную функциональность и работает в Excel для Mac. Приложение доступно в Microsoft Office Store.

Баба Андхейл Уважаемый сэр, я скачал XLTools и создал запрос, как мне нужно. Но я должен сохранить его имя и запустить макрос. Как это будет сделано, подскажите пожалуйста

Мария Балобанова Привет, Баба! Спасибо за интерес, проявленный к XLTools! Вы можете скопировать текст запроса в редакторе запросов SQL и использовать его в своем макросе, если вы это имеете в виду. Или вы можете попробовать нашу функцию автоматизации, чтобы выполнять SQL-запросы к таблицам Excel без сценариев VBA.

Deepak R. Rechwad Здравствуйте, сэр, поскольку мы являемся разработчиком программирования SQL, можем ли мы использовать ваш XLTools PRO для развертывания на нашем клиенте? напрямую генерировать свой отчет в excel? Пожалуйста, помогите мне. Спасибо Дипак Р. Речвад (Индия, Мумбия)

Мария Балобанова Уважаемый Дипак, Благодарим Вас за интерес, проявленный к XLTools! Вы можете создавать и запускать отчеты с помощью SQL-запросов XLTools для своих клиентов. Обратите внимание, что если вы планируете развернуть XLTools и на компьютерах ваших клиентов, им также потребуется лицензия PRO. Количество компьютеров = количество лицензий. Прежде чем покупать лицензию PRO, я настоятельно рекомендую вам воспользоваться бесплатной пробной версией и посмотреть, подойдет ли XLTools SQL Queries для отчетов, которые вы хотите разработать. Как только вы загрузите надстройку XLTools, вы автоматически получите бесплатную пробную лицензию на свою электронную почту. Дайте мне знать, если возникнут вопросы.

Мария Балобанова Привет, Туомо, это два разных инструмента для разных целей. Надстройка Microsoft Power Query позволяет работать с внешними данными. SQL Queries от XLTools — это надстройка, которая позволяет анализировать данные непосредственно в Excel. По сути, вы можете создавать и запускать запросы непосредственно к таблицам Excel, используя язык SQL. У вас могут быть обе надстройки на вашем компьютере.

Мор Сагмон Получившаяся таблица "живая"? то есть связаны с источниками данных и автоматически вычисляются при изменении исходных таблиц (например, Excel автоматически вычисляет зависимые ячейки)?

Неразбериха с Dana SQL Query . БЕСПЛАТНО . Купить 19,95 . Бесплатная пробная версия или 6,99 месяца. Есть ли разница между версией 19.95 и месячной версией 6.99? У меня Эксель 2013. Цена? - Это бесплатно! .. действительно вводит в заблуждение. Пожалуйста, порекомендуйте . Спасибо, Дана Мастерс

Джулиан Я предполагаю, что это позволяет запрашивать рабочие листы в ОДНОЙ книге? Если это так, и я раздаю эту книгу членам моей команды, будут ли запросы работать, если у участников не установлено добавление SQL-запросов?

Peter Liapin Привет, Джулиан! На этой странице мы разместили информацию о нашей надстройке COM, которую нельзя встроить в одну рабочую книгу, поэтому всем членам вашей команды, которым нужны SQL-запросы, также необходимо установить надстройку XLTools. Кстати, это позволяет выполнять SQL-запросы по нескольким таблицам в нескольких книгах, если вы открываете их все одновременно.

Читайте также: