Migrar tabla de datos en Excel a sentencias SQL. Código VBA

De vez en cuando, me ocurre, que en mi trabajo me llega un archivo Excel, dónde necesito migrar la información de una tabla a MySQL o MaríaDB, y he encontrado un método rápido: crear sentencias SQL. Una vez que tengo las sentencias, sólo es cuestión de ejecutar lo guardado en el fichero en el administrador de nuestra base de datos.

El motivo para utilizar esta forma de trabajo es cuando, una vez preparada la tabla en Excel, te resulta más cómodo de trabajar con la información desde la base de datos.

Varios formas de migrar datos desde Excel a MariaDB o MySQL

No existe una única forma de migrar datos desde Excel a bases de datos MariaDB o MySQL. Y aunque la opción que presento aquí es una de ellas, otras pasan por:

• Migrar a Access y desde Access a MySQL utilizando el conector ODBC
• O usar MySQL for Excel
• O usar Python, para lograr exáctamente lo mismo (este método ya lo expliqué en esta entrada)

El caso es completar la acción: migrar la tabla de datos desde Excel hasta MariaDB o MySQL.

El planteamiento del código VBA

Los pasos a seguir son:

• Conocer las dimensiones de la tabla
• Preparar la sentencia de creación de tabla con los contenidos de la primera fila
• Guardar la sentencia de creación de tabla en el archivo de texto
• Preparar la sentencias INSERT
• Guardar la sentencia en un archivo de texto

Para llevar a cabo el planteamiento, necesitamos definir en nuestro módulo cómo se llamará la tabla a crear y la ruta del fichero .sql que se va a crear.

Después necesitamos 3 funciones:

Una para escribir en el fichero de texto: Append2File, donde se especifica el fichero y lo que se quiere agregar.

Una segunda función, MakeSQLText, que, a partir del contenido, prepara la información con comillas o sin ellas dependiendo del tipo de campo que sea

La tercera es la que ejecuta el control de todo el proceso: genera_inserts. Tras localizar las dimensiones de la tabla, primero crea la sentencia de creación de tabla, y luego los inserts, para lo que hace uso de las dos funciones anteriores.

El código VBA

El código completo VBA para que lo uses es:

Option Explicit
' Version 2.0.0
'   OK  Incluye CREATE TABLE xls todos los campos como VARCHAR(250)
Private Const sFile = "H:\r\out.sql"
Private Const sTable = "xls"


Private Sub genera_inserts()
Dim uf As Integer, i As Integer, j As Integer
Dim uc As Integer
Dim s As String
Dim sInicio As String, sF As String
Dim v As String, k As Integer
Dim create As String
Dim cfield As String
cfield = " VARCHAR(250) " & vbCrLf
Range("B" & 2).Select
uf = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
uc = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
s = "INSERT INTO " & sTable & "(" & Cells(1, 1).Value
k = 0
create = "CREATE TABLE " & sTable & " (" & Cells(1, 1).Value & cfield
For j = 2 To uc
    v = Cells(1, j).Value
    s = s & "," & v
    k = k + 1
    create = create & "    ," & v & cfield
    If k = 4 Then k = 0: s = s & vbCrLf
Next
s = s & ") " & vbCrLf & " VALUES (" & vbCrLf
create = create & "); " & vbCrLf
    Append2File sFile, create
k = 0
sInicio = s
For i = 2 To uf
    sF = MakeSQLText(Cells(i, 1).Value)
    For j = 2 To uc
        v = MakeSQLText(Cells(i, j).Value)
        sF = sF & "," & v
        k = k + 1
    If k = 4 Then k = 0: sF = sF & vbCrLf
    Next
    sF = sInicio & sF & ");"
    Append2File sFile, sF
Next
MsgBox "Fin " & uf
End Sub


Public Function Append2File(sFile As String, Contenido As String) As Boolean
Dim aFileNumber As Integer, bln As Boolean

aFileNumber = FreeFile
Open sFile For Append As #aFileNumber
  Print #aFileNumber, Contenido
Close #aFileNumber
    bln = True
Ending:

Append2File = bln
End Function

Function MakeSQLText(data As String) As String
    If Len(data) = 0 Then
        MakeSQLText = "Null"
    ElseIf IsDate(data) Then
        If InStr(1, data, ",", vbTextCompare) > 0 Then
            MakeSQLText = Replace(data, ",", ".")
        Else
            MakeSQLText = "'" & Year(data) & "-" & Month(data) & "-" & Day(data) & "'"
        End If
    ElseIf (IsNumeric(data)) Then
        MakeSQLText = Replace(data, ",", ".")
    ElseIf data = "Null" Then
        MakeSQLText = data
    Else
        MakeSQLText = "'" & Replace(data, "'", " ") & "'"
    End If
End Function

No es muy largo, pero es efectivo.

Algunas recomendaciones previas a ejecutar genera_inserts

Aunque el código es sencillo y hace su cometido, no está de más incluir algunas recomendaciones:

• Usa un copia del Excel original, para evitar problemas, o por si los tienes, que puedas empezar de nuevo.
• Los nombres de la primera fila sería deseable que los modifiques para que no incluyan caracteres raros, ni espacios en blando, que siempre dan problemas con la sentencias SQL
• Una vez tengas ejecutado, quizás te toque “retocar” la sentencia de creación de tabla si conoces que algún tipo de datos es numérico o de tipo fecha, porque por defecto, todos son VARCHAR(250).

Espero que te sirva!!!