Foreversoft.ru

IT Справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Выгрузить данные из sql в excel

Импорт данных SQL в Excel

Импорт данных SQL в Excel

Небольшой экскурс в MS SQL

Добрый день, уважаемые читатели и подписчики блога. Как вы уже догадались из названия статьи, речь сегодня пойдёт об импорте данных SQL в таблицу Excel.

Небольшое предисловие. Имеется база данных MS SQL, в которой содержится определённая таблица, её нужно загрузить в Excel.

Зачем использовать такую модель? Базы данных SQL могут быть объёмом гораздо больше чем файлы Excel и работают гораздо быстрее, а Excel используется как инструмент тонкой настройки загруженной таблицы.

Задача проста, но есть несколько моментов: необходимо знать, как называется инстанс (экземпляр) где находится база данных, естественно, нужно знать учётные данные, нужно знать название таблицы. Что ж, приступим.

Я предпочитаю в таких случаях использовать Microsoft Management Studio, для поиска основных данных. Найти ярлык можно в меню Пуск.

В консоли MS SQL есть несколько полей:

Server Type — можно выбрать тип подключения (в этом случаем остаётся Database Engine);

  • Analysis Services — сервисы аналитики;
  • Reporting Services — сервисы отчётов;
  • Integration Services — сервисы интеграции (встраивания).

Далее следует имя сервера (его можно задать на этапе установки).

Тип аутентификации — WIndows Authentication или SQL Server Authentication, эти пункты позволяют выбрать тип проверки пользователя. Windows — можно войти под логином и паролем для операционной системы, SQL Server — под специальной учётной записью sa и заданным для неё паролем.

Оставим первый вариант — он проще (но запомните — только для тестов!). Хорошим тоном считается смешанная аутентификация и смена пароля для учётки sa на случай непредвиденных ситуаций.

Видно, что имя сервера — BLACKPRINCE, логин axiro. Пароль не скажу:) Осталось нажать кнопку «Connect» и зайти в базу данных.

Поди цифрой 1 — список баз данных, под 2 — тестовая база с именем Test, под 3 — запущенный SQL Server Agent — он должен быть запущен иначе база данных может быть не видна для других программ, если на нем стоит крест — щёлкнуть правой кнопкой мышки и выбрать «Start».

Исходные данные есть, теперь необходимо создать таблицу с данными. Это можно сделать через графический интерфейс, но лучше воспользоваться запросом — «New Query».

Откроется окно запроса.

Все запросы будут производиться на языке Transact SQL. У него очень много общего с синтаксисом SQL, но есть и много отличий.

Создаём базу данных

Если необходимо создать новую базу данных, например, магазины (SHOPS), синтаксис будет следующий:

CREATE DATABASE SHOPS

Обязательно после написания запроса нажать «Execute» или F5! В противном случае запрос не будет выполнен.

Под цифрой 1 — «Execute», под 2 — текст запроса, под 3 — результат выполнения. Если в процессе выполнения будут выявлены ошибки, они незамедлительно будут показаны. Пока всё идёт по плану.

Создаём таблицу

Если необходимо выполнить запрос к определённой базе данных — щёлкаем правой кнопкой на неё и выбираем «New query».

В окне запроса введём текст запроса. Цель — создать таблицу с четырьмя столбцами: номер магазина, название магазина, выручка магазина, менеджер. Лучше все столбцы называть по английски (хотя бы на первоначальном этапе).

Читать еще:  Excel не сохраняет при закрытии

CREATE TABLE dbo.Shops
(ShopID int PRIMARY KEY NOT NULL,
ShopName varchar(25) NOT NULL,
Revenue money NULL,
Manager text NULL)
GO

Нажать «Execute». Таблица создалась. Проверить можно раскрыв дерево базы данных и раскрыть группу «Tables».

Цель достигнута. В запросе также были упомянуты типы данных (varchar(25), money, text, PRIMARY KEY). Соответственно — varchar это длина текста, в скобках указано, что длина названия магазина может быть до 25 символов включительно, money — тип данных, представляющий денежные (валютные) значения, text — обычный текст, PRIMARY KEY — ограничение, позволяющее однозначно идентифицировать каждую запись в таблице SQL. Также есть атрибут NULL и NOT NULL — позволяет значениям принимать нулевое значение или не принимать (может в поле стоять ноль или не может).

Добавим в таблицу одну строчку (пока). Открываем окно нового запроса базе данных и добавляем запрос.

INSERT INTO dbo.Shops VALUES ( ‘1’, ‘Ручеёк’, ‘120000’, ‘Петров В.И.’)

Добавилась одна строка.

Импорт данных SQL в Excel

Со стороны Excel действий будет гораздо меньше. Переходим на вкладку «Данные», нажимаем кнопку «Получение внешних данных», «Из других источников», «С сервера SQL Server».

Как было написано выше, нужно указать имя сервера SQL — BLACKPRINCE, и выбрать проверку подлинности — Windows. Нажать «Далее».

В следующем окне выберем базу данных SHOPS и отметим галкой таблицу Shops. Если нужно выбрать несколько таблиц — отметить галкой соответствующую настройку. Нажать «Далее».

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

Теперь можно выбрать расположение загруженной таблицы и указать необходимые настройки. Я пока указывать их не буду, просто нажму «ОК». Таблица будет размещена в ячейку A1.

Цель достигнута! Таблица из базы данных перекочевала в Excel и теперь над ней можно совершать привычные действия.

В следующих уроках мы поговорим о более сложных манипуляциях с запросами к базам данных .

Выгружаем результаты запроса из MS SQL Server в Excel

