What if you have to search and replace some text in source file before reading it in BODS?

First thing which comes up to our mind is to use the function “replace_substr”. If we have multiple columns on which we need to perform search & replace, we will have to copy paste same replace commands for every column.

Better way will be to ship replace task to external utility – outside BODS. Windows users can take advantage of either VB-Script or Powershell.

Powershell does search & replace in single line command which can be called using exec function in BODS.


Replace command

powershell -Command "(gc myFile.txt) -replace 'foo', 'bar' | Out-File myFile.txt"




Create a new custom function as show below

Capture.PNG




Custom Function Content

print (‘Executing find and replace command on the file ‘ ||$FileName );

print(‘Search string: ‘ || $SearchTxt );

print(‘Replace string: ‘  || $Replacetxt);

$Command = ‘ -Command “(gc ‘ || $FileName  || ‘) -replace \” || $SearchTxt  || ‘\’, \” || $Replacetxt  || ‘\’ | Out-File -encoding UTF8 ‘ || $FileName  ||'”‘;

print(‘powershell ‘ || $Command );

exec(‘powershell’, $Command,1);

Return(0);




Usage

Capture.PNG



This little method saves us a lot of time and effort.


Cheers

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply