Importar fechas de Excel con ODBC

Velneo
1

Hola.

No sé si alguno se ha encontrado con el mismo problema, o es que yo estoy haciendo algún paso mal (que sería lo más lógico).
El caso es que estoy haciendo una importación desde Excel con ODBC a través de la integración de AccessDatabaseEngine y no consigo importar los campos Fecha.
El dato de Fecha lo he intentado importar tanto en formato fecha, como número como texto, y de ninguna de las maneras consigo recibir este campo.

El proceso que uso es:

BD: Crear manejador ( hExcel, ODBC (compatible Microsoft SQL Server), “Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; DBQ=”+RUTA_ARCHIVO, , , )
BD: Conectar ( hExcel, , , OK_CONEXION, OK_MENSAJE )
BD: SQL ( hExcel, “SELECT * FROM \c”+NOMBRE_TABLA+"$"+INICIO_RANGO+":"+FIN_RANGO+"\c", ODBC_NUM_FILAS, )
BD: Recorrer lista ( hExcel )
BD: Get dato de columna por nombre ( hExcel, “F2”, FECHA )
BD: Get dato de columna por nombre ( hExcel, “F5”, NOMBRE )
Mensaje ( "Fecha: “+FECHA+”\nNombre: "+NOMBRE, Información, 2, )

De la forma expuesta, ni siquiera siendo la variable FECHA del tipo alfanumérico me devuelve datos. También he probado a formatear el dato recibido: Mensaje ( "Fecha: “+stringToDate(FECHA, “dd/mm/yyyy”)+”\nNombre: "+NOMBRE, Información, 2, ), pero ni por esas.

Sospecho que el problema no está con velneo, ya que el resto de datos sí que los recoge correctamente, o sea que debe ser un problema de la conexión intermedia, o sea, del ODBC.

Como aclaración:
Tanto el servidor como el desarrollo como el Excel están en el mismo equipo, teniendo este Windows 10 con Office 2013 y AccessDataEngine 2010

¿Alguna sugerencia? ¿Alguna otra forma de realizar la importación? No puedo tratar en CSV ni en TXT ya que se trata de libros que me envía el cliente y necesito que sea un proceso desasistido, es decir, la menor manipulación por parte del cliente o mía. El libro que me llega es exportado por diferentes programas de contabilidad y el nexo común, sin tener que obligarles a modificar sus sistemas, es la exportación a Excel.

2

Hola gAb.

