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.
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.
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.
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
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.
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.
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.
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.
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
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
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.
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.
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.
anonymous
Tiene buena pinta !!! 🙂
lmblanco
Gracias Enrique, me alegro que te parezca interesante. Espero que la segunda parte también te guste 😉
Un saludo,
Luismi
anonymous
bueno Luismi, para el próximo estudio lo aplicamos ;p ¿unas avellanas?
lmblanco
Hola Laura
Gracias por tu interés en el post, y te acepto esas avellanas 😉
Un saludo,
Luismi
anonymous
Con motivo de un próximo artículo que dedicaremos a la elaboración de pirámides
lmba
Hola
Interesante, me gustaría saber si está el código fuente de las macros disponible para descarga.
Saludos
anonymous
This article addresses the challenge of develop a process to create a database with demographic information