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

Подготовка^{(Preparation)}

Во-первых, вы должны загрузить самую последнюю версию драйвера Open Database Connectivity ( ODBC ) для MySQL . Текущий драйвер ODBC^(ODBC) для MySQL можно найти по адресу

https://dev.mysql.com/downloads/connector/odbc/

Убедитесь^(Make) , что после загрузки файла вы сверяете хэш md5 файла с хэшем, указанным на странице загрузки.

Далее вам нужно будет установить драйвер, который вы только что скачали.  Дважды^(Double) щелкните файл, чтобы начать процесс установки. После завершения процесса установки вам потребуется создать имя источника базы данных^{(Database Source Name)} ( DSN ) для использования с Excel .

Создание DSN^{(Creating the DSN)}

DSN будет содержать всю информацию о подключении, необходимую для использования таблицы базы данных MySQL . В системе Windows вам нужно будет нажать « Пуск^(Start) », затем « Панель управления^{(Control Panel)} », затем « Администрирование^{(Administrative Tools)} », затем «Источники данных (ODBC)^{(Data Sources (ODBC))} » . Вы должны увидеть следующую информацию:

Обратите внимание^(Notice) на вкладки на изображении выше. Пользовательский DSN^{(User DSN)} доступен только пользователю, который его создал. Системный DSN^{(System DSN)} доступен любому, кто может войти в систему. Файловый DSN^{(File DSN)} — это файл .DSN, который можно транспортировать и использовать в других системах с той же ОС и установленными драйверами.

Чтобы продолжить создание DSN , нажмите кнопку « Добавить^(Add) » в правом верхнем углу.

Вероятно, вам придется прокрутить вниз, чтобы увидеть драйвер MySQL ODBC 5.x. ^{(MySQL ODBC 5.x Driver)}Если его нет, значит, что-то пошло не так при установке драйвера в разделе « Подготовка^{(Preparation)} » этого поста. Чтобы продолжить создание DSN , убедитесь, что драйвер ^(Driver)MySQL ODBC 5.x выделен, и нажмите кнопку « Готово^(Finish) » . Теперь вы должны увидеть окно, похожее на то, что указано ниже:

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

После того, как вы предоставили сведения о конфигурации источника данных, вы должны нажать кнопку « Проверить»^(Test) , чтобы убедиться, что все в порядке. Далее нажмите на кнопку ОК^(OK) . Теперь вы должны увидеть имя источника данных, указанное вами в форме в предыдущем наборе, в списке в окне администратора источника данных ODBC^{(ODBC Data Source Administrator)} :

Создание подключения к электронной таблице

Теперь, когда вы успешно создали новый DSN , вы можете закрыть окно администратора источника данных ODBC^{(ODBC Data Source Administrator)} и открыть Excel . После того, как вы открыли Excel , нажмите на ленту данных . ^(Data)Для более новых версий Excel нажмите «Получить данные^{(Get Data)} », затем « Из других источников^{(From Other Sources)} », затем « Из ODBC^{(From ODBC)} » .

В более старых версиях Excel это немного больше похоже на процесс. Во-первых, вы должны увидеть что-то вроде этого:

Следующий шаг — щелкнуть ссылку « Подключения^{(Connections)} », расположенную прямо под словом « Данные^(Data) » в списке вкладок. Расположение ссылки « Подключения^{(Connections)} » обведено красным на изображении выше. Вам должно быть представлено окно « Подключения к рабочей книге»:^{(Workbook Connections)}

Следующим шагом является нажатие на кнопку « Добавить^(Add) » . Это представит вам окно « Существующие подключения »:^{(Existing Connections)}

Очевидно, вы не хотите работать ни с одним из перечисленных соединений. Поэтому нажмите кнопку « Найти еще…^{(Browse for More…)} » . Это представит вам окно выбора источника данных :^{(Select Data Source)}

Как и в предыдущем окне « Существующие соединения^{(Existing Connections)} », вы не хотите использовать соединения, перечисленные в окне « Выбрать источник данных^{(Select Data Source)} » . Поэтому вам нужно дважды щелкнуть папку +Connect to New Data Source.odc . При этом вы должны увидеть окно мастера подключения к данным :^{( Data Connection Wizard)}

