Author Posts

December 1, 2017 at 11:37 am

Hi there,

I am new in Powershell scripting, so sorry for the questions.
I am busy writing a script and need some help. I will try explaining step by step what I want to do:

1. I am pulling data from a DB into a flat file (c:\temp\db.txt). The info what is in the file:
Output:
Submitted/Executing Jobs
ORDERID NAME TYPE ODATE PID RUNCNT
——– —————– —– ——– —————- ——
00000005|Test |FLD |20171201| |22 |
00000006|sleep |CMD |20171201|12688 |22 |

Result: Success.

2. I now need to grab the one line and then grab the one column / string. I have defined doing this in Powershell, and here my line I use and I pump that into a flat file, c:\temp\output.txt:
– My command:
cat c:\temp\db.txt | where {$_-match "sleep"} > c:\temp\output.txt
– output of the file:
00000006|sleep |CMD |20171201|12688 |22 |

Problem:
I need to grab now the first 8 characters on this line and pipe it to a variable which I will then use to execute an external command using this variable.

I tried the following: (I use output file to see if I pass the correct information – I need ot pass it as a variable)
cat c:\temp\db.txt | where {$_-match "sleep"} | foreach{$_.split()[0]} > c:\temp\output.txt
But the output bring me the following:
00000006|sleep
So it sees this as 1 string.

1- How can I get only those characters? (00000006)
2- How can I pass that to a variable which I then can use again for external execution?

December 1, 2017 at 1:05 pm

You seem like someone who came from a long background of working with Linux where we do a lot of text parsing and manipulation. My advise is not to try to use PowerShell in the same manner. PowerShell like Windows deals in objects not text. Unlike text, objects have properties and methods that lend themselves very handy in scripting and processing.

Having said that I recommend pulling the data out of the DB and processing it from there bypassing the unnecessary step of dumping it into text file (I'm assuming that the only reason we're using a text file is to read, parse, and process the data).

You'll see that the code I will recommend will take the data from the text file and turns it back to more structured objects.

For example, in this code block:

$TextFile = '.\db1.txt'
$CSVFile = '.\db1.csv'

$myTextLines = Get-Content $TextFile
$myTextLines[0].Split(' ').Trim() -join ',' | Out-File $CSVFile 
2..($myTextLines.count-1) | % { $myTextLines[$_].Split('|').Trim() -join ',' | Out-File $CSVFile -Append }

$myRecordSet = Import-Csv $CSVFile 
$myRecordSet | FT -a 

where .\db1.txt content is

ORDERID NAME TYPE ODATE PID RUNCNT
——– —————– —– ——– —————- ——
00000005|Test |FLD |20171201| |22 |
00000006|sleep |CMD |20171201|12688 |22 |

.\db1.csv content will be

ORDERID,NAME,TYPE,ODATE,PID,RUNCNT
00000005,Test,FLD,20171201,,22,
00000006,sleep,CMD,20171201,12688,22,

and console output will be

ORDERID  NAME  TYPE ODATE    PID   RUNCNT
-------  ----  ---- -----    ---   ------
00000005 Test  FLD  20171201       22    
00000006 sleep CMD  20171201 12688 22    

Now this is a lot easier to process than parsing text every time you need to use a data element..
For example, getting the 'orderid' where 'name' is 'sleep' is simply:

PS D:\Sandbox> ($myRecordSet | ? { $_.Name -eq 'sleep' }).orderid
00000006

You're simply leveraging the way PS was designed to work..
To use that data as an executable:

& ($myRecordSet | ? { $_.Name -eq 'sleep' }).orderid

December 4, 2017 at 6:00 pm

Did someone say "cut"?

function cut {
  param(
    [Parameter(ValueFromPipeline=$True)] [string]$inputobject,
    [string]$delimiter='\s+',
    [string[]]$field
  )

    process {
    if ($field -eq $null) { $inputobject -split $delimiter } else {
      ($inputobject -split $delimiter)[$field] }
  }
}

PS C:\> echo hi:there | cut -d : -f 1
there

December 6, 2017 at 6:59 am

Hi Sam,

Thank you for the code. Yes, I am from a UNIX back ground so bit different on Windows 🙂

I have tried your code and made a small change, now it is the final bash 🙂

CODE:
$TextFile = 'c:\temp\db1.txt'
$CSVFile = 'c:\temp\db1.csv'
$myTextLines = Get-Content $TextFile
$myTextLines[0].Split(' ').Trim() -join ',' | Out-File $CSVFile
2..($myTextLines.count-1) | % { $myTextLines[$_].Split('|').Trim() -join ',' | where {$_-match "sleep"} | Out-File $CSVFile -Append }
$myRecordSet = Import-Csv $CSVFile
$myRecordSet | FT -a

OUTPUT:
PS C:\Temp> C:\Temp\script.ps1

Output:
——-
00000006

Question:
How do I now echo "00000006" as a variable? (let's say variable name is ORDERID)
I will need to execute an external command from the same script like this:
&"C:\test\script.exe" %%ORDERID

December 6, 2017 at 7:24 am

Hello.

You can use this

Code:
$ORDERID = $myRecordSet.ORDERID
$ORDERID

Output:
00000006

Or just use $myRecordSet.ORDERID as the variable.

December 6, 2017 at 8:51 am

Hi Anders,

The Output should not be there
Output:
00000006

I need to have only the "00000006" as a variable

December 6, 2017 at 9:43 am

Hello again,

If you try and run the code, you will see that it only prints whats inside the variable.
Output is not shown.

Output is just written so you could see what the code was and what the output is.

December 6, 2017 at 12:56 pm

$TextFile = '.\db1.txt'
$CSVFile = '.\db1.csv'
$myExePath = 'C:\windows\notepad.exe'

$myTextLines = Get-Content $TextFile
$myTextLines[0].Split(' ').Trim() -join ',' | Out-File $CSVFile 
2..($myTextLines.count-1) | % { $myTextLines[$_].Split('|').Trim() -join ',' | Out-File $CSVFile -Append }

$myRecordSet = Import-Csv $CSVFile 

$myArg = ($myRecordSet | ? { $_.Name -eq 'sleep' }).orderid

& $myExePath $myArg

December 8, 2017 at 8:36 am

Guys,

I got so far at the moment as the other coding seems not to work as it should

So here the code:
cat c:\temp\db1.txt | where {$_-match "sleep"} | foreach{$_.split()[0]} > c:\temp\ORDERID.txt
Get-Content .\ORDERID.txt | % { ($_.Split('|'))[0] } > C:\Temp\ORDERID1.txt
Content of ORDERID1.txt is the value I am looking for (example of the file) – THIS WORKS 100%
0000000i

So how do I put this output as a variable?? (Lets say varibale name is "TEST"

And I need to run a command:
&"c:\temp\test.exe" %%TEST

December 8, 2017 at 12:40 pm

Hello,

This will work.

$TextFile = 'c:\temp\db1.txt'
$CSVFile = 'c:\temp\db1.csv'
$myTextLines = Get-Content $TextFile
$myTextLines[0].Split(' ').Trim() -join ',' | Out-File $CSVFile
2..($myTextLines.count-1) | % { $myTextLines[$_].Split('|').Trim() -join ',' | where {$_-match "sleep"} | Out-File $CSVFile -Append }
$myRecordSet = Import-Csv $CSVFile

$test = $myRecordSet.ORDERID

cmd.exe /C c:\temp\test.exe %%$test

If you try and run the $test you will see it only contains 00000006

December 12, 2017 at 7:50 am

Hi Guys, Thank you for your help so far.

I have one last question on this. In my txt file that I am reading, I need to search for an exact string.

Here is the script so far:
$source = "C:\temp\dboutput.txt"
$destination = "C:\temp\output.txt"
$SearchString = "HENK_Test01_TST_Uptime_Process"
Get-Content $source | foreach{$_.split()[0]} | where {$_-match "HENK"} > $destination

Output file looks like this:
000004c4|HENK_Test01_TST_Uptime_Process|CMD
000004c5|HENK_Test01_TST_Uptime_Program|CMD

I only want to see the line:
000004c4|HENK_Test01_TST_Uptime_Process|CMD