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
6 months, 2 weeks ago.

  • Author
    Posts
  • #105536

    Participant
    Points: 14
    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: 642
    Helping Hand
    Rank: Major Contributor

    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: 14
    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,811
    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,811
    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,811
    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.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort