แน่ใจว่า ใช้ 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จากนั้นไปที่ Data Sources (ODBC^{(Data Sources (ODBC))} ) คุณควรเห็นข้อมูลต่อไปนี้:

สังเกต^(Notice)แท็บในภาพด้านบน DSN ของผู้ใช้^{(User DSN)}มีให้สำหรับผู้ใช้ที่สร้างเท่านั้น DSN ของระบบ^{(System DSN)}มีให้สำหรับทุกคนที่สามารถเข้าสู่ระบบเครื่องได้ ไฟล์DSN^{(File DSN)}คือไฟล์ .DSN ที่สามารถขนส่งและใช้กับระบบอื่นที่ติดตั้งระบบปฏิบัติการและไดรเวอร์เดียวกัน

หากต้องการสร้างDSNต่อ ให้คลิก ปุ่ม เพิ่ม^(Add)ใกล้มุมบนขวา

คุณอาจต้องเลื่อนลงเพื่อดูMySQL ODBC 5.x Driver ^{(MySQL ODBC 5.x Driver)}หากไม่มี แสดงว่ามีบางอย่างผิดปกติในการติดตั้งไดรเวอร์ใน ส่วน การจัดเตรียม^{(Preparation)}ของโพสต์นี้ หากต้องการสร้างDSNต่อ ตรวจสอบให้แน่ใจว่าได้ไฮไลต์ไดรเวอร์^(Driver)MySQL ODBC 5.x แล้วคลิก ปุ่มเสร็จสิ้น ^(Finish)ตอนนี้คุณควรเห็นหน้าต่างที่คล้ายกับที่แสดงด้านล่าง:

ถัดไป คุณจะต้องให้ข้อมูลที่จำเป็นในการกรอกแบบฟอร์มที่แสดงด้านบน ฐานข้อมูลและ ตารางMySQL ที่ เราใช้สำหรับโพสต์นี้อยู่ในเครื่องพัฒนาและใช้งานโดยบุคคลเพียงคนเดียว สำหรับสภาพแวดล้อม "การผลิต" ขอแนะนำให้คุณสร้างผู้ใช้ใหม่และให้สิทธิ์SELECTแก่ผู้ใช้ใหม่เท่านั้น ในอนาคต คุณสามารถให้สิทธิ์เพิ่มเติมได้หากจำเป็น

หลังจากที่คุณให้รายละเอียดสำหรับการกำหนดค่าแหล่งข้อมูลของคุณแล้ว คุณควรคลิกที่ ปุ่ม ทดสอบ^(Test)เพื่อให้แน่ใจว่าทุกอย่างทำงานได้ดี จากนั้นคลิกที่ปุ่มOK ในตอนนี้ คุณควรเห็นชื่อแหล่งข้อมูลที่คุณระบุในแบบฟอร์มในชุดก่อนหน้านี้ที่แสดงอยู่ใน หน้าต่าง ผู้ดูแลแหล่งข้อมูล ODBC^{(ODBC Data Source Administrator)} :

การสร้างการเชื่อมต่อสเปรดชีต

เมื่อคุณสร้างDSN ใหม่สำเร็จ แล้ว คุณสามารถปิดหน้าต่างODBC Data Source AdministratorและเปิดExcelได้ เมื่อคุณเปิดExcelแล้ว ให้คลิกที่ริบบิ้นข้อมูล ^(Data)สำหรับ Excel^(Excel)เวอร์ชันใหม่กว่าให้คลิกที่Get Dataจากนั้น คลิก From Other Sourcesจากนั้นคลิกFrom ODBC

ในExcel เวอร์ชันเก่า เป็นกระบวนการที่มากกว่าเล็กน้อย ประการแรก คุณควรเห็นสิ่งนี้:

ขั้นตอนต่อไปคือการคลิกที่ ลิงค์ Connections ที่ อยู่ใต้คำว่าDataในรายการแท็บ ตำแหน่งของ ลิงก์ Connectionsนั้นอยู่ในวงกลมสีแดงในภาพด้านบน คุณควรเห็นหน้าต่างการเชื่อมต่อเวิร์^{(Workbook Connections)} กบุ๊ก :

ขั้นตอนต่อไปคือการคลิกที่ปุ่มเพิ่ม ^(Add)ซึ่งจะแสดง หน้าต่างการ เชื่อมต่อที่มีอยู่^{(Existing Connections)} :

