VB Script - Find and Replace in pipe delimited file
Posted on 2013-01-11
Trying to get our source files in best possible shape so they load to database faster. I have a text file that is pipe delimited. It has 14 fields. First 4 are metadata and last 10 are data. Here's what I need.
For each field:
Field 1 - Nothing - Stays as is
Field 2 - Change 'Fiscal Rcpt' to 'Fiscal' and 'Fiscal Sales' to 'Fiscal'. So 'Fiscal Sales Aug' would be 'Fiscal Aug' and so on.
Field 3 - Nothing - Stays as is
Field 4 - Change '2012' to '2012_New'
Field 5 to Field 14 - Change '0' to #Missing and/or '0.00' to #Missing. These have to be exact matches only. So 50 cant become 5#Missing. Only '0' and '0.00' can be changed to #Missing.
This has to run as fast as possible as this is whole point as we are bumping up against SLA already. I have a bunch more of these so some way where I can change a few things around for a different file would be helpful too.
So a way where I can identify the filename to apply this to and then the resulting file would just be prefixed with 'LD_'. That would be great.
I appreciate all help you gurus have been providing.