martes, 19 de marzo de 2019

Power Query: Conexión a base de datos MySQL

Hoy aprenderemos a acceder a una base de datos en SQL, en particular una base de datos MySQL.

Obviamente debemos conocer la ubicación de nuestra base de datos (nombre del servidor o IP) así como el nombre de la base de datos.

En mi caso para el ejemplo la base de datos llamada 'bbdd_excelforo' se encuentra en mi local, servidor: 'localhost'.

No necesitamos nada más...


Para acceder a esta base de datos desde Power Query (Obtener y transformar) iremos a la ficha Datos > grupo Obtener y transformar > desplegable Nueva consulta > Desde una base de datos > opción Desde una base de datos de MYSQL

Power Query: Conexión a base de datos MySQL



Se puede observar que existen diferentes posibilidades de acceso a distintos tipos de base de datos (basadas en SQL Server, Oracle, Access, PostgreSQL, IBM ...)

En todos los casos es el asistente de conexión de Power Query quien gestiona la relación... visualmente, para el usuario, todos los casos son muy similares.


Para nuestro ejemplo de acceso a MySQL, tras la selección anterior se nos preguntará por el nombre del servidor y nombre de la base de datos:

Power Query: Conexión a base de datos MySQL



Tras aceptar veremos una nueva ventana con las tablas, consulta y vistas de la base de datos...

Power Query: Conexión a base de datos MySQL


En mi ejemplo me interesa trabajar sobre las dos únicas tablas que componen mi base de datos 'bbdd_excelforo', por lo que marco la opción: Seleccionar varios elementos, para marcar después ambas tablas...

El paso siguiente consiste en Editar la nueva consulta de Power Query.
Esto nos lleva al Editor de Power Query donde ejecutar todo tipo de acciones como con cualquier consulta....

Power Query: Conexión a base de datos MySQL



Y listo...

Sin duda una forma muy ágil y simple de conectar a base de datos tipo 'SQL'.

jueves, 14 de marzo de 2019

Pasar fecha de yyyymmdd a dd/mm/yyyy

Un clásico que tenía hace tiempo en los pendiente...
Convertir una 'fecha' dada como 'yyyymmdd' a fecha estándar 'dd/mm/yyyy'.

Es un problema bastante frecuente ya que muchos sistemas devuelven las fechas como valores numéricos no tratables directamente...

Pasar fecha de yyyymmdd a dd/mm/yyyy



Veamos tres posibles fórmulas para la conversión.
=FECHANUMERO(TEXTO(B3;"0000\/00\/00"))

La función TEXTO con ese formato de barras opuestas \/ separa los dígitos como corresponde 4+2+2... (año + mes + día), para que luego la función FECHANUMERO pase la fecha como texto a fecha real...

La segunda función es casi idéntica
=--TEXTO(B3;"0000\/00\/00")

Misma función TEXTO.. pero esta vez el encargado de devolver la fecha como tal es el doble signo menos.

La última es una clásica muy laboriosa.... que consiste en extraer los caracteres por posiciones de año, mes y día:
=FECHA(IZQUIERDA(B3;4);EXTRAE(B3;5;2);DERECHA(B3;2))


Existe una alternativa a las funciones que es el empleo del Asistente de Texto en columnas.
Si seleccionamos la/s fecha/s con forma 'yyyymmdd' y desde la ficha de Datos > grupo Herramienta de datos > botón Texto en columnas, en el tercer paso del asistente seleccionaremos formato Fecha: AMD y aceptaremos...

Pasar fecha de yyyymmdd a dd/mm/yyyy

martes, 12 de marzo de 2019

VBA: Extraer elementos de las diagonales de una matriz

Hoy construiremos una macro para extraer los distintos elementos que componen la diagonal de un elemento seleccionado en una matriz...
Se trata de dar una posible solución a un lector que preguntaba:
[...]Tengo una matriz en Excel de 5x5
por favor me ayudan con una forma de extraer todas las diagonales y saber cuales son los números que le corresponden cada ubicación.
1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
16 17 18 19 20
21 22 23 24 25

Hay unas particularidades con los números 3, 6, 13, 19
las casillas que le corresponden a las diagonales del 3 son ( 3, 7,11,9 y 15)
las casillas que le corresponden a las diagonales del 6 son ( 6,2,12,18, y 24)
las casillas que le corresponden a las diagonales del 13 son ( 1,7,13,19, y 25)
las casillas que le corresponden a las diagonales del 19 son ( 1,7,13,19, 25,23 y 15)[...]

VBA: Extraer elementos de las diagonales de una matriz



El funcionamiento será simple, sobre una matriz de cualquier dimensión (en el ejemplo 5x5), seleccionamos un único elemento... y tras presionar el botón que ejecuta la macro, obtendremos todos los elementos de las diagonales
de dicho elemento.


Insertamos un módulo estándar, y en esta ventana de código añadiremos nuestra macro:

Sub Diagonales()
'www.excelforo.com
Dim myRange As Range
Set myRange = Selection.CurrentRegion

Dim iniFil As Long, iniCol As Long, finFila As Long, finCol As Long, i As Long
iniFil = myRange.Row
iniCol = myRange.Column
finFila = myRange.Rows.Count + iniFil - 1
finCol = myRange.Columns.Count + iniCol - 1

Dim numFil As Long, numCol As Long
'contamos número de filas y columnas de la matriz
numFil = myRange.Rows.Count
numCol = myRange.Columns.Count

Dim celdaIni As Range
Dim fil As Long, col As Long
'una sola celda seleccionada dentro de la matriz de datos!!
Set celdaIni = Selection
fil = celdaIni.Row - iniFil + 1
col = celdaIni.Column - iniCol + 1

Dim delta As Long

Dim elementos() As Long
Dim columna As Long, fila As Long   'para identificar los elementos que cumplan..
Dim n As Long
n = 0
'recorremos cada elemento de la matriz
'por fila y luego por columna
For f = 1 To numFil
        For c = 1 To numCol
            delta = col - fil   'valor de ajuste según posición inicial en la matriz
            'me compara con la posición de la celda seleccionada
            If c = (fil - Abs(fil - f) + delta) Or c = (fil + Abs(fil - f) + delta) Then
                n = n + 1
                'añadimos elemento que pertenezca a su diagonal
                ReDim Preserve elementos(n)
                elementos(n) = myRange.Cells(f, c).Value
            End If
        Next c
Next f

'llevamos los elementos a la hoja de cálculo...
Range("J1:J99").Clear   'Limpia el destino...
For i = 1 To n
    Range("J1").Offset(i - 1, 0).Value = elementos(i)
Next i

End Sub



El algoritmo es simple... de especial relevancia el empleo de ReDim Preserve para cargar los elementos de las diagonales... un número de elementos a priori desconocido.