Skip to content

How To Blog

how to

  • How To Update Mac OS X & Mac Apps From Terminal OS X
  • HDG Explains – Swapfile.sys, Hiberfil.sys and Pagefile.sys in Windows Help Desk
  • Is Best iPhone Recovery Software – iMyFone Fixppo? Tools Review
  • Link to Specific Slides in Other PowerPoint Presentations MS Office Tips
  • Private Browsing: What is it and What it is not How-To
  • SATA 3 vs M.2 vs NVMe – Overview and Comparison Computer Tips
  • 15 Coolest Free Screensavers For Windows 10 Windows 10
  • 12 Effective Ways to Stay Anonymous on the Internet How-To

Connecting Excel to MySQL

Posted on October 9, 2020 By blog_786 No Comments on Connecting Excel to MySQL

Sure, Excel is used for spreadsheets, but did you know you can connect Excel to external data sources? In this article, we are going to discuss how to connect an Excel spreadsheet to a MySQL database table and use data from the database table to populate our spreadsheet. To prepare for this connection, you need to do a few things.

Preparation

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

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

Make sure that after downloading the file, you compare the hashcode of the md5 file with the one listed on the download page.

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

Create a DSN

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

– /

Connecting Excel to MySQL

Notice the tabs in the image above. A custom DSN is only available to the user who created it. The system DSN is available to anyone who can log on to the system. File DSN is a .DSN file that can be carried and used on other systems that have the same OS and drivers installed.

To proceed with DSN creation, click the Add button in the upper right corner.

Connecting Excel to MySQL

You may have to scroll down to see the MySQL ODBC 5.x driver. If not, then something went wrong with the driver installation in the Prepare section of this publication. To proceed with DSN creation, make sure the MySQL ODBC 5.x driver is highlighted and click Finish. You should now see a window similar to the one below:

Connecting Excel to MySQL

You will then need to provide the information required to complete the form shown above. The MySQL database and table we are using for this article are on a development machine and are used by only one person. For “production” environments, it is recommended that you create a new user and grant only SELECT privileges to him. In the future, if necessary, you can grant additional privileges.

After you have provided the details of the data source configuration, you must click the Test button to ensure everything is in working order. Then click OK. You should now see the data source name you specified in the form in the previous set, as specified in the ODBC Data Source Administrator window:

Connecting Excel to MySQL

Create a spreadsheet connection

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

Connecting Excel to MySQL

In earlier versions of Excel, this is a slightly more process. First, you should see something like this:

Connecting Excel to MySQL

The next step is to click on the Connections link located just below the word Data in the list of tabs. The location of the Connections link is circled in red in the image above. You should be presented with the Book Connections window:

Connecting Excel to MySQL

The next step is to click on the “Add” button. The “Existing Connections” window will open:

Connecting Excel to MySQL

Obviously you don’t want to work with any of the connections listed. Therefore, click the button “View more “. The data source selection window will open:

Connecting Excel to MySQL

As with the previous Existing Connections window, you do not want to use the connections listed in the Select Data Source window. Hence, you want to double click on the + Connect to New Data Source.odc folder. In this case, you should see the Data Connection Wizard window:

Connecting Excel to MySQL

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

Hopefully, if all goes to plan, you will see the DSN created in the previous steps in the list of ODBC data sources. Highlight it and click Next.

Connecting Excel to MySQL

The next step of the Data Connection Wizard is save and complete. The file name field should be automatically filled in for you. You can provide a description. The description used in the example is pretty self explanatory for anyone who might use it. Then click the Finish button in the lower right corner of the window.

Connecting Excel to MySQL

You should now return to the workbook connection window. The data connection you just created should appear:

Connecting Excel to MySQL

Import table data

You can close the book connection window. We need to click the Existing Connections button on the Excel data ribbon. The Existing Connections button should be on the left of the Data ribbon.

Connecting Excel to MySQL

