Consultar base datos SQL con Excel

Voy a explicar cómo estudiar una Base datos SQL Server, para ello podemos  hacerlo de dos formas:

1º.  Haciendo una consulta sobre el SQL SERVER lo cual veremos más adelante

2º. Trabajar directamente sobre una copia de seguridad de SQL Server con lo cual lo primero que debemos hacer es bajar SQL SERVER 2017 e instalarlo https://www.microsoft.com/en-us/sql-server/sql-server-downloads-free-trial le damos a todo que si y luego bajamos e instalamos  Microsoft Management Studio https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017 una vez instalados los dos simplemente debemos restarurar las base datos dentro del SQL SERVER 2017 instalado para ello vemos este simple vídeo  https://www.youtube.com/watch?v=R6FA3MrPytE

 Tenemos finalmente nuestro servidor llamado Router al cual conectamos bajos las claves de incio sesion windowa.

Queremos visualizar las tablas de SQL , pero qué método usar , estas las podríamos ver de muchas formas:

1. Desde un Acces que no voy a explicar
2. Desde el mismo SQL SERVER 2017
3. Hacer una consulta Power BI
4. Utilizar el excel, macros que es lo que voy a explica

Desde el mismo SQL SERVER 2017

Desde el mismo SQL  SERVER MANAGER STUDIO para ello lo abrimos " SSMS buscar y le damos a ejecutar" , sin nos vamos a base datos tenemos la copia seguridad restaurada que  llamada SAGE y luego expandimos para ver unos 3400 tablas donde están los datos, dentro del  programa podríamos ir una a una viendo cosas, dependencias, etc pero es bastante lento.

Desde Microsft SQL Management Studio :vamos a la base tatos, la seleccionamos, click ratón derecho, le damos a Taks y luego le damos a Export data…, sale una pantallita de aviso, le damos a siguiente, seleccionamos en este caso SQL SERVERT NATIVE CLIENT, el nombre del servidor, el tipo de autentificación y el nombre de la base datos, en este caso “ Sage”. Le damos a siguiente  seleccionamos que la destinación es un Excel, seleccionamos el archivo, que habremos creado previamente “ Excel 97-2003” y le decimos que estamos en la versión de Excel 97-2003

Seleccionamos la primera opción  y le damos a siguiente donde nos aparecen todas las tablas, tenemos la opción de darle exportar a  un excel y seguramente no funicione o aproverchar el Preview para movernos por todas y poder visualizalas. En este caso previsualicé las 3.487 tablas para saber cuales tenias datos y pasar luego a analizarlas

Hacer una consulta Power BI

Otra forma de ver las tablas y los datos es a través de una consulta POWER BI que usa el mismo motor Query pero ofrece menos fluidez que el excel sirba este enlace para aprender algo https://docs.microsoft.com/es-es/power-bi/service-gateway-sql-tutorial , pero esto tiene el inconveniente de que POWER BI va a ser incapaz de establacer relaciones 

Utilizar el excel, macros

El  método  más rápido es hacer una consulta datos SQL desde Excel y con Query podemos visualizar todas las tablas e ir moviéndonos por ellas, aquí tiene la limitación que Query no va a mostrar todas las filas, pero para un primer análisis es más que suficiente y  se mueve con mucha fluidez.

Con esa consulta de base datos general que hace el QUERY podemos devolver a un Excel el nombre de las 3.487 tablas, etc.


Llegado a este punto en el que hemos pre-visualizado todas las tablas que contienen datos y las cuales nos pueden resultar útiles " podemos ver las dependencias de las mismas en el mismo SSMS " si es que dominamos ese entorno " o podemos construir un Excel que nos ayude a analizar las tablas que hemos marcado y esto lo podemos hacer de dos formas:

1. Consulta SQL 
2. Macro Consulta SQL 



Mediante una simple consulta

Ponemos el nombre del Servidor en este caso es  ROUTER y el nombre de la base datos que es SAGE y le damos ACEPTAR

La consulta nos muestra 3.487 Tablas con Información  o tablas que tiene el programa donde no todas deben haber sido utilizadas,  podemos maximizar pantalla e ir pinchando sobre cada una de ellas para poder verlas

O le podemos dar editar y ver las tablas desde Query con tan solo movernos con el cursor, con la limitación que no van a ser mostrados todos los campos,para mostrar todos los campos debemos visualizarla desde un excel

Cualquier tabla la podemos cargar luego en un excel y visualizarla con todo detalle pero esto tiene la pega que debemos ir al Power Query y editar la tabla seleccionada con el editor avanzado o ir otra vez al listado  y seleccionar una tabla en concreto otra vez. Si hemos visualizado y nos interesan 100 tablas en concreto debemos ver una forma más concreta y rápida de hacerlo, de hecho lo primero que deberíamos haber hecho es llevar a un listado excel   las 3.487 tablas para marcar aquellas que queremos , en un Query vamos visualizando y sobre  otro hoja con el listado vamos marcando aquellos que nos interesa, para eso es tan fácil como decirle que cargue las tablas en una hoja.

Nota: para entrar a las tablas seleccionamos una y luego quitamos las conexión para verlas todas.

Ahora en vez de plantear una consulta SQL como hemos visto antes que no nos dejaría modificarla salvo que entremos en Query por lo cual la vamos a plantear de otra forma.

Ahora la consulta la haremos desde unos menús antiguos de excel para  ello vamos a http://www.ubit.ch/software/ubitmenu-languages/ y nos bajamos el exce en español

Con lo cual nos quedan unos menús dobles, ahora nos queda una barra doble, podemos usar las Barra norml o si le damos la pestaña Menu nos aparece la barra antigua, si le damos a cualquier botón del menú 365 se contrar

Ahora en la barra antigua vamos a ..

Y nos queda una Tabla la cual si hacemos botón derecho sobre ella la podemos editar

Llegado a este punto es mucho más rápido con tan solo copiar una tabla y copiarla en el texto del comando puedo hacer una consulta sin salir del excel

Por ejemplo podría tener el listado de  los nombres de las tablas que me interesan en la hoja A y las consulta en la hoja B y por medio de una macro, al hacer doble click la copio en el portapapeles para ello habría que hace referencia a una librería y nos situaría en la Hoja B para simplemente hacer click ratón derecho y copiar el contenido como nuevo comando.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

'Para esto se necesita hacer referencia a una libreria Microsoft Forms 2.0 Object Library para ello gregamos FM20.DLL

    With New DataObject

        .SetText ActiveCell.Text

        .PutInClipboard

    End With

Sheets("B").Select

End Sub

A su vez una vez se ejecuta la consulta podría ejecutar una segunda macro que me da más espacio en la Fila 1, como la tabla está a partir de B1 me copia todos los enunciados hasta la columna BL y mes los pega empezando por C1 hasta por ejemplo  IS1, luego selecciono las columnas 2 a 50000 y le doy un ancho de 15

Sub Macro3()

    Rows("1:1").RowHeight = 280

    ActiveWorkbook.RefreshAll

        

        Columns("b:LS").Select

    Selection.Columns.AutoFit

    

    Range("C1:IS1").Select

    Selection.Copy

    Range("B2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=True

        Columns("B:B").EntireColumn.AutoFit

        

         Rows("2:50000").Select

    Selection.RowHeight = 15

    Range("A1").Select

    

End Sub

A su vez podemos crear una serie de macros y formatos condicionales que nos ayuden al análisis, por ejemplo : con un doble click sobre la un valor de la Fiba B podemos ir a la cabecera de la columna, recordar que la macro coge los valores cabecera de las columnas y nos los pega en B1 para no tener que estar moviéndonos ya que  son mas visibles de en Fila que en columna, a su vez tanto las Fila 1 como la columna B tienen formatos condicionales que indican que si hemos puesto ese título de columna en una Tabla lo resaltará en Naraja