This article covers some hints that might be useful when configuring the migration-center database scanner for scanning Excel files.
Requirements
MS Office is not necessarily to be installed on the Jobserver machine but at least the Excel ODBC driver needs to be installed. One of the easiest ways to install the Excel ODBC driver is to download and install the Microsoft Access Database Engine 2010 Redistributable that includes the necessary drivers: http://www.microsoft.com/en-us/download/confirmation.aspx?id=13255
For running with Java 8 please see: How to enable JDBC-ODBC bridge for Java 8?
Scanner parameter configurations
The connectionURL parameter should be like that:
jdbc:odbc:DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=PATH-TO-EXCEL-FILE;ReadOnly=1
Note: All extensions (*.xls, *.xlsx, *.xlsm, *.xlsb) are required.
The driverClass parameter should be:
oracle.jdbc.OracleDriver or sun.jdbc.odbc.JdbcOdbcDriver
Query File configuration
The sheet selection should be written as [sheetName$] and the columns specified in quotes "myColumn". Like in the following example:
select distinct "VER_ID", "OBJ_ID" from [Sheet1$]
Common errors and solutions:
- The job could not be executed! ERROR: de.fme.mc.common.JobConfigException: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory ([Microsoft][ODBC Excel Driver] Your network access was interrupted. To continue, close the database, and then open it again.)
To resolve this error please run the jobserver service with a local user which was used to install the ODBC drivers.
Comments
0 comments
Please sign in to leave a comment.