Manejando excel desde Acces. Manejo de datos con SQL.

 Desde que me prejubilé no tocaba este tema. Hace casi 20 años. Mas que para difundir este tipo de trabajos es para que yo mismo los recuerde.

Se trata de manejar unos datos que tengo en un libro excel con  SQL. En principio tengo un excel con los movimientos de una cuenta bancaria de los últimos años. Me interesa, a partir de esos datos en excel, obtener un informe tipo:

GastosMensuales

campo1	aaaa	Enero	Febrero	Marzo	Abril	Mayo	Junio	Julio	Agosto	Septiembre	Octubre	Noviembre	Diciembre	total	Num
Bizum	2021	0	0	0	0	0	0	0	0	0	0	-62	32,95	-29,05	5
Bizum	2022	-48	-65,5	-17,2	-1,5	-26	0	0	-42,7	0	0	-52	10	-242,9	27
Bizum	2023	-145	0	25	0	5,51	-399	18	-21,15	-15	-24	99	-44	-500,64	18

 A partir de unos datos, en excel, del tipo:

Consulta2

Fecha Operación	Fecha Valor	Importe	Concepto
07/01/2026	07/01/2026	-21,31	Recibo Naturgy Clientes, S.a.u. Naturgy Clientes, Factura: Fe25390043657535 , Contrato: xxx Nº Recibo xxx Bbjyywx Ref. Mandato xxx, De
05/01/2026	05/01/2026	250,00	Transferencia , Concepto Gastos Mensuales Conjunta.

Primer paso: Vincular la los datos excel a una B.D. Acces

• Bien en una B.D. nueva, creada para este propósito, bien en una B.D. existente vinculamos el excel.
• Abrimos la B.D., buscamos "Datos Externos" Excel.
• Buscamos nuestro archivo excel.
• Seleccionamos el último item,"Vincualar al origen de datos creando una tabla vinculada"
• La siguiente pantalla nos da a elegir la hoja excel que tiene nuestros datos. Seleccionamos la indicada.
• La tercera pantalla nos permite decir que la primera línea son los nombres de los distintos campos que forman nuestros datos.
• La última pantalla nos pide el nombre que le vamos a dar a la tabla vinculada.

Ya podemos trabajar la hoja excel con acces.

Segundo paso: Adecuar esos datos a nuestras necesidades, de manera que los siguientes pasos sean lo mas cómodos posibles.

• En este caso necesito, por un lado, acortar los nombres de los campos, haciendo desaparecer espacios, acentos y otros caracteres "molestos" a la hora de prepara consultas y por otro lado separar la fecha en año, mes y día (sin descartar la fecha)
• Filtrar los datos para eliminar algunos registros que no son propiamente datos.

Primera consulta (SQL): Consulta Paso1

SELECT [fecha operación] AS FOp, year( [fecha operación]) AS AAAA, month( [fecha operación]) AS MM, day( [fecha operación]) AS DD, val([fecha operación] & " ") AS VV, [fecha valor] AS FV, Importe, concepto

 FROM  Gastos

 WHERE Val(([Fecha Operación]) & " ")>0 ORDER BY [fecha operación]   

Con este paso, a partir de esta consulta, [fecha operación] pasa a ser FOp, [fecha valor] pasa FV, mucho mas sencillos de manejar en consultas sucesivas. Además obtenemos año, mes y día de la fecha de operación.

Después de varios intentos fallidos (si no hay nada en fecha operación da un error, por lo que hay que añadir un espacio) el filtro de registros no validos lo hago con condición Val(([Fecha Operación]) & " ")>0

Segunda consulta: Necesito incorporar 12 columnas, una por mes, para separar el importe por meses. Para ello utilizo la función iif().

SELECT AAAA, MM, DD, FOp, VV, FV,

 iif(MM=1,Importe,0) AS M1, iif(MM=2,Importe,0) AS M2, iif(MM=3,Importe,0) AS M3, iif(MM=4,Importe,0) AS M4, iif(MM=5,Importe,0) AS M5, iif(MM=6,Importe,0) AS M6, iif(MM=7,Importe,0) AS M7, iif(MM=8,Importe,0) AS M8, iif(MM=9,Importe,0) AS M9, iif(MM=10,Importe,0) AS M10, iif(MM=11,Importe,0) AS M11, iif(MM=12,Importe,0) AS M12,

 1 AS N, *

