ODI Series - Reversing a collection of files from an excel template

If you have a collection of flat files that need reversing or change occasionally then it can be time consuming to reverse them one by one and set up their parameters, currently there is no way of automatically reversing files from a model.

There is a Knowledge module “RKM File (FROM Excel” available to help manage flat files, this RKM provides the ability to reverse files and maintain the structure definition based on the information held in an excel template.

There is an example of the excel template at :- \OraHome_1\oracledi\demo\excel\file_repository.xls
The template consists two worksheets, the first holding the file information and relates to the file tab in a file datastore



The second holds the column information for the files and relates to the columns tab in a file datastore.



If you highlight the column heading a brief description is given of what the column is used for, most of them are self-explanatory anyway. Most of the information can be cross-referenced with a datastore for example finding the code for field seperator.

Please note though if you are going to use the templates do not change the structure of the file as the RKM will fail or not produce the desired results.

Ok, say I have the following files I want to maintain their structure in the template.



The first step is to fill in the relevant information into the files sheet of the template, most of the parameters will be the same so it is easiest just to copy and paste rows and change file names, table name and alias.



Now the columns sheet needs to be populated with the structure of the columns in the files.



Now the template is complete an ODBC connection is required so the ODI excel technology can access it.



Be careful when picking the excel driver, I tried to use the latest driver which comes with excel 2007 and had problems with it, reverting to an older driver fixed the issue.

In the topology manager a new data server is required using the JDBC driver sun.jdbc.odbc.JdbcOdbcDriver and the JDBC URL pointing to the ODBC data source name just created e.g. jdbc:odbc:FILETEMPLATE



In the physical schema it is easiest to give the Logical Schema the name of “EXCEL_FILE_REPOSITORY” as this the name the RKM will be looking for, it is possible to create a different name though the RKM will need to be updated.



Before moving on to the next stage be sure to have created all the necessary components pointing to the location of the flat files in the topology manager (I have covered how to do this in an early blog)



Moving on to the designer, if you don’t have the “RKM File (FROM EXCEL)” imported then this is a good time to import it.
A new model can be created using the File technology and the associated logical schema.



In the Reverse tab select customized and the RKM should be automatically selected.



Apply and reverse to build the file datastores from the template, make sure the excel template is closed before running the reverse otherwise it will fail, after reversing it is good practice to check in the operator that everything was successful.



So there we have it an easy way of managing the structure of files from an excel template.