Home > Enterprise >  Extracting multiple values from text file
Extracting multiple values from text file

Time:04-13

New to PowerShell and trying hard to get something out of the text file.

Here is sample data:

RD32 **MK12001** 3160 211 50 **02100123**001 SERVER LOCAL - STREET 
„ ERROR MESSAGE SERVER LOCAL IS MANDATORY 
RD32 **MK13103** 3160 211 50 **02100124**001 SERVER LOCAL - CITY 
„ ERROR MESSAGE SERVER LOCAL IS MANDATORY 
RD32 J4834-00009-92051 **MRDOOP** 3160 211 50 **021005237**001 SERVER GIVEN NAME 
PETER „ ERROR MESSAGE SERVER NAME IS MANDATORY 
RD32 B5509-00000-00522 **JPPK** 3160 211 50 **02123133**001 SERVER GIVEN NAME 
SUNNY „ ERROR MESSAGE SERVER NAME IS MFiNDATORY

I'm trying to extract the bold fields from each line.

The output I'm looking for:

MK12001 C2100123
MK13103 C2100124
MRDOOP C21005237
JPPK C2123133

What I'm getting currently:

MK12001 3160
C2100123
MK13103 3160
C2100124
MRDOOP 3160
C21005237
JPPK 3160
C2123133

Problem:

Because I'm using "3160" for my match criteria for my first field so it's showing up in the results as well and for my second field which is a ticket# (C1234567), due to the use of "pipe" operator or "second" search/match criteria its going in the next line, If someone can help me to keep the ticket # in the same field then I guess I can live with having "3160" in between so it will look like

MK12001 3160 C2100123

or if someone can suggest me to only display the bold fields i.e before the 3160 then that would be awesome.

MK12001 C2100123

P.S: with my script, I'm already changing the "0" to "C" in ticket # field (C1234567)

Here is the code so far:

#Location of original file
$Location = "C:\Temp\Ap8.txt"
#Location of file where the "0" is replaced with "C"
$Location2 = "C:\Temp\results9.txt"
#Final results
$Location3 = "C:\Temp\tickets9.txt"

#get the original file
$Change = Get-Content $Location

# replace C with 0
$Change | ForEach-Object {$_ -Replace "3160 311 50 0",  "3160 311 50 C"} | 

#write the results to staging file
Set-Content $Location2

#get the staging/udpated file
Get-Content $Location2 -Raw |

#look up for a specific fileds, I have to fetch two fields from each line therefore using pipe operator inbetween
Select-String "\s\w{1,8}\s3160| C\d{7}" -AllMatches |


  % { $_.Matches.Groups.Value } |
  Out-File $Location3 -Encoding ascii -Force

CodePudding user response:

i am truly bad with complex regex patterns, so this is done with string operators and only very simple regex patterns. [grin]

the code ...

#region >>> fake reading in a text file
#    when ready to do thiw with real data, use Get-Content
$InStuff = @'
RD32 MK12001 3160 211 50 02100123001 SERVER LOCAL - STREET„ ERROR MESSAGE SERVER LOCAL IS MANDATORY 
RD32 MK13103 3160 211 50 02100124001 SERVER LOCAL - CITY„ ERROR MESSAGE SERVER LOCAL IS MANDATORY 
RD32 J4834-00009-92051 MRDOOP 3160 211 50 021005237001 SERVER GIVEN NAME PETER „ ERROR MESSAGE SERVER NAME IS MANDATORY 
RD32 B5509-00000-00522 JPPK 3160 211 50 02123133001 SERVER GIVEN NAME SUNNY „ ERROR MESSAGE SERVER NAME IS MFiNDATORY
'@ -split [System.Environment]::NewLine
#endregion >>> fake reading in a text file

$Result = foreach ($IS_Item in $InStuff)
    {
    $TempBlock = ($IS_Item -split 'server')[0].trim()
    $First = (($TempBlock -split '3160')[0].Trim().Split())[-1]
    $Second = (($TempBlock -split '3160')[1].Trim().Split())[-1] -replace '\d{3}$' -replace '^0', 'C'

    '{0} {1}' -f $First, $Second
    }

$Result

output ...

MK12001 C2100123
MK13103 C2100124
MRDOOP C21005237
JPPK C2123133

what it does ...

  • fakes reading in a text file
    when doing this with real data, use Get-Content.
  • iterates thru the collection of lines
  • grabs the block that has the wanted data
  • splits out, trims, and saves the 1st target value
  • splits out, trims, and saves the 2nd target value
  • builds the output string from the 2 above values
  • sends that to the $Result collection
  • shows that collection on screen

CodePudding user response:

I suggest a regex approach based on the -match and -replace operators:

# The substring that the lines of interest must contain
$searchStr = ' 3160 211 50 '

# Filter the lines down to those of interest with -match,
# then use -replace to extract the tokens on either side of the search string.
@(Get-Content $Location) `
  -match $searchStr `
  -replace "^.  (\w )${searchStr}0(\w )\d{3} . $", '$1 C$2'

The above outputs to the display; pipe to Out-File as needed.

For an explanation of the regex and the ability to experiment with it, see this regex101.com page.

  • Related