물론 Excel 은 스프레드시트에 사용되지만 (Excel)Excel 을 외부 데이터 원본에 연결할 수 있다는 사실을 알고 계셨습니까? 이 기사에서는 Excel 스프레드시트를 MySQL 데이터베이스 테이블에 연결하고 데이터베이스 테이블의 데이터를 사용하여 스프레드시트를 채우는 방법에 대해 설명합니다. 이 연결을 준비하려면 몇 가지 작업을 수행해야 합니다.
준비(Preparation)
먼저 MySQL(MySQL) 용 최신 ODBC ( Open Database Connectivity ) 드라이버를 다운로드해야 합니다 . 현재 MySQL 용 (MySQL)ODBC 드라이버 는 다음 위치에 있습니다.
https://dev.mysql.com/downloads/connector/odbc/
(Make)파일을 다운로드한 후 다운로드 페이지에 나열된 것과 비교하여 파일의 md5 해시를 확인 했는지 확인하십시오.
다음으로 방금 다운로드한 드라이버를 설치해야 합니다. 파일을 두 번(Double) 클릭하여 설치 프로세스를 시작합니다. 설치 프로세스가 완료되면 Excel 에서 사용할 (Excel)데이터베이스 소스 이름(Database Source Name) ( DSN )을 만들어야 합니다 .
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) 은 동일한 OS 및 드라이버가 설치된 다른 시스템으로 전송하고 사용할 수 있는 .DSN 파일입니다 .
DSN 생성을 계속하려면 오른쪽 상단 모서리 에 있는 추가(Add) 버튼 을 클릭합니다 .
MySQL ODBC 5.x 드라이버(MySQL ODBC 5.x Driver) 를 보려면 아래로 스크롤해야 할 것입니다 . 없는 경우 이 게시물 의 준비 섹션에서 드라이버를 설치하는 데 문제가 있는 것입니다. (Preparation)DSN 생성을 계속하려면 MySQL ODBC 5.x 드라이버(Driver) 가 강조 표시되어 있는지 확인 하고 마침(Finish) 버튼을 클릭합니다. 이제 아래 나열된 것과 유사한 창이 표시되어야 합니다.
다음으로 위에 표시된 양식을 작성하는 데 필요한 정보를 제공해야 합니다. 이 포스트에서 사용하는 MySQL 데이터베이스와 테이블은 개발 머신에 있으며 한 사람만 사용합니다. "프로덕션" 환경의 경우 새 사용자를 만들고 새 사용자에게 SELECT 권한만 부여하는 것이 좋습니다. 향후 필요한 경우 추가 권한을 부여할 수 있습니다.
데이터 소스 구성에 대한 세부 정보를 제공한 후 테스트(Test) 버튼을 클릭하여 모든 것이 제대로 작동하는지 확인해야 합니다. 그런 다음 확인(OK) 버튼을 클릭합니다. 이제 ODBC 데이터 원본 관리자(ODBC Data Source Administrator) 창 에 나열된 이전 집합의 양식에 제공한 데이터 원본 이름이 표시되어야 합니다 .
스프레드시트 연결 생성
새 DSN(DSN) 을 성공적으로 만들었 으므로 ODBC 데이터 원본 관리자(ODBC Data Source Administrator) 창 을 닫고 Excel 을 열 수 있습니다. Excel 을 연 후 데이터(Data) 리본 을 클릭합니다 . 최신 버전의 Excel 의 경우 (Excel)데이터 가져오기, (Get Data)기타 소스(From Other Sources) 에서, ODBC에서(From ODBC) 를 차례로 클릭합니다 .
이전 버전의 Excel 에서는 좀 더 많은 프로세스입니다. 먼저 다음과 같이 표시되어야 합니다.
다음 단계는 탭 목록에서 데이터(Data) 라는 단어 바로 아래 에 있는 연결 링크를 클릭하는 것입니다. (Connections)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 을 강조 표시하고 (ODBC DSN)다음(Next) 을 클릭 합니다. 데이터 연결 마법사(Data Connection Wizard) 의 다음 단계 는 사용 중인 시스템에서 사용 가능한 모든 ODBC 데이터 원본을 표시합니다.
모든 것이 계획대로 진행된다면 이전 단계에서 생성한 DSN 이 (DSN)ODBC 데이터 원본 에 나열되는 것을 볼 수 있기를 바랍니다. 강조 표시(Highlight) 하고 다음(Next) 을 클릭합니다 .
데이터 연결 마법사(Data Connection Wizard) 의 다음 단계는 저장하고 완료하는 것입니다. 파일 이름 필드는 자동으로 채워져야 합니다. 설명을 제공할 수 있습니다. 예제에 사용된 설명은 그것을 사용할 수 있는 모든 사람에게 매우 자명합니다. 그런 다음 창의 오른쪽 하단 에 있는 마침 버튼을 클릭합니다.(Finish)
이제 통합 문서 연결(Workbook Connection) 창으로 돌아가야 합니다. 방금 생성한 데이터 연결이 나열되어야 합니다.
테이블 데이터 가져오기(Importing the Table Data)
통합 문서 연결(Workbook Connection) 창 을 닫을 수 있습니다 . Excel 의 (Excel)데이터(Data) 리본 에서 기존 연결(Existing Connections) 버튼 을 클릭해야 합니다 . 기존 연결 단추는 (Connections)데이터(Data) 리본 의 왼쪽에 있어야 합니다 .
기존 연결(Existing Connections) 버튼을 클릭하면 기존 연결(Existing Connections) 창이 나타납니다. 이전 단계에서 이 창을 보았지만 이제 차이점은 데이터 연결이 상단 근처에 나열되어야 한다는 것입니다.
이전 단계에서 생성한 데이터 연결이 강조 표시되어 있는지(Make) 확인한 다음 열기(Open) 버튼을 클릭합니다. 이제 데이터 가져오기(Import Data) 창이 표시되어야 합니다.
이 게시물의 목적을 위해 데이터 가져오기(Import Data) 창의 기본 설정을 사용하겠습니다 . 그런 다음 확인(OK) 버튼을 클릭합니다. 모든 것이 잘 되었다면 이제 워크시트에 MySQL 데이터베이스 테이블 데이터가 표시되어야 합니다.
이 게시물에서 우리가 작업한 테이블에는 두 개의 필드가 있었습니다. 첫 번째 필드는 ID라는 제목 의 자동 증분 INT 필드입니다. (INT)두 번째 필드는 VARCHAR (50)이고 제목은 fname입니다. 최종 스프레드시트는 다음과 같습니다.
아마 눈치채셨겠지만 첫 번째 행에는 테이블 열 이름이 포함되어 있습니다. 열 이름 옆에 있는 드롭다운 화살표를 사용하여 열을 정렬할 수도 있습니다.
마무리(Wrap-Up)
이 게시물에서는 MySQL 용 최신 ODBC 드라이버를 찾을 수 있는 위치, (MySQL)DSN 을 만드는 방법, DSN 을 사용하여 스프레드시트 데이터 연결을 만드는 방법 및 Excel 스프레드시트 로 데이터를 가져오기 위해 스프레드시트 데이터 연결을 사용하는 방법을 다루었습니다 . 즐기다!
Connecting Excel to MySQL
Sure Excel is used for spreadsheеts, but did you know you can connect Excel to externаl datа sources? In this article we’re going to discuss how to connеct an Excеl spreadsheet to a MySQL database table and use the data in the database table to populatе our spreadshеet. There are a few things yoυ nеed 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!
