Как перевести sql в excel

Excel — Подключение и получение данных с SQL сервера

Мало пользователей, да и начинающих программистов, которые знают о возможности 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»

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

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

В следующем окне нам предложат задать имя файла подключения, название и описание, я например, написал вот так:

После того как Вы нажмете «Готово» у Вас откроется окно импорта этих данных, где можно указать в какие ячейки копировать данные, я например, по стандарту выгружу данные, начиная с первой ячейки, и жмем «ОК»:

В итоге у меня загрузятся из базы вот такие данные:

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

Вот собственно и все, как мне кажется все достаточно просто.

Таким способом получать данные в Excel из базы SQL сервера очень удобно и главное быстро, надеюсь, Вам пригодятся эти знания полученные в сегодняшнем уроке. Удачи!

Источник

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

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

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

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

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

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

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

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

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

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

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

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

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

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

CREATE DATABASE SHOPS

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

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

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

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

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

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

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

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

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

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

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

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

Источник

Импорт данных из Excel или экспорт данных в Excel с помощью служб SQL Server Integration Services (SSIS)

В этой статье приводится информация о подключении, которую вы должны предоставить, и необходимые настройки подключения для импорта данных из Excel и экспорта данных в Excel с помощью SQL Server Integration Services (SSIS).

В следующих разделах содержится вся информация, которая потребуется для успешной работы с приложением Excel из служб SSIS, а также для понимания и устранения распространенных проблем:

Средства, которые можно использовать.

Файлы, которые вам потребуются.

Сведения о подключении, которые необходимо предоставить, и параметры, которые необходимо настроить для загрузки данных в/из Excel с использованием SSIS.

Известные проблемы и ограничения.

Средства, которые можно использовать

Вы можете импортировать и экспортировать данные в Excel с использованием SSIS при помощи любого из следующих инструментов.

Получение файлов, необходимых для подключения к Excel

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

Скачать последнюю версию компонентов подключения для Excel можно на следующей странице: Распространяемый пакет ядра СУБД Microsoft Access 2016. Последняя версия компонентов позволяет открывать файлы, созданные в более ранних версиях Excel.

Примечания по загрузке и установке

Обратите внимание, что необходимо скачать распространяемый пакет ядра СУБД Access 2016, а не среду выполнения Microsoft Access 2016.

Если на компьютере уже установлена 32-разрядная версия Office, необходимо установить 32-разрядную версию компонентов. Кроме того, следует выполнять пакет служб SSIS в 32-разрядном режиме или 32-разрядную версию мастера импорта и экспорта.

C:\Users\ \Downloads\AccessDatabaseEngine.exe /quiet

При возникновении проблем с установкой распространяемого пакета версии 2016 установите вместо него распространяемый пакет версии 2010 со следующей страницы: Распространяемый пакет ядра СУБД Microsoft Access 2010. (Для Excel 2013 распространяемый пакет не предусмотрен.)

Назначение Excel в качестве источника данных

Сначала необходимо указать, что вам требуется установить подключение к Excel.

В службах SSIS

В службах SSIS создайте диспетчер соединений Excel для подключения к файлу источника или назначения Excel. Создать диспетчер соединений можно несколькими способами:

Щелкните правой кнопкой в области Диспетчеры соединений и выберите команду Создать соединение. В диалоговом окне Добавление диспетчера соединений со службами SSIS выберите EXCEL и затем Добавить.

В меню Службы SSIS выберите Создать соединение. В диалоговом окне Добавление диспетчера соединений со службами SSIS выберите EXCEL и затем Добавить.

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

В мастере импорта и экспорта SQL Server

В мастере импорта и экспорта на странице Выбор источника данных или Выбор назначения выберите Microsoft Excel в списке Источник данных.

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

Файл Excel и путь к нему

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

Введите имя файла и путь к нему в следующем формате:

Для файла на локальном компьютере: C:\TestData.xlsx.

Для файла в общей сетевой папке: \\Sales\Data\TestData.xlsx.

Также можно нажать кнопку Обзор и выбрать электронную таблицу в диалоговом окне Открыть.

Подключить защищенный паролем файл Excel нельзя.

Версия Excel

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

Выберите версию Microsoft Excel, в которой был создан файл, или другую совместимую версию. Например, если вам не удалось установить компоненты подключений для версии 2016, вы можете установить компоненты для версии 2010 и выбрать в этом списке вариант Microsoft Excel 2007-2010.

