Excel technology in Oracle Data Integrator

Working with Excel data in ODI is hard and named ranges have to be created in the excel file. This is because the ODI excel technology works on the basis of turning named ranges into datastores.
We can change the ODI standard configuration in order to manage Excel data easily as we do with other technologies.
The configuration explained above was tested in odi 10.3 and Java 1.4. The process is as follows:

ODI CONFIGURATION

1.- XLSQL Excel JDBC driver is needed. This driver is completely free and we can download it from http://sourceforge.net/projects/xlsql/
2.- Add the new driver to ODI. To do that, copy the file called xlSQL_Y7.jar (this name depends on the JDBC driver version) into the drivers directory (usually at OraHome\oracledi\drivers). Also check that a classpath is set for this folder in “odiparams.bat” file.
3.- Add the new scripting engines to work with the driver into the ODI scripting folder (usually OraHome\oracledi\lib\scripting). To do that, copy these jar files into the folder:
• commons-cli-1.0.jar
• commons-logging.jar
• crimson.jar
• hsqldb.jar
• jaxp.jar
• jconfig.jar
• jmxri.jar
• jxl.jar
• mysql-connector-java-3.0.10-stable-bin.jar
Also check that a classpath is set for this folder in “odiparams.bat” file.
4.- Close all ODI tools and restart ODI agent service.

CREATE EXCEL FILE CONNECTIONS

1.- Open Topology Manager.
2.- Insert a new data server for Microsoft Excel technology.
3.- Write this “com.nilostep.xlsql.jdbc.xlDriver” into the driver field.
4.- Write the folder path where your Excel file will be stored into the URL field. The URL follows this format: jdbc:nilostep:excel:folderpath

5.- Create a logical schema and assign it to the physical within a context.

GET FILE STRUCTURE

1.- Open designer module.
2.- Insert new model.
3.- In the definition tab, choose Microsoft Excel as the technology.
4.- In the reverse tab, select standard method.
5.- In the selective reverse tab, retrieve all the tables. The tables will be obtained in format FILE_NAME.SHEET_NAME.
6.- Click on reverse and the tables will be available in the model tree.
7.- Edit the table and define the columns data types.

OPERATE WITH EXCEL DATA

The knowledge modules included in ODI work fine with Excel through this driver. Only one issue was found when loading data from excel into a sql server database table. This issue is that the field and table names in the select statement must go into double quotes. For loading excel data into a database table follow these steps:
1.- Duplicate the LKM SQL to SQL knowledge module and edit it.
2.- Select Microsoft Excel as a Source Technology in the definition tab.

3.- Open the load data step in the details tab.

The code below assures that table and columns names are between double quotes just in case the table name and the source excel table alias in the interface has the same name:

select <%=snpRef.getPop(“DISTINCT_ROWS”)%>
<%=snpRef.getColList(“”, “\u0022[COL_HEADING]\u0022″, “,\n\t”, “”, “”)%>
from “<%=odiRef.getSrcTablesList(“[TABLE_ALIAS]“,”")%>”
where (1=1)
<%=snpRef.getFilter()%>
<%=snpRef.getJrnFilter()%>
<%=snpRef.getJoin()%>
<%=snpRef.getGrpBy()%>
<%=snpRef.getHaving()%>
Also quotes can be managed following these steps:
1. Open Topology manager.
2. Edit the Microsoft Excel technology (phisical architecture)
3. Go to tab Language, column “Object Delimiter”, delete the quotes and let it with no value.
4.- Create a new interface. Drag your Excel table from the model tree into the source tables area and drag your target datastore.
5.- In the flow tab of your interface, select the new LKM created in step 3 and the IKM of your choice.
6.- Execute the interface and the Excel data will be stored in your target table.
CONCLUSION

After little work of configuration and with no extra cost, using Excel data in ODI becomes easy and gives us the possibility to operate with Excel technology in the same way we operate with database technology.