Excel подключение к sql

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

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

Теперь на экране появилось окно мастера подключения к данным. Изначально требуется указать имя сервера и учетные сведения для подключения (если Вы их не знаете, то обратитесь к администратору БД).

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

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

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

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

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

SQL-запрос

По умолчанию, при импорте из SQL Server, Excel выгружает всю таблицу. Можно изменить выборку, указав SQL-запрос.

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

В свойствах подключения на вкладке «Определение» в поле «Тип команды» поменяйте значение на «SQL», а в поле «Текст команды» введите SQL-код и нажмите «ОК».

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

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы мы могли развивать его дальше.

У Вас недостаточно прав для комментирования.

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

Настройка Excel для получения данных с SQL сервера

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

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

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

• Имя сервера – это адрес Вашего сервера, здесь можно указывать как ip адрес так и DNS имя, в моем случае сервер расположен на этом же компьютере поэтому я и указал localhost;
• Учетные данные – т.е. это логин и пароль подключения к серверу, здесь возможно два варианта, первый это когда в сети Вашей организации развернута Active directory (Служба каталогов или домен), то в этом случае можно указать, что использовать те данные, под которыми Вы загрузили компьютер, т.е. доступы доменной учетки, и в этом случае никаких паролей здесь вводить не надо, единственное замечание что и на MSSql сервере должна стоять такая настройка по проверки подлинности. У меня именно так и настроено, поэтому я и выбрал этот пункт. А второй вариант, это когда администратор сам заводит учетные данные на SQL сервере и выдает их Вам, и в этом случае он должен их Вам предоставить.

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

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

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

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

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

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

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

Подключите Excel к отдельной базе данных в Базе данных SQL Azure и импортируйте данные, а также создавайте таблицы и диаграммы на основе значений в базе данных. Connect Excel to a single database in Azure SQL Database and import data and create tables and charts based on values in the database. Работая с этим руководством, вы установите подключение между Excel и таблицей базы данных, сохраните файл, в котором хранятся данные и сведения о соединении для Excel, а затем создадите сводную диаграмму на основе значений базы данных. In this tutorial you will set up the connection between Excel and a database table, save the file that stores data and the connection information for Excel, and then create a pivot chart from the database values.

Чтобы начать работу, вам понадобится отдельная база данных. You’ll need a single database before you get started. Если база данных отсутствует, см. статью Краткое руководство. Создание отдельной базы данных в Базе данных SQL с помощью портала Azure, а также Краткое руководство. Создание правила брандмауэра на уровне сервера для Базы данных SQL Azure с помощью портала Azure, чтобы за несколько минут получить единую базу данных с примерами данных. If you don’t have one, see Create a single database and Create server-level IP firewall to get a single database with sample data up and running in a few minutes.

Следуя инструкциям в этой статье, вы импортируете демонстрационные данные в Excel, но те же действия можно выполнять и с собственными данными. In this article, you’ll import sample data into Excel from that article, but you can follow similar steps with your own data.

Вам также понадобится копия Excel. You’ll also need a copy of Excel. В этой статье используется Microsoft Excel 2016. This article uses Microsoft Excel 2016.

Подключение Excel к базе данных SQL и загрузка данных Connect Excel to a SQL database and load data

Чтобы подключить Excel к базе данных SQL, откройте программу Excel, а затем создайте новую книгу или откройте существующую. To connect Excel to SQL database, open Excel and then create a new workbook or open an existing Excel workbook.

В строке меню в верхней части страницы выберите вкладку Данные, нажмите кнопку Получить данные, выберите пункт "Из Azure", а затем — пункт Из базы данных SQL Azure. In the menu bar at the top of the page, select the Data tab, select Get Data, select From Azure, and then select From Azure SQL Database.

Откроется мастер подключения данных. The Data Connection Wizard opens.

В диалоговом окне Подключение к серверу баз данных введите имя сервера базы данных SQL, к которому нужно подключиться, в формате .database.windows.net. In the Connect to Database Server dialog box, type the SQL Database Server name you want to connect to in the form .database.windows.net. Пример: msftestserver.database.windows.net. For example, msftestserver.database.windows.net. При необходимости введите имя базы данных. Optionally, enter in the name of your database. Нажмите кнопку ОК, чтобы открыть окно учетных данных. Select OK to open the credentials window.

В диалоговом окне База данных SQL Server выберите слева пункт База данных, а затем введите имя пользователя и пароль для сервера Базы данных SQL, к которому нужно подключиться. In the SQL Server Database dialog box, select Database on the left side, and then enter in your User Name and Password for the SQL Database server you want to connect to. Нажмите кнопку Подключиться, чтобы открыть навигатор. Select Connect to open the Navigator.

В зависимости от сетевой среды, возможно, вам не удастся подключиться или соединение будет разорвано, если сервер базы данных SQL не разрешает трафик с IP-адреса клиента. Depending on your network environment, you may not be able to connect or you may lose the connection if the SQL Database server doesn’t allow traffic from your client IP address. Перейдите на портал Azure, щелкните "Серверы SQL Server", выберите свой сервер, в разделе "Параметры" щелкните "Брандмауэр" и добавьте IP-адрес клиента. Go to the Azure portal, click SQL servers, click your server, click firewall under settings and add your client IP address. Дополнительные сведения см. в статье Настройка правила брандмауэра уровня сервера базы данных SQL Azure с помощью портала Azure. See How to configure firewall settings for details.

