Después de la generación de datos realizada en Excel, que explicamos en la primera parte del artículo, en esta segunda entrega trataremos acerca de la forma de insertar dicha información en una base de datos SQL Server.
Creación de la base de datos
Finalizada la creación de la hoja de cálculo, volcaremos su contenido en una base de datos SQL Server, que crearemos a continuación ejecutando el siguiente script desde SQL Server Management Studio.
USE master
GO
CREATE DATABASE Poblacion
GO
USE Poblacion
GO
CREATE TABLE DatosPoblacion (
Fila_ID int NOT NULL,
Edad_ID int NULL,
Sexo_ID char(1) NULL,
CCAA_ID int NULL,
Pais_ID int NULL,
Fecha_Alta datetime NULL,
CONSTRAINT PK_DatosPoblacion PRIMARY KEY CLUSTERED (Fila_ID ASC))
GO
CREATE TABLE Sexo (
Sexo_ID char(1) NOT NULL,
Sexo_DS varchar(10) NULL,
CONSTRAINT PK_Sexo PRIMARY KEY CLUSTERED (Sexo_ID ASC))
GO
CREATE TABLE CCAA (
CCAA_ID int NOT NULL,
CCAA_DS varchar(50) NULL,
CONSTRAINT PK_CCAA PRIMARY KEY CLUSTERED (CCAA_ID ASC))
GO
CREATE TABLE Pais (
Pais_ID int NOT NULL,
Pais_DS varchar(50) NULL,
CONSTRAINT PK_Paises PRIMARY KEY CLUSTERED (Pais_ID ASC))
GO
Como acabamos de comprobar, además de la tabla que albergará los datos que generamos desde Excel, también crearemos las tablas catálogo, que contendrán las descripciones de ciertos campos de código existentes en la tabla DatosPoblacion, con las que estableceremos las oportunas relaciones.
Importar los datos de Excel desde SQL Server
Para insertar datos en la tabla DatosPoblacion de SQL Server, utilizaremos la función OPENROWSET de Transact-SQL de la siguiente manera.
INSERT INTO DatosPoblacion
SELECT Fila_ID,Edad_ID,Sexo_ID,CCAA_ID,Pais_ID,Fecha_Alta
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 8.0;Database=C:\DatosOrigen\GenerarDatosPoblacion.xlsx',
'SELECT * FROM [Hoja1$]')
Sin embargo, es posible que al intentar ejecutar esta sentencia de inserción, nos encontremos con un error, que nos informa de que SQL Server no está configurado para realizar consultas de estas características, denominadas 'Ad Hoc Distributed Queries'.
Si queremos comprobar la configuración de opciones de SQL Server, debemos ejecutar el procedimiento almacenado de sistema sp_configure. No obstante, lo más probable es que entre las opciones mostradas, no veamos la configuración de consultas distribuidas. Si estamos en este caso, tenemos que activar la visualización de opciones avanzadas utilizando las siguientes sentencias.
EXECUTE sp_configure 'show advanced options',1
GO
RECONFIGURE
GO
Ahora ya podremos ver el valor de la opción 'Ad Hoc Distributed Queries' al ejecutar sp_configure. Para activarlo ejecutaremos lo siguiente.
EXECUTE sp_configure 'Ad Hoc Distributed Queries',1
GO
RECONFIGURE
GO
Al volver al ejecutar sp_configure, veremos que ya está activada la posibilidad de ejecutar consultas distribuidas.
De esta forma, la anterior sentencia con OPENROWSET ya funcionará correctamente, llenando la tabla DatosPoblacion con el contenido del archivo GenerarDatosPoblacion.xlsx.
Optimizando la importación de datos de Excel
En el punto actual podemos encontrarnos, no obstante, ante un problema de rendimiento, ya que si hemos seguido los pasos indicados anteriormente durante la creación del archivo Excel, tendremos una hoja de cálculo con un millón de filas, que puede tardar alrededor de quince minutos en cargarse en la tabla de SQL Server. Para el ejemplo que estamos desarrollando en este artículo se ha utilizado una máquina virtual con Windows 7 como sistema operativo y 1,5 GB de memoria, por lo que los mencionados tiempos pueden variar en función de la configuración del equipo utilizado para estas pruebas.
Si queremos disminuir estos tiempos de carga podemos optar por el uso de la técnica alternativa de traspaso de datos que explicamos seguidamente.
En primer lugar volveremos a abrir desde Excel el archivo GenerarDatosPoblacion.xlsx, guardándolo como archivo de tipo "CSV (delimitado por comas)".
De esta forma obtendremos un archivo de texto con los campos delimitados por el carácter de punto y coma. Podemos ver su contenido abriéndolo con el Bloc de notas.
A continuación crearemos en la base de datos una nueva tabla con la siguiente estructura.
CREATE TABLE DatosPoblacionExcel (
Fila_ID varchar(20) NULL,
Edad_ID varchar(20) NULL,
Sexo_ID varchar(20) NULL,
CCAA_ID varchar(20) NULL,
Pais_ID varchar(20) NULL,
Anualidad varchar(20) NULL,
Mes varchar(20) NULL,
Dia varchar(20) NULL,
Fecha_Alta varchar(20) NULL)
En esta nueva tabla importaremos el contenido del archivo GenerarDatosPoblacion.csv, utilizando la sentencia BULK INSERT de Transact-SQL. Mediante la opción FIELDTERMINATOR especificaremos el carácter usado como separador de campos, mientras que con la opción FIRSTROW indicaremos que la lectura de datos comience en la segunda fila del archivo, ya que la primera contiene los nombres de las columnas.
BULK INSERT DatosPoblacionExcel
FROM 'C:\DatosOrigen\GenerarDatosPoblacion.csv'
WITH (FIELDTERMINATOR =';', FIRSTROW=2)
El tiempo consumido por esta operación de inserción masiva en la tabla DatosPoblacionExcel será de unos treinta segundos.
Para terminar con este proceso, insertaremos en la tabla DatosPoblacion los registros de la tabla DatosPoblacionExcel, excluyendo los campos innecesarios, como vemos en la siguiente sentencia, cuya ejecución tardará unos 15 segundos aproximadamente.
INSERT INTO DatosPoblacion
SELECT Fila_ID,Edad_ID,Sexo_ID,CCAA_ID,Pais_ID,Fecha_Alta
FROM DatosPoblacionExcel
Como acabamos de comprobar, esta técnica de inserción de datos, si bien nos obliga a realizar un paso adicional, supone una importante ganancia de tiempo, ya que emplea menos de un minuto en el traspaso de datos a la tabla DatosPoblacion, frente a los quince minutos utilizados por la función OPENROWSET.
Importando el resto de tablas catálogo
Para las tablas CCAA y Pais, recurriremos a dos archivos Excel que contienen, respectivamente, la clasificación oficial de comunidades autónomas y países. Estos archivos se encuentran disponibles en formato comprimido, en el sitio Web del Instituto de Estadística de la Comunidad de Madrid. Una vez descargados y descomprimidos ejecutaremos las siguientes sentencias SQL para importarlos a nuestra base de datos.
INSERT INTO CCAA
SELECT DISTINCT ccaa, liteccaa
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\DatosOrigen\ccaaprov.xls',
'SELECT * FROM [ccaaprov$]')
INSERT INTO Pais
SELECT DISTINCT isopais,lpaisc
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\DatosOrigen\cozonu.xls',
'SELECT * FROM [cozonu$]')
Observemos que según la versión de Excel del archivo a importar, en la función OPENROWSET utilizaremos un proveedor distinto para obtener los datos. Si el archivo corresponde a Excel 2007-2010 emplearemos 'Microsoft.ACE.OLEDB.12.0', mientras que para versiones anteriores será 'Microsoft.Jet.OLEDB.4.0'.
Ajustando el código de país en la tabla DatosPoblacion
Si observamos con detenimiento los registros de la tabla Pais, nos percataremos de que los valores del campo Pais_ID no son correlativos; siendo, además, el valor menor 4 y el mayor 894.
Esto contrasta con los datos existentes en el campo del mismo nombre correspondiente a la tabla DatosPoblacion, ya que, si bien el valor máximo y mínimo de dicho campo también está entre 4 y 894, encontraremos una buena cantidad de registros en los que el campo Pais_ID no corresponderá a ningún valor en la tabla Pais.
Para solucionar este problema vamos a recurrir a un par de técnicas, de las cuales, la primera consistirá en tomar, de la tabla DatosPoblacion, cada uno de los valores del campo Pais_ID que no existan en la tabla Pais, sumándole uno hasta que lleguemos a un valor que sí exista en la mencionada tabla catálogo de países. Este proceso lo implementaremos en la función de SQL Server que vemos a continuación.
CREATE FUNCTION dbo.ObtenerPais(@nPais_ID int)
RETURNS int
AS
BEGIN
WHILE (SELECT COUNT(*) FROM Pais WHERE Pais_ID = @nPais_ID) = 0
BEGIN
SET @nPais_ID = @nPais_ID + 1
END
RETURN @nPais_ID
END
GO
La actualización del campo Pais_ID en la tabla DatosPoblacion la llevaremos a cabo con la siguiente sentencia.
UPDATE DatosPoblacion
SET Pais_ID = dbo.ObtenerPais(Pais_ID)
WHERE Pais_ID NOT IN (SELECT Pais_ID FROM Pais)
La segunda de las técnicas resulta más directa, ya que evitamos el uso de la función de búsqueda del campo Pais_ID en la tabla de países. Lo que hacemos en este caso es una actualización del campo Pais_ID para toda la tabla DatosPoblacion, buscando en la tabla Pais, el valor de Pais_ID más próximo al que existe en el mismo campo de la tabla DatosPoblacion.
UPDATE DatosPoblacion
SET Pais_ID = (SELECT TOP 1 Pais.Pais_ID FROM Pais
WHERE Pais.Pais_ID >= DatosPoblacion.Pais_ID
ORDER BY Pais.Pais_ID)
En ambos casos, conseguimos que todos los registros de la tabla DatosPoblacion crucen correctamente con la tabla Pais por el campo Pais_ID.
Inserción manual de datos
Después de las operaciones anteriores, la única tabla que permanece sin datos es Sexo, por lo que ejecutaremos las siguientes sentencias, que crearán los registros necesarios.
INSERT INTO Sexo VALUES ('H','Hombre')
INSERT INTO Sexo VALUES ('M','Mujer')
Estableciendo relaciones entre las tablas
Para finalizar la creación de la base de datos, estableceremos las oportunas relaciones entre los campos de la tabla DatosPoblacion y el resto de tablas catálogo, utilizando las siguientes sentencias.
ALTER TABLE DatosPoblacion WITH CHECK ADD
CONSTRAINT FK_DatosPoblacion_Sexo FOREIGN KEY(Sexo_ID) REFERENCES Sexo (Sexo_ID),
CONSTRAINT FK_DatosPoblacion_CCAA FOREIGN KEY(CCAA_ID) REFERENCES CCAA (CCAA_ID),
CONSTRAINT FK_DatosPoblacion_Pais FOREIGN KEY(Pais_ID) REFERENCES Pais (Pais_ID)
Después de esta operación damos por concluido este artículo, en el que hemos explicado las diferentes partes de un proceso para generar, desde Excel, un volumen considerable de datos de prueba, que poder utilizar posteriormente desde SQL Server. En el siguiente enlace se encuentra disponible el código del ejemplo. Esperamos que os resulte de utilidad.
Un saludo.
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.