Skip navigation
Up to Discussions in Enzee Universe
3061 Views 4 Replies Latest reply: Feb 23, 2012 11:38 AM by Mukesh Singh RSS
Bhuvaneswaran K New Enzee
Bhuvaneswaran K
39 posts since
Nov 26, 2011
Currently Being Moderated

Feb 21, 2012 9:55 AM

Reading from a file which has a specific pattern for its name

Hi,

 

We have a requiremnt in which we have to read data from a file... and untimately taking the count of records in that file...

 

That file will arrive everyday... the name of the file will be in a specific apttern...

 

e-g:

 

Files with the name format "FILE_XYZ_????????.txt" will have the names like "FILE_XYZ_02212012.txt", "FILE_XYZ_02222012",...

 

Note that the last four numbers give the present date.

 

 

Like that we can have names like "FILE_*_NAME". This format can give names like "FILE_12345_NAME", "FILE_ANYNUMBEROFCHAR_NAME" or "FILE_0_NAME".

 

 

We used external table to get the record count. But in that we had to use the exact file name(not the format). Now, since the file name will be different everyday, we need to use this pattern in some way to read the file.

 

 

Is there a way to get the file name in a given directory, which is having a particular file name pattern?

 

 

Any guidance on this will be really helpful in solving a major set back now.

 

 

Thanks in advance.

  • nickg Active Enzee
    nickg
    242 posts since
    Mar 16, 2011
    Currently Being Moderated
    1. Feb 21, 2012 10:56 AM (in response to Bhuvaneswaran K)
    Re: Reading from a file which has a specific pattern for its name

    An easy thing to do is to have a unix script that builds a list of file names of interest, have a stored procedure in netezza to read the file (with a known name), then process the file names off the list using dynamic SQL to create and drop the external tables.

  • Bhuvaneswaran K New Enzee
    Bhuvaneswaran K
    39 posts since
    Nov 26, 2011
    Currently Being Moderated
    2. Feb 21, 2012 11:05 AM (in response to nickg)
    Re: Reading from a file which has a specific pattern for its name

    We are in windows environment... and we have Perl installed and that would be helpful in doing this... I thought of doing this using NZ alone...

  • nickg Active Enzee
    nickg
    242 posts since
    Mar 16, 2011
    Currently Being Moderated
    3. Feb 21, 2012 12:08 PM (in response to Bhuvaneswaran K)
    Re: Reading from a file which has a specific pattern for its name

    You don't have that kind of view of the world from within a stored procedure.  You could concievably write a UDF that runs on the host to read directories from an NFS mounted disk, but its much simpler to do that on the OS that holds the files of interest.  Doing it in Netezza is more work, requires more specialized skills, more difficult to maintain, and obfuscates what is going on.

  • Mukesh Singh New Enzee
    Mukesh Singh
    9 posts since
    Dec 4, 2008
    Currently Being Moderated
    4. Feb 23, 2012 11:38 AM (in response to Bhuvaneswaran K)
    Re: Reading from a file which has a specific pattern for its name

    You can use TRANSIENT external tables.

     

    Transient external tables (TET) pr ovide a way to define an external table that exists only for
    the duration of a single query. Transient external tables have the same capabilities and lim-
    itations as normal external tables. A special  feature of a TET is that the schema does not
    have to be defined when the TET is used to l oad data into a table or when the TET is cre-
    ated as the target  of a SELECT statement.

    Transient external tables (TET) pr ovide a way to define an external table that exists only for

    the duration of a single query. Transient external tables have the same capabilities and lim-

    itations as normal external tables. A special  feature of a TET is that the schema does not

    have to be defined when the TET is used to l oad data into a table or when the TET is cre-

    ated as the target  of a SELECT statement.

     

    SELECT <column_list | *> FROM EXTERNAL 'filename' (schema_definition)

    [USING (external_table_options)];

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • Correct Answers - 4 points
  • Helpful Answers - 2 points