Если у вас установлены старые версии компонентов подключений, вы не сможете выбрать более новые версии Excel в этом списке. Список Версия Excel содержит все версии Excel, поддерживаемые службами SSIS. Наличие элементов в этом списке не означает, что необходимые компоненты подключений были установлены. Например, вариант Microsoft Excel 2016 будет присутствовать в этом списке даже в том случае, если у вас не установлены компоненты подключений для версии 2016.

Первая строка содержит имена столбцов

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

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

Листы и диапазоны

В качестве источника или назначения для данных можно использовать три типа объектов Excel: лист, именованный диапазон или неименованный диапазон ячеек, который задается с помощью адреса.

Чтобы выбрать или указать тип объекта Excel, который вы хотите использовать в качестве источника или назначения для данных, выполните одно из следующих действий:

В службах SSIS

В службах SSIS на странице Диспетчер соединений в редакторе источника Excel или редакторе назначения Excel выполните одно из следующих действий:

Чтобы использовать лист или именованный диапазон, выберите Таблица или представление в разделе Режим доступа к данным. Затем выберите лист или именованный диапазон в списке Имя листа Excel.

Чтобы использовать неименованный диапазон, который задается по адресу, выберите Команда SQL в разделе Режим доступа к данным. Затем в поле Текст команды SQL введите запрос, аналогичный представленному ниже примеру:

В мастере импорта и экспорта SQL Server

В мастере импорта и экспорта выполните одно из следующих действий:

При импорте из Excel выполните одно из следующих действий:

Чтобы использовать лист или именованный диапазон, на странице Выбор копирования таблицы или запроса выберите Скопировать данные из одной или нескольких таблиц или представлений. Затем на странице Выбор исходных таблиц и представлений в столбце Источник укажите исходные листы и именованные диапазоны.

Чтобы использовать неименованный диапазон, который задается по адресу, на странице Выбор копирования таблицы или запроса выберите Написать запрос, указывающий данные для передачи. Затем на странице Определение исходного запроса введите запрос, аналогичный показанному ниже:

При экспорте в Excel выполните одно из следующих действий:

Чтобы использовать лист или именованный диапазон, на странице Выбор исходных таблиц и представлений в столбце Назначение выберите целевые листы и именованные диапазоны.

После выбора или ввода объектов Excel для импорта или экспорта вы также можете выполнить следующие действия на странице Выбор исходных таблиц и представлений мастера:

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

Выполнить предварительный просмотр данных, чтобы убедиться в правильности выбора, с помощью команды Предварительный просмотр.

Проблемы с типами данных

Типы данных

Драйвер Excel распознает только ограниченный набор типов данных. Например, все числовые столбцы воспринимаются как тип double (DT_R8), а все строковые столбцы (кроме столбцов типа memo) воспринимаются как строки в Юникоде длиной 255 символов (DT_WSTR). Службы SSIS сопоставляют типы данных Excel следующим образом:

Числовой — с плавающей запятой двойной точности (DT_R8)

Денежный — денежный (DT_CY)

Логический — логический (DT_BOOL)

Дата и время — datetime (DT_DATE)

Строковый — строка в Юникоде длиной в 255 символов (DT_WSTR)

Memo — текстовый поток в Юникоде (DT_NTEXT)

Преобразования типов данных и длины по умолчанию

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

Ниже приведены некоторые примеры преобразований, которые могут потребоваться:

Преобразование между строковыми столбцами Excel в Юникоде и строковыми столбцами в формате с конкретными кодовыми страницами, отличными от Юникода.

Преобразование между строковыми столбцами Excel длиной в 255 символов и строковыми столбцами другой длины.

Преобразование между числовыми столбцами Excel с плавающей запятой двойной точности и числовыми столбцами других типов.

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

Проблемы с импортом

Пустые строки

Если в качестве источника указан лист или диапазон, драйвер считывает непрерывный блок ячеек, начиная с первой непустой ячейки в верхнем левом углу листа или диапазона. В результате данные могут начинаться не со строки 1, однако в данных источника не должны присутствовать пустые строки. Например, пустые строки не допускаются между заголовками столбцов и строками данных, а также после названия в верхней части листа.

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

Отсутствующие значения

Драйвер Excel считывает определенное количество строк (по умолчанию 8 строк) в указанном источнике для определения типа данных каждого столбца. Если столбец содержит смешанные типы данных, особенно если числовые данные смешаны с текстовыми данными, драйвер принимает решение в пользу того типа данных, которого больше, и возвращает значения NULL в ячейки, содержащие данные другого типа. (В случае равенства преимущество получает числовой тип.) Большинство параметров форматирования ячеек в листе Excel не затрагивает это определение типа данных.

