Cómo insertar datos de Excel hacia MySQL

¡Comparte nuestro contenido!

Dado que en una ocasión anterior, nos enfocamos en la manera de conectar un archivo de Excel con MySQL, ahora toca el turno de comenzar con el primer elemento típico de un CRUD, es decir, la inserción de registros desde nuestra hoja de cálculo hacia la base de datos. Así que no perdamos más tiempo ¡Manos a la obra!

Paso previo: La conexión a la base de datos

Dado que eso lo explicamos en el artículo anterior, dejaré solo el enlace para su correspondiente lectura. Así que partimos de tener el procedimiento que hará eso. Por motivos prácticos, trabajaremos con el usuario default (recuerda cambiarlo) , el cual no tiene contraseña. El nombre de nuestra base de datos será excelmysql.

Global conexion As ADODB.Connection
Sub conectar()
    Dim cadena As String
    Set conexion = New ADODB.Connection
    cadena = "DRIVER={MySQL ODBC 8.0 ANSI DRIVER};"
    cadena = cadena + "SERVER=localhost;DATABASE=excelmysql;"
    cadena = cadena + "USER=root;PASSWORD=;"
    conexion.Open cadena
    If conexion.State < 1 Then
        End
    End If
End Sub

Notemos que el código para lograr la conexión, tiene una pequeña variación. En este caso State es un enum que contiene los diversos estados de la conexión, siendo que cualquier valor inferior a 1 implica que la conexión no se logró, es entonces cuando abortamos el intento de conexión. Así debe estar al momento.

Añadimos otra variable Global de tipo ADODB.Recordset. Un Recordset representa el conjunto de registros que obtenemos al hacer consultas o vistas.

Global registro As ADODB.Recordset

Dentro de nuestro procedimiento para conectar, necesitamos inicializar esta variable:

Set registro = New ADODB.Recordset

Vamos a crear dos procedimientos más. El primero será para hacer la desconexión a la base de datos, y el segundo será para ejecutar sentencias SQL tipo INSERT, DELETE o UPDATE. De esta manera tenemos todo nuestro código un poco más ordenado.

En el procedimiento para desconectar, necesitamos cerrar la conexión con el método Close de la clase ADODB.Connection, también usamos Set para hacer que nuestras dos variables globales regresen a sus valores default (Nothing)

Sub desconectar()
    conexion.Close
    Set registro = Nothing
    Set conexion = Nothing
End Sub

En cuanto al método para ejecutar cadenas SQL, le llamamos simplemente ejecutar, además de tener como único parámetro la cadena SQL. Dentro de ese procedimiento, utilizaremos el método Execute que recibe como valor una cadena SQL válida.

Sub ejecutar(sql As String)
    conexion.Execute sql
End Sub

Finalmente, así queda todo el código del módulo:

Por supuesto hay que crear la base de datos en nuestro servidor.

Definiendo la tabla para trabajar

Dentro de nuestra base de datos, vamos a crear una tabla de empleados con 7 campos básicos (id, nombre, apellidos, sexo, fecha de nacimiento, salario base y tipo de empleado)

Por supuesto, esta es la definición de los datos:

Y su diccionario de datos debe quedar así:

Preparando nuestra hoja de Excel para la tabla

Ahora que ya tenemos la base de datos preparada. Es momento de crear lo necesario en nuestra hoja de Excel. Hagamos algo parecido a lo siguiente:

Necesitamos algunos datos adicionales para los campos Sexo y Tipo de empleado. En una hoja separada, escribimos lo necesario:

De regreso a nuestra hoja con el “formulario”, tomamos la celda correspondiente al campo Sexo, luego vamos al menú Datos dentro de la Cinta de opciones, en el apartado Herramientas de datos encontramos el botón Validación de datos al que damos clic.

En la ventana de diálogo llamada Validación de datos, cambiamos la opción Permitir de Cualquier valor a Lista. A continuación en la opción Origen, escribimos en formato absoluto el rango de datos con los elementos que buscamos para el campo sexo. O sea:

=Datos!$A$2:$A$3

Como se ve en la imagen:

Al terminar, presionamos el botón Aceptar. Con eso, ya podemos ver la lista dentro del campo correspondiente:

Para el campo Tipo de empleado de nuestro “formulario”, hacemos lo mismo:

Pero en la ventana de diálogo Validación de datos, el Origen de la Lista que definimos en la opción Permitir es:

=Datos!$B$2:$B$4

Tal como se ve en la imagen:

Por último, presionamos el botón Aceptar, y con eso ya podemos ver la lista correspondiente en la celda que seleccionamos:

Ahora bien, vamos al menú Programador (Si no está visible, hay que activarlo) Lo primero es presionar el botón Modo Diseño para poder insertar objetos. Lo segundo, es abrir la lista de opciones llamada Insertar y tomamos el Botón de comando dentro del grupo llamado Controles ActiveX.

