Need help with a script

This topic contains 13 replies, has 3 voices, and was last updated by Profile photo of Ricardo Bragança Ricardo Bragança 5 months ago.

  • Author
    Posts
  • #43783
    Profile photo of Ricardo Bragança
    Ricardo Bragança
    Participant

    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.

  • #43836
    Profile photo of random commandline
    random commandline
    Participant

    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?

    • #43843
      Profile photo of Ricardo Bragança
      Ricardo Bragança
      Participant

      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.

  • #43850
    Profile photo of random commandline
    random commandline
    Participant

    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}
    }
    
    • #43866
      Profile photo of Ricardo Bragança
      Ricardo Bragança
      Participant

      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

  • #43885
    Profile photo of random commandline
    random commandline
    Participant

    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}}
    }
    
    • #43899
      Profile photo of Ricardo Bragança
      Ricardo Bragança
      Participant

      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

  • #43910
    Profile photo of random commandline
    random commandline
    Participant

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

    • #43918
      Profile photo of Ricardo Bragança
      Ricardo Bragança
      Participant

      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.

    • #45852
      Profile photo of Ricardo Bragança
      Ricardo Bragança
      Participant

      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.

  • #45900
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    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
    
    • #46066
      Profile photo of Ricardo Bragança
      Ricardo Bragança
      Participant

      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.

    • #46101
      Profile photo of Ricardo Bragança
      Ricardo Bragança
      Participant

      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
      }
    • #46257
      Profile photo of Ricardo Bragança
      Ricardo Bragança
      Participant

      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.

You must be logged in to reply to this topic.