Как sql запрос выгрузить в excel

Импорт данных из базы данных с помощью запроса на ее языке (Power Query)

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

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

Ввод запроса на языке базы данных

При подключении к базе данных можно указать SQL в поле Заявление о SQL базы данных. В примере ниже мы импортируем данные из SQL Server базы данных с помощью запроса на ее языке. Эта процедура аналогична процедуре для всех остальных поддерживаемых баз данных.

1. Выберите Данные>Получить данные >из баз данных > из SQL Server базы данных.Появится База данных SQL диалоговое окно.
2. Укажите сервер и базу данных, из которых вы хотите импортировать данные с помощью запроса на языке базы данных.
3. Выберите элемент Дополнительные параметры .
4. Введите запрос базы данных в поле SQL базы данных.

В зависимости от базы данных могут быть доступны дополнительные параметры.

Нажмите ОК.

Если вы подключаетсяе к этому серверу впервые, вы увидите запрос на выбор режима проверки подлинности для подключения к базе данных. Выберите нужный режим проверки подлинности и нажмите кнопку «Продолжить».

Данные возвращаются в редактор Power Query.

Вы можете сформировать данные, а затем нажать кнопку Применить & Закрыть, чтобы сохранить изменения и импортировать данные на этот таблицу.

Примечание При импорте базы данных Access она не отображает SQL, но в окне Навигатор отображаются запросы. Запрос Access основан на SQL и вы можете выбрать его, чтобы указать SQL запроса.

Изменение запроса на языке базы данных

Чтобы изменить содержимое SQL заявления:

1. Выберите ячейку в запросе, основанном на источнике базы данных.
2. Выберите запрос>изменить. Появится редактор Power Query.
3. В области Параметры запроса слева в области Примененные шаги щелкните правой кнопкой мыши шаг Источник и выберите изменить Параметры. Появится диалоговое окно Навигатор.
4. Внести изменения в поле выписки SQL, а затем выберите ОК.

Выполнять 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 идентифицируются как имена столбцов таблицы.

Экспорт результатов запроса в Excel

Нужно выгрузить результаты запроса SQL в таблицу EXCEL.

Обрывки информации в сети есть, но не стыкуются в полную картину. Кто-нибудь делал такое?

Добавлено через 1 час 1 минуту

1 2 3 4 5 6

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\temp\temp.xls;HDR=YES', [Sheet1$]) SELECT 'TEST1' AS Line1 --'TEST2' AS Line2 FROM dbo.Database

Позволяет выводить только один столбец, если раскоментить TEST2, то вываливает ошибку

Сообщение 213, уровень 16, состояние 1, строка 10
Column name or number of supplied values does not match table definition.

94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:

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

Экспорт данных из запроса или таблицы используя джоб MS SQL в Excel
Добрый день. Никак не могу сделать авто-выгрузку в файлы эксель из SQL. Запрос select * from.

Экспорт результатов моделирования в Excel
Здравствуйте, возник вопрос: можно ли в эксперименте варьирования параметров записывать результаты.

Экспорт результатов расчета в Excel
Всем привет! Работаю в Mathcad PTC. Пытаюсь экспортировать результаты расчета в Excel. На.

Не знаете, как выгрузить данные из SQL в Excel для отчета?

Итак, перед нами поставлена задача сделать автоматизированный отчет на основании данных сервера SQL . Есть несколько способов решения данной задачи: вы можете использовать Microsoft Office Access, но мы п ойдем простым путем и сделаем этот отчет в Microsoft Office Excel. ВНИМАНИЕ: для создания данного отчета Вам необходимо проверить настройки (Источники данных (ODBC) в п анели управления. В разделе системный DNS добавьте коннектор, который будет смотреть на Ваш сервер SQL . Также проверьте, что для Ваш ей учетн ой записи доступны те вьюхи и те таблицы которые вам нужны для отчета. После настройки ODBC мы откр ываем Microsoft Office Excel.

На листе 2 пишем период отчета.

Переходим на страницу куда будут выгружаться данны е. Я переименовал страницу «Лист1» в «Запрос». Теперь необходимо сделать связку на наш SQL сервер «Действие >Импорт внешних данных > Создать запрос»

В данном окне Вам необходимо вбить логин и пароль на доступ к SQL серверу. Который Вам должен предоставить администратор SQL сервера.

В водим пароль и логин.

Далее вываливается список доступных таблиц и вьюх по данному логину.

Жмем отмена.
В диалоговом окне « Microsoft Query »

Выбираем вьюху или таблицу из которой вам нужно выгружать данные.

Видим такую картинку

Нажать на эту кнопку.

Выбираем поле дата_создания и в значении пишем

Внимание: Если у Вас для выгрузки используется другое поле, не ДАТА_СОЗДАНИЯ, как у меня, то Вам нужно указать ключ по которому Вы будете выгружать данные из базы.(Можно без ключа, только не забывайте про ограничения Exl )

Теперь сохраняем запрос.

Дважды щелкнуть на * (Левой кнопкой мыши)

Указать начало периода.

Указать окончание периода.

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

Нажмите «Файл» «Вернуть данные в Microsoft Office Excel»

Указали строчку с которой начнутся выгружаться данные.
На листе «Запрос»

Нажимаем на Параметры

Видим картинку параметров.

Выбираем нижнее значение и указываем поле, в котором будет указанна дата начала периода.

То, что указанно на Листе 2

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

Жмем ок

Теперь сохраняем файл.
Переименовываем страницу.

Ну а дальше дело техники.

Теперь для обновления отчета Вам понадобится только сменить интервал времени и обновить данные на листе «Запрос». Правой кнопкой на данные и выбрать пункт «Обновить запрос»

Мы получили данные с SQL в Microsoft Office Excel. Для анализа данных используете связанные таблицы, или Вы можете написать свои VB скрипты.