ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

Getting one or several unknown files from a directory

Hi Friends…

Today I would like to share with you a way that I have been using for several years, to get unknown files from a directory in Oracle Data Integrator.

For instance, if you have files in a directory that had no controlled names but needs to be load by ODI, here there are an option:

The hole idea is based in create a file with contains all file names and read it from ODI.

(Windows example but pretty much the same at Unix/Linux)

  1. Create an ODI Procedure with a single step.
    • Source Tab
    • Technology: OS Command
    • Code:  cmd /c dir c:my_directory*.* /b /a:-d > c:my_directoryAll_Files_Names.txt
  2. Now use the technique describe at: http://odiexperts.com/?p=273 to read each line at time  using the All_Files_Names.txt as source.

So simple like that… all you need to do is, before go to the next line (loop) use the variable in the way that you want!

An example is use it as name for a datastore that is used as source in an interface, than you can call the interface once to each variable value allowing to load all files from that directory.

I hope this technique could help you guys…

Best Regards,

Cezar Santos

18 Comments

  1. Hi Guys. I have a question.

    let say I have files in multiple directories like (c:/fileexamples/in/test1.txt , c:/fileexamples/in/archive/test2.txt , c:/fileexamples/blpl/test3.txt ) and so on. I want only file names (test1,test2, test3) in 1 .txt file along with its path so that I can you a mapping to load that file in a table. Is it possible in odi12c. if so can you please help me with this.

    Thank You.

  2. Hi
    I have a requirement to process the list of files to Staging table without any control on naming conventions.Could you please give us the list of steps.I tried with variable.But my question is wht will be there in the refreshing section of variable and where ths code needs to be executed as it’s a file.I have worked with static files but dynamic file naming needs to be bit simple to handle it.

  3. hi guys,
    I think that is more tidy and clear load the file list into table so you have the file list that you need to load in the DB.
    After, inside the Refresh variable setting you can write just a select:

    “select FILE_NAME from TABLE_LS_FILE where WRK_FLAG is not null and where ROWNUM = 1”

    the elapsed time to build it is the same.

    cheers

    so you can order, flag worked file (in case that the process fail for some reason).

  4. Hi Cezar,

    I am continuously getting error in ODI “Wrong process return code: 1 ” for command
    cmd /c dir c:my_directory*.* /b /a:-d > c:my_directoryAll_Files_Names.txt

    my_directoryAll_Files_Names.txt is getting successfully created via cmd when I use
    dir c:my_directory*.* /b /a:-d > c:my_directoryAll_Files_Names.txt

    Could you please let me know if I am missing anything in ODI?

    Thanks
    Aditya

    • HI Aditya,

      There is no error information at All_Files_Names.txt??
      What is wrote at it?

      • Hi Cezer,

        All_Files_Names.txt is not getting created through ODI.

        Thanks
        Aditya

        • OK…

          Are you executing through a specific agent or in the Local Agent?

          • Tried with both Local agent and Standalone agent which is installed on my local m/c.

            Even tried cmd /c dir c:\my_directory*.* /b /a:-d > c:\my_directoryAll_Files_Names.txt
            But still the same error.

            Thanks
            Aditya

          • I can’t see why didn’t work…

            I tried in my environment cmd /c dir c:\my_directory\*.* /b /a:-d > c:\my_directory\All_Files_Names.txt

            and works fine… unfortunately to help more it’s necessary to be at your environment..

        • Just use the drive letter in capital it will work perfectly.
          Thanks

  5. < c:my_directoryAll_Files_Names.txt
    >>
    can you please provide an UNIX alternative to the above mentioned commands which will be save in the same directory where all the files resides. (home directory for the files)

    Thanks
    Animesh

  6. Dear Cezar,

    Very Nice arttical…
    Thanks…

    Zakeer

  7. Hi Olivier,

    To do that you need to do is to use the ODI variable in the “resouce name” textbox in the source datastore.

    After that, use the variable and the interface in a package and in sequence, calling it in loop, until the list from text be finished.

    Make any sense?

    Any doubts, drop me an email….

    Thank you for visit ODI Experts.

    Cezar

  8. You said : “An example is use it as name for a datastore that is used as source in an interface, than you can call the interface once to each variable value allowing to load all files from that directory.”

    Please, could you explain how to do that in the interface?

Leave a Reply

Required fields are marked *.