Можно изменить поведение драйвера Excel, указав режим импорта для импорта всех значений в виде текста. Чтобы указать режим импорта, добавьте IMEX=1 к значению расширенных свойств в строке соединения диспетчера соединений с Excel в окне «Свойства».

Усеченный текст

Когда драйвер определяет, что столбец Excel содержит текстовые данные, он выбирает тип данных (строковый или memo) на основании самого длинного значения. Если драйвер не обнаруживает значений длиннее 255 символов в выбираемых строках, он считает, что столбец является строковым с длиной 255 символов, а не столбцом типа memo. Поэтому значения длиннее 255 символов могут быть усечены.

Чтобы импортировать данные из столбца типа memo без усечения, можно воспользоваться любым из двух способов:

Убедитесь, что столбец типа memo как минимум в одной из выбранных строк содержит значение длиной более 255 символов.

Увеличьте число строк в выборке драйвера, чтобы включить такую строку. Чтобы увеличить количество строк, включаемых в выборку, достаточно увеличить значение TypeGuessRows в следующем разделе реестра:

Проблемы с экспортом

Создание нового файла назначения

В службах SSIS

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

В мастере импорта и экспорта SQL Server

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

Экспорт в достаточно большой диапазон

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

Экспорт длинных текстовых значений

См. также

Дополнительные сведения о компонентах и процедурах, описываемых в этой статье, см. в следующих статьях:

Источник

Как перенести или экспортировать данные SQL Server 2005 в Excel

14 ответов

используйте «внешние данные» из Excel. Он может использовать соединение ODBC для извлечения данных из внешнего источника: Data/Get External Data/New Database Query

таким образом, даже если данные в базе данных изменяются, вы можете легко обновить.

SSIS-это без проблем для таких вещей, как это, и очень прямолинейно (и это именно то, для чего он предназначен).

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

Я нашел простой способ экспортировать результаты запросов из SQL Server Management Studio 2005 в Excel.

после этого, когда вы выбираете все и копируете результаты запроса, вы можете вставить их в Excel, и заголовки столбцов будут присутствовать.

это, безусловно, лучший пост для экспорта в excel из SQL:

Помимо использования DTS и мастера экспорта, мы также можем использовать этот запрос для экспорта данных из SQL Server2000 в Excel

создайте файл Excel с именем testing, имеющий заголовки, такие же, как у столбцов таблицы и используйте эти запросы

1 экспорт данных в существующий файл EXCEL из таблицы SQL Server

2 экспорт данных из Excel в новую таблицу SQL Server

3 экспорт данных из Excel в существующую таблицу SQL Server

4 Если вы не хотите создавать файл EXCEL заранее и хотите экспортировать в него данные, используйте

(теперь вы можете найти файл с данными в табличном формате)

5 для экспорта данных в новый файл EXCEL с заголовком (имена столбцов), создайте следующую процедуру

после создания процедуры выполните ее, указав имя базы данных, имя таблицы и путь к файлу:

его whomping 29 страниц, но это потому, что другие показывают различные другие способы, а также люди, задающие вопросы, как и этот, о том, как это сделать.

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

обновить отдельные ячейки

член Также там Питер Ларсон публикует следующее: Мне кажется, здесь чего-то не хватает. Здорово иметь возможность экспортировать и импортировать файлы Excel, но как насчет обновления отдельных ячеек? Или ряд клеток?

это принцип того, как вы управляете этим

вы также можете добавьте формулы в Excel, используя это:

экспорт с именами столбцов с помощью T-SQL

член Младен Прайдич также имеет запись в блоге о том, как это сделать здесь

ссылки:www.sqlteam.com (кстати, это отличный блог / форум для тех, кто хочет получить больше от SQL Server).

вот видео, которое покажет вам, шаг за шагом, как экспортировать данные в Excel. Это отличное решение для «одноразовых» проблем, когда вам нужно экспортировать в Excel:
Отчеты

гораздо проще просто сделать это из Excel. Открыть Excel Данные>Импорт / Экспорт Данных>Импорт Данных Далее «Файл» выберите «новый источник» кнопку В окне Добро пожаловать в мастер подключения к данным выберите Microsoft SQL Server. нажимать далее. Введите имя сервера и учетные данные. В раскрывающемся списке выберите базу данных, содержащую нужную Вам таблицу. Выберите таблицу, затем далее. Введите описание и нажмите кнопку Готово. Когда вы закончите и вернетесь в Excel, просто нажмите » OK» Простой.

Источник