replace string from database lookup

Welcome Forums General PowerShell Q&A replace string from database lookup

This topic contains 1 reply, has 2 voices, and was last updated by

 
Keymaster
3 months, 3 weeks ago.

  • Author
    Posts
  • #105212

    Participant
    Points: 0
    Rank: Member

    I have 1 folder filled with .DAT 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*map1stString ~

    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

  • #105380

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

    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:

    NM1*PR*2*AARP MEDICARE COMPLETE*****PI*map1stString

    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.

The topic ‘replace string from database lookup’ is closed to new replies.