Need help with a script

ricardo-braganca · June 17, 2016, 3:31pm

Good evening,

I plan to create a script to go to a folder where several txts-filled with data like this:

The goal is to seek the line where it appears the mail and pass not only the line but also the lines that have the same numeric field to another txt or csv.
So far I can find the mail and passes it to a csv but I could also bring the lines with the numerical value equal to that of the mail line.
get-childitem C: \ Usersxxxxx \ Desktop \ NEW \ * -include * .txt -recurse | select-string -pattern “@” | Export-Csv -path C: \ Users \ xxxxxx \ Desktop \ NEW \ dados.csv
Can you help me?
Thank you.

random-commandline · June 18, 2016, 7:51am

Does the pipe symbol,‘|’, separate the fields in each text file? What do you mean by ‘lines that have same numeric field’? What lines do you need to capture from each text file other than lines with email addresses?

ricardo-braganca · June 18, 2016, 8:34am

Sorry if I did not explain myself properly.

Yes, the pipe symbol ‘|’ separate the field in each line of the text file.

for the second and third question:

in the all lines of the text file have something like this:

and copy all the lines having the same contract number in this case:

for another file .CSV or .TXT.

thank you.

random-commandline · June 18, 2016, 1:51pm

A csv file will be created for each contract number.

$files = Get-ChildItem \\path\to\textfiles -Filter *.txt -Recurse

# Group objects by number
foreach ($file in $files){
   $groups = Get-Content $file.FullName | ConvertFrom-Csv -Delimiter '|' -Header H1,H2,H3,H4,H5 |
   Group-Object -Property H3
   $groups | ForEach-Object {$_.group |
   Export-Csv "\\path\to\textfiles\$($_.Name).csv" -NoTypeInformation}
}

ricardo-braganca · June 18, 2016, 5:46pm

Good evening,
first of all I thank the attention given to this subject.

I wanted to say I’ve tested it and made the necessary adjustments, but there is a problem I could not solve.

the script gathers data in documents with the same contract number, but I just want the documents that have email.

Thank you

random-commandline · June 19, 2016, 7:02am

There may be a more efficient way of doing this with less code, but I think this is what you are looking for.

$files = Get-ChildItem \\path\to\textfiles -Filter *.txt -Recurse

# Group objects by number
foreach ($file in $files){
   $groups = Get-Content $file.FullName | ConvertFrom-Csv -Delimiter '|' -Header H1,H2,H3,H4,H5 |
   Group-Object -Property H3
   $groups | ForEach-Object {If ($_.group.H4 -like '*@*'){
   $_.group | Export-Csv "\\path\to\textfiles\$($_.Name).csv" -NoTypeInformation}}
}

ricardo-braganca · June 19, 2016, 11:24am

Setting the code to my reality the end result is this . I do not know if it’s the right one but it does what I needed.
Thank you by the help.

$files = Get-ChildItem C:\Users\ricar\Desktop\new\ -Filter *.txt -Recurse
$CSVFolder = ‘C:\Users\ricar\Desktop\new*.csv’
$OutputFile = ‘C:\Users\ricar\Desktop\new\dados.txt’

Group objects by number

foreach ($file in $files){
$groups = Get-Content $file.FullName | ConvertFrom-Csv -Delimiter ‘|’ -Header H1,H2,H3,H4,H5,H6,H7,H8,H9,H10,H11,H12,H13,H14,H15,H16,H17,H18,H19,H20,H21,H22,H23,H24,H25,H26,H27,H28,H29,H30,H31,H32,H33,H34,H35,H36,H37,H38,H39,H40,H41 |
Group-Object -Property H9
$groups | ForEach-Object {If ($.group.H29 -like ‘@’){
$.group | Export-Csv “C:\Users\ricar\Desktop\new$($_.Name).csv” -NoTypeInformation}}
}

#join files in txt
$CSV= @();

Get-ChildItem -Path $CSVFolder -Filter *.csv | ForEach-Object {
$CSV += @(Import-Csv -Path $_)

}

$CSV | Export-Csv -Path $OutputFile -NoTypeInformation -Force

#Remove csv files

Remove-Item -Path $CSVFolder -Filter *.csv -Force -Recurse

#Replace ‘“,”’ for “|”
(Get-Content -Path $OutputFile ) |
Foreach-Object {$_ -replace ‘“,”’,‘|’} |
Out-File $OutputFile

random-commandline · June 19, 2016, 1:05pm

I understand now. I didn’t know more than five fields existed in each text file.

ricardo-braganca · June 19, 2016, 2:33pm

The number of fields is not important, it is easy to customize, the important thing is the logic behind and you helped me a lot.
Tank You.

ricardo-braganca · July 5, 2016, 11:52am

Good afternoon,

I had a problem with the previous code as the input file structure is different from the output file structure. And then I managed to get here.

$files = Get-ChildItem "C:\Users\ricardo.braganca\Desktop\NEW\*.txt"
$folder = "C:\Users\ricardo.braganca\Desktop\NEW\dados*.txt"
$count = $null

