Author Posts

June 17, 2016 at 9:01 pm

Good evening,

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

YYYYY | BBBBB | 11111 | blue | cccccc |
XXXXX | YYYYY | 11111 | AAA@gmail.com | BBBBB |
ZZZZZ | CCCCC | 11111 | yellow | DDDDD |
.
.
.
YYYYY | BBBBB | 22222 | blue | cccccc |
XXXXX | YYYYY | 22222 | AAA@hotmail.com | BBBBB |
ZZZZZ | CCCCC | 22222 | yellow | DDDDD |
.
.
.
YYYYY | BBBBB | 33333 | blue | cccccc |
XXXXX | YYYYY | 33333 | AAA@sapo.pt | BBBBB |
ZZZZZ | CCCCC | 33333 | yellow | DDDDD |

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.

June 18, 2016 at 1:21 pm

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?

June 18, 2016 at 2:04 pm

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:

YYYYY | BBBBB | 33333 | blue | cccccc |
XXXXX | YYYYY | 33333 | AAA@sapo.pt | BBBBB |
ZZZZZ | CCCCC | 33333 | yellow | DDDDD |

The goal is find the line that has an email in text file :
XXXXX | YYYYY | 33333 | AAA@sapo.pt | BBBBB |

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

YYYYY | BBBBB | 33333 | blue | cccccc |
XXXXX | YYYYY | 33333 | AAA@sapo.pt | BBBBB |
ZZZZZ | CCCCC | 33333 | yellow | DDDDD |

for another file .CSV or .TXT.

thank you.

June 18, 2016 at 7:21 pm

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}
}

June 18, 2016 at 11:16 pm

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

June 19, 2016 at 12:32 pm

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}}
}

June 19, 2016 at 4:54 pm

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

June 19, 2016 at 6:35 pm

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

June 19, 2016 at 8:03 pm

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.

July 5, 2016 at 5:22 pm

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.

July 5, 2016 at 11:27 pm

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

July 7, 2016 at 8:48 am

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.

July 7, 2016 at 2:22 pm

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
}

July 8, 2016 at 2:32 pm

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.