Reading and exporting lines to a headered CSV depending on a line preceeding it

bigelowcc · March 29, 2019, 8:41pm

Hey guys, back again with a brain buster for me.For those of you who assisted on my other project i very much appreciate it. i have another quandary for you guys

$reg1file = "C:\install\script\cashdrawerlogexport\REG1_oposcashdrawer.log"
$oposlogfile = "C:\Install\Script\CashDrawerLogExport\"+$StoreNo+"_"+$date+"_LineDetail_oposlog.csv"
Add-Content -Path $oposlogfile -Value '"Store","REG","Keyed_Event_Details"'
if (Test-Path $reg1file) {
write-host "##########################################################################"
write-host "Count Pattern for Total Drawer pops on REG1 and return corresponding lines"
write-host "##########################################################################"
$reg1_totalcount = (get-content $reg1file | Select-String -pattern 'Cash Drawer 1 Opened. evt','Rx pop drawer 1 from POS').Line
}

How can i take these lines and output only the lines containing ‘Cash Drawer 1 Opened. evt’ that dont have the ‘Rx pop drawer 1 from POS’ within the last 4 preceeding lines.

i.e.

,‘Rx pop drawer 1 from POS’

line 2

line 3

‘Cash Drawer 1 Opened. evt’(write this line to a csv with headers)

random-commandline · March 30, 2019, 7:38am

Will you provide sample data for REG1_oposcashdrawer.log. Replace any personal data within the sample.

postanote · March 30, 2019, 2:57pm

Well if your log file actually looks that way, meaning all those extra spaces, then they have to be checked as well.
So, extending you sample log a bit. You could do this via RegEx and the -Context switch of Get-Content.

Get-Content -Path 'E:\temp\REG1_oposcashdrawer.log'

# Results

i.e.

,'Rx pop drawer 1 from POS'

line 2

line 3

'Cash Drawer 1 Opened. evt'(write this line to a csv with headers)

line 4

line 5

line 6

line 7

'Cash Drawer 1 Opened. evt'(write this line to a csv with headers)

line 8

line 9

'Cash Drawer 1 Opened. evt'(write this line to a csv with headers)


# Read the log, Reversse check line for matching string and exclude a string
[RegEx]::Matches(((Get-Content -Path 'E:\temp\REG1_oposcashdrawer.log'  | 
Select-String -pattern 'Cash Drawer 1 Opened. evt' -Context 6,1) -notmatch 
'Rx pop drawer 1 from POS'),'Cash Drawer 1 Opened. evt').Value

#Resutls
Cash Drawer 1 Opened. evt
Cash Drawer 1 Opened. evt

Also, you never really need Write-Host to write to the screen, well, except for when you are using foreground / background colors.
Write-Output is the default and you don’t have to write that either, unless it’s your style. The below will do the same thing.

# Set a line separator / banner line
$BannerLine = ('#')*75

# Use the banner line
$BannerLine 
'Count Pattern for Total Drawer pops on REG1 and return corresponding lines'
$BannerLine 

# Results
###########################################################################
Count Pattern for Total Drawer pops on REG1 and return corresponding lines
###########################################################################

You could also do this, to make your string concat a bit less quote’y and those extra ‘+’. ;-}

$StoreNo = '24' $date = (Get-Date).ToString('mmddyyyy') $oposlogfile = "C:\Install\Script\CashDrawerLogExport\$($StoreNo)_$($date)_LineDetail_oposlog.csv" # Results $oposlogfile C:\Install\Script\CashDrawerLogExport\24_05302019_LineDetail_oposlog.csv

Yet, it’s really a personal style this as well, or standards you may need to adhere to in your org.

bigelowcc · March 31, 2019, 10:19am

Here is a snippet of the log file i am trying to parse.

as mentioned if the ‘Cash Drawer 1 Opened’ line occurs but the ‘Rx pop drawer’ line does not precede within 3-4 lines then i want to output that ‘Cash Drawer…’ Line.

The syntax of the line will always follow

[pre]

line = Rx pop drawer

line2 = device report current state

line3 = open drawer return

line4 = cash drawer 1 opened

line5 = status sent to client

line6 = cash drawer closed

line7 = status sent to client

[/pre]

The Rx force_getstate line is an error due to a hardware issue and will not always be there

[pre]

