Excel, SQL Server, SQL Server 2012

Generar datos de prueba para SQL Server desde Excel. El traspaso a SQL Server (y 2)

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.

GenerarDatosPruebaParaSQLServerDesdeExcel_13

 

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.

GenerarDatosPruebaParaSQLServerDesdeExcel_14

 

De esta forma, la anterior sentencia con OPENROWSET ya funcionará correctamente, llenando la tabla DatosPoblacion con el contenido del archivo GenerarDatosPoblacion.xlsx.

GenerarDatosPruebaParaSQLServerDesdeExcel_15

 

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)».

GenerarDatosPruebaParaSQLServerDesdeExcel_16

 

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.

GenerarDatosPruebaParaSQLServerDesdeExcel_17

 

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.

GenerarDatosPruebaParaSQLServerDesdeExcel_18

 

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. Esperamos que os resulte de utilidad.

Un saludo.

3 Comentarios

  1. anonymous

    Excelente, se podría descargar el código fuente ? salu2grz

  2. lmblanco

    Hola preguntón cojonero

    Gracias por tu opinión, celebro que te haya gustado el artículo 🙂

    Acabo de añadir al final un enlace para poder descargar el archivo Excel que contiene la macro para generar los datos y otro archivo de script SQL para generar la BBDD y el resto de sentencias.

    Un saludo,
    Luismi

  3. anonymous

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

Deja un comentario

Tema creado por Anders Norén