Hecho esto, dibujamos un botón lo suficientemente grande al lado de nuestro formulario.

Luego, con el botón seleccionado, regresamos el menú Programador y presionamos el botón Propiedades.

En la ventana flotante de Propiedades, cambiamos la propiedad (Name) por algo más apropiado, por ejemplo: btnAceptar. También cambiamos la propiedad Caption (texto o etiqueta del botón) por ejemplo: Guardar Empleado. Una hecho esto, podemos cerrar la ventana flotante.

Finalmente, damos un doble click a nuestro botón para entrar en el editor de código de Visual Basic.

Realizando las comprobaciones necesarias

Comenzamos creando algunas variables antes del código del botón, primero las cadenas de texto:

Dim nombre, apellidos, sexo, fecha, tipo, salario, mensaje, sql as String

Como podemos observar, la fecha la hemos declarado como String y no como Date, ya que en MySQL una fecha es básicamente un String con formato. También, usaremos la variable mensaje para guardar el texto de las notificaciones, la variable sql para las cadenas SQL requeridas y el salario que también será pasado como un String para evitar hacer demasiadas conversiones.

Y una bandera para saber cuando podemos guardar o no.

Dim bandera as Boolean

Dentro del método Click de nuestro botón, inicializamos la bandera a True y el mensaje lo dejamos vacío.

bandera = True
mensaje = ""

Ahora procedemos a asignar a cada variable el valor que se encuentra en nuestras celdas. En este caso necesitamos la referencia completa de la hoja. En este ejemplo, la hoja del formulario se llama Hoja1. Con la colección Range apuntamos a la celda que necesitamos y con la propiedad Value recuperamos lo que dicha celda tenga escrito.

nombre = Hoja1.Range("E4").Value
apellidos = Hoja1.Range("E6").Value
sexo = Hoja1.Range("E8").Value
tipo = Hoja1.Range("E14").Value

Para el salario, haremos una única conversión con la función Str. Este función permite convertir un valor numérico en texto. Sin embargo, a veces en Excel al hacer este tipo de conversión suele añadirse un espacio en blanco adicional, así que por seguridad, usamos la función Trim. Está función elimina espacios sobrantes a la derecha e izquierda de una variable.

salario = Trim(Str(Hoja1.Range("E12").Value))

Para la fecha, hacemos lo mismo que el salario, pues en Excel las fechas son formatos especiales basados en el tiempo Unix.

fecha = Trim(Str(Hoja1.Range("E10").Value))

Así es como va nuestro código al momento.

Lo siguiente es revisar los campos de nuestro formulario. Usaremos la función Len que nos permite obtener la cantidad de caracteres de una variable tipo String, si este valor es cero entonces cambiamos la bandera a False y concatenamos el mensaje correspondiente a cada variable. La constante vbCrLf es un simple salto de línea.

If Len(nombre) = 0 Then
   bandera = False
   mensaje = mensaje + "Escriba el nombre" + vbCrLf
End If
If Len(apellidos) = 0 Then
   bandera = False
   mensaje = mensaje + "Escriba los apellidos" + vbCrLf
End If
If Len(sexo) = 0 Then
   bandera = False
   mensaje = mensaje + "Seleccione el sexo de la persona" + vbCrLf
End If
If Len(tipo) = 0 Then
   bandera = False
   mensaje = mensaje + "Seleccione el tipo de empleado" + vbCrLf
End If

Para salario y fecha simplemente revisamos si hemos obtenido como carácter un cero, ya que aunque la celda se quede vacía, la conversión a String dará al menos un cero.

If salario = "0" Then
   bandera = False
   mensaje = mensaje + "Escriba el salario a percibir" + vbCrLf
End If
If fecha = "0" Then
   bandera = False
   mensaje = mensaje + "Escriba la fecha de nacimiento" + vbCrLf
End If

Así vamos al momento:

una vez que tenemos todos los posibles mensajes, comprobamos si la bandera paso alguna vez de True a False con el conector lógico Not. De ser cierto, enviamos el mensaje a una ventana de diálogo informativa con vbInformation a la vez que le ponemos un título pertinente. De una vez colocamos el Else para trabajar más tarde el caso contrario.

If Not bandera Then
   MsgBox mensaje, vbInformation, "AVISO IMPORTANTE"
Else
   'Si la bandera se quedo en True    
End If

Para evitar despistes, este código va justo debajo de lo anterior.

Es momento de probar lo que llevamos hasta ahora ¡Veámoslo en acción!

Insertando un registro desde Excel hacia MySQL

Ahora que ya tenemos todo el escenario preparado, trabajaremos dentro de ese último Else. Lo primero será verificar que no vayamos a repetir un registro por accidente.