เห็นได้ชัดว่าคุณไม่ต้องการทำงานกับคนรู้จักในรายการ ดังนั้น ให้คลิกที่ปุ่มBrowse for More… ^{(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 ทั้งหมดที่มีอยู่ในระบบที่คุณกำลังใช้^(ODBC)

หวังว่าหากทุกอย่างเป็นไปตามแผน คุณจะเห็นDSNที่คุณสร้างในขั้นตอนก่อนหน้าซึ่งแสดงรายการอยู่ในแหล่งข้อมูล  ODBC เน้น^(Highlight)และคลิกถัด^(Next)ไป

ขั้นตอนต่อไปในตัวช่วยสร้างการเชื่อม^{(Data Connection Wizard)} ต่อข้อมูล คือการบันทึกและเสร็จสิ้น ฟิลด์ชื่อไฟล์ควรกรอกอัตโนมัติสำหรับคุณ คุณสามารถใส่คำอธิบาย คำอธิบายที่ใช้ในตัวอย่างนี้อธิบายตนเองได้ค่อนข้างดีสำหรับทุกคนที่อาจใช้ ถัดไป คลิกที่ ปุ่ม เสร็จสิ้น^(Finish)ที่ด้านล่างขวาของหน้าต่าง

ตอนนี้คุณควรกลับมาที่หน้าต่าง การ เชื่อมต่อสมุดงาน ^{(Workbook Connection)}การเชื่อมต่อข้อมูลที่คุณเพิ่งสร้างขึ้นควรอยู่ในรายการ:

การนำเข้าข้อมูลตาราง^{(Importing the Table Data)}

คุณสามารถปิดหน้าต่าง การ เชื่อมต่อเวิ ร์กบุ๊ก ^{(Workbook Connection)}เราจำเป็นต้องคลิกที่ ปุ่ม การ เชื่อมต่อที่มีอยู่^{(Existing Connections)}ในData ribbon ของExcel ปุ่มการ เชื่อม^{(Connections)}ต่อที่มีอยู่ควรอยู่ทางด้านซ้ายบนริบบิ้นข้อมูล^(Data)

การคลิกที่ ปุ่ม Existing Connectionsจะทำให้คุณเห็นหน้าต่างExisting Connections คุณเคยเห็นหน้าต่างนี้แล้วในขั้นตอนก่อนหน้านี้ ความแตกต่างในตอนนี้คือการเชื่อมต่อข้อมูลของคุณควรอยู่ใกล้ด้านบนสุด:

ตรวจสอบ ให้^(Make)แน่ใจว่าได้เน้นการเชื่อมต่อข้อมูลที่คุณสร้างในขั้นตอนก่อนหน้านี้แล้วคลิกปุ่มเปิด ^(Open)ตอนนี้คุณควรเห็น หน้าต่าง นำเข้าข้อมูล^{(Import Data)} :

สำหรับจุดประสงค์ของบทความนี้ เราจะใช้การตั้งค่าเริ่มต้นในหน้าต่างนำเข้าข้อมูล ^{(Import Data)}จากนั้นคลิกที่ปุ่มOK ถ้าทุกอย่างออกมาดีสำหรับคุณ ตอนนี้คุณควรจะนำเสนอ ข้อมูลตารางฐานข้อมูล MySQLในเวิร์กชีตของคุณ

สำหรับโพสต์นี้ ตารางที่เราทำงานด้วยมีสองฟิลด์ ฟิลด์แรกเป็นฟิลด์ INT^(INT)ที่เพิ่มค่าอัตโนมัติที่ชื่อ ID ฟิลด์ที่สองคือVARCHAR (50) และมีชื่อว่า fname สเปรดชีตสุดท้ายของเรามีลักษณะดังนี้:

อย่างที่คุณอาจสังเกตเห็น แถวแรกมีชื่อคอลัมน์ของตาราง คุณยังสามารถใช้ลูกศรดรอปดาวน์ข้างชื่อคอลัมน์เพื่อจัดเรียงคอลัมน์ได้อีกด้วย

สรุป^(Wrap-Up)

ในโพสต์นี้ เราได้กล่าวถึงตำแหน่งที่จะค้นหาไดรเวอร์ ODBC ล่าสุดสำหรับMySQLวิธีสร้างDSNวิธีสร้างการเชื่อมต่อข้อมูลสเปรดชีตโดยใช้DSNและวิธีใช้การเชื่อมต่อข้อมูลสเปรดชีตเพื่อนำเข้าข้อมูลลงในสเปรดชีตExcel สนุก!

 

Connecting Excel to MySQL

Sure Excel is used for spreadsheets, but did you know you cаn connect Excel to externаl data sources? In this article we’re going to disсuss how to connect an Excel spreadsheet to a MySQL database table and use the dаta in thе databasе table to populate our spreadsheet.  There are a few things you need to do in order to prepare for thiѕ 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

• วิธีใช้ฟีเจอร์ Speak Cells ของ Excel

• วิธีแทรกแผ่นงาน Excel ลงใน Word Doc

• วิธีใช้การวิเคราะห์แบบ What-If ของ Excel

• วิธีแก้ไขแถวใน Excel

• จัดกลุ่มแถวและคอลัมน์ในแผ่นงาน Excel

• การใช้เครื่องมือค้นหาเป้าหมายการวิเคราะห์แบบ What-If ของ Excel

• วิธีแทรกหลายแถวอย่างรวดเร็วใน Excel

• วิธีแชร์ไฟล์ Excel เพื่อการทำงานร่วมกันอย่างง่ายดาย

• เมื่อใดควรใช้ Index-Match แทน VLOOKUP ใน Excel

• การเรียงลำดับข้อมูลแบบหนึ่งคอลัมน์และหลายคอลัมน์ขั้นพื้นฐานในสเปรดชีต Excel

• 4 วิธีในการใช้เครื่องหมายถูกใน Excel

• วิธีแก้ไขข้อผิดพลาด #N/A ในสูตร Excel เช่น VLOOKUP

• 5 วิธีในการแปลงข้อความเป็นตัวเลขใน Excel

• วิธีสร้างรายการแบบหล่นลงที่เชื่อมโยงหลายรายการใน Excel

• วิธีการค้นหาใน Excel

• วิธีใช้คำสั่ง if และ Nested If ใน Excel

• วิธีการเขียนสูตร IF/คำสั่งใน Excel

• ใช้แป้นพิมพ์เพื่อเปลี่ยนความสูงของแถวและความกว้างของคอลัมน์ใน Excel

• วิธีจัดเรียงตามวันที่ใน Excel