Skip to content

How To Blog

  • What Is Roblox Premium and Is It Worth It? Software Reviews
  • How to add titles and graphics to your video How-To
  • Best 4 function student calculator 2020

    Best 4 function student calculator 2020

  • iMessage Not Working on Mac ? Here are 12 ways to Fix it General Software
  • How to Fix You Have Downloads on Too Many Devices Netflix Error How-To
  • Export Contacts from Outlook, Outlook Express and Windows Live Mail MS Office Tips
  • Why Microsoft Allows People To Use Pirated windows How-To
  • How to Find Hidden Apps on iPhone 2023 iPhone

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

  • What Is Microsoft 365? Office Tips
  • How to Layer Objects in a PowerPoint Presentation Office Tips
  • Google Docs vs Microsoft Word – What are the Differences? Office Tips
  • Make OpenOffice Writer Look and Function More Like Microsoft Word Office Tips
  • How to Delete Blank Lines in Excel Office Tips
  • How to Customize the Ribbon in MS Word 2010 Office Tips

Leave a Reply Cancel reply

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

Archives

  • June 2023
  • May 2023
  • April 2023
  • 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
  • March 2021
  • 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
  • September 2018
  • August 2018

Categories

  • Amazon Web Services
  • Apple Watch
  • Computer Tips
  • Cool Websites
  • Featured Posts
  • 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
  • OS X
  • Product Reviews
  • Reviews
  • Safari
  • Smart Home
  • Smartphones
  • Software Reviews
  • technology
  • text
  • Tools Review
  • Troubleshooting
  • Tutorials
  • Uncategorized
  • Urdu Books PDF
  • Web Site Tips
  • Windows
  • Windows 10
  • Windows 7
  • Wordpress

Recent Posts

  • How can I open an audible app for my computer?
  • Which DAW is better? – Fl Studio vs Ableton
  • The best Abelton tips and tricks for beginners
  • How Websites Work: Learn what works
  • Fast implementation of Google Analytics in Squarespace

Recent Comments

  1. How I Fix a Clock_Watchdog_Timeout BSOD in Windows 10 - How To Blog on How to Reset BIOS to Default Settings
  2. How I Fix a Clock_Watchdog_Timeout BSOD in Windows 10 - How To Blog on What to Do When Your USB Drive Is Not Showing Up
  3. Alexa Routines and Skills 2023: A Tutorial - How To Blog on How to Create a Routine With Amazon Alexa
  4. How I Fix a Clock_Watchdog_Timeout BSOD in Windows 10 - How To Blog on How To Enter Outlook Safe Mode To Fix Issues
  5. How I Fix a Clock_Watchdog_Timeout BSOD in Windows 10 - How To Blog on How To Roll Back A Driver In Windows 10
  • How to Share your Laptop Screen to Another Laptop How-To
  • Everything You Should Know About Power Banks Reviews
  • How to Fix MBR in Windows XP and Vista How-To
  • What Should I Upgrade On My PC? Investing In The Right Hardware Help Desk
  • Has Your Windows Taskbar Disappeared? Computer Tips
  • Make Money Online for Beginners 2022 technology
  • How to Fix the ( Settings app not opening in Windows 10 ) Windows
  • How to Record 1080p Footage without PS4 Pro How-To

Copyright © 2023 How To Blog.

Powered by PressBook News WordPress theme

Go to mobile version