Skip to content

5000+ Great Articles

Best Articles & Essays: Interesting Articles to Read Online

  • How To Move Your Thunderbird Profile & Email To a New Windows Computer How-To
  • Show/Hide Field Codes in Word and Convert Fields to Text Office Tips
  • What Is An OGG File? Computer Tips
  • Best simple calculator 2020

    Best simple calculator 2020. 1 - Desktop Calculator, BESTWYA 10-Digit Dual Power Handheld Desktop Calculator with Large LCD Display Big Sensitive But

  • Competition Ultra Wide vs Double Monitor Reviews
  • 5 Sites to Alphabetize and Sort Text Lists Online Cool Websites
  • 5 Easy Ways to Make Money Online technology
  • Hide the Menu Bar and Status Bar in VirtualBox Virtualization

Connecting Excel to MySQL

Posted on October 9, 2020 By bilal 1983 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!

–

Share this:

  • Facebook
  • X
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

  • The best way to add a slide number to PowerPoint Office Tips
  • Make OpenOffice Writer Look and Function More Like Microsoft Word Office Tips
  • How To Insert An Animated GIF In PowerPoint Office Tips
  • How To Add a Signature In Outlook Office Tips
  • How to Repair a Damaged or Corrupt Outlook PST File Office Tips
  • How to Create a Simple Pivot Table in Excel Office Tips

Leave a Reply Cancel reply

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

Archives

  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • July 2023
  • 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
  • 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

  • – 436
  • – 939
  • 1Win AZ Bonuslar, Mobil Tətbiq və Mərc Marketləri – 376
  • 1xBet giriş, güzgü 1 xBet Azərbaycanda rəsmi sayt – 413
  • AI Tools & Guides
  • Amazon Web Services
  • Apple Watch
  • B1 Bet Aviator: Jogo de Cassino Online Tutorial Passo a Passo 202 – 14
  • blog
  • Calculator
  • Computer Tips
  • Cool Websites
  • Dasinmaz emlak elanlari, ev elanlari, ev alqi satqisi, kiraye evler, torpaq, obyekt, bina, bina ev, mənzil, villa, kreditle satilan evler – 814
  • Featured Posts
  • Free Software Downloads
  • Gadgets
  • Gaming
  • General Software
  • Google Software/Tips
  • Hardware
  • Help Desk
  • How-To
  • iOS
  • iPad
  • iPhone
  • islamic Books
  • Linux
  • Linux Tips
  • Mac OS X
  • macOS
  • MS Office Tips
  • Networking
  • Office Tips
  • OS X
  • Product Reviews
  • Reviews
  • Safari
  • Smart Home
  • Smartphones
  • Software Reviews
  • technology
  • text
  • Tools Review
  • Troubleshooting
  • Tutorial
  • Tutorials
  • Uncategorized
  • Urdu Books PDF
  • Web Site Tips
  • Windows
  • Windows 10
  • Windows 7
  • Windows XP Tips
  • Wordpress
  • бонусы до 250%, официальный сайт в Узбекистане – 69
  • Мостбет Уз Ставки на спорт и казино в букмекерской конторе Mostbet Uz – 527
  • ベラジョンカジノの出金方法一覧【2023年 最新】出金限度額・出金の流れ・出金手数料・出金条件 – 466
  • How to Listen to YouTube with Screen OFF (Android & iOS)
  • Youtuber Onlyfans Try Now OnlyFans Sites!
  • How to Access Your Android From Computer Using SSH [No ROOT]
  • Onlyfans Cuckold Wife – Try Online Beautiful-Nudes
  • How to Convert Video on Android with FFmpeg Using Termux
DMCA.com Protection Status

Recent Posts

  • How to Automatically Mute your Mac When it Wakes Up From Sleep
  • Get Android O Custom Navigation Bar on any Android Running Nougat [No Root]
  • How to Access Your Android From Computer Using SSH [No ROOT]
  • How to Convert Video on Android with FFmpeg Using Termux
  • Now Upload Photos To Instagram From Computer Without any Software

Recent Comments

  1. Instagram stories not working/loading? Try These 9 Fixes on Can’t update PS5 system software? Try These 10 Fixes
  2. How to Link to a Specific Part Of A Webpage & Share it on Best PDF to Word Converter Online (Free Without Email)
  3. See What’s Taking up Space on Your Hard Drive on 7 best 3D scanning apps for Android and iOS
  4. Make your Devices Read Out Text, With Text to Speech on 9 Best 10K Running Apps for 2023
  5. How to Find your Router’s IP Address on How to Fix Apple CarPlay Not Working? 7 Possible ways
  • VR Virtual Desktop Apps: Can You Actually Work in VR? Reviews
  • 5 Secure Online Services To Transfer Large Files To People Software Reviews
  • How to Use Checkboxes in Excel MS Office Tips
  • The Best Smart Home Starter Kit Smart Home
  • How To Change Your Skype Name How-To
  • Disable Aero (Permanently or Temporarily) in Windows 7 How-To
  • 6 Best Free Messaging Apps for Android

    Do you remember the last time you didn't send or receive SMS all day? Probably no. Whether you use your computer to send messages or your phone, thi

  • How to Install Custom Fonts on iOS 13 How-To

Copyright © 2023 How To Blog.

Powered by PressBook News WordPress theme

Go to mobile version