none
ETL de un fichero Norma43 (Consejo Superior Bancario) RRS feed

  • Pregunta

  • Hola amigos.

    He creado un procedimiento que vuelca a una tabla el fichero normalizado de extractos bancarios (Norma 43). Funciona correctamente y además tiene el plus que nos añade una columna donde acumulamos el saldo bancario tras cada movimiento, cosa que el fichero normalizado sólo informa al principio (saldo inicial) y al final (saldo final).

    Sólo funciona con ficheros que lleven una única cuenta bancaria, es decir, si te bajas del banco un compendio de varias cuentas abiertas en dicha sucursal en el mismo fichero, no funcionará. No es demasiado habitual, pero me gustaría que si alguien quiere "perder" un poco de tiempo con esto, me completara el código.

    También estoy interesado en realizar esto mismo pero en Power BI. Si alguien puede remitirme algún recurso en la web donde informarme acerca de trabajar con ficheros no estructurados típicamente (delimitados), se lo agradecería un montón.

    Saludos.

    Jorge

    USE [DBMovimientos]
    GO
    
    /****** Object:  StoredProcedure [dbo].[Importacion_C43]    Script Date: 26/02/2018 17:32:52 *****
    Author: Jorge R.*/
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[Importacion_C43] AS
    BEGIN
    
    IF object_id('dbo.Conceptos_C') IS NOT NULL
    	BEGIN
    		DROP TABLE dbo.Conceptos_C 
    	END
    	CREATE TABLE dbo.Conceptos_C(
    
    	Id			varchar(2),
    	Descripcion	varchar(255)
    );
    
    INSERT INTO dbo.Conceptos_C (Id, Descripcion)
    VALUES
    ('01',' TALONES - REINTEGROS'),
    ('02',' ABONARÉS - ENTREGAS - INGRESOS'),
    ('03',' DOMICILIADOS - RECIBOS - LETRAS - PAGOS POR SU CTA.'),
    ('04',' GIROS - TRANSFERENCIAS - TRASPASOS - CHEQUES'),
    ('05',' AMORTIZACIONES PRÉSTAMOS, CRÉDITOS, ETC.'),
    ('06',' REMESAS EFECTOS'),
    ('07',' SUSCRIPCIONES - DIV. PASIVOS - CANJES.'),
    ('08',' DIV. CUPONES - PRIMA JUNTA - AMORTIZACIONES'),
    ('09',' OPERACIONES DE BOLSA Y/O COMPRA /VENTA VALORES'),
    ('10',' CHEQUES GASOLINA'),
    ('11',' CAJERO AUTOMÁTICO'),
    ('12',' TARJETAS DE CRÉDITO - TARJETAS DÉBITO'),
    ('13',' OPERACIONES EXTRANJERO'),
    ('14',' DEVOLUCIONES E IMPAGADOS'),
    ('15',' NÓMINAS - SEGUROS SOCIALES'),
    ('16',' TIMBRES - CORRETAJE - PÓLIZA'),
    ('17',' INTERESES - COMISIONES – CUSTODIA - GASTOS E IMPUESTOS'),
    ('98',' ANULACIONES - CORRECCIONES ASIENTO'),
    ('99',' VARIOS ');
    
    
    -- Importación del fichero de texto en NORMA43 a una tabla 'bruta'
    
    IF object_id('dbo.C43tmp') IS NOT NULL
    BEGIN
      DROP TABLE dbo.C43tmp
    END
    CREATE TABLE C43tmp(
    
    	campo VARCHAR(255) NOT NULL
    )
    BULK INSERT dbo.C43tmp
       FROM 'C:\Users\Jorge\Desktop\C43.txt' -- Especifica la ruta del fichero descargado del Banco
       WITH 
          (
             ROWTERMINATOR ='\n'
          )
    END
    
    -- Extracción/Transformación de las líneas 11 de CABECERA
    
    IF object_id('dbo.tmpCabecera_11') IS NOT NULL
    	BEGIN
    		DROP TABLE dbo.tmpCabecera_11
    	END
    	CREATE TABLE tmpCabecera_11(
    
    	Banco		varchar(4),
    	Sucursal	varchar(4),
    	Cuenta		varchar(10),
    	CC			varchar(20),
    	F_inicial	date,
    	F_Final		date,
    	Saldo_ini	float,
    	Divisa		varchar(3),
    	Modalidad_i	varchar(1),
    	Cliente		varchar(26),
    	S_Def		varchar(3)
    
    );
    
    INSERT INTO [dbo].[tmpCabecera_11](
    			 Banco,
    			 Sucursal,
    			 Cuenta,
    			 CC,
    			 F_inicial,
    			 F_Final,
    			 Saldo_ini,
    			 Divisa,
    			 Modalidad_i,
    			 Cliente,
    			 S_Def)
    
    SELECT 
    			SUBSTRING(campo,3,4),
    			SUBSTRING(campo,7,4),
    			SUBSTRING(campo,11,10),
    			SUBSTRING(campo,3,18),
    			CONVERT(DATETIME, SUBSTRING(campo,25,2)+'/'+SUBSTRING(campo,23,2)+'/'+SUBSTRING(campo,21,2)),
    			CONVERT(DATETIME, SUBSTRING(campo,31,2)+'/'+SUBSTRING(campo,29,2)+'/'+SUBSTRING(campo,27,2)),
    			CASE WHEN SUBSTRING(campo,33,1)='1' 
    				THEN (CONVERT(FLOAT, SUBSTRING(campo,34,14))/100)*-1 
    				ELSE (CONVERT(FLOAT, SUBSTRING(campo,34,14))/100)	
    			END,
    			SUBSTRING(campo,48,3),
    			SUBSTRING(campo,51,1),
    			SUBSTRING(campo,52,26),
    			SUBSTRING(campo,78,3)
    			
    FROM [dbo].[C43tmp]
    WHERE SUBSTRING(campo,1,2)='11'
    
    -- Extracción/Transformación de las líneas 23 de DETALLE
    
    IF object_id('dbo.tmpLineas_23') IS NOT NULL
    	BEGIN
    		DROP TABLE dbo.tmpLineas_23
    	END
    	CREATE TABLE tmpLineas_23(
    	
    	Id			int NOT NULL IDENTITY,	
    	Dato		varchar(2),
    	Concepto_1	varchar(38),
    	Concepto_2	varchar(38),
    	CC			varchar(20)
    );
    
    INSERT INTO [dbo].[tmpLineas_23](
    	Dato,
    	Concepto_1,
    	Concepto_2,
    	CC)
    
    SELECT 
    			
    			SUBSTRING(campo,3,2),			
    			LTRIM(RTRIM(SUBSTRING(campo,5,38))),
    			LTRIM(RTRIM(SUBSTRING(campo,43,38))),
    			(SELECT cc FROM tmpCabecera_11)
    			
    FROM [dbo].[C43tmp]
    WHERE SUBSTRING(campo,1,4)='2301'
    
    -- Extracción/Transformación de las líneas 22 de DETALLE (OBLIGATORIA)
    
    IF object_id('dbo.tmpLineas_22') IS NOT NULL
    	BEGIN
    		DROP TABLE dbo.tmpLineas_22
    	END
    	CREATE TABLE tmpLineas_22(
    
    	Id			int NOT NULL IDENTITY,
    	Origen		varchar(4),
    	Fecha_Op	date,
    	Orden		int,
    	Fecha_Va	date,
    	Concepto_C	varchar(2),
    	Concepto_P	varchar(3),
    	Concepto	varchar(77),
    	Importe		float,
    	Saldo		float,
    	Documento	varchar(10),
    	Ref_1		varchar(12),
    	Ref_2		varchar(16),
    	CC			varchar(20)
    );
    
    INSERT INTO [dbo].[tmpLineas_22](
    	Origen,
    	Fecha_Op,
    	Orden,
    	Fecha_Va,
    	Concepto_C,
    	Concepto_P,
    	Concepto,
    	Importe,
    	Saldo,
    	Documento,
    	Ref_1,
    	Ref_2,
    	CC)
    
    SELECT 
    	SUBSTRING(campo,7,4), -- Origen
    	CONVERT(DATETIME, SUBSTRING(campo,15,2)+'/'+SUBSTRING(campo,13,2)+'/'+SUBSTRING(campo,11,2)), -- Fecha OP
    	0, -- #
    	CONVERT(DATETIME, SUBSTRING(campo,21,2)+'/'+SUBSTRING(campo,19,2)+'/'+SUBSTRING(campo,17,2)), -- Fecha Valor			
    	SUBSTRING(campo,23,2), -- Id Concepto Común
    	SUBSTRING(campo,25,3), -- Id Concepto Propio
    	'', -- Descripción Movimiento
    	CASE WHEN SUBSTRING(campo,28,1)='1' -- Importe
    		THEN (CONVERT(FLOAT, SUBSTRING(campo,29,14))/100)*-1 
    		ELSE (CONVERT(FLOAT, SUBSTRING(campo,29,14))/100)	
    	END,
    	0, -- Saldo
    	SUBSTRING(campo,43,10), -- Documento
    	SUBSTRING(campo,53,12), -- Ref. 1
    	SUBSTRING(campo,65,16), -- Ref. 2
    	(SELECT cc FROM tmpCabecera_11)	-- Cuenta Cliente		
    FROM [dbo].[C43tmp]
    WHERE SUBSTRING(campo,1,4)='22';
    
    
    -- ASIGNAR EL SALDO INICIAL
    
    WITH R AS (
    	SELECT id, Importe, saldo, sum(Importe) OVER( ORDER BY id ROWS between UNBOUNDED PRECEDING AND CURRENT ROW) 
    			+ (SELECT saldo_ini FROM tmpCabecera_11) as acumulado			
    FROM tmpLineas_22)
    
    UPDATE R SET Saldo = acumulado;
    
    -- Actualizamos el campo ORDEN de la tabla tmpLineas_22 
    
    WITH R AS (
    SELECT id, fecha_op, orden, ROW_NUMBER() OVER(PARTITION BY fecha_op ORDER BY id) AS rn
    FROM tmpLineas_22)
    UPDATE R SET orden = rn;
    
    -- ENLAZAR LA DESCRIPCION DEL MOVIMIENTO QUE FIGURA EN tmpLineas_23
    
    UPDATE tmpLineas_22
    SET tmpLineas_22.Concepto = tmpLineas_23.Concepto_1 + ' ' +tmpLineas_23.Concepto_2
    FROM tmpLineas_22 
    INNER JOIN tmpLineas_23
    ON tmpLineas_22.id = tmpLineas_23.id;
    
    
    -- Extracción/Transformación de las líneas 33 de FIN
    
    IF object_id('dbo.[tmpImportarFin_33]') IS NOT NULL
    	BEGIN
    		DROP TABLE dbo.[tmpImportarFin_33]
    	END
    	CREATE TABLE [tmpImportarFin_33](
    
    	Banco		varchar(4),
    	Sucursal	varchar(4),
    	Cuenta		varchar(10),
    	Apuntes_D	int,
    	Suma_D		float,
    	Apuntes_H	int,
    	Suma_H		float,
    	Signo		varchar(1),
    	Saldo_fin	float,
    	Divisa		varchar(3),
    	S_Def		varchar(4)
    
    );
    
    INSERT INTO [dbo].[tmpImportarFin_33](
    
    			 Banco,
    			 Sucursal,
    			 Cuenta,
    			 Apuntes_D,
    			 Suma_D,
    			 Apuntes_H,
    			 Suma_H,
    			 Signo,
    			 Saldo_fin,
    			 Divisa,
    			 S_Def)
    
    SELECT 
    			SUBSTRING(campo,3,4),
    			SUBSTRING(campo,7,4),
    			SUBSTRING(campo,11,10),
    			CONVERT(INT, SUBSTRING(campo,21,5)),
    			CONVERT(FLOAT, SUBSTRING(campo,26,14))/100,
    			CONVERT(INT, SUBSTRING(campo,40,5)),
    			CONVERT(FLOAT, SUBSTRING(campo,45,14))/100,
    			SUBSTRING(campo,59,1),
    			CONVERT(FLOAT, SUBSTRING(campo,60,14))/100,
    			SUBSTRING(campo,74,3),
    			SUBSTRING(campo,77,4)
    			
    FROM [dbo].[C43tmp]
    WHERE SUBSTRING(campo,1,2)='33'
    

    lunes, 26 de febrero de 2018 16:50

