¿Alguna vez has necesitado combinar múltiples archivos CSV con una estructura de datos similar en un solo conjunto de datos en una base de datos de SQL Server? Si bien existen varias herramientas y lenguajes de script disponibles para lograr esta tarea, hoy exploraremos cómo lograrlo utilizando únicamente T-SQL.
Paso 1 – Verificar los permisos de la cuenta de servicio
Antes de comenzar, asegúrate de que la cuenta de servicio de tu SQL Server tenga los permisos necesarios para acceder a la unidad y la carpeta desde la cual deseas cargar los archivos CSV. Puedes verificar el nombre de la cuenta de servicio abriendo la versión correspondiente del Administrador de configuración de SQL Server y navegando hasta los Servicios de SQL Server.
Paso 2 – Previsualizar los archivos CSV
Supongamos que tenemos un directorio de trabajo con varios archivos CSV, como “1.csv” y “2.csv”, cada uno de ellos contiene una sola columna. Nuestro objetivo es insertar dinámicamente los datos de cada archivo CSV en una tabla temporal de SQL Server. Para lograr esto, podemos utilizar el procedimiento almacenado del sistema xp_DirTree para obtener los nombres de todos los archivos en el directorio y cargar los resultados en una tabla temporal.
Código:
IF OBJECT_ID('TEMPDB..#TEMP_FILES') IS NOT NULL DROP TABLE #TEMP_FILES
CREATE TABLE #TEMP_FILES
(
FileName VARCHAR(MAX),
DEPTH VARCHAR(MAX),
[FILE] VARCHAR(MAX)
)
INSERT INTO #TEMP_FILES
EXEC master.dbo.xp_DirTree 'C:\Test\',1,1
A continuación, podemos eliminar cualquier registro de la tabla temporal donde la extensión del archivo no sea “.csv”.
Código:
DELETE FROM #TEMP_FILES WHERE RIGHT(FileName,4) != '.CSV'
Paso 3 – Crear la estructura de la tabla temporal para los nombres de archivo
Ahora, creemos otra tabla temporal con el mismo esquema que los archivos CSV que vamos a cargar. En este ejemplo, utilizaremos una sola columna llamada “A”. Iteraremos sobre los registros restantes en nuestra primera tabla temporal, que debería contener los nombres de los archivos CSV de los cuales queremos cargar datos. Para cada iteración, asignaremos el nombre del archivo a una variable y lo pasaremos a una instrucción SQL dinámica, que ejecutará el comando BULK INSERT.
Código:
IF OBJECT_ID('TEMPDB..#TEMP_RESULTS') IS NOT NULL DROP TABLE #TMP
CREATE TABLE #TEMP_RESULTS
(
[A] VARCHAR(MAX)
)
DECLARE @FILENAME VARCHAR(MAX),@SQL VARCHAR(MAX)
WHILE EXISTS(SELECT * FROM #TEMP_FILES)
BEGIN
SET @FILENAME = (SELECT TOP 1 FileName FROM #TEMP_FILES)
SET @SQL = 'BULK INSERT #TEMP_RESULTS
FROM ''C:\Test\' + @FILENAME +'''
WITH (FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'');'
PRINT @SQL
EXEC(@SQL)
DELETE FROM #TEMP_FILES WHERE FileName = @FILENAME
END
Después de ejecutar el código anterior, todos los datos de “1.csv” y “2.csv” se almacenarán en la tabla #temp_results.
Paso 4 – Mejorar el proceso de inserción con un bloque Try-Catch
Aunque el enfoque anterior es una forma simple y rentable de cargar datos en una base de datos de SQL Server, es importante considerar posibles problemas con la operación de inserción masiva. Los datos delimitados en cualquier forma pueden contener valores que pueden interrumpir el proceso de inserción. Para manejar tales escenarios, podemos utilizar una declaración try-catch para procesar cada archivo individualmente, incluso si el primer archivo falla.
Código:
WHILE EXISTS(SELECT * FROM #TEMP_FILES)
BEGIN
BEGIN TRY
SET @FILENAME = (SELECT TOP 1 FileName FROM #TEMP_FILES)
SET @SQL = 'BULK INSERT #TEMP_RESULTS
FROM ''C:\Test\' + @FILENAME +'''
WITH (FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'');'
PRINT @SQL
EXEC(@SQL)
END TRY
BEGIN CATCH
PRINT 'Error al procesar: ' + @FILENAME
END CATCH
DELETE FROM #TEMP_FILES WHERE FileName = @FILENAME
END
Paso 5 – Un ejemplo más complejo
Apliquemos la lógica que hemos creado a un conjunto de datos más complejo. Supongamos que tenemos tres archivos CSV que contienen datos de viviendas en diferentes calles, incluyendo información sobre el tamaño en pies cuadrados, el número de habitaciones y baños, y un indicador de garaje. En este ejemplo, actualizaremos el directorio a “C:\Test\Houses”.
Código:
IF OBJECT_ID('TEMPDB..#TEMP_FILES') IS NOT NULL DROP TABLE #TEMP_FILES
CREATE TABLE #TEMP_FILES
(
FileName VARCHAR(MAX),
DEPTH VARCHAR(MAX),
[FILE] VARCHAR(MAX)
)
INSERT INTO #TEMP_FILES
EXEC master.dbo.xp_DirTree 'C:\Test\Houses\',1,1
DECLARE @FILENAME VARCHAR(MAX),@SQL VARCHAR(MAX)
IF OBJECT_ID('TEMPDB..#TEMP_RESULTS') IS NOT NULL DROP TABLE #TEMP_RESULTS
CREATE TABLE #TEMP_RESULTS
(
[Address] VARCHAR(MAX),
[BedRoom] VARCHAR(MAX),
[Baths] VARCHAR(MAX),
[SquareFootage] VARCHAR(MAX),
[Garage] VARCHAR(MAX)
)
WHILE EXISTS(SELECT * FROM #TEMP_FILES)
BEGIN
BEGIN TRY
SET @FILENAME = (SELECT TOP 1 FileName FROM #TEMP_FILES)
SET @SQL = 'BULK INSERT #TEMP_RESULTS
FROM ''C:\Test\Houses\' + @FILENAME +'''
WITH (FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'');'
PRINT @SQL
EXEC(@SQL)
END TRY
BEGIN CATCH
PRINT 'Error al procesar: ' + @FILENAME
END CATCH
DELETE FROM #TEMP_FILES WHERE FileName = @FILENAME
END
Puedes ver los datos cargados en su formato original VARCHAR(MAX) utilizando la siguiente consulta:
Código:
SELECT * FROM #TEMP_RESULTS
Si lo deseas, también puedes convertir los tipos de datos a algo más apropiado para este conjunto de datos. Por ejemplo:
Código:
SELECT [Address], CAST(BEDROOM AS INT) AS BedRooms, CAST(BATHS AS Decimal(3,2)) AS BathRooms, CAST(SQUAREFOOTAGE AS INT) As SquareFootage, CAST(CASE WHEN GARAGE = 'Y' THEN 1 ELSE 0 END AS BIT) as GarageIndicator FROM #TEMP_RESULTS
Paso 6 – Evitar reprocesar archivos ya procesados
Para evitar reprocesar archivos que pueden existir en varios directorios, puedes crear una tabla “PreviouslyProcessed” y excluir los nombres de archivo que ya han sido procesados. Esto se puede lograr modificando la condición del bucle while de la siguiente manera:
Código:
IF OBJECT_ID('TEMPDB..#TEMP_FILES') IS NOT NULL DROP TABLE #TEMP_FILES
CREATE TABLE #TEMP_FILES
(
FileName VARCHAR(MAX),
DEPTH VARCHAR(MAX),
[FILE] VARCHAR(MAX)
)
-- Crear una tabla para registrar los registros procesados.
IF OBJECT_ID('dbo.PreviouslyProcessed') IS NOT NULL DROP TABLE PreviouslyProcessed
CREATE TABLE PreviouslyProcessed
(
FileName VARCHAR(MAX)
)
INSERT INTO PreviouslyProcessed
VALUES('Budapest_Ln.csv')
INSERT INTO #TEMP_FILES
EXEC master.dbo.xp_DirTree 'C:\Test\Houses',1,1
DECLARE @FILENAME VARCHAR(MAX),@SQL VARCHAR(MAX)
IF OBJECT_ID('TEMPDB..#TEMP_RESULTS') IS NOT NULL DROP TABLE #TEMP_RESULTS
CREATE TABLE #TEMP_RESULTS
(
[Address] VARCHAR(MAX),
[BedRoom] VARCHAR(MAX),
[Baths] VARCHAR(MAX),
[SquareFootage] VARCHAR(MAX),
[Garage] VARCHAR(MAX)
)
WHILE EXISTS(SELECT * FROM #TEMP_FILES WHERE FILENAME NOT IN(SELECT FILENAME FROM PreviouslyProcessed)) -- Evitar procesar archivos ya procesados
BEGIN
SET @FILENAME = (SELECT TOP 1 FileName FROM #TEMP_FILES WHERE FILENAME NOT IN(SELECT FILENAME FROM PreviouslyProcessed))
SET @SQL = 'BULK INSERT #TEMP_RESULTS
FROM ''C:\Test\Houses\' + @FILENAME +'''
WITH (FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'');'
PRINT @SQL
EXEC(@SQL)
DELETE FROM #TEMP_FILES WHERE FileName = @FILENAME
INSERT INTO dbo.PreviouslyProcessed
VALUES(@FILENAME)
END
Siguiendo estos pasos, puedes agregar eficientemente múltiples archivos CSV en una base de datos de SQL Server utilizando T-SQL. Este enfoque permite una fácil automatización y la adición de nuevos archivos a tus datos de producción.
Recuerda manejar cualquier problema potencial de datos utilizando bloques try-catch y considera convertir los tipos de datos para que coincidan con tu conjunto de datos. Además, crea un mecanismo para evitar reprocesar archivos que ya han sido procesados para garantizar la integridad de los datos.