replace string with database value

PowerShell Help
1

I have 1 folder filled with .txt 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*****PIdatabase value tied to field 4~

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
2

Hmmm … your text pattern is quite confusing - at least for me. :wink: What do mean with “fields”? If your source data have a structure you might “re-create” this structure in your script and use it for your good. If your source data do not contain any sensitive data you could post a chunk of it here (formated as code as well please) and we might find a way to help you. What are the criterias for the text pattern to match to one of your 4 database fields?

3

I’ll see if I can make the example simpler:
Line from file:
Apple|DescriptionToReplace|Boy|FieldToUseInSQLquery

Select ID FROM myLookupTable where ID_Description = FieldToUseInSQLquery

New Line:
Apple|777|Boy|FieldToUseInSQLquery

I don’t know how to do the REPLACE.

-Kevin

4

“Apple|DescriptionToReplace|Boy|FieldToUseInSQLquery” |
Import-CSV -Headers “Fruit”,“Desc”,“Gender”,“SQL” -Delim “|”

This produces objects from your lines of text.

"Apple|DescriptionToReplace|Boy|FieldToUseInSQLquery" |
Import-CSV -Headers "Fruit","Desc","Gender","SQL" -Delim "|" |
ForEach {
 # Perform SQL query
 # put new value into $whatever
 $_.Desc = $whatever # this changes that column
} |
Export-Csv output.txt -Delim "|" -NoHead

If you let PowerShell take you from text-parsing into structured objects, which is what the -CSV commands already know how to do, life is a lot easier. I’ve applied headers so I can easily refer to the “columns” (properties) by name. Stop thinking “how do I replace a value in a string” and start thinking “how do I turn this string into objects so I don’t have to muck around with strings.”

5

Oops, that should have been ConvertFrom-CSV, I think, not Import-Csv. Sorry, working from my phone. Anyway, aside from syntax flubs, that’s the pattern to follow.

6
7

if it is a txt file, why not treat it like a bunch of strings and use .replace?

basics:
(Get-Content $file).Replace($findString, $replaceString) | Set-Content $file

now if you have multiple finds and multiple replaces, this can be run several times (loop it) I don’t know how processer friendly this would be… I’m not a master at read/write of files, so I’m not sure how it would go line by line (foreach)