Himu\’s Attempt at Blogging

Tidbits from my thoughts

ODI – How to Reverse Flat Files

with 12 comments

The most important thing to remember before using flat files as datastores in ODI is that the Designer looks for them in the client PC where it runs. I haven’t tried using a UNC (\\computer\share\…) but that should work.

ODI comes pre-configured with a folder for dealing with flat files – in the Topology Manger, you’ll find the FILE_GENERIC data server set up to serve files from the ODI_HOME/oracledi/demo/file directory. But if you want your own file data server then follow these steps:

  1. Open Topology Manager
  2. In the Physical Architecture tab, right-click Technologies>File and choose Insert Data Server
  3. Give a name in the Definition tab, then click on the JDBC tab
  4. Choose Sunopsis File JDBC Driver (com.sunopsis.jdbc.driver.file.FileDriver) and jdbc:snps:dbfile as the Url
  5. Test, and the close the dialog
  6. Right-click the data server you just created and choose Insert Physical Schema
  7. Decide on a directory in your PC (where ODI is being run) that you will use to keep all the flat files you want to use as datastores
  8. Put the complete path (or one relative to ODI_HOME/oracledi) of the directory in Directory (Schema) and Directory (Work Schema) of the Definition tab
  9. Make sure you check Default
  10. Click on the Context tab
  11. Click the small grid button to insert a new context (defaults to Global) and then give a name for the associated Logical Architecture – ODI will create it for you
  12. Close the dialog
  13. You’re done!

Before you can reverse a flat (text) file, it should be in either of the following formats:

  • Delimited text files – the field delimiter can be anything, e.g. (note the quotes around 456, Darling Street – some sort of text delimiter is required if the value contains the field separator character)


ID,Name,DoB,Address
1,Himu,01/01/2000,123 Racoon City
2,Jumanji,01/01/2001,”456, Darling Street”

  • Text files with fixed-width columns – each column starts at the same position in each line, e.g.


ID  Name     DoB        Address
1   Himu     01/01/2000 123 Racoon City
2   Jumanji  01/01/2001 456, Darling Street

You need a model to reverse the files – do the following:

  1. Place the file in the directory you set up as the physical schema
  2. Open Designer
  3. Go to the Models tab and insert a new model
  4. Give a name
  5. In Technology, choose File
  6. Select the Logical Schema you created earlier
  7. Click the Reverse tab and choose the Context (usually its Global)
  8. Click OK to save the model

Now the actual reversal process –

  1. Right-click the newly created model and choose Insert Datastore
  2. Give a name of your choosing and a good alias if you want to
  3. Click the ellipsis button next to the Resource Name text box and choose the file you want to reverse
  4. Click the Files tab
  5. Choose the file format – Fixed or Delimited
  6. If the file has header lines (giving column names as in the examples above), then put the number of lines to be treated as headers in Heading (usually 1)
  7. Choose the appropriate Field Separator (tab or comma is common)
  8. If the contents have text delimiters as shown in example 1 above then specify it in Text Delimiter; this is commonly double quotes (“) if CSV text files are generated from Excel
  9. Click the Apply button; ODI will ask if you want to lock the object; make sure you click No or the column reversal later might not work; also, don’t close the dialog
  10. Click the Columns tab
  11. Click the Reverse button and you should get suggestions from ODI itself
  12. Adjust the physical and logical column sizes as appropriate – usually these should match with the target datastore you are planning to populate ultimately

Sometimes, ODI cannot reverse the columns when you click Reverse. If this happens, make sure the datastore is not in locked mode.

Advertisements

Written by mhimu

May 1, 2009 at 3:15 pm

12 Responses

Subscribe to comments with RSS.

  1. Good, how to read a multiple file from a directory.

    Raja

    July 6, 2009 at 3:14 pm

  2. Thanks for this wonderful tutorial/article.

    iqtf

    September 10, 2009 at 6:42 am

  3. 10X man, Short and Accurate

    Eldad

    January 27, 2010 at 8:53 pm

  4. Thanks for the post. It solved mystery for me.

    Ritesh

    March 3, 2010 at 12:01 am

  5. I am unable to write a CLOB to a file. Do you have any solutions?

    Codrin

    June 9, 2010 at 2:42 am

  6. It works! Thanks a lot for the tips 🙂

    Alex K

    June 17, 2010 at 7:30 pm

  7. GREAT HELP

    OB

    March 4, 2011 at 2:37 am

  8. HI.. This is good post. I have learned how to read files in ODI.. 🙂 But I have a small query related to this.

    In my file, i will be having only one column and I have to return this column value into a ODI variable.. can you please help me how to do this?

    Pavan

    March 14, 2012 at 12:53 pm

  9. hi ..this is for converting a csv file to table right .then for table to csv file conversion in ODI ..do we need to follow the same procedure ? if yes ,then our target datastore we need to create a csv file in that location and try to reverse it in the file data model ?

    Radhika

    May 18, 2012 at 3:24 pm

    • Hi
      csv files are just delimited files as the first example above shows.
      Any text file that you want to use as a datastore must be accessible from ODI physically.

      mhimu

      May 22, 2012 at 2:17 pm

  10. My programmer is trying to convince me to move to .
    net from PHP. I have always disliked the idea because
    of the expenses. But he’s tryiong none the less. I’ve been using Movable-type on a variety of websites for about a year and am nervous about
    switching to another platform. I have heard excellent things about blogengine.
    net. Is there a way I can transfer all my wordpress content into it?
    Any help would be greatly appreciated!

    Rickey

    April 18, 2013 at 3:07 pm

    • You are an a&&hole, rickey.

      Sumit Shukla

      December 31, 2014 at 3:49 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: