replace string from database lookup

PowerShell Help
1

I have 1 folder filled with .DAT files.
I need to find all lines that look like this: NM1PR2AARP MEDICARE COMPLETE*****PI87726~
And replace the last field with the value from a database lookup from field 4 like this:

NM1PR2AARP MEDICARE COMPLETE*****PImap1stString ~

I have no idea how to this the smart way so this is what I am doing:

  1. Combine all the files. 
    Get-Content C:\test2\QTR2WHCfull\QTR2WHC*.DAT | Out-File C:\test2\Combined.DAT
  2. Find all the strings I need to replace. 
    get-content “C:\test2\Combined.DAT” | Select-String -Pattern ‘NM1*PR’ -simplematch | sort | Get-Unique | Set-Content “NM1lines.txt” # there are about 300
  3. Then I loop through each file and do a replace looking for one of those 300 lines.
$configFiles = Get-ChildItem -path "C:\test2\" *.DAT 
foreach ($file in $configFiles)
{
    (Get-Content $file.PSPath) |
    Foreach-Object { $_ -replace " NM1\*PR\*2\*AARP HEALTHCARE OPTIONS\*\*\*\*\*PI\*36273", " NM1*PR*2*AARP HEALTHCARE OPTIONS*****PI*map1stString" `
                        -replace " NM1\*PR\*2\*AARP MED SUPP\*\*\*\*\*PI\*36273", " NM1*PR*2*AARP MED SUPP*****PI*map2ndString"  `
                        #298 more possible replacements } |
    set-Content $file.PSPath

This is the SQL I would use if I knew how to pull field 4 from the string and replace field 10
Select MappedValue FROM [837WHCinsuranceDictionary] WHERE [Name]= ‘AARP HEALTHCARE OPTIONS’

-Thanks,
Kevin

2

There’s not really a smarter way to do that. However, you’re highlighting a deeply important thing, which is that in SQL, which uses structured data (e.g, fields have names you can refer to), things are easier. What you’re doing is straight text parsing, which is a PITA.

ME… I would probably write an Import-SomethingOrOther function that did nothing but read the text file and parse it into structured objects. I’d then have an Export-SomethingOrOther which did the opposite - accepted those objects and exported them to a text file with all the proper delimiters.

Now, I know nothing about your file format but:

NM1PR2AARP MEDICARE COMPLETE*****PImap1stString

If each * is a field delimiter, then Import-Csv and Export-Csv can do this for you. Otherwise, you might play around with ConvertFrom-String as a shortcut to turning your lines of text into structured objects.