Как выгрузить таблицу из sql в excel
В СУБД Microsoft SQL Server 2008 существует отличный функционал по импорту и экспорту данных, причем в разные форматы и разные базы данных. Его можно также использовать для простого переноса данных из одной базы в другую или с одного сервера на другой. Сегодня мы рассмотрим примеры использования данного средства, и, как мне кажется, это очень удобно.
Мы с Вами уже не раз затрагивали тему импорта и экспорта данных в MS SQL Server 2008, например, в статьях:
Но так или иначе, это было связанно с клиентским приложением, т.е. Access, другими словами, мы это делали для пользователей, чтобы они могли выгружать или загружать данные в базу. А теперь пришло время поговорить о том, чем может, и, наверное, должен пользоваться системный администратор или программист для подобного рода задач.
Примечание! Далее подразумевается, что у Вас уже установлена СУБД Microsoft SQL Server 2008 и средство импорта и экспорта данных, так как оно идет в комплекте, и на примере Windows 7 Вы можете наблюдать в меню «Пуск-> Все программы-> Microsoft SQL Server 2008 R2-> Импорт и экспорт данных (32-разрядная версия)». У меня это выглядит следующим образом:
И для того чтобы рассмотреть функционал по импорту и экспорту данных, давайте разберем два примера, первый по импорту данных, а второй по экспорту данных.
Импорт данных из Excel документа в MSSql 2008
Создаем тестовые данные в документе Excel
Мы будем использовать старый, но проверенный Excel 2003 и формат файла у нас будет xls.
Данные будут вот такие, файл я назвал test_file.xls:
Сразу скажу, что в данном примере мы будем импортировать данные в новую таблицу, поэтому на название полей мы не обращаем внимания. Но если бы мы импортировали уже в существующую таблицу, то нам в процессе импорта пришлось бы задавать соответствие этих полей или изначально в файле создать столько полей с тем же названием и в той же последовательности, как и в таблице. А если этого не сделать, то те поля, которые отсутствуют в таблице в базе, будут со значением null. Как задать соответствие этих полей и как импортировать данные в уже существующую таблицу я покажу в процессе импорта.
Примечание! Сервер располагается локально, база данных называется test.
Шаг 1
Итак, приступим, у нас есть файл, теперь запускаем средство импорта, и у нас открывается следующее окно:
Шаг 2
Шаг 3
Жмем далее, нам предлагают выбрать назначение, куда копировать эти данные, мы выбираем SQL Server, указываем имя сервера, т.е. его адрес, в нашем случае, как я уже сказал, он расположен локально. Также не забудьте про проверку подлинности, выбирайте тот метод, который у Вас настроен на сервере, и, конечно же, про базу данных, в которую копировать:
Шаг 4
Снова жмем далее, где мы укажем все ли данные копировать, в нашем случае мы говорим что все:
Шаг 5
Жмем далее, и попадаем на окно выбора листа с данными и задания названия таблицы в нашей базе, я выбрал лист 1 и назвал таблицу test_table:
И, кстати, вот на этом этапе можно задать соответствие полей. В случае если Вы импортируете данные в существующую таблицу, для соответствия полей Вам необходимо выбрать таблицу из выпадающего списка и нажать изменить, где Вы также можете задать такие параметры как назначение, тип данных и другие, для примера вот это окно:
Шаг 6
Это было небольшое отступление, а в нашем примере мы жмем далее, и попадаем в окно, в котором можно сохранить все наше действия в пакет, но мы этого делать не будем, а сразу же нажмем готово:
Шаг 7
После появится окно, где мы все проверяем и жмем готово:
Шаг 8
И в заключение у нас появится еще одно окно, так сказать результат наших действий, жмем закрыть:
И для того, чтобы проверить какие данные у нас импортировались, выполним в Management Studio простой запрос select.
И как видите все хорошо!
Экспорт данных из Microsoft SQL Server 2008 в файл Excel
А теперь давайте рассмотрим пример экспорта данных из нашей только что созданной таблицы в Excel документ.
Шаг 1
Для этого делаем практически то же самое, открываем средство экспорта, но уже здесь в качестве источника указываем нашу базу:
Шаг 2
Жмем далее, где нам предлагают указать назначение экспорта, мы соответственно выбираем Excel, и задаем путь и название выгружаемого файла:
Шаг 3
Шаг 4
И в следующем окне вставляем свой запрос, например, я написал вот такой:
Также в этом окне Вы можете выполнить анализ своего запроса, на предмет наличия ошибок или выбрать файл, который содержит текст запроса, и, если все хорошо, жмем далее.
Шаг 5
На следующем окне сразу же можем нажимать далее, если конечно Вы не хотите задать свои названия полей в выгружаемом файле.
Шаг 6
Затем на следующем окне все проверяем и жмем готово.
Шаг 7
Далее, как и в импорте, жмем последний раз готово. И все, после этого у Вас в той папке, которую Вы указали, появится документ Excel с Вашими данными.
После рассмотрения этих примеров, я думаю стало понятно, как можно осуществлять импорт и экспорт данных в MS SQL Server 2008. Использовать можно не только Excel, но и другие источники данных, ну я думаю, дальше Вы разберетесь сами, так как это не так сложно, но если у Вас все равно возникают вопросы, можете задавать их в комментариях.
Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.
Приветствую всех.
При автоматизации небольших магазинов для хранения данных часто используют PostgreSQL. И часто возникает потребность экспортировать эти данные в Excel. В этой статье я расскажу вам как я решал эту задачу. Естественно, матерые специалисты вряд ли откроют для себя что-то новое. Однако, материал будет интересен тем кто «плавает» в этой теме.
Итак, естественно, самый просто и банальный способ экспортировать данные результатов запросов в csv-файлы, а затем открыть их в Excel. Это выглядит вот так:
- во-первых, вставка данных из PostgreSQL происходит именно на сервере;
- во-вторых, можно конечно заморочиться написать batch-скрипт, который будет удаленно вызывать этот запрос на сервере, затем этот файл скопировать на компьютер пользователя и инициировать открытие в Excel.
1. Идем по ссылке и в зависимости от разрядности компьютера скачиваем установщик ODBC драйвера. Установка его проста и не требует особых знаний.
2. Чтобы пользователи могли со своих компьютеров цепляться к БД не забудьте в файле pg_hba.conf установить параметры для IP-адресов, с которых можно производить подключения:
В данном примере, что все рабочие станции смогут подключаться к серверу с БД:
3. Далее через Excel просто генерируем файл динамического запроса к данным *.dqy. Далее этот файл просто можно менять по своему усмотрению. Можно прям ниже следующий текст взять, скопировать в блокнот и там отредактировать, сохранив файл *.dqy. Вводим имя файла и расширение dqy. Выбираем типа файла ВСЕ(All files):
DATABASE – указывается наименование БД к которой будет производиться подключение;
SERVER – адрес сервера;
PASSWORD – пароль на подключение к БД.
Обратите внимание, что в большом тексте указываются параметры подключения к БД и ваша БД. Также можно еще сконфигурировать множество параметров подключения
В последней строке пишется сам запрос. Далее сохраняем файл. Если на компьютере установлен Microsoft Excel, тогда файл сразу же приобретет пиктограмму:
При запуске файла будет выдано диалоговое окно. Смело нажимаем «Включить»:
И получаем результат запроса из БД:
Теперь можно создать несколько таких файлов и спокойно скопировать их на рабочий стол пользователя:
Кстати, я пошел немного дальше. Откопал старый добрый VB6. Можно так сделать с любым языком программирования. Сделал форму, которая по выбранной дате запрашивает данные из БД, путем генерации этого *.dqy файла:
Затем немного покодил (вот часть кода):
Результат получился тот же — данные из Excel, и пользователю удобно. Да, кстати, в строке:
если речь идет о 64-битном процессоре и драйвере ODBC, установленном для 64 бит, то надо писать:
Ну, и самое главное, несмотря на всю простоту способа, у него есть конечно недостатки: запрос можно писать только в одну строку, т.е. записать строку вот в таком виде не получиться. Нужно только в одну:
— Не сможет обрабатывать на изменение данных типа:
или
Ну и может выводить только результат запроса в виде списка, т.е. красивый документ сделать не получиться. На этом все. Надеюсь данный способ кому-нибудь пригодиться. Буду рад получить ваши рекомендации по усовершенствованию моего метода или альтернативного решения данной проблемы.
Мало пользователей, да и начинающих программистов, которые знают о возможности Excel подключаться к внешним источникам, и в частности к SQL серверу, для загрузки данных из этих источников. Эта возможность достаточно полезна, поэтому сегодня мы займемся ее рассмотрением.
Функционал Excel получения данных из внешних источников значительно упростит выгрузку данных с SQL сервера, так как Вам не придется просить об этом программиста, к тому же данные попадают сразу в Excel. Для этого достаточно один раз настроить подключение и в случае необходимости получать данные в Excel из любых таблиц и представлений Views, из базы настроенной в источнике, естественно таких источников может быть много, например, если у Вас несколько баз данных.
Задача для получения данных в Excel
И для того чтобы более понятно рассмотреть данную возможность, мы это будем делать как обычно на примере. Другими словами допустим, что нам надо выгрузить данные, одной таблицы, из базы SQL сервера, средствами Excel, т.е. без помощи вспомогательных инструментов, таких как Management Studio SQL сервера.
Примечание! Все действия мы будем делать, используя Excel 2010. SQL сервер у нас будет MS Sql 2008.
И для начала разберем исходные данные, допустим, есть база test, а в ней таблица test_table, данные которой нам нужно получить, для примера будут следующими:
Эти данные располагаются в таблице test_table базы test, их я получил с помощью простого SQL запроса select, который я выполнил в окне запросов Management Studio. И если Вы программист SQL сервера, то Вы можете выгрузить эти данные в Excel путем простого копирования (данные не большие), или используя средство импорта и экспорта MS Sql 2008. Но сейчас речь идет о том, чтобы простые пользователи могли выгружать эти данные.
Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.
Настройка Excel для получения данных с SQL сервера
Настройка, делается достаточно просто, но требует определенных навыков и консультации администратора SQL сервера. Вы, конечно, можете попросить программиста настроить Excel на работу или сделать это сами, просто спросив пару пунктов, а каких мы сейчас узнаем.
И первое что нам нужно сделать, это конечно открыть Excel 2010. Затем перейти на вкладку «Данные» и нажать на кнопку «Из других источников» и выбрать «С сервера SQL Server»
Затем у Вас откроется окно «Мастер подключения данных» в котором Вам необходимо, указать на каком сервере располагается база данных и вариант проверки подлинности. Вот именно это Вам придется узнать у администратора баз данных, а если Вы и есть администратор, то заполняйте поля и жмите «Далее».
- Имя сервера – это адрес Вашего сервера, здесь можно указывать как ip адрес так и DNS имя, в моем случае сервер расположен на этом же компьютере поэтому я и указал localhost;
- Учетные данные – т.е. это логин и пароль подключения к серверу, здесь возможно два варианта, первый это когда в сети Вашей организации развернута Active directory (Служба каталогов или домен), то в этом случае можно указать, что использовать те данные, под которыми Вы загрузили компьютер, т.е. доступы доменной учетки, и в этом случае никаких паролей здесь вводить не надо, единственное замечание что и на MSSql сервере должна стоять такая настройка по проверки подлинности. У меня именно так и настроено, поэтому я и выбрал этот пункт. А второй вариант, это когда администратор сам заводит учетные данные на SQL сервере и выдает их Вам, и в этом случае он должен их Вам предоставить.
Далее необходимо выбрать базу, к которой подключаться, в нашем примере это база test. Также это подключение можно настроить сразу на работу с определенной таблицей или представлением, список таблиц и представлений у Вас будет отображен, давайте мы сделаем именно так и настроем подключение сразу на нашу таблицу test_table. Если Вы не хотите этого, а хотите чтобы Вы подключались к базе и потом выбирали нужную таблицу, то не ставьте галочку напротив пункта «Подключаться к определенной таблице», а как я уже сказал, мы поставим эту галочку и жмем «Далее».
В следующем окне нам предложат задать имя файла подключения, название и описание, я например, написал вот так:
После того как Вы нажмете «Готово» у Вас откроется окно импорта этих данных, где можно указать в какие ячейки копировать данные, я например, по стандарту выгружу данные, начиная с первой ячейки, и жмем «ОК»:
В итоге у меня загрузятся из базы вот такие данные:
Т.е. в точности как в базе. Теперь когда, например, изменились данные в этой таблице, и Вы хотите выгрузить их повторно Вам не нужно повторять все заново, достаточно в excel перейти на вкладку «Данные» нажать кнопку «Существующие подключения» и выбрать соответствующее, т.е. то которое Вы только что создали.
Вот собственно и все, как мне кажется все достаточно просто.
Таким способом получать данные в Excel из базы SQL сервера очень удобно и главное быстро, надеюсь, Вам пригодятся эти знания полученные в сегодняшнем уроке. Удачи!
У многих при работе с PHP в связке с MySQL возникает такая потребность, как экспорт данных из базы в формат xls, для того чтобы люди, которым нужны эти данные, обрабатывали их в программе Excel или просто пользователям было удобно смотреть эти данные. Недавно у меня возникла такая потребность и сегодня я расскажу, как это дело можно реализовать.
Сразу скажу, что этот способ достаточно простой, но данные выгружаются нормально.
Для начала приведу пример конечного xls файла, в программе Excel выгрузка будет выглядеть примерно так:
Другими словами, никаких картинок, стилей выгружено не будет, только заголовки столбцов и сами данные.
До того как я пришел именно к такому варианту выгрузки я пробовал выгружать в формат csv, но получалось немного коряво, потом пробовал отрисовывать таблицу и сохранять ее с расширением xls, также получалось какая-то ерунда, способ, который я сейчас опишу, меня полностью устроил, и сейчас с Вами я им поделюсь.
Для начала приведу весь код, который я максимально прокомментировал, можете выделить его и сохранить с расширением php и пробовать, только не забудьте прописать настройки подключения к базе данных.
Экспорт данных из MySQL в Excel на PHP
А еще чтобы Вы понимали, какие данные я выгружаю, приведу пример простой таблицы в БД (у меня ее название test):
Тестовая таблица:
| id | firstname | name |
| 1 | Иванов | Иван |
| 2 | Петров | Петр |
| 2 | Петров2 | Петр2 |
Код:
Вся идея здесь заключается в функции pack(), которая упаковывает данные в бинарную строку, а мы в свою очередь последовательно заполняем эту строку данными, которые мы выгрузили из базы данных MySql с помощью обычной функции mysql_query().
Для того чтобы проверить работоспособность данного кода, с учетом того, что Вы настроили подключение к базе данных и создали аналогичную таблицу, можете послать такой запрос:
И у Вас должно выгрузиться две строки с id равным 2.
И теперь каждый, которому Вы разрешите выгружать данные может легко экспортировать их на свой локальный компьютер через web интерфейс. Данный способ удобен как для корпоративных пользователей, если Вы разрабатываете приложение для своей организации, так и для пользователей Вашего web сайта в Интернете. Надеюсь, данный способ Вам помог. Удачи!
Заметка! Если Вас интересует язык SQL, рекомендую пройти мой онлайн-курс по основам SQL, который ориентирован на изучение SQL как стандарта, таким образом, Вы сможете работать в любой системе управления базами данных. Курс включает много практики: онлайн-тестирование, задания и многое другое.
Читайте также: