Sin categoría

Generar datos de prueba para SQL Server desde Excel. Operaciones en Excel (1)

 

La creación de datos de muestreo

Durante las diversas fases del desarrollo de una aplicación, en la mayor parte de las ocasiones, nos encontramos ante la necesidad de tener a nuestra disposición un conjunto de datos de prueba, para poder utilizarlos en los diferentes procesos que estamos desarrollando. Una situación similar se produce si estamos construyendo un sistema de información basado en cubos de datos OLAP mediante SQL Server Analysis Services, ya que, por lo general, en estos casos necesitaremos además, que el volumen de los datos sea de un tamaño considerable, para poder realizar simulaciones de análisis.

Supongamos que tenemos que generar una base de datos con información poblacional, en la que una tabla albergaría los datos de los individuos tales como edad, códigos de comunidad autónoma de residencia, país de procedencia, sexo, fecha de alta en el registro, etc. Por otro lado, necesitaríamos también una serie de tablas catálogo de países, comunidades autónomas, y demás valores relacionados con los campos de la tabla de individuos.

Entre todo el abanico de utilidades, trucos, etc., que existen para llevar a cabo esta tarea, en el presente artículo haremos uso de Excel como herramienta para la generación del conjunto de datos ficticios correspondiente a los individuos, que posteriormente volcaremos en una base de datos SQL Server, la cual podríamos usar como fuente de datos de la aplicación o sistema en desarrollo.

 

Creando los datos con Excel

Una vez iniciado Excel 2010 (también podemos utilizar versiones anteriores), nuestra primera tarea será la creación de una columna con los valores que nos servirán para identificar las filas de la tabla. La forma más simple de generarlos, consiste en introducir un par de números correlativos en sendas celdas de una de las columnas de la hoja de cálculo, seleccionar ambas celdas, y arrastrar el marcador de relleno hasta la última fila para la cual queramos generar los números.GenerarDatosPruebaParaSQLServerDesdeExcel_01

 

No obstante, la generación de valores mediante esta técnica puede resultar un tanto engorrosa en el caso de que debamos producir una gran cantidad de filas y/o columnas, por lo que para la creación de datos en todas las columnas de la hoja vamos a recurrir a un medio más flexible a la par que potente: las macros de Excel.

 

Creación de una macro

Para crear una macro haremos clic en la pestaña Vista de la cinta de opciones de Excel, y dentro del grupo Macros haremos clic en la opción del mismo nombre, que abrirá el cuadro de diálogo Macro, donde escribiremos el nombre de nuestra macro: CrearDatosPoblacion.

GenerarDatosPruebaParaSQLServerDesdeExcel_02

 

Haciendo clic en el botón Crear, se abrirá la ventana de Visual Basic para Aplicaciones (VBA), con el editor de la nueva macro recién creada, para que podamos empezar a escribir su código.

GenerarDatosPruebaParaSQLServerDesdeExcel_03

 

Dentro del cuerpo del procedimiento CrearDatosPoblación, escribiremos el siguiente bloque de código, donde en primer lugar, limpiaremos las celdas de la hoja de cálculo en la que estemos actualmente posicionados. A continuación solicitaremos al usuario que introduzca, mediante una caja de diálogo InputBox, el número de filas a generar. Después de insertar el título de la columna, introduciremos los dos primeros valores que inician la serie, la cual generaremos mediante el método Selection.AutoFill.

Option Explicit
 
Sub CrearDatosPoblacion()
 
Dim nFilaDestino As Long
Dim sCeldaOrigen As String
Dim sCeldaDestino As String
 
'limpiar las celdas de la hoja de cálculo
Cells.Select
Selection.ClearContents
 
nFilaDestino = InputBox("Número de registros a generar")
nFilaDestino = nFilaDestino + 1
 
'columna fila_id
'---------------

'título de columna
Range("A1").Select
ActiveCell.FormulaR1C1 = "Fila_ID"
 
'valores iniciales de la serie a generar
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Range("A3").Select
ActiveCell.FormulaR1C1 = "2"
 
'seleccionar rango de valores iniciales
Range("A2:A3").Select
 
'rellenar rango total de celdas
sCeldaOrigen = "A2"
sCeldaDestino = "A" & nFilaDestino
Selection.AutoFill Destination:=Range(sCeldaOrigen & ":" & sCeldaDestino), Type:=xlFillDefault
 
End Sub

 

 

GenerarDatosPruebaParaSQLServerDesdeExcel_04

 

Para ejecutar la macro seleccionaremos la opción de menú Ejecutar | Ejecutar Sub/UserForm, o pulsaremos la tecla F5, rellenándose la primera columna de la hoja con la cantidad de valores indicada en el InputBox.

GenerarDatosPruebaParaSQLServerDesdeExcel_05

 

Antes de proseguir grabaremos nuestro trabajo desde la ventana de VBA o de Excel, teniendo en cuenta que deberemos hacerlo en un archivo de tipo «Libro de Excel habilitado para macros» (extensión .xlsm), de lo que seremos avisados por un cuadro de diálogo en el momento de la grabación. Haciendo clic en el botón No de dicho diálogo, guardaremos nuestra hoja de cálculo en un archivo con el nombre GenerarDatosPoblacion.xlsm.

GenerarDatosPruebaParaSQLServerDesdeExcel_06

 

Generación de datos aleatorios

La siguiente columna a crear corresponderá a la edad de las personas; en ella, necesitaremos generar valores aleatorios entre un intervalo de números, que representen las edades máxima y mínima que una persona puede tener, por ejemplo, entre 0 y 120.

Para crear, en una celda de Excel, un valor aleatorio comprendido entre dos números, podemos utilizar una fórmula que incluya la función ALEATORIO.ENTRE, que recibe como parámetro los mencionados números, devolviendo como resultado el número generado.

GenerarDatosPruebaParaSQLServerDesdeExcel_07

 

Trasladando esta funcionalidad a la macro que estamos desarrollando, añadiremos a la misma el siguiente bloque de código, en el que asignaremos a una celda de la segunda columna una expresión de fórmula conteniendo RANDBETWEEN(0,120) (equivalente a la función ALEATORIO.ENTRE), seleccionaremos dicha celda, y repetiremos la fórmula que contiene a lo largo del rango de celdas, usando el método ActiveCell.AutoFill.

'edad
'----
Range("B1").Select
ActiveCell.FormulaR1C1 = "Edad_ID"
 
sCeldaOrigen = "B2"
sCeldaDestino = "B" & nFilaDestino
Range(sCeldaOrigen).Select
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(0,120)"
ActiveCell.AutoFill Destination:=Range(sCeldaOrigen & ":" & sCeldaDestino), Type:=xlFillDefault

 

Ejecutando otra vez la macro, obtendremos la nueva columna con los datos de edad.

GenerarDatosPruebaParaSQLServerDesdeExcel_08

 

En nuestro siguiente paso le toca el turno a la columna con los valores para el sexo de los individuos. Aquí podríamos haber usado dos números: 1 y 2, generados igualmente de forma aleatoria, para identificar respectivamente a hombre y mujer, pero vamos a complicar un poco esta operación, utilizando en su lugar las letras H y M como valores para la columna, por lo que el problema se encuentra ahora en cómo generar aleatoriamente estas letras en las celdas de la columna, ya que la función RANDBETWEEN sólo recibe y devuelve resultados numéricos.

La solución es muy simple, ya que también consiste en utilizar la función RANDBETWEEN, pero combinándola con la expresión de decisión IF. A RANDBETWEEN le pasaremos los números 1 y 2 como parámetros, y según el resultado obtenido, mediante IF devolveremos la letra H o M. A continuación se muestra el bloque de código para esta columna, que añadiremos a la macro.

'sexo
'----
Range("C1").Select
ActiveCell.FormulaR1C1 = "Sexo_ID"
 
sCeldaOrigen = "C2"
sCeldaDestino = "C" & nFilaDestino
Range(sCeldaOrigen).Select
ActiveCell.FormulaR1C1 = "=IF(RANDBETWEEN(1,2)=1,""H"",""M"")"
ActiveCell.AutoFill Destination:=Range(sCeldaOrigen & ":" & sCeldaDestino), Type:=xlFillDefault

 

GenerarDatosPruebaParaSQLServerDesdeExcel_09

 

Para las dos siguientes columnas: código de comunidad autónoma de residencia y código de país de procedencia, seguiremos la misma mecánica que en la columna de edades, si bien utilizando distintos intervalos numéricos.

'ccaa
'----
Range("D1").Select
ActiveCell.FormulaR1C1 = "CCAA_ID"
 
sCeldaOrigen = "D2"
sCeldaDestino = "D" & nFilaDestino
Range(sCeldaOrigen).Select
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(1,19)"
ActiveCell.AutoFill Destination:=Range(sCeldaOrigen & ":" & sCeldaDestino), Type:=xlFillDefault
 
'país
'----
Range("E1").Select
ActiveCell.FormulaR1C1 = "Pais_ID"
 
sCeldaOrigen = "E2"
sCeldaDestino = "E" & nFilaDestino
Range(sCeldaOrigen).Select
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(4,894)"
ActiveCell.AutoFill Destination:=Range(sCeldaOrigen & ":" & sCeldaDestino), Type:=xlFillDefault

 

GenerarDatosPruebaParaSQLServerDesdeExcel_10

 

Nuestro siguiente paso consistirá en crear los datos de una supuesta fecha de alta de los individuos en este sistema poblacional, tarea que realizaremos en dos fases. En primer lugar, crearemos cada parte de la fecha en columnas separadas.