FROM paso1

Incorporo N como para establecer un contador, si fuese preciso.

Estas dos consultas SQL se pueden hacer en un solo paso, pero, como es lógico, queda una consulta mucho mas compleja. Es una consulta concatenada con otra en SQL:

SELECT AAAA, MM, DD, FOp, VV, FV, iif(MM=1,Importe,0) AS M1, iif(MM=2,Importe,0) AS M2, iif(MM=3,Importe,0) AS M3, iif(MM=4,Importe,0) AS M4, iif(MM=5,Importe,0) AS M5, iif(MM=6,Importe,0) AS M6, iif(MM=7,Importe,0) AS M7, iif(MM=8,Importe,0) AS M8, iif(MM=9,Importe,0) AS M9, iif(MM=10,Importe,0) AS M10, iif(MM=11,Importe,0) AS M11, iif(MM=12,Importe,0) AS M12, 1 AS N, *

FROM

(SELECT [fecha operación] AS FOp, year( [fecha operación]) AS AAAA, month( [fecha operación]) AS MM, day( [fecha operación]) AS DD, val([fecha operación] & " ") AS VV, [fecha valor] AS FV, Importe, concepto FROM Gastos WHERE Val(([Fecha Operación]) & " ")>0 ORDER BY [fecha operación])  

Tercera consulta: Ahora nos interesa encontrar dentro de "concepto" una serie de literales que nos faciliten identificar el movimiento bancario.

• Creamos una tabla con los literales buscados, y su significado. La dejo abierta por si hay que modificar la busqueda de esos literales dentro de concepto. Campo1 es el literal buscado, campo2 es el literal que va a aparecer en el informe y campo3 es por si necesito modificar la busqueda (podría incorporar mas campos si fuese necesario)

Tabla1

Id	Campo1	Campo2	Campo3
9	Lit1	SPG
8	958	PR15
20	013	Luz Madrid
10	117	Luz CM
19	2005	Gas 23
13	30973	Gas

SELECT Campo1, Campo2, Paso2.*, instr(Concepto,campo1) AS P

FROM Tabla1, Paso2 where instr(Concepto,campo1)>0

Esta sentencia SQL multiplica matricialmente todos los registro de la tabla1 por los registros resultantes del paso2. Con  instr(Concepto,campo1)>0 elimina todos los registros que no contienen el valor de campo1 en el concepto.

En el siguiente paso acumulamos los distintos importes de cada columna de mes, por año y concepto y ya tendríamos nuestro informe de acumulados mes.

SELECT campo1, aaaa, Sum(m1) AS Enero, Sum(m2) AS Febrero, Sum(m3) AS Marzo, Sum(m4) AS Abril, Sum(m5) AS Mayo, Sum(m6) AS Junio, Sum(m7) AS Julio, Sum(m8) AS Agosto, Sum(m9) AS Septiembre, Sum(m10) AS Octubre, Sum(m11) AS Noviembre, Sum(m12) AS Diciembre, Sum(Importe) AS total, Sum(n) AS Num

FROM paso3

GROUP BY campo1,AAAA

ORDER BY aaaa, campo1;

Paso4

campo1	campo2	aaaa	Enero	Febrero	Marzo	Abril	Mayo	Junio	Julio	Agosto	Septiembre	Octubre	Noviembre	Diciembre	total	Num
diputacion	Diputación	2013	0	0	0	0	0	-71,94	0	0	0	-207,66	0	0	-279,6	2
Gas Natural		2013	-31,21	0	-47,23	-67,04	0	-56,02	0	10,23	0	0	-81,01	-71,1	-343,38	9
impuesto	Impuestos	2013	0	0	0	0	0	-71,94	0	0	0	-207,66	0	0	-279,6	2
liquidacion		2013	0	0	0	1,28	0	0	0	0	0	3,44	0	0	4,72	2
San Pedro		2013	0	-30	0	0	0	0	0	0	0	0	0	0	-30	2