03/29/2019 01:59:54.801 [t2520] OposCashDrawer: <100004:Trace> cServerCtl::OnAcceptConnection() Server Socket Count = 3
03/29/2019 04:17:31.584 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Opened. evt=[2] State=[1]
03/29/2019 04:17:31.594 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients
03/29/2019 04:17:36.351 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Closed. evt=[2] State=[0]
03/29/2019 04:17:36.361 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients
03/29/2019 04:22:21.230 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Opened. evt=[2] State=[1]
03/29/2019 04:22:21.240 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients
03/29/2019 04:23:17.591 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Closed. evt=[2] State=[0]
03/29/2019 04:23:17.601 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients
03/29/2019 05:02:50.153 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Opened. evt=[2] State=[1]
03/29/2019 05:02:50.163 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients
03/29/2019 05:05:00.911 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Closed. evt=[2] State=[0]
03/29/2019 05:05:00.921 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients
03/29/2019 05:05:45.044 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Opened. evt=[2] State=[1]
03/29/2019 05:05:45.054 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients
03/29/2019 05:06:00.737 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Closed. evt=[2] State=[0]
03/29/2019 05:06:00.747 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients
03/29/2019 06:00:08.908 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Opened. evt=[2] State=[1]
03/29/2019 06:00:08.918 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients
03/29/2019 06:04:25.417 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Closed. evt=[2] State=[0]
03/29/2019 06:04:25.437 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients
03/29/2019 06:04:40.638 [t2520] OposCashDrawer: <100004:Trace> Rx pop drawer 1 from POS
03/29/2019 06:04:40.638 [t2520] OposCashDrawer: <100004:Trace> Device reported current state [Closed]
03/29/2019 06:04:40.658 [t2520] OposCashDrawer: <100004:Trace> OpenDrawer return = 0
03/29/2019 06:04:40.899 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Opened. evt=[2] State=[1]
03/29/2019 06:04:40.899 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients
03/29/2019 06:04:42.371 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Closed. evt=[2] State=[0]
03/29/2019 06:04:42.381 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients
03/29/2019 06:04:42.641 [t2520] OposCashDrawer: <100004:Trace> Rx ‘FORCE_GETSTATE’ from POS
03/29/2019 06:04:42.641 [t2520] OposCashDrawer: <100004:Trace> ForceGetState: Status sent to 1 clients
03/29/2019 06:08:10.620 [t2520] OposCashDrawer: <100004:Trace> Rx ‘FORCE_GETSTATE’ from POS
03/29/2019 06:08:10.620 [t2520] OposCashDrawer: <100004:Trace> ForceGetState: Status sent to 1 clients
03/29/2019 06:08:50.237 [t2520] OposCashDrawer: <100004:Trace> Rx pop drawer 1 from POS
03/29/2019 06:08:50.237 [t2520] OposCashDrawer: <100004:Trace> Device reported current state [Closed]
03/29/2019 06:08:50.237 [t2520] OposCashDrawer: <100004:Trace> OpenDrawer return = 0
03/29/2019 06:08:50.748 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Opened. evt=[2] State=[1]
03/29/2019 06:08:50.748 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients
03/29/2019 06:09:00.232 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Closed. evt=[2] State=[0]
03/29/2019 06:09:00.242 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients
03/29/2019 06:11:06.944 [t2520] OposCashDrawer: <100004:Trace> Rx ‘FORCE_GETSTATE’ from POS
03/29/2019 06:11:06.944 [t2520] OposCashDrawer: <100004:Trace> ForceGetState: Status sent to 1 clients
03/29/2019 06:12:29.192 [t2520] OposCashDrawer: <100004:Trace> Rx pop drawer 1 from POS
03/29/2019 06:12:29.192 [t2520] OposCashDrawer: <100004:Trace> Device reported current state [Closed]
03/29/2019 06:12:29.192 [t2520] OposCashDrawer: <100004:Trace> OpenDrawer return = 0
03/29/2019 06:12:29.553 [t2520] OposCashDrawer: <100004:Trace> Cash Drawer 1 Opened. evt=[2] State=[1]
03/29/2019 06:12:29.553 [t2520] OposCashDrawer: <100004:Trace> Status sent to 1 clients

[/pre]

Olaf · March 31, 2019, 10:57am

Please go back and edit your post again to fix the formatting of the code. Use the code tag button (pre) for it. Thanks

bigelowcc · March 31, 2019, 11:04am

[quote quote=147953]Please go back and edit your post again to fix the formatting of the code. Use the code tag button (pre) for it. Thanks

[/quote]
Thanks, i did not even notice since

Olaf · March 31, 2019, 11:52am

I’m pretty unsure if I got you right … try if this logic applies to your requirement:

