ODI Snippets: Oracle Data Integrator, Excel data types, and limitations of the MS Excel ODBC driver.
Get our e-books Discover the Oracle Data Integrator 11g Repository Data Model and Oracle Data Integrator Snippets and Recipes
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
|
1 2 3 4 5 |
Sub apost() For r = 2 To 15556 Cells(r, 11) = "'" & Cells(r, 11) Next r End Sub |
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.
|
1 2 3 4 5 6 7 |
Sub Format_to_General() For Each c In Range("J2:J1614") c.NumberFormat = "General" v = c.Value c.Value = v Next c End Sub |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Sub Format_Changes() apost Format_to_General End Sub Sub apost() For r = 2 To 15556 Cells(r, 11) = "'" & Cells(r, 11) Next r End Sub Sub Format_to_General() For Each c In Range("J2:J1614") c.NumberFormat = "General" v = c.Value c.Value = v Next c End Sub |
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)
Uli Bethke
Uli has been rocking the data world since 2001. As the Co-founder of Sonra, the data liberation company, he’s on a mission to set data free. Uli doesn’t just talk the talk—he writes the books, leads the communities, and takes the stage as a conference speaker.
Any questions or comments for Uli? Connect with him on LinkedIn.
Follow Uli Bethke: 