switch -Regex -File $files.fullname
{
    'MAI000' {$count++;Write-Verbose "Group $count found" -Verbose}
    '^FE|^01' { $_ |Add-Content -Path "C:\Users\ricardo.braganca\Desktop\NEW\dados$($count).txt"}
    'MAIFIM' {continue}
}


Get-Content -Path $folder | where {$_ -match "@" | Out-File C:\Users\ricardo.braganca\Desktop\NEW\1.txt}

However I can not put the contents of the various .txt files that have the “@” in the text in another file. this time I have this and I am not able to get the result.

curtis-smith · July 5, 2016, 5:57pm

Your closing curly brace for your where-object cmdlet is in the wrong spot.

Get-Content -Path $folder | where {$_ -match "@" | Out-File C:\Users\ricardo.braganca\Desktop\NEW\1.txt}

#should be

Get-Content -Path $folder | where {$_ -match "@"} | Out-File C:\Users\ricardo.braganca\Desktop\NEW\1.txt

ricardo-braganca · July 7, 2016, 3:18am

Hi Curtis,

In a file I have a lots of documents. The document is this starts at “MAI000” and ends in “MAIFIM”.
:

01|  |4620-583| 6001368887|00|0001|LIB|MAI000||700|1CA|0|
01|  |4620-583| 6001368887|00|1001|LIB|CBC001|| |4620-|
01|  |4620-583| 6001368887|00|2101|LIB|MAICA0||92|96093|N| |||N|S|9162|E004| | |N| | | | |S|N|N| 
01|  |4620-583| 6001368887|00|2001|LIB|MAIITP||CUI|PT1601000000404853QM|
01|  |4620-583| 6001368887|00|3001|LIB|MAIDTP||| | |
01|  |4620-583| 6001368887|00|3002|LIB|MAIDTP||2|0.06100000|/kWh|
01|  |4620-583| 6001368887|00|3003|LIB|MAIDTP|||0.10410000||
01|  |4620-583| 6001368887|00|3004|LIB|MAIDTP||| | |
01|  |4620-583| 6001368887|00|3005|LIB|MAIDTP|||0.06100000|/kWh|
01|  |4620-583| 6001368887|00|3006|LIB|MAIDTP|||0.10540000||
01|  |4620-583| 6001368887|00|4001|LIB|MAIFTP|| |
01|  |4620-583| 6001368887|00|9999|LIB|MAIFIM|| |
FE|  |3025-493| 6000372175|00|0001|LIB|MAI000||1CA|0|
FE|  |3025-493| 6000372175|00|1001|LIB|CBC001|15| |COSTA| | | | | | | |C10366854| |06|
FE|  |3025-493| 6000372175|00|2102|LIB|MAICA0||91|11000|N| |||N|S||*@S*| 
01|  |3025-493| 6000372175|00|2001|LIB|MAIITP||
01|  |3025-493| 6000372175|00|3001|LIB|MAIDTP|| | |
01|  |3025-493| 6000372175|00|3002|LIB|MAIDTP||0.06540000||
01|  |3025-493| 6000372175|00|3003|LIB|MAIDTP|||0.06810000||
01|  |3025-493| 6000372175|00|4001|LIB|MAIFIM|| |

My problem right now is that is returne only line that has “* @ *” and not the entire document.

With this code I wont search in txt file for a line how have “@” and return the the entire document
starts at “MAI000” and ends in “MAIFIM” how have the “@” only.

ricardo-braganca · July 7, 2016, 8:52am

Hi Curtis,´

Has almost managed to get the expected result, however there is a problem I am having some difficulties to solve it has to do with special characters such as “à” and “ç”, which comes in the text replaced by “?”.

I’ve tried the -encoding unicode, UTF8, UTF32 and now the ASCII but not about the characters. Anyway here is the code I have at the moment.

$files = Get-ChildItem "C:\Users\ricar\Desktop\NEW\*.txt"
$folder = "C:\Users\ricar\Desktop\NEW\dados*.txt"
$count = $null

switch -Regex -File $files.fullname
{
    'MAI000' {$count++;Write-Verbose "Group $count found" -Verbose}
    '^FE|^01' { $_ |Add-Content -Path "C:\Users\ricar\Desktop\NEW\dados$($count).txt" -Encoding Ascii }
    'MAIFIM' {continue}
}

Get-ChildItem -path $folder -Recurse |

Where-Object {
    
      (Select-String -InputObject $_ -Pattern '@' -Quiet) -eq $true
} |

ForEach-Object {
   
    Get-Content $_ | Add-Content C:\Users\ricar\Desktop\NEW\dados.txt -Encoding Ascii
}

ricardo-braganca · July 8, 2016, 9:02am

Hi curtis,

I have figured out what the problem is, the initial file is in UNIX ANSI and I’m not able to put the OUTPUT with the same format. Any idea to solve this? I’ve googled and found nothing so far.

Topic		Replies	Views
Search in file PowerShell Help	10	119	May 16, 2024
Help searching through text file PowerShell Help	8	207	May 16, 2024
search a file for multiple strings and output to another file PowerShell Help	3	173	May 16, 2024
regex PowerShell Help	9	226	May 16, 2024
Some basic staff and parsing help PowerShell Help	4	162	May 16, 2024

Need help with a script

Group objects by number

Related Topics