Select-String -Path E:\temp\REG1_oposcashdrawer.log -Pattern  ‘Cash Drawer 1 Opened’ -Context 3 |

Where-Object {$_.Context.PreContext[0] -notmatch  ‘Rx pop drawer’ } |

Select-Object -Property LineNumber,Line

postanote · March 31, 2019, 1:17pm

BigelowCC35, thx for the update, but my response still applies. Just change the -context numbers to whatever you need. In your case 3,1.
If you want more of the line, just change that match string, or Or Olaf’s answer. They both do the same thing, capturing the string you are after based on your criteria. How much of that line you’d like is up to you.

bigelowcc · March 31, 2019, 3:47pm

@postanote

Can you give me a little detail on how context works in this aspect. I looked at the microdocs for it but was having a hard time understanding.

bigelowcc · March 31, 2019, 4:29pm

[quote quote=147960]I’m pretty unsure if I got you right … try if this logic applies to your requirement:

PowerShell

3 lines

1

2

3

Select-String -Path E:\temp\REG1_oposcashdrawer.log -Pattern 'Cash Drawer 1 Opened' -Context 3 |

Where-Object {$_.Context.PreContext[0] -notmatch 'Rx pop drawer' } |

Select-Object -Property LineNumber,Line

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

[/quote]

This works perfectly however now i need to be able to append each line to a csv file under a header specified like

[pre]

“Store”,“REG”,“Keyed_Event_Details”

“9999”,“REG1”,"\line returned from SLS"

[/pre]

i am able to place the data alone in the file without effort but i seem to be doing something wrong when running it under a specific header

bigelowcc · March 31, 2019, 4:59pm

[pre]

$reg1file = “C:\install\script\cashdrawerlogexport\REG1_oposcashdrawer.log”
$oposlogfile = “C:\Install\Script\CashDrawerLogExport"+$StoreNo+”_"+$date+"LineDetail_oposlog.csv"
Add-Content -Path $oposlogfile -Value ‘“Store”,“REG”,“Keyed_Event_Details”’
if (Test-Path $reg1file) {
write-host “##########################################################################”
write-host “Count Pattern for Total Drawer pops on REG1 and return corresponding lines”
write-host “##########################################################################”
$reg1_content = select-string -Path $reg1file -pattern ‘Cash Drawer 1 Opened’ -Context 3,1 | Where-Object {$.Context.PreContext[0] -notmatch ‘Rx pop drawer 1’ } | Select-Object -Property LineNumber,Line
}
#Export-Csv -Path $oposlogfile -InputObject $reg1_content -property Store,REG,Keyed_Event_details -Append
foreach ($line in $reg1_content){
$oposlog = @(
[pscustomobject]@{
Store = $StoreNo
REG = ‘REG1’
Keyed_Event_Details = $line.Line
}
)
}

$oposlog | export-csv -path $oposlogfile -append -NoTypeInformation[/pre]

This is what i have so far but this only prints the last line found to the csv file not each line found

bigelowcc · March 31, 2019, 8:43pm

Olaf, Thanks for the info it really helped me work through this. i have figured out after ripping my hair out that i simply had to put the export cmd into the foreach statement instead of outside. i.e.

[pre]

$reg1_content = select-string -Path $reg1file -pattern ‘Cash Drawer 1 Opened’ -Context 3,1 | Where-Object {$_.Context.PreContext[0] -notmatch ‘Rx pop drawer 1’ } | Select-Object -Property Line
}
foreach ($line in $reg1_content){
$oposlog = @(
[pscustomobject]@{
Store = $StoreNo
REG = ‘REG1’
Keyed_Event_Details = $line.line
}
$oposlog | export-csv -path $oposlogfile -append
)
}

[/pre]

those looking over this after the fact the $line.line is there because if i dont declare the .line it will return not only the data but the line number as well and that was just extraneous data i did not need.

This works perfectly now. I appreciate all the help you guys have given in the project of mine. It will certainly save us a big headache =)

Topic		Replies	Views
[New to Powershell] Need help in exporting data to csv/html PowerShell Help	4	176	November 12, 2013
Exporting to CSV PowerShell Help	4	119	July 25, 2015
ForEach Loop Export to CSV file PowerShell Help	3	192	December 15, 2018
Help with creating csv file PowerShell Help	3	184	April 15, 2022
Needs some CSV help for some complex info PowerShell Help	4	140	August 25, 2014

Reading and exporting lines to a headered CSV depending on a line preceeding it

Related Topics