Учитывая перечисленные варианты источников данных, вы хотите выделить ODBC DSN и нажать Next . На следующем шаге мастера подключения данных^{(Data Connection Wizard)} будут показаны все источники данных ODBC , доступные в используемой вами системе.

Надеюсь, если все пойдет по плану, вы должны увидеть DSN , созданный на предыдущих шагах, в списке источников данных ODBC .  Выделите^(Highlight) его и нажмите Далее^(Next) .

Следующим шагом мастера подключения данных^{(Data Connection Wizard)} является сохранение и завершение. Поле имени файла должно быть заполнено автоматически. Вы можете предоставить описание. Описание, используемое в примере, довольно понятно для любого, кто может его использовать. Затем нажмите кнопку « Готово^(Finish) » в правом нижнем углу окна.

Теперь вы должны вернуться в окно подключения к рабочей книге. ^{(Workbook Connection)}Только что созданное подключение к данным должно появиться в списке:

Импорт данных таблицы^{(Importing the Table Data)}

Окно подключения^{(Workbook Connection)} к книге можно закрыть . Нам нужно нажать кнопку « Существующие соединения^{(Existing Connections)} » на ленте « Данные » ^(Data)Excel . Кнопка «Существующие подключения^{(Connections)} » должна располагаться слева на ленте « Данные ».^(Data)

Нажав кнопку « Существующие подключения^{(Existing Connections)} », вы увидите окно « Существующие подключения ». ^{(Existing Connections)}Вы видели это окно на предыдущих шагах, теперь разница в том, что ваше подключение для передачи данных должно быть указано вверху:

Убедитесь^(Make) , что подключение для передачи данных, созданное на предыдущих шагах, выделено, а затем нажмите кнопку « Открыть^(Open) » . Теперь вы должны увидеть окно импорта данных :^{(Import Data)}

Для целей этого поста мы собираемся использовать настройки по умолчанию в окне « Импорт данных^{(Import Data)} » . Далее нажмите на кнопку ОК^(OK) . Если у вас все получилось, теперь вам должны быть представлены данные таблицы базы данных MySQL на вашем листе.^(MySQL)

Для этого поста таблица, с которой мы работали, имела два поля. Первое поле представляет собой поле ID с автоматическим приращением INT . Второе поле называется VARCHAR (50) и называется fname. Наша окончательная таблица выглядит так:

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

Заворачивать^(Wrap-Up)

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

 

Connecting Excel to MySQL

Sure Excel is usеd for spreadsheets, but did you know yоu can connect Excel to external data sources? In this article we’re going to discυsѕ how to connect an Εxcel spreadsheet to a MySQL database table and use the data in the database table to populate our spreadsheet.  There are a few things you need to do in order to prepare for this connection.

Preparation

First, you must download the most recent Open Database Connectivity (ODBC) driver for MySQL.  The current ODBC driver for MySQL can be located at

https://dev.mysql.com/downloads/connector/odbc/

Make sure after you download the file that you check the file’s md5 hash against that listed on the download page.

Next, you will  need to install the driver you just downloaded.  Double click the file to start the install process.  Once the install process is complete you will need to create a Database Source Name (DSN) to use with Excel.

Creating the DSN

The DSN will contain all of the connection information necessary to use the MySQL database table.  On a Windows system, you will need to click on Start, then Control Panel, then Administrative Tools, then Data Sources (ODBC).  You should see the following information:

Notice the tabs in the image above.  A User DSN is only available to the user that created it.  A System DSN is available to anyone that can log into the machine.  A File DSN is a .DSN file that can be transported to and used on other systems that have the same OS and drivers installed.

To continue creating the DSN, click on the Add button near the top right corner.

You will probably have to scroll down to see the MySQL ODBC 5.x Driver.  If it’s not present, something went wrong with installing the driver in the Preparation section of this post.  To continue creating the DSN, make sure MySQL ODBC 5.x Driver is highlighted and click on the Finish button. You should now see a window similar to the one listed below:

Next you will need to supply the information necessary to complete the form shown above.  The MySQL database and table we’re using for this post is on a development machine and is only used by one person. For “production” environments, it is suggested you create a new user and grant the new user SELECT privileges only.  In the future, you can grant additional privileges if necessary.

After you have supplied the details for your data source configuration, you should click on the Test button to make sure everything is in working order.  Next, click on the OK button. You should now see the data source name you supplied on the form in the previous set listed on the ODBC Data Source Administrator window:

Creating the Spreadsheet Connection

Now that you have successfully created a new DSN, you can close the ODBC Data Source Administrator window and open Excel.  Once you have opened Excel, click on the Data ribbon.  For newer versions of Excel, click on Get Data, then From Other Sources, then From ODBC.

In older versions of Excel, it’s a bit more of a process. Firstly, you should see something like this:

The next step is to click on the Connections link located right under the word Data in the tab list. The location of the Connections link is circled in red in the above image. You should be presented with the Workbook Connections window:

The next step is to click on the Add button.  This will present you with the Existing Connections window:

Obviously you don’t want to work on any of the connections listed.  Therefore, click on the Browse for More… button. This will present you with the Select Data Source window:

Just like the previous Existing Connections window, you do not want to use the connections listed in the Select Data Source window.  Therefore, you want to double click on the +Connect to New Data Source.odc folder.  In doing so, you should be now see the Data Connection Wizard window:

Given the data source choices listed, you want to highlight ODBC DSN and click Next.  The next step of the Data Connection Wizard will display all of the ODBC data sources available on the system you are using.

Hopefully, if all as gone according to plan, you should see the DSN that you created in previous steps listed among the ODBC data sources.  Highlight it and click on Next.

The next step in the Data Connection Wizard is to save and finish.  The file name field should be auto filled for you.  You can supply a description.  The description used in the example is pretty self explanatory for anyone that might use it.  Next, click on the Finish button in the lower right of the window.

You should now be back at the Workbook Connection window.  The data connection you just created should be listed:

Importing the Table Data

You can close the Workbook Connection window.  We need to click on the Existing Connections button in the Data ribbon of Excel.  The Existing Connections button should be located to the left on the Data ribbon.

Clicking on the Existing Connections button should present you with the Existing Connections window.  You’ve seen this window in previous steps, the difference now is that your data connection should be listed near the top:

Make sure the data connection you created in the previous steps is highlighted and then click on the Open button.  You should now see the Import Data window:

For the purposes of this post, we are going to use the default settings on the Import Data window.  Next, click on the OK button.  If everything worked out for you, you should now be presented with the MySQL database table data in your worksheet.

For this post, the table we were working with had two fields.  The first field is an auto-increment INT field titled ID.  The second field is VARCHAR(50) and is titled fname.  Our final spreadsheet looks likes like this:

As you’ve probably noticed, the first row contains the table column names.  You can also use the drop down arrows next to the column names to sort the columns.

Wrap-Up

In this post we covered where to find the latest ODBC drivers for MySQL, how to create a DSN, how to create a spreadsheet data connection using the DSN and how to use the spreadsheet data connection to import data into an Excel spreadsheet. Enjoy!

 

Related posts

• Как удалить пустые строки в Excel

• Как использовать функцию «Говорить ячейки» в Excel

• Как вставить лист Excel в документ Word

• Как использовать анализ «что, если» в Excel

• Как исправить строку в Excel

• Использование инструмента поиска цели для анализа «что, если» в Excel

• Как открыть Word и Excel в безопасном режиме

• Расширенное руководство по VBA для MS Excel

• Как создать простую сводную таблицу в Excel

• Центрируйте данные рабочего листа в Excel для печати

• Что такое массив VBA в Excel и как его запрограммировать

• Как поделиться файлом Excel для удобной совместной работы

• Как использовать Flash Fill в Excel

• 2 способа использования функции транспонирования Excel

• Как написать формулу/оператор ЕСЛИ в Excel

• Как создать несколько связанных раскрывающихся списков в Excel

• Как переместить столбцы в Excel

• Как исправить ошибки #N/A в формулах Excel, таких как ВПР

• Базовая сортировка данных в один столбец и несколько столбцов в электронных таблицах Excel

• Как сделать круговую диаграмму в Excel