How to use while loop on a script object?

dataservices job design
1

My intention is to use WHILE loop in a script object, bu I’m having difficulties to create one. The idea is to check the count of .txt files in a folder and then use move command to move and rename those .txt files. Any ideas how to implement this? Many thanks!

larus (BOB member since 2010-03-31)

2

Hi,

Pass an OS command (ls for Linux / dir for Windows) into the path that you want to check the files using exec() function and assign it to a variable. (Say $Filelist). Maintain the length of the variable to be long enough (say varchar 5000) to accommodate all the available files.

While loop:
In $Filelist, check for .txt extention and extract the file names one by one into another variable (Say $Filename) using word_ext() function and remove the same from $G_Filelist using replace_substr(). Iterate a counter along with. This way, you can get the count.
A variable $New_Name can be appended with the existing filename along with the change that you want to make. (Eg: if you want to add the current date, you can concatenate sysdate()) with filename.
In the same while loop, pass a move command (mv for Linux, move for Windows) and move each of the $FileName from source path to destination path. Include $New_Name for renaming, at the end of the move command, after destination path.

If required, you can use a print command before exec function with return code 8, so that you can see the OS command status in job log.

Exit the while loop when length($Filename) is zero.

Regards,

Suneer Mehmood,
Consultant - SAP BI/BOBJ/BODS.

suneer333 :us: (BOB member since 2010-07-15)

3

Thank you for your reply. I’ll try to implement this and get back to you when i have somehing to show!

larus (BOB member since 2010-03-31)

4

This thread might help with the some of the command syntax:-

Nemesis :australia: (BOB member since 2004-06-09)

5

another thing you can do is setup a dummy reader that is set to only read one record, and load the file names into a table.

then in your while loop, walk the table, process each file, delete from the table, archive your files… bla bla

once the file names are tabled, you have all sorts of options.

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

6

Hey, I suppose you can also use VBscript to achive the same.

Please find a link below it is very useful as it consist of important VBscripts that I frequently use in my job.

http://activexperts.com/activmonitor/windowsmanagement/adminscripts/filesfolders/files/#RenameFiles.htm

Cheers,
Shazin

Shazin :india: (BOB member since 2011-07-19)

7

Hi,

In the method that I mentioned above, there is a chance that the loop executes once more, even after all the files have been moved to the target.

This is because, the value of $FileName remains to be not null, even after all the files are moved. It becomes null only in the next execution.

So, after the while loop, do a count-1 on the counter, so that you get the exact number of files moved.

We will have to check the length ($FileName)<>0 for the while loop as there is a chance that some other string may enter, which does not have the filename. In this scenario, the while loop should not run infinite times.

Regards,

Suneer Mehmood,
Consultant - SAP BI/BOBJ/BODS.

suneer333 :us: (BOB member since 2010-07-15)

8

Thanks to suneer333, here is what I ended up doing. Hope this helps someone else also:


#Naming convention
#$G... --Global variable
#$V... --Local variable

# Load date
$G_LOAD_DATE = sysdate( );

#Cleaned load date 
$G_LOAD_DATE_CHAR = replace_substr(replace_substr(replace_substr(cast( $G_LOAD_DATE, 'varchar(24)'), '.', ''), ':', ''), ' ', '');

#Set path
$V_Path = '\\\\UNC\\Path\\To\\Folder\\';
$V_Path_History = '\\\\UNC\\Path\\To\\History\\Foder\\';

#Set the extension
$V_FileExtension = 'txt';

#File count
$V_FileCount = cast(exec( 'cmd.exe', 'dir/b/a-d [$V_Path]*.[$V_FileExtension] | find /v /c "::"' ), 'int');

#Files into a list (string)
$V_FileList = exec( 'cmd.exe', 'dir /b /a-d [$V_Path]*.[$V_FileExtension]');

#Move files
while ( $V_FileCount != 0 )
	begin
		#Take the first file name
		$V_FileName = word_ext( $V_FileList , 1, ' ');

		#Remove it from the list (string)		
		$V_FileList = replace_substr( $V_FileList , $V_FileName , '');
		
		#Make a new name
		$V_FileNewName = replace_substr( $V_FileName, '.[$V_FileExtension]', '') || '_' || $G_LOAD_DATE_CHAR || '.[$V_FileExtension]';

		#Move and rename
		exec( 'cmd.exe', 'MOVE [$V_Path][$V_FileName] [$V_Path_History][$V_FileNewName]', 0);		

		#Substract loop counter
		$V_FileCount  = $V_FileCount - 1;
	end

larus (BOB member since 2010-03-31)

9

larus, how and where did you define V_Path, referenced in your code snippet?

CNV_Ben (BOB member since 2002-08-15)

10

Hi Larus,

Glad to know. Like the way you took the count.

Hi Ben,

Larus has used V_Path as a Variable, the definition of which can be given in the job parameters if the variable is Global, or in a script preceding this code. V_Path is the folder in which the files exist.

Regards,

Suneer Mehmood,
Consultant - SAP BI / BOBJ /BODS.

suneer333 :us: (BOB member since 2010-07-15)

11

@CNV_Ben: I edited my script to show you how to use paths.

larus (BOB member since 2010-03-31)

12

Hi Larus,

You can give $G_LOAD_DATE_CHAR like this
$G_LOAD_DATE_CHAR = to_char(sysdate(),‘yyyymmddhh24miss’); This makes the script smaller.

You can use quotes in your execution command
Eg : print(‘MOVE “[$V_Path_Luku][$V_FileName]” “[$V_Path_Arkisto][$V_FileNewName]”’);

so that you can avoid the double slashes while defining value for path. In this case, a double slash will be required at the end of the path. Eg:’:<Rootpath>\Ariksto\’

Thank you and regards,

Suneer Mehmood,
Consultant - SAP BI/BOBJ/BODS

suneer333 :us: (BOB member since 2010-07-15)