Using MySQL for Excel

Posted by in MySQL

Again talking about MySQL, and let’s try MySQL for Excel, an add-on to work inside Microsoft Excel with MySQL data.

Install MySQL for Excel

You can install MySQL for Excel from two sources: download from here, or you can use MySQL Windows Installer (you can see a detailled entrance in a few days!). Let’s use the second option:

Instalando MySQL 5.6

Install MySQL 5.6

Click on Add / Modify, and let’s verify under Applications what you have installed. In my case, versión 1.1:

Instalando MySQL for Excel

Install MySQL for Excel

“Playing” with MySQL for Excel

Once installed, let’s open Excel, and you’ll have a new icno in the data  tab:

MySQL for Excel

MySQL for Excel

If you clic on the icno, you can see several options for connecting with MySQL. Let’s connect to out database.

Conectando con MySQL desde Excel

Conecting MySQL from Excel

For the connection, you need to provide the user and passwork..

Creating a connection to MySQL

I already had a connection, but … let’s create a new one to my LocalHost. You need to clic on Manage Connections and MySQL WorkBench will open. Clic on New Connection and fill your data before proceed:

Creando conexión

Creating a connetion

Once the connection is created, you will be asked for the password in order to stay connected.

Conexión creada

Conexión creada

Once connected, you can see the databases of your server (my localhost):

Conectado a MySQL desde Excel

Conectado a MySQL desde Excel

Last step is to select the database you are going to work with. Let’s create a table, on the test database:

Tabla para migrar a MySQL

Table created for migrating to MySQL

Once the data is ready, you need to create a selection of the data to migrate, and clic on Expor Excel data to New Table: all selected data will be migrated in a new table on MySQL.

Migrando a MySQL la selección

Migrating data to MySQL

Next step is prepareing the table that will be created with its names, types, keys, …. and once done, clic on Export. You can see a resume of everything that had happened in the database, even the SQL code executed:

Migración realizada con éxito

Migración realizada con éxito

It’s time to move to heidiSQL, a MySQL manager, to verify that the new table have been created!

Comprobando la migración

Testing the migration

Conclussion

Although I have this tool installed for some time, I never used it before … until I needed to migrate data from Excel to MySQL, the reason that push me to try it. I must admit I like the tool and the results are great because in other circunstances, I am pretty sure that I had migrated first from Excel to Microsoft Access, and from here to MySQL, a longer process to get the same result as using MySQL for Excel.

A great tool. Have you tried?

Google+ Comments - Comentarios Google+