Respuestas

  • Hola R_Jorge:

    Otra opción.

    Desde que insertas con el bulk copy el origen de datos.(luego le das formato a lo que te pongo y le das una vuelta si te vale)

    CREATE TABLE C43TMPCONROWS (PKEY INT IDENTITY(1,1), CAMPO VARCHAR(255)); /*
    una tabla con su identificador
    */
    INSERT INTO C43TMPCONROWS SELECT * FROM C43tmp
    //* LE PONES EL CONTENIDO DE C43TMP*/ 
    SELECT MIN (PKEY) AS PKEY, CAMPO FROM C43TMPCONROWS WHERE SUBSTRING(campo,1,2)='11'
    GROUP BY SUBSTRING(campo,1,2),CAMPO
    ORDER BY PKEY
    /*AQUI TIENES TODAS LAS FILAS PARA INSERTAR EN LAS CABECERAS, PERO LA COLUMNA IDENTIFICADOR, TE DA LA OPCIÓN
    DE FILTRAR LAS LINEAS, EN EL RANGO PORQUE ENTRE PKEY 1 Y 5 EN EL EJEMPLO ESTA EL DETALLE DE LAS LINEAS CORRESPONDIENTES A LA EMPRESA 1
    TODA LA OPERATIVA QUE HACES A POSTERIOR LA PUEDES EJECUTAR CON UN CURSOR....*/

    Un saludo


    • Marcado como respuesta R_Jorge jueves, 1 de marzo de 2018 19:19
    miércoles, 28 de febrero de 2018 21:00

