replace string with database value

Welcome Forums General PowerShell Q&A replace string with database value

This topic contains 6 replies, has 4 voices, and was last updated by

 
Participant
3 months, 2 weeks ago.

  • Author
    Posts
  • #105536

    Participant
    Points: 0
    Rank: Member

    I have 1 folder filled with .txt files.
    I need to find all lines that look like this: NM1*PR*2*AARP MEDICARE COMPLETE*****PI*87726~
    And replace the LAST field with the value from a database lookup from field 4 like this:

    NM1*PR*2*AARP MEDICARE COMPLETE*****PI*database 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 
  • #105545

    Participant
    Points: 135
    Helping Hand
    Rank: Participant

    Hmmm ... your text pattern is quite confusing – at least for me. 😉 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?

  • #105580

    Participant
    Points: 0
    Rank: Member

    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

  • #105584

    Keymaster
    Points: 1,619
    Helping HandTeam Member
    Rank: Community Hero

    "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."

  • #105587

    Keymaster
    Points: 1,619
    Helping HandTeam Member
    Rank: Community Hero

    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.

  • #105590

    Keymaster
    Points: 1,619
    Helping HandTeam Member
    Rank: Community Hero
  • #105593

    Participant
    Points: 0
    Rank: Member

    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)

The topic ‘replace string with database value’ is closed to new replies.