When you click on the “Existing Connections” button, you will see the “Existing Connections” window. You saw this window in the previous steps, now the difference is that your data connection must be listed at the top:

Connecting Excel to MySQL

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

Connecting Excel to MySQL

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

In this post, the table we worked with had two fields. The first field is an auto-increment INT field called ID. The second field is VARCHAR (50) and is called fname. Our final table looks something like this:

Connecting Excel to MySQL

As you may have noticed, the first line contains the names of the table columns. You can also use the dropdown arrows next to the column names to sort the columns.

Conclusion

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

–

Office Tips Tags:Conclusion

Post navigation

Previous Post: Disable Meta Refresh in Internet Explorer
Next Post: How to Use XP Mode in Windows 7

Related Posts

  • Use Spike to Cut and Paste Multiple Text Items in Word Office Tips
  • How to Trace Dependents in Excel Office Tips
  • Center Your Worksheet Data in Excel for Printing Office Tips
  • Use Dynamic Range Names in Excel for Flexible Dropdowns Office Tips
  • Generate Random Text or Lorem Ipsum Text in MS Word Office Tips
  • How to Recover Deleted Emails in Office 365 Office Tips

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

  • Amazon Web Services
  • Apple Watch
  • Computer Tips
  • Cool Websites
  • Free Software Downloads
  • Gadgets
  • Gaming
  • General Software
  • Google Software/Tips
  • Hardware
  • Help Desk
  • How-To
  • iOS
  • iPad
  • iPhone
  • Linux
  • Linux Tips
  • macOS
  • MS Office Tips
  • Networking
  • Office Tips
  • Product Reviews
  • Reviews
  • Safari
  • Smart Home
  • Smartphones
  • Software Reviews
  • technology
  • text
  • Troubleshooting
  • Tutorials
  • Uncategorized
  • Urdu Books PDF
  • Web Site Tips
  • Windows
  • Windows 10
  • Windows 7
  • February 2023
  • January 2023
  • December 2022
  • November 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • December 2021
  • November 2021
  • October 2021
  • September 2021
  • August 2021
  • July 2021
  • March 2021
  • February 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • December 2019
  • July 2019
  • May 2019
  • April 2019
  • January 2019
  • December 2018
  • November 2018
  • October 2018

2021 Android ) Apple apps Best black browser change Chrome Closing words Concluding remarks Conclusion Delete Download Error Facebook file Files Find Free) From games Google Mac make Music Netflix ( online Open Packaging Password phone? Screen sites Tips using Video Videos Watch Websites What With Working? your YouTube

  • Prevent Popup Notifications from Appearing on Your Windows Desktop How-To
  • 15 Windows Diagnostics Tools to Improve Your PC’s Health Tools Review
  • How to Find the Best Hashtags for Instagram Cool Websites
  • How to Use Internet Effectively to Consume Information How-To
  • Fix Black or Blank Screen and Flash Videos Not Playing Mac OS X
  • How To Block Internet Explorer & Edge From Automatically Opening Computer Tips
  • How to Update iTunes or iCloud Credit Card Info Tutorials
  • How to Reset BIOS to Default Settings Computer Tips

Copyright © 2023 How To Blog.

Powered by PressBook News WordPress theme

Manage Cookie Consent
To provide the best experiences, we use technologies like cookies to store and/or access device information. Consenting to these technologies will allow us to process data such as browsing behavior or unique IDs on this site. Not consenting or withdrawing consent, may adversely affect certain features and functions.
Functional Always active
The technical storage or access is strictly necessary for the legitimate purpose of enabling the use of a specific service explicitly requested by the subscriber or user, or for the sole purpose of carrying out the transmission of a communication over an electronic communications network.
Preferences
The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
Statistics
The technical storage or access that is used exclusively for statistical purposes. The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
Marketing
The technical storage or access is required to create user profiles to send advertising, or to track the user on a website or across several websites for similar marketing purposes.
Manage options Manage services Manage vendors Read more about these purposes
View preferences
{title} {title} {title}
Go to mobile version