How to scan Excel files with the database scanner

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

Java 1.7 or older since ODBC bridge was removed from Java 1.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.

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.