'elementos de fecha:
'===================
'año
'---
Range("F1").Select
ActiveCell.FormulaR1C1 = "Anualidad"
 
sCeldaOrigen = "F2"
sCeldaDestino = "F" & nFilaDestino
Range(sCeldaOrigen).Select
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(2008,2010)"
ActiveCell.AutoFill Destination:=Range(sCeldaOrigen & ":" & sCeldaDestino), Type:=xlFillDefault
 
'mes
'---
Range("G1").Select
ActiveCell.FormulaR1C1 = "Mes"
 
sCeldaOrigen = "G2"
sCeldaDestino = "G" & nFilaDestino
Range(sCeldaOrigen).Select
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(1,12)"
ActiveCell.AutoFill Destination:=Range(sCeldaOrigen & ":" & sCeldaDestino), Type:=xlFillDefault
 
'día
'---
Range("H1").Select
ActiveCell.FormulaR1C1 = "Dia"
 
sCeldaOrigen = "H2"
sCeldaDestino = "H" & nFilaDestino
Range(sCeldaOrigen).Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=2, RANDBETWEEN(1,28), IF(OR(RC[-1]=4, RC[-1]=6, RC[-1]=9, RC[-1]=11), RANDBETWEEN(1,30), RANDBETWEEN(1,31)))"
ActiveCell.AutoFill Destination:=Range(sCeldaOrigen & ":" & sCeldaDestino), Type:=xlFillDefault

Como acabamos de ver, el día es la parte de la fecha que requiere mayor trabajo, ya que debemos poner cuidado en evitar la generación, por ejemplo, de un día 31 si en la columna de mes tenemos el valor 2. Esto lo solucionaremos comprobando, en primer lugar, el valor de dicha columna de mes, y en función del mismo, usaremos un intervalo distinto para generar el día; para ello, en la fórmula utilizaremos varias expresiones IF combinadas con funciones RANDBETWEEN.

GenerarDatosPruebaParaSQLServerDesdeExcel_11

 

En la segunda fase de esta operación, crearemos una última columna en la hoja de cálculo con una fecha en un formato comprensible para SQL Server, fruto de la concatenación de las anteriores columnas de anualidad, mes y día.

Es necesario tener en cuenta, al componer la fecha de esta manera, que debemos añadir un cero al mes y al día, cuando estos valores tengan solamente un dígito.

'componer fecha
'--------------
Range("I1").Select
ActiveCell.FormulaR1C1 = "Fecha_Alta"
 
sCeldaOrigen = "I2"
sCeldaDestino = "I" & nFilaDestino
Range(sCeldaOrigen).Select
ActiveCell.FormulaR1C1 = "=RC[-3] & IF(LEN(RC[-2])=1,""0"" & RC[-2],RC[-2]) & " & _
    "IF(LEN(RC[-1])=1,""0"" & RC[-1],RC[-1]) "
 
ActiveCell.AutoFill Destination:=Range(sCeldaOrigen & ":" & sCeldaDestino), Type:=xlFillDefault

Al volver a ejecutar la macro, obtendremos la fecha correctamente formateada en la última columna. En esta ocasión, además, estableceremos la cifra de registros a generar en un millón, de esa forma probaremos el potencial de creación de los datos.

GenerarDatosPruebaParaSQLServerDesdeExcel_12

 

Para finalizar las operaciones con Excel grabaremos el archivo, esta vez con formato de «Libro de Excel» (extensión .xlsx). Nuevamente aparecerá el cuadro de diálogo de aviso, que nos informará de que no podemos guardar las macros en un archivo .xlsx, al que contestaremos haciendo clic en Sí.

Llegados a este punto concluimos la primera parte del artículo; en la segunda entrega explicaremos cómo traspasar los datos que acabamos de crear a SQL Server.

Un saludo.

7 Comentarios

  1. anonymous

    Tiene buena pinta !!! 🙂

  2. lmblanco

    Gracias Enrique, me alegro que te parezca interesante. Espero que la segunda parte también te guste 😉

    Un saludo,
    Luismi

  3. anonymous

    bueno Luismi, para el próximo estudio lo aplicamos ;p ¿unas avellanas?

  4. lmblanco

    Hola Laura

    Gracias por tu interés en el post, y te acepto esas avellanas 😉

    Un saludo,
    Luismi

  5. anonymous

    Con motivo de un próximo artículo que dedicaremos a la elaboración de pirámides

  6. lmba

    Hola

    Interesante, me gustaría saber si está el código fuente de las macros disponible para descarga.

    Saludos

  7. anonymous

    This article addresses the challenge of develop a process to create a database with demographic information

Responder a Cancelar respuesta

Tema creado por Anders Norén