This is the Sample of source Table to be populated as Flat File.

Target [Flat File - region.dat]

1. Go to the Physical architecture of the Topology and insert a New Data Server under File Technology

2. For the JDBC Driver choose the " Sunopsis File JDBC Driver "

3. Define Physical schema by entering the Directory as shown in the image for Windows or define the Directory path of the Unix environment. ( Make sure your agent should be having permission for that folder and file )

For windows enter the path with " /" even though windows shows directory with " \"

4. Link to the Logical Schema

5. Define your Target model with the required Logical Schema defined above .

6. Next Step is to define the columns and make sure all the data types are defined as string even for dates.

7. Comparing to the source , I have defined Target as with Two columns region_id and region_name with data type string.

8. Next step is to define the Properties of the File to be populated.

( Note – The most important part of File property is how  we define  them i.e  how the Records are stored and how ODI can categorize Columns while reading from the File ]

9. Next step is to define the Interface , since our target is a File we have to check mark the option " Staging area different from target " and selecting either the source or other schema which you want to use as the Staging schema , but preferably Source Schema.

10. Select the required LKM if the staging is different from the Source and " IKM Sql to File Append" . Apply and Execute .

11. Enable the option " GENERATE_HEADER " if you wish to populate the data with Headers.

12. Finally your file is created with the data and headers in it.

[Tip : - If you wish not to create a new data server for File Technology , you can use the default data server " FILE GENERIC " , the path is to store and create file under FILE_GENERIC  is  ‘ ODI_HOME\oracledi\demo\file ‘ . ]

1. Sample of the Excel file to be populated in Oracle.

   

   2. Set up the odbc connection to excel file through

   Control panel -> administrative tools – >Data source (odbc) (Windows )

   

   

   3. Use the JDBC – ODBC BRIDGE DRIVER in the put the ‘odbc’ name defined above (for this example its  ‘detail’) in the URL as show below in ODI

   

   4. Define your Physical and Logical Schema

   

   5. Link it to logical schema

   

   6. Create a model and using a selective reverse and do the revere engineering

   

   7. Finally all the selected tables with be visible as the data store as shown below

   

   8. Right click to see the data, to check everything is fine

   

   [ Note: The above procedure can be used to connect to Microsoft Access. Set up the ODBC connection using the Access driver and use the above process to set up the connection to Microsoft Access ]

This part of the Post is update on 20 – jun – 2010 in order to cover the few issue related to reading and  avoiding unnecessary null Rows .

For some of the Excel files , the sheets don’t appear if you select ‘ Tables’ , we need to select ‘ System Tables ‘

Removing Unnecessary Null Rows

Due to space  between the Main Header and the data , the space rows is captured  as you can see in the below screenshot.

to remove this just put a Filter condition

and you will not have any more space being captured

[Note : please use the column which is unique and is like a primary id , since using a wrong column can lead to Filter of Correct Data ]

Removal of Extra Header

Some time we have header being in First few lines and they are visible in the data to remove that just use a simple Filter condition

Here i am trying to remove the unnecessary Header called base from the data , just use a simple query with  column_name !=’string to be removed’

Hi everyone!

I’m really glad to start this site to share knowledge and exchange experiences with everyone that wants! That is an old project that only comes true because I found a really good friend and a incredible professional guy called Kshitiz Devendra who has been working hard to bring all of this to “production”, not more just a “project”.

Thanks Dev, you’re the best.

A little about me: My name is Cezar Santos and I have been working with ODI/Sunopsis since 2003, more than 6 years today. Besides that, I have always worked in the Data Integration world… At true, I started in the IT by “accident”. An accident called GAMES.

Let me try to explain, I started to “work” with computers when I was 12 or 13 years old because of games…. at that time we didn’t have Play Station, X-Box or Wii we had the TK85!!!

Take a look in its picture:

That was the "game console" in 80's

 Seems crazy but we recorded in K7 tape and “load” the program every time that you wanted to executed it. Some time it took more than 30 minutes to 1 hour and, if the k7 tape was old, some noise could make all loading be loose. Too, after typing a game program (bought in specialized magazines from that time), the K7 tape was our “CD” or HD! Besides this, there were programs from radio stations to “download” games… it was like:

“In 10 seconds the computer program transmission will start. 10, 9, 8, 7, 6, 5, 4, 3, 2, 1. GO!   shhhhhiiiiii uhhhhhhh grrrrrrrrrr ziiii xxxxxxxxiiiiiii…”

Just like an old modem by half to an hour some times…. all of this to play a game with 3 or 4 colours!!!!

Let me stop to talk about it! There is a lot more to tell but this post isn’t about this! hehehehehehe…..

After got contact with the “IT” world in my childhood I studied everything that I could find about computers and here I’m today, still absolutely in love for this incredible world. I have been working for companies like IBM, Brazilian Army, Banks, Brazilian Mail Company, Health companies, TELECOM, CRM, etc in a few countries like: Brazil, USA, Spain, Portugal, Argentina, Peru, Ireland, Chile (and few more)  at all kind of projects and with a lot of technologies.

This is just our first post with a little about me. From now forwarding we are going to start a lot of discussions about how ODI can be used at its maximum power.

Everyone is welcome!

Cezar Santos