Д овольно часто пользователи MS SQL Server сталкиваются с задачей конвертации данных из базы данных формата MS SQL Server в формат Excel. Результаты запроса необходимо каким-то образом интерпретировать , и понятное дело , лучше это делать в представлении данных в наиболее популярном Excel’ е . Если знать последовательность действий для выполнения этой задачи конвертации , то можно убедиться в том , что сделать это совсем не сложно.

В качестве примера , предположим , что нужно отконвертировать в Excel данные справочника товаров — табличка Tovary нашей учебной базы OOO_RogaKopyta.

Для выполнения первого шага становимся на нужную нам таблицу в панели Обозревателя объектов ( в Management Studio, понятное дело) и по правой кнопке в контекстном меню выбираем Выбрать первые строк :

Прим. Как изменить количество строк для выборки по правой кнопке можно посмотреть здесь . Ну и кроме того, подобным образом мы моделируем получение результатов какой-то выборки. Совершенно очевидно , что вместо приведенного запроса типа Select Top 1000 может быть абсолютно любой другой запрос , который возвращает хотя бы одну строчку .

Далее в таблице Результатов щёлкаем по верхнему левому углу :

Теперь становимся в любом месте на выбранные таким образом записи и в контекстном меню выбираем Сохранить результат как . :

Читать еще:  Listbox vba excel свойства и методы

. и сохраняем наши данные в формате CSV:

Прим . Кстати сказать, описанная выше манипуляция поможет ответить на еще один популярный вопрос — Как вывести результаты запроса в текстовый файл ?

Продолжим. Теперь переключаемся в Excel , идём меню Файл > Открыть и выбираем Текстовые файлы :

Ищем только что созданный выше файл Tovary:

Попадаем в следующую форму считывания данных Мастера импорта тестовых файлов:

Жмём кнопку Далее и выбираем символ-разделитель Точка с запятой :

Опять жмём Далее :

Теперь осталось только выбрать на форме выше Готово и получить нужный нам результат — таблицу в Excel:

Существует еще один, может даже более простой и доступный в некоторых случаях способ выгрузки в Excel — в сетке результатов запроса в Management Studio в контекстном меню ( по правой кнопке мыши) выбираем Копировать с заголовками . :

. и сразу вставляем содержимое буфера обмена в Excel :

На этом пока все.. . Удачи !

С ещё одним подходом к задаче импорта/экспорта из Excel можно ознакомиться здесь

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Жмем ок


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

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

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

Автоматизация обработки таблиц в Excel и перенос данных в MySQL [Разбор кейса]

Благодарность: Карло Мелис (Carlo Melis), ИТ консультант, работает по заданию международного негосударственного органа по сертификации пищевой продукции, Рим, Италия.

Надстройки в фокусе: Автоматизация без VBA (извлечение таблиц, SQL запросы, экспорт из Excel в MySQL). Поддерживается в версии XLTools 5.0.0.762 и выше.

Обзор кейса

Карло, ИТ консультант, работал над сложной, но довольно типичной задачей. Ему нужно было разработать систему, которая бы создавала отчеты для руководства на основе Excel файлов, полученных от порядка 10-20 исследовательских лабораторий.

Читать еще:  Пропала строка меню в excel

Сначала эти отчеты создавали в Excel – в основном вручную – и направляли руководству и лабораториям в печатном виде или PDF.

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

Первоначальный подход занимал около 20 дней ужасной работы. Нам приходилось иметь дело с ошибками после копирования огромного объема данных из одного сводного отчета в Excel в отдельные отчеты по каждой задействованной лаборатории. Форматы очень разные, поэтому не представлялось возможным автоматизировать этот процесс в Excel. Кроме того, клиенту нужны были графики, где можно было бы сравнить результаты, полученные много месяцев назад – такое возможно только при выгрузке данных в MySQL.

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

Один из подходов к автоматизации – создать специальный макрос VBA. Но при таком большом объеме необходимых операций написание макроса с нуля займет неоправданно много времени. Кроме того, если вы хоть раз пробовали настроить интеграцию макроса с внешней базой данных, вы представляете, насколько утомительной может быть синхронизация вручную. Одно небольшое изменение в процессе (к примеру, другое название исходной таблицы) ведет к бесконечным правкам в скрипте.

Надстройка XLTools, с другой стороны, дала Карло гибкие инструменты для автоматизации всего процесса без VBA: извлечь, подготовить и экспортировать данные в базу данных MySQL.

XLTools Автоматизация – это мощный и многоцелевой инструмент. Последовательность всех операций записана на одном листе Excel, и все операции вручную сводятся к нажатию одной единственной кнопки.

Мой клиент очень доволен результатами, потому что теперь мы можем выполнить всю работу за 2 часа вместо 20 дней. XLTools помог нам облегчить работу с текущей XLS структурой. Процесс открыт для правок в будущем – скрипт очень легко изменить. Сейчас мы переносим данные в базу данных за минуты, где уже формируем все нужные отчеты.

Рассмотрим некоторые операции, автоматизированные в этом процессе.

Шаг 1. Автоматически собрать данные из файлов Excel

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

Именно это и выполняет команда XLTools.ExtractTable:

  • Она извлекает данные из внешних Excel или CSV файлов. Вы можете собрать все необходимые данные в одном месте и продолжить работу с полным объемом данных. По большому счету, это инструмент копирования-вставки.
  • Извлекая простой диапазон, он будет вставлен как именованная таблица. Только данные в формате таблицы могут служить источником данных для дальнейших операций, в т.ч. для выполнения SQL запросов в Excel.

Например, нам нужно извлечь эту таблицу с числовыми кодами, присвоенными лабораториям:

Ссылка на основную публикацию
Adblock
detector