Replace text in source file before reading

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

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

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

Cheers