How to find & replace a string appearing after 6th occurrence of pipe character in a text file using powershell

Question

I am trying to search a specific string at a specific position in a text file and replace it with some other text. the texts are separated by the pipe character. So I know I have to replace the text which appears after the 6th pipe character. but I am unable to formulate the regular expression. Please help please is the example of my entire search string in a file

MACADD||TEST|Street1|CITY||Taiwan|||10000000|||FIRE||Taiwan||||||||12 days||30 Days|DDTE||812148709231890||124-Taiwan|DENE|||

$Files = Get-content -Path F:\Newfolder\*.csv




 $CountryCodeLookup = @{
    'USA'= 'United States'
    'Taiwan' = "Taiwan, Republic of China" # for this item csv file become strange
    }

foreach ($File in $Files)
    {
    $DelimCount = ($File -replace '[^|]', '').Length
    $CSV_Thing = ConvertFrom-Csv -Delimiter '|' -InputObject $File -Header @(1..$DelimCount)

    If ($CountryCodeLookup.ContainsKey($CSV_Thing.7))    
    {
    $CSV_Thing.7 = $CountryCodeLookup[$CSV_Thing.7]
    }


    $OutString = (($CSV_Thing |
        ConvertTo-Csv -Delimiter '|' -NoTypeInformation |
        Select-Object -Skip 1) -replace '"', '') + '|' | Set-Content $File.PSPath -Force

    }

Answer 1

here is a demo of what i meant by using the CSV cmdlets to get to the item after the 6th delimiter.

what it does ...

• creates a list of strings to work with
  when ready to do this for real, remove the entire #region/#endregion block and use Get-Content.
• creates a lookup table for the 2-letter country codes to full country names
• iterates thru the collection of sample strings
• replaces all the non-pipe chars with nothing
• counts the remaining chars
• converts the string to an object using the pipe as a delimiter and the count of delimiters as the column headers
• checks to see if the look up table contains the item in column 7
• if YES, replaces the value in the property named 7 with the value looked up from the $CountryCodeLookup hashtable
• if NO, leave the item value as is
• converts the object to a CSV
• skips the 1st line
  that holds the unwanted header line.
• removes the unwanted double quotes
• adds the missing trailing single pipe
  that was caused by the way that the empty object didn't show up in the new CSV string.
• displays the original string and the new version

the code ...

#region >>> fake reading in a file of plain text lines
#    in real life, use Get-Content
$InStuff = @'
MACADD||TEST|Street1|CITY||USA|||10000000|||FIRE||0||||||||12 days||30 Days|DDTE||812148709231890||124-USA|DENE|||
MACADD||TEST|Street1|CITY||UK|||10000000|||FIRE||0||||||||12 days||30 Days|DDTE||812148709231890||124-USA|DENE|||
MACADD||TEST|Street1|CITY||DE|||10000000|||FIRE||0||||||||12 days||30 Days|DDTE||812148709231890||124-USA|DENE|||
MACADD||TEST|Street1|CITY||RU|||10000000|||FIRE||0||||||||12 days||30 Days|DDTE||812148709231890||124-USA|DENE|||
MACADD||TEST|Street1|CITY||ZIGZAG|||10000000|||FIRE||0||||||||12 days||30 Days|DDTE||812148709231890||124-USA|DENE|||
'@ -split [System.Environment]::NewLine
#endregion >>> fake reading in a file of plain text lines

$CountryCodeLookup = @{
    USA = 'United States'
    UK = 'United Kingdom'
    DE = 'Germany'
    RU = 'Russian Federation'
    }

foreach ($IS_Item in $InStuff)
    {
    $DelimCount = ($IS_Item -replace '[^|]', '').Length
    $CSV_Thing = ConvertFrom-Csv -Delimiter '|' -InputObject $IS_Item -Header @(1..$DelimCount)
    # deal with items that are not in the lookup table
    #    if the item is in the key list, do the lookup & assign the matching value
    #    else leave it unchanged
    if ($CountryCodeLookup.ContainsKey($CSV_Thing.7))
        {
        $CSV_Thing.7 = $CountryCodeLookup[$CSV_Thing.7]
        }

    $OutString = (($CSV_Thing |
        ConvertTo-Csv -Delimiter '|' -NoTypeInformation |
        # get rid of the unwanted double quote and add the missing trailing pipe
        Select-Object -Skip 1) -replace '"', '') + '|'

    $IS_Item
    $OutString
    '=' * 30
    }

output ...

MACADD||TEST|Street1|CITY||USA|||10000000|||FIRE||0||||||||12 days||30 Days|DDTE||812148709231890||124-USA|DENE|||
MACADD||TEST|Street1|CITY||United States|||10000000|||FIRE||0||||||||12 days||30 Days|DDTE||812148709231890||124-USA|DENE|||
==============================
MACADD||TEST|Street1|CITY||UK|||10000000|||FIRE||0||||||||12 days||30 Days|DDTE||812148709231890||124-USA|DENE|||
MACADD||TEST|Street1|CITY||United Kingdom|||10000000|||FIRE||0||||||||12 days||30 Days|DDTE||812148709231890||124-USA|DENE|||
==============================
MACADD||TEST|Street1|CITY||DE|||10000000|||FIRE||0||||||||12 days||30 Days|DDTE||812148709231890||124-USA|DENE|||
MACADD||TEST|Street1|CITY||Germany|||10000000|||FIRE||0||||||||12 days||30 Days|DDTE||812148709231890||124-USA|DENE|||
==============================
MACADD||TEST|Street1|CITY||RU|||10000000|||FIRE||0||||||||12 days||30 Days|DDTE||812148709231890||124-USA|DENE|||
MACADD||TEST|Street1|CITY||Russian Federation|||10000000|||FIRE||0||||||||12 days||30 Days|DDTE||812148709231890||124-USA|DENE|||
==============================
MACADD||TEST|Street1|CITY||ZIGZAG|||10000000|||FIRE||0||||||||12 days||30 Days|DDTE||812148709231890||124-USA|DENE|||
MACADD||TEST|Street1|CITY||ZIGZAG|||10000000|||FIRE||0||||||||12 days||30 Days|DDTE||812148709231890||124-USA|DENE|||
==============================

that seems to do what your current version of the problem requires. [grin]