File_Exists BODS Function - Dynamic File name and Unzip file

dataservices job design
1

Hi,

I have a zipped file in specific folder on server, I have to unzip that file into the folder and then Load it into Database using Business Objects Data Services.

The challange is the name of the file is dynamic.

eg. All apps by name and operating system_364259_20_January_2012_00_22_41_324.csv.

The file name includes Date which will change every day. eg. 20 January 2012 in the above case, I have to extract the date from the file name and insert it into table(I can do it by using di_filename).

First I am checking the file is exists of not by using file_exists function of bods by following code.

$VSTR_DIR = ‘C:\ETL\data’;
$VSTR_FILE_NAME = ‘Application Name_364259_*.csv’;

$VSTR_FILE =’"’||$VSTR_DIR||’\’||$VSTR_FILE_NAME||’"’;

print ('File path and Name is '||$VSTR_FILE);

IF (file_exists($VSTR_FILE) = 0)
BEGIN

PRINT(‘file not found’);
raise_exception(‘file not found’);
END

But even though the file is there in the path I am unable to find it.

So anybody can let me know how to know the file is exists or not when the file name is dynamic and how to unzip a file using dos command in bat file.

Thanks In Advance,
Yogesh

Thanks,
Yogesh

mahayogesh (BOB member since 2011-12-20)

2

\ is an escape character. I believe depending on the character that follows it, it may or may not path as intended. (this is related to your dir path)

hard code the path in a reader and see if it finds it. If so double up your slashes on your dir variable.

as far as the unzipping files … this attachment might be a bit old, so buyer beware.
pkzipc_87_SZ_desktop.pdf (723.0 KB)

jlynn73 :us: (BOB member since 2009-10-27)

3

Unfortunately the file_exists function does not recognise wildcards in the file name. Another way you can do this is to create a dummy dataflow which reads from your file format object into a template table. The file source allows wildcards and in the file source options there is a setting under ‘Source Information’ called ‘Include file name column’. Set this to ‘Yes’ and then you can specify the properties of a column that will be included in the output schema and will contain the actual file name of all the files found matching your wildcard. If you have only this output field and check the ‘Distinct rows’ then you will just get one row with the file name matching the wildcard (or an empty table if no file exists). You can then use the SQL function in a script after the dataflow to query the template table and get the actual filename. If you need to keep checking until a file(s) is found then just put the dataflow and script inside a WHILE loop.

But please note that if the file has thousands of rows you may need to add a relevant WHERE clause to reduce the rows read from the file if it exists, but this WHERE clause has to be such that it will read at least 1 row if the file exits, otherwise your target file may be blank even if a file exists.

There may be a better way to do this, but it’s the only other way I can think of right now.

Regarding unzipping the file you will need to find a command-line unzip program, there are free apps available. Then you can either build a batch file and then execute the batch file from DS using the EXEC function in a script, or if you only have the one command line to perform the unzip then you can specify it directly in the EXEC function.

ClintL :south_africa: (BOB member since 2011-01-06)

4

to zip a file using exec:
print(exec(‘d:\programs\winzip\wzunzip.exe’,’-o -yb [$archivePath][$archiveDSN] [$destPath]’,8));

zip:
print(exec(‘d:\programs\winzip\wzzip.exe’,’-yb [$archivePath][$archiveDSN].zip [$sourcePath][$sourceDSN]’,8));

heres some python code to zip a file mask

def genDateStamp():
   import time
   tupTime = list(time.localtime())
   for idx in range(5):
      tupTime[idx+1] = str(tupTime[idx+1]).zfill(2)
   tupTime[0] = str(tupTime[0])
   return('_'.join([''.join(tupTime[0:3]),
                    ''.join(tupTime[3:6])]) )

def zipArchive():
   dateStamp = genDateStamp()
   searchPath  = r'\\Dsvcs1\repository\Finder_Supp\Input\ARCHIVE'
   archivePath = searchPath + '\\' + 'DRMinput_' + dateStamp + '.zip'
   inputPath   = searchPath + '\\' + '*DRM.TXT'
   import subprocess
   subprocess.Popen([r'd:\programs\winzip\wzzip.exe','-m','-yb',archivePath,inputPath]).wait()
   return

zipArchive()

jlynn73 :us: (BOB member since 2009-10-27)

5

Thanks All this will really help me.

mahayogesh (BOB member since 2011-12-20)

6

Hello All,

I am able to unzip the source file using below comment… Please help me on this.

print(exec(‘C:\Program Files\7-Zip\7z.exe’,‘e D:\Tc1\fin.zip D:\Tc1’,8));

Regards,
Srini…

srinivasan.ve (BOB member since 2011-06-01)

7

i got it. Now i am able to unzip the same using below

print(exec(‘C:\Program Files\7-Zip\7z.exe’,‘x D:\Tc1\fin.zip -oD:\Tc1’,8));

regards,
Srini…

srinivasan.ve (BOB member since 2011-06-01)