Yo creo que sí es culpa de Velneo, que en algunas funciones está poco depurado y nos puede gastar bromas pesadas.

  • Fíjate si las fechas leídas por el driver ODBC son de la forma dd/MM/yyyy h:mm → por ejemplo: “23/10/2016 0:00”
  • Si es así utiliza la conversión stringToDateTime(#FECHA, “dd/MM/yyyy h:mm”)
  • Fíjate si la hora está en formato de h o hh

Saludos
Paco Satué

3

Hola Paco, disculpa la demora.

A falta de hacer pruebas con alguna otra aplicación que acceda a Excel con ODBC y verificarlo, en principio coincido contigo ya que a través de VBS he hecho prueba de conexión y si me retorna el dato fecha.

Pero al margen, y volviendo a Velneo, lo que me extraña es que, ya o digamos recoger el dato en un campo, que puede tener un formato determinado y rechazarlo si no coincide. El caso es que estoy intentando mostrarlo en un cuadro de mensaje y tampoco muestra nada. Ahora, no sé donde puede perderse este dato, si al mostrar el mensaje en el cuadro de texto o en la variable que lo recoge.

Para recogerlo he probado con:
FECHA -> Alfabético
FECHA -> Numérico
FECHA -> Fecha
FECHA -> Tiempo (este ya por ver…)

Para mostrarlo he probado con:
Mensaje(“Fecha: " + FECHA, Información,2,)
Mensaje(“Fecha: " + stringToDate(FECHA, “dd/mm/yyyy”), Información,2,)
Mensaje(“Fecha: " + stringToDateTime(FECHA, “dd/mm/yyyy”), Información,2,) //(Este siguiendo tu recomendación)
Mensaje(“Fecha: " + day(FECHA)+”/”+month(FECHA)+”/”+year(FECHA), Información,2,)

Esto hecho para cada uno de los tipos de variable y en ningún caso visualizo datos.

Pero después de estas pruebas, aún estoy más desconcertado. Hemos dicho que los datos en formato FECHA no los estamos recogiendo. Pero es que voy a Excel, meto datos de cualquier otro tipo (y les cambio el formato, lógicamente) y Velneo sigue sin recoger nada. Únicamente me muestra datos cuando TODA la columna de la que estoy extrayendo NO es del tipo FECHA, es decir, que como haya una sola celda con formato FECHA, ya no me recoge nada.

He leído que ODBC analiza los primeros 8 registros y en función de lo que encuentre en ellos, así será el formato de lo que devuelva, pero he ido cambiando el formato celda por celda y ni 8 ni con 18

He leído también sobre el DriverId, y he probado a ir cambiándolo, al igual que FIL, incluso he exportado el excel con formato 97, pero tampoco.
Según he leído, las variaciones de FIL y DriverId van en función del proveedor de datos y el Excel, son estas:

Rem ( DriverId para versiones de excel 95/97 = 790, para versiones de excel 2007 = 278, para versiones de excel 2013 = 1046 )
Rem ( FIL=Excel 5,0 para excel 95, FIL=Excel 8.0 para excel 2000/XP/2003/2007, FIL=Excel 12.0 para excel 2007/2013 )
Rem ( HDR Yes/No para decir si la primera fila son encabezados o no )

Ahora, otra duda, el AccessDatabaseEngine es 2010 y el Office es 2013, ¿Habrá alguna incompatibilidad ahí?

Saludos
Gabriel

4

Hola Gabriel.

Yo tengo AccessDatabaseEngine 2010 (14.0.715) con Office 2016 y sin problemas. No creo que sea tema de versiones.

No uses el comando “BD: Get dato de columna por nombre” , usa “BD: Get dato de columna por número” por si el nombre de columna no existe.

Olvídate de los tipos de datos. Yo lo importo todo en variables locales de tipo Alfa y luego hago la conversión.

Asegúrate que realmente no estás recibiendo nada en la columna de la Fecha. No te vale de nada los comandos Mensaje(“Fecha: ” + stringToDate(FECHA …) si la variable FECHA está en blanco.

Puedes adjuntarme un ejemplo de la Hoja excel que estás importando y hago pruebas.

Saludos
Paco Satué

5

Hola Paco,

Efectivamente, hay que tener cuidado con los nombres y los números de columnas, ya que el proveedor de datos únicamente nos devuelve el rango útil de la hoja, así que por ejemplo la columna C real de la hoja se deberá llamar B al pedir los datos para el caso de que la columna A no contenga datos. Pero creo que ese punto lo estoy controlando bien, ya que sí recibo el resto de datos en su orden correspondiente.

Te adjunto el proceso tal y como lo tengo ahora mismo para las pruebas que estoy haciendo:

Cargar lista ( DATOSLIBROS@ConversorExcel_Dat, ID, , , , )
Recorrer lista eliminando fichas
Libre
Set ( NOMBRE_TABLA, “Facturación” )
Set ( INICIO_RANGO, “B” )
Set ( FIN_RANGO, “T” )
Set ( RUTA_LIBRO, “C:/Users/root/Desktop/DatosReales.xlsx” )
Rem ( DriverId para versiones de excel 95/97 = 790, para versiones de excel 2007 = 278, para versiones de excel 2013 = 1046 )
Rem ( FI=Excel 5,0 para excel 95, =Excel 8.0 para excel 2000/XP/2003/2007, =Excel 12.0 para excel 2007/2013 )
Rem ( HDR Yes/No para decir si la primera fila son encabezados o no )
// BD: Crear manejador ( hExcel, ODBC (compatible Microsoft SQL Server), “Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, .xlsb)}; DBQ="+RUTA_LIBRO, , , )
BD: Crear manejador ( hExcel, ODBC (compatible Microsoft SQL Server), "Driver={Microsoft Excel Driver (.xls, *.xlsx, *.xlsm, *.xlsb)};DriverId=1046;FIL=Excel 8.0;HDR=No;Dbq=”+RUTA_LIBRO, , , )
BD: Conectar ( hExcel, , , OK_CONEXION, OK_MENSAJE )
BD: SQL ( hExcel, “SELECT * FROM [”+NOMBRE_TABLA+"$"+INICIO_RANGO+":"+FIN_RANGO+"]", ODBC_NUM_FILAS, )
BD: Recorrer lista ( hExcel )
BD: Get dato de columna por nombre ( hExcel, “F2”, FECHA1 )
BD: Get dato de columna por nombre ( hExcel, “F2”, FECHA2 )
BD: Get dato de columna por nombre ( hExcel, “F2”, FECHA )
BD: Get dato de columna por nombre ( hExcel, “F2”, FECHA3 )
BD: Get dato de columna por nombre ( hExcel, “F5”, NOMBRE )
BD: Get dato de columna por nombre ( hExcel, “F11”, DESCRIPCION )
BD: Get dato de columna por nombre ( hExcel, “F12”, CUENTA )
BD: Get dato de columna por nombre ( hExcel, “F19”, SALDO )
Crear nueva ficha en memoria ( NuevaFicha, DATOSLIBROS@ConversorExcel_Dat )
Modificar campo ( FECHA, “Fecha1: “+FECHA1+”, Fecha2: “+FECHA2+”, Fecha3:”+FECHA3+", Fecha: “+FECHA+” - “+“Fecha1f: “+stringToDate(FECHA1, “dd/mm/yyyy”)+”, Fecha2f:”+stringToDate(FECHA2, “dd/mm/yyyy”)+”, Fecha3f: “+stringToDate(FECHA3, “dd/mm/yyyy”)+”,Fechaf: “+stringToDate(FECHA, “dd/mm/yyyy”)+” - “+“Fecha1fd: “+stringToDateTime(FECHA1, “dd/mm/yyyy”)+”, Fecha2fd: “+stringToDateTime(FECHA2, “dd/mm/yyyy”)+”, Fecha3fd:”+stringToDateTime(FECHA3, “dd/mm/yyyy”)+”, Fechafd: “+stringToDateTime(FECHA, “dd/mm/yyyy”) )
Modificar campo ( NOMBRE, NOMBRE )
Modificar campo ( DESCRIPCION, DESCRIPCION )
Modificar campo ( CUENTA, CUENTA )
Modificar campo ( SALDO, SALDO )
// Mensaje ( “Fecha1: “+FECHA1+”\nFecha2: “+FECHA2+”\nFecha3: “+FECHA3+”\n\n”+“Fecha: “+FECHA+”\nFecha Conv: “+stringToDate(FECHA,“dd/mm/yyyy”)+”\nFecha Form:”+day(FECHA)+”/"+month(FECHA)+"/"+year(FECHA)+"\nNombre: “+NOMBRE+”\nDescripción:"+DESCRIPCION+"\nCuenta: “+CUENTA+”\nSaldo: "+SALDO, Información, 2, )
Alta de ficha ( NuevaFicha )
Libre
Emitir sonido
Mensaje ( “Importación Finalizada”, Información, 1, )

Esta no es la importación real, simplemente es para las pruebas, lógicamente luego el campo FECHA no contendrá toda la parafernalia. Para probar, FECHA es Alfabético, FECHA1 es numérico, FECHA2 dos es Fecha y FECHA3 es Tiempo.

El tipo del campo actual donde recojo la acumulación de los datos FECHA es Alfanumérico 256, aunque en mensaje tampoco recibe nada.

Adjunto el archivo Excel, y te explico un poco: como los datos pueden legar de varios orígenes, y cada uno exportado desde un programa de contabilidad diferente, puede venir con formatos, saltos de línea, espacios en blanco, etc…(lo puedes ver en el excel adjunto) y se debe hacer un proceso desasistido. Todo esto lo digo porque no es viable tomar los Excel y ajustar los datos para que le lleguen limpios a Velneo, sino que, de cada “origen/cliente/programa de contabilidad” se toma una muestra del formato de exportación, se define que hojas, columnas y datos van a servir y luego es usa la misma lógica con todos los archivos que procedan del mismo “origen/cliente/programa de contabilidad”. Tampoco se le puede decir al origen que cambie su programa y su forma de exportar para que se adapte a nosotros.

Es por eso que se debe importar la fecha con ese formato, si no, tomaría cada uno, convertiría las fechas a número y listo. (tampoco se podrá automatizar esto último sin visualizar los datos y decidir que es fecha y que es número, porque en la misma columna de fechas también pueden llegar datos de texto y número)

DatosReales.xlsx (16 KB)

6

Aquí de nuevo,

A medida que estaba copiando el proceso me di cuenta que DriverId lo tenía en 1046, y FIL en 8.0, así que puse FIL=12.0 y ya recibí datos. (¿Por qué si 12.0 es para Excel 2007/2013 y 8.0 es para Excel 2000/xp/2003/2007?)

Así que finalmente era un error de sintaxis.

Seguiré probando, porque ahora estoy con un excel 2007, pero como decía, también puede llegarme 97 e incluso 95, y lógicamente en un futuro 2013 y 2016, así que tendré que afinar bien con esa configuración de DriverId y FIL.

En principio, tema solucionado.
Muchas gracias por guiarme.

Saludos

7

Hola Gabriel.

Me alegro que haya funcionado. Conocía el valor DriverId=790 para Excel (XLS) 97-2003 y DriverId=1046 para Excel (XLSX) 2007-2016. El valor FIL no lo he usado nunca que yo recuerde.

De todas formas revisa el uso que haces de las funciones stringToDate y stringToDateTime porque no es correcto. La expresión dd/mm/yyy debe ser dd/MM/yyyy porque “mm” se usa para los minutos. En stringToDateTime debes especificar hh:mm o h:mm según el caso.

Haz pruebas en el editor de fórmulas y verás que Velneo en muchos casos devuelve vacío aunque la FECHA o FECHA_HORA tenga un dato válido. Hay que tener mucho cuidado con este tema y Velneo debería tomar nota.

Este caso que tú tienes es bastante problemático y teniendo en cuenta que son datos contables se complica mucho más, porque no es admisible ningún error en el proceso de importación.

Saludos
Paco Satué

8

Hola,

Esa estructura es porque la copié de los VBScript que tengo, con los que accedo a Excel, y ya que el proveedor de datos es el mismo, suponía que la estructura debía ser igual. Ya veo que no.
Ahora que conseguí que devuelva datos, iré jugando con esos parámetros para ver hasta donde se puede reducir.

Ya corregí la estructura de fechas, no me había dado cuenta de la confusión meses-minutos.

Haciendo pruebas veo que sí recojo los datos de fecha en variables numéricas, fecha, alfabética y tiempo. Que el formato stringToDate y stringToDateTime sólo se le puede aplicar a las de tipo fecha (lógico si vemos el dato que contiene cada una (numérico = 1456790400, fecha=01/03/2016, tiempo=01/03/2016 0:00, y alfabético=01/03/2016 0:00)). Aquí surge otra duda, si fecha y alfabético me muestran el mismo dato, ¿por qué no le puedo aplicar stringToDate?. Esto no tiene mayor inconveniente, ya que sí puedo tratar el dato texto, buscar los separadores “/” y construir la salida. Aunque eso conlleva más código y más proceso, es decir, más tiempo y para pocos datos no importa, pero para archivos de 30000 registros en adelante, cada milésima cuenta.

Bueno, de momento ya consigo importar los datos, ahora tendré que hacer una prueba con cada tipo de archivo (95, 97, 2003, 2007, 2010…) y anotar la estructura que tiene que tener la cadena de conexión para cada caso.
Aunque, como tú bien dices, en este tipo de importación no es admisible ningún error ya que un campo no válido me eliminaría una línea entera, y por tanto la suma final. Así que lo siguiente será hacer un libro con todas las hipótesis de formatos que me puedan llegar para estar seguro de que se van a importar sin problema (por ejemplo los datos pueden llegar en formato de fecha corta “##/##/####” que es lo más lógico, pero también como fecha larga “DíaSemana,## de mes de ####”, o “#-#-##” o “##-##-##” o “##-mes-##” o “## de mes de ####” o “##-#-## ##:##” o algún otro más que Excel admite como fecha y que el usuario puede poner). Aunque para esto si se podría crear un script que cambie el formato de toda la columna a tipo Fecha Corta, porque lo que si que no varía, o no debe variar es la forma de cada libro de cada cliente ya que siempre lo lanza con el mismo programa de gestión (se supone :-)), pero claro, ya volvemos al handicap de terceras aplicaciones para el tratamiento de los datos.

Una pregunta, ¿Como sería hacerlo con JS desde Velneo, usaría de igual modo el proveedor ODBC o habría otra forma de acceder?, ¿Sabes si Velneo dispone de alguna otra aplicación nativa, utilidad o procedimiento que no use productos de terceros o dll, como v2excel.dll por ejemplo?

9

Hola Gabriel.

Si es posible, lo mejor es que recojas la fecha en Velneo siempre como un string y hagas la transformación con stringToDate().
Te vuelvo a recordar que hagas pruebas en Velneo con las funciones stringToDate() y stringToDateTime() con todas las posibilidades de formato de entrada porque Velneo es muy estricto y cualquier variación entregará una fecha nula aunque sea válida.

Lo que no estoy seguro ahora es si el ODBC siempre pasa la fecha como un dato DateTime independientemente del formato que haya utilizado el Usuario para formatear esa fecha.

En el API de Velneo existe la clase VSqlDataBase con la misma funcionalidad que los comandos nativos de Velneo, aunque yo creo que en este caso no aporta ninguna ventaja.

En cuanto a aplicaciones externas, olvídate, el ODBC de momento es el que te va a dar más efectividad. Estás abriendo directamente el fichero xls/xlsx y haciendo consultas SQL, más no se puede pedir.

Saludos
Paco Satué

10

Hola Paco,

Seguiré tu consejo de probar todas las posibles variantes para asegurar los dato. No quedará más remedio que incluir en la aplicación la selección del tipo de archivo Excel para que en función a su tipo se aplique una cadena de conexión u otra, pero bueno, eso es un problema menor, puesto que como hay que crear una ficha para cada tipo de origen con los formatos, ubicaciones de los datos, etc, un campo más no es lo que más importa, lo importante es que una vez se haya decidido la estructura se pueda, con el resto de libros que provengan del mismo origen, o sea, con la misma estructura, tratarlos de forma automática.

Gracias

Un Saludo
Gabriel