Todas las respuestas

  • Hola R_Jorge:

    Evidentemente, echarte un cable sin tener la información, es complicado, pero a vista de tu procedure, te diré lo que yo haría.

    Separar.

    Porque no haces un procedure que te carga el archivo, y en un bucle, por cada cuenta bancaria que traiga el fichero, llamas al segundo procedure (que sería el que expones sin el tema de la tabla C43tmp, que podrías montar en el bucle exterior y eliminar de igual modo), que es el que hace toda la logica, pasándole, como parametro la cuenta sobre la que operar.

    Dentro del procedure dos, podrías poner algunos try catch porsi algo ocurriera, tener cuenta de ello en el exterior, e incluso, pasarle un parametro int como out, para saber que es lo que ha ido ocurriendo (incrementando este parametro interior, según vaya superando fases).

    ¿Que te parece?

    Un saludo

    martes, 27 de febrero de 2018 6:56
  • Hola R_Jorge:

    Evidentemente, echarte un cable sin tener la información, es complicado, pero a vista de tu procedure, te diré lo que yo haría.

    Separar.

    Porque no haces un procedure que te carga el archivo, y en un bucle, por cada cuenta bancaria que traiga el fichero, llamas al segundo procedure (que sería el que expones sin el tema de la tabla C43tmp, que podrías montar en el bucle exterior y eliminar de igual modo), que es el que hace toda la logica, pasándole, como parametro la cuenta sobre la que operar.

    Dentro del procedure dos, podrías poner algunos try catch porsi algo ocurriera, tener cuenta de ello en el exterior, e incluso, pasarle un parametro int como out, para saber que es lo que ha ido ocurriendo (incrementando este parametro interior, según vaya superando fases).

    ¿Que te parece?

    Un saludo

    Muchas gracias, Javi.

    Yo había pensado en hacerlo un poco más fácil, detectando cuántas cuentas distintas hay separando el fichero .txt en bloques y tratando cada uno de ellos con el mismo código que ya tengo escrito. Le daré unas vueltas.

    La estructura del fichero es tal que esta, por si a alguien le da alguna idea.

    Identificador 11 (Cabecera de cuenta)

    Identificador 22 (Registro principal de movimientos)

    Identificador 23 (Registro complementario de movimientos -suele ser continuacion de líneas del anterior-)

    Identificador 33 (Registro de final de cuenta)

    Identificador 88 (Registro de final de fichero)

    miércoles, 28 de febrero de 2018 11:56
  • Hola R_Jorge:

    Otra opción.

    Desde que insertas con el bulk copy el origen de datos.(luego le das formato a lo que te pongo y le das una vuelta si te vale)

    CREATE TABLE C43TMPCONROWS (PKEY INT IDENTITY(1,1), CAMPO VARCHAR(255)); /*
    una tabla con su identificador
    */
    INSERT INTO C43TMPCONROWS SELECT * FROM C43tmp
    //* LE PONES EL CONTENIDO DE C43TMP*/ 
    SELECT MIN (PKEY) AS PKEY, CAMPO FROM C43TMPCONROWS WHERE SUBSTRING(campo,1,2)='11'
    GROUP BY SUBSTRING(campo,1,2),CAMPO
    ORDER BY PKEY
    /*AQUI TIENES TODAS LAS FILAS PARA INSERTAR EN LAS CABECERAS, PERO LA COLUMNA IDENTIFICADOR, TE DA LA OPCIÓN
    DE FILTRAR LAS LINEAS, EN EL RANGO PORQUE ENTRE PKEY 1 Y 5 EN EL EJEMPLO ESTA EL DETALLE DE LAS LINEAS CORRESPONDIENTES A LA EMPRESA 1
    TODA LA OPERATIVA QUE HACES A POSTERIOR LA PUEDES EJECUTAR CON UN CURSOR....*/

    Un saludo


    • Marcado como respuesta R_Jorge jueves, 1 de marzo de 2018 19:19
    miércoles, 28 de febrero de 2018 21:00
  • Gracias! estoy dándole vueltas con algún matiz. En cuanto lo eche a andar, cuelgo el resultado.
    jueves, 1 de marzo de 2018 19:19
  • Bueno, pues finalmente parece que funciona. Eché mano de la primera opción que expusiste. Aunque he de decir que con los ficheros de un banco en concreto no tira, y eso que supuestamente son todos normalizados. PD. Acabo de reparar en lo de "EcoSoft", vaya casualidad, creo que hemos hablado hace unas semanas (jlarenas.com). Saludos!
    lunes, 5 de marzo de 2018 9:55
  • Hola Jorge:

    Que pequeño es el mundo.....

    Me alegra mucho, que te haya servido para algo alguna opción.

    Es posible, que el problema del banco (que seguro que son unos taraos....pero...), realmente sea un problema de codificación del archivo en base al origen de donde extraes los datos. Alguna vez he visto, que los archivos, vienen con tres carácteres hexadecimales, por delante (si mal no recuerdo cuando se recibian desde un mail...tampoco me acuerdo cual, ni versión...pero es un planteamiento, si te hace la puñeta). Si este planteamiento es correcto, te descoloca la entrada de datos, puesto que las posiciones, no son las que deben. Y si lo abres con el bloc de notas, no lo aprecias.

    Por si te vale, un ejemplo simple para hacer esta prueba.

    https://techlandia.com/utilizar-editor-hexadecimal-como_281319/

    Un saludo

    lunes, 5 de marzo de 2018 11:44