ODI Snippets: Oracle Data Integrator, Excel data types, and limitations of the MS Excel ODBC driver.

Uli Bethke Oracle Data Integrator (ODI)

I have come across two serious issues when loading data from Excel into Oracle Data Integrator. These are not related to ODI, but are shortcomings of the Microsoft Excel ODBC driver. Both issues are explained on Metalink:

(1) Excel Returns NULL Values When Numeric And Alphanumeric Datatypes Are Intermixed Within The Same Column (424419.1)

Extract from Metalink

"Having defined a Datastore based on a Microsoft Excel spreadsheet. One of the columns in the Datastore contains both numeric and NULL values.
In the Datastore definition, the column datatype has been set to NUMERIC, which is correct.
However, when consulting the Datastore content from Oracle Data Integrator Designer, all numeric values are returned as Null, while the underlying value is not a Null (for example 11,325).
If changing the column datatype in the Datastore definition to VARCHAR, all alphanumeric values will be now returned as Null, while the underlying value is not a Null (for example, AZERTY)."

(2) Issues loading numeric data types (424471.1)

Extract from Metalink

"After successfully setting up an ODI Source Datastore on an Excel file named zone, it appears that only a subset of a numeric column is loaded to the Target Datastore. When consulting the original Excel file, certain cells of the numeric field are indicated with a small triangle in the upper left corner."

For the above issues I propose to use Excel macros that are triggerd when the user saves the Excel sheet as an automated solution.

For issue 1 we use a macro that prefixes the values in the alphanumeric columns with an apostrophe

The above function prefixes cell 11 in your spreadsheet with an apostrophe for rows 1 to 15556

For issue 2 we use a macro that sets the formatting of the cell to General and subsequently refreshes it.

The above function sets the format of cell J to General for rows 2 to 1614.

To insert the above functions into your spreadsheet do the following:

In MS Excel Tools > Macro > Visual Basic Editor

In Visual Basic Editor > Insert > Module > Copy & Paste the following code:

excel_macros

Save the changes and close the editor.

To execute the Macro go to Tools > Macro > Format_Changes > Run

In order to master scripting in ODI I recommend the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

The Definitive Guide to Jython: Python for the Java Platform.

Jython Essentials (O'Reilly Scripting)

About the author

Uli Bethke LinkedIn Profile

Uli has 18 years’ hands on experience as a consultant, architect, and manager in the data industry. He frequently speaks at conferences. Uli has architected and delivered data warehouses in Europe, North America, and South East Asia. He is a traveler between the worlds of traditional data warehousing and big data technologies.

Uli is a regular contributor to blogs and books, holds an Oracle ACE award, and chairs the the Hadoop User Group Ireland. He is also a co-founder and VP of the Irish chapter of DAMA, a non for profit global data management organization. He has co-founded the Irish Oracle Big Data User Group.