В навигаторе выберите в списке нужную базу данных, выберите нужные таблицы или представления (мы выбрали vGetAllCategories), а затем нажмите кнопку Загрузить, чтобы перенести данные из базы данных в электронную таблицу Excel. In the Navigator, select the database you want to work with from the list, select the tables or views you want to work with (we chose vGetAllCategories), and then select Load to move the data from your database to your Excel spreadsheet.

Импорт данных в Excel и создание сводной диаграммы Import the data into Excel and create a pivot chart

Теперь, когда вы установили подключение, вы можете загрузить данные несколькими способами. Now that you’ve established the connection, you have several different options with how to load the data. Например, ниже описывается создание сводной диаграммы на основе данных из базы данных SQL. For example, the following steps create a pivot chart based on the data found in your SQL Database.

Выполните действия из предыдущего раздела, но на этот раз не нажимайте кнопку Загрузить, а выберите пункт Загрузить в из раскрывающегося списка Загрузить. Follow the steps in the previous section, but this time, instead of selecting Load, select Load to from the Load drop-down.

Затем выберите способ представления данных в книге. Next, select how you want to view this data in your workbook. Мы выбрали режим Сводная диаграмма. We chose PivotChart. Кроме того, можно создать новый лист или добавить эти сведения в модель данных. You can also choose to create a New worksheet or to Add this data to a Data Model. Дополнительные сведения о моделях данных см. в статье Создание модели данных в Excel. For more information on Data Models, see Create a data model in Excel.

Лист теперь содержит пустую сводную таблицу и диаграмму. The worksheet now has an empty pivot table and chart.

В разделе Поля сводной таблицыустановите все флажки для полей, которые требуется просмотреть. Under PivotTable Fields, select all the check-boxes for the fields you want to view.

Если нужно подключить другие книги и листы Excel к базе данных, на вкладке Данные нажмите кнопку Последние источники, чтобы открыть диалоговое окно Последние источники. If you want to connect other Excel workbooks and worksheets to the database, select the Data tab, and select Recent Sources to launch the Recent Sources dialog box. Выберите в списке ранее созданное подключение и нажмите кнопку Открыть. From there, choose the connection you created from the list, and then click Open. диалоговое окно

Создание постоянного подключения с помощью файла ODC Create a permanent connection using .odc file

Чтобы сохранить сведения о подключении, можно создать файл ODC. После этого подключение можно будет выбирать в диалоговом окне Существующие подключения. To save the connection details permanently, you can create an .odc file and make this connection a selectable option within the Existing Connections dialog box.

В строке меню в верхней части страницы выберите вкладку Данные и нажмите кнопку Существующие подключения, чтобы открыть диалоговое окно Существующие подключения. In the menu bar at the top of the page, select the Data tab, and then select Existing Connections to launch the Existing Connections dialog box.

Нажмите кнопку Найти другие, чтобы открыть диалоговое окно Выбор источника данных. Select Browse for more to open the Select Data Source dialog box.

Выберите файл +Новое подключение к SQL-серверу.odc и нажмите кнопку Открыть, чтобы запустить мастер подключения к данным. Select the +NewSqlServerConnection.odc file and then select Open to open the Data Connection Wizard.

В мастере подключения к данным введите имя сервера и учетные данные для базы данных SQL. In the Data Connection Wizard, type in your server name and your SQL Database credentials. Щелкните Далее. Select Next.

В раскрывающемся списке выберите нужную базу данных. Select the database that contains your data from the drop-down.

Выберите нужную таблицу или представление. Select the table or view you’re interested in. Мы выбрали vGetAllCategories. We chose vGetAllCategories.

Щелкните Далее. Select Next.

На следующем экране мастера подключения к данным выберите расположение файла, имя файла и понятное имя. Select the location of your file, the File Name, and the Friendly Name in the next screen of the Data Connection Wizard. Вы также можете сохранить пароль в файле, но это может сделать данные уязвимыми для несанкционированного доступа. You can also choose to save the password in the file, though this can potentially expose your data to unwanted access. По завершении нажмите кнопку Готово. Select Finish when ready.

Выберите способ импорта данных. Select how you want to import your data. Мы выбрали создание сводной таблицы. We chose to do a PivotTable. Кроме того, можно изменить свойства подключения, нажав кнопку Свойства. You can also modify the properties of the connection by select Properties. По окончании нажмите кнопку ОК. Select OK when ready. Если вы не сохранили пароль в файле, вам будет предложено ввести учетные данные. If you did not choose to save the password with the file, then you will be prompted to enter your credentials.

Проверьте, сохранилось ли новое подключение. Для этого на вкладке Данные нажмите кнопку Существующие подключения. Verify that your new connection has been saved by expanding the Data tab, and selecting Existing Connections.