Pero antes, regresamos a la zona de declaración de variables para añadir una variable tipo Integer que nos permitirá obtener la cantidad de registros encontrados.

Dim encontrado As Integer

Por si acaso, va justo aquí:

De regreso a nuestro Else inicial, escribimos una cadena SQL que buscará contar los IDs repetidos utilizando como criterios de búsqueda el nombre y apellidos de la persona. usaremos la función Count de SQL y un alias para obtener el datos necesitamos.

sql = "select count(empId) as cuantos from empleados where empNombre ='"
sql = sql + nombre + "' and empApellidos = '" + apellidos + "'"

Nos conectamos a la base de datos, usando nuestro procedimiento para conectar.

conectar

Abrimos nuestro objeto Recordset en modo dinámico (adOpenDynamic) ya que podríamos tener más de un registro coincidente. También habilitamos el bloqueo optimista para evitarle problemas a cualquier otro usuario que este trabajando con la misma tabla (adLockOptimistic)

registro.Open sql, conexion, adOpenDynamic, adLockOptimistic

El dato que hayamos encontrado, lo pasamos a nuestra variable encontrado, esto lo hacemos a través de la colección Fields que en este caso sólo tiene el contenido del alias que escribimos dentro de nuestra cadena SQL.

encontrado = registro.Fields("cuantos")

Nos desconectamos para evitar saturar de conexiones a la base de datos.

desconectar

Si la variable encontrado vale cero, quiere decir que no existe el registro, por lo que podemos insertarlo de manera segura. En caso contrario creamos el mensaje notificando que el registro este repetido. Adicionalmente, enviamos el mensaje afuera del IF – Else para que envié la notificación de acuerdo a lo que suceda.

If encontrado = 0 Then
   'Aquí va la parte para insertar
Else
   mensaje = "El empleado que intenta insertar ya existe"
End If
MsgBox mensaje, vbInformation, "AVISO IMPORTANTE"

Así vamos al momento:

Trabajemos ahora justo donde dejamos ese comentario, ya que es ahí donde podemos insertar el registro de manera segura. Pero antes, necesitamos modificar el contenido de algunas variables para que se ajusten a las exigencias de nuestra tabla.

En primer lugar, el campo sexo solo ocupa la primera letra de cada valor (F,M) Para lograr esto, usamos la función Left que permite extraer una determinada cantidad de caracteres a la izquierda de una variable.

sexo = Left(sexo, 1)

En segundo lugar, el campo tipo lo establecimos con un campo tipo Set que solo acepta 3 valores posibles (A,B,C), en este caso A será un empleado por honorarios, B será de confianza y C será de tipo eventual. Esto lo logramos simplemente con una sentencia Select Case.

Select Case tipo
   Case "Honorarios"
      tipo = "A"
   Case "De confianza"
      tipo = "B"
   Case "Eventual"
      tipo = "C"
End Select

En tercer lugar, la fecha tiene el formato default de MySQL que es Año-Mes-Día (yyyy-mm-dd) así que con la función Format modificamos nuestra variable al formato de fecha necesario.

fecha = Format(fecha, "yyyy-mm-dd")

Ahora que ya tenemos todo, creamos nuestra sentencia INSERT utilizando la versión corta de la misma (es decir, sin la lista de campos). Recordemos que al hacer eso, el ID debe recibir por default un valor NULL para que se actualice solo; los demás datos los concatenamos respetando el orden original en la tabla.

sql = "INSERT INTO empleados VALUES(NULL,'" + nombre + "','"
sql = sql + apellidos + "','" + sexo + "','" + fecha + "',"
sql = sql + salario + ",'" + tipo + "')"

Después de eso, abrimos otra conexión a la base de datos, ejecutamos la cadena SQL y cerramos la conexión.

conectar
ejecutar (sql)
desconectar

Limpiamos las celdas para dejarlas listas para un nuevo registro.

Hoja1.Range("E4").Value = ""
Hoja1.Range("E6").Value = ""
Hoja1.Range("E8").Value = ""
Hoja1.Range("E10").Value = ""
Hoja1.Range("E12").Value = ""
Hoja1.Range("E14").Value = ""

Y creamos el mensaje, notificando que hemos insertado el registro.

mensaje = "El empleado se ha insertado correctamente"

Así queda nuestro código:

Probando el resultado final

Es momento de probarlo todo. Observe el video de muestra.

DESCARGA EL CODIGO FUENTE PARA PROBAR

¿Te ha resultado? Déjanos saber en los comentarios aquí abajo, en nuestra cuenta de twitter @cablenaranja7 o en nuestra página de facebook.

Xamus Dyson

Docente, IT Manager, Blogger & Developer. Escribo por diversión, educo por pasión. | Grandstanding is not my thing.

www.cablenaranja.com

¡Comparte nuestro contenido!