none
Check that range of elements exist for a given code RRS feed

  • Question

  • Hello everyone,

    I need to check that all 13 elements (Al, Ca, Co, Cr etc.) for a given code (column SolutionLabel) exists. There can be more than 2 codes in the file

    For example SampleLabel 118835 must contain all 13 elements, if not the file is invalid (send mail to user)

    SolutionLabel,"Element","Soln Conc","Units"
    118835,"Al 396.152","15.7734","mg/L"
    118835,"Ca 422.673","472.334","mg/L"
    118835,"Co 238.892","0.0400198","mg/L"
    118835,"Cr 267.716","-0.0398675","mg/L"
    118835,"Cu 324.754","0.000282429","mg/L"
    118835,"Fe 238.204","4.77562","mg/L"
    118835,"Mg 285.213","48.5316","mg/L"
    118835,"Mn 257.610","0.015","mg/L"
    118835,"Ni 216.555","1.82932","mg/L"
    118835,"S 181.972","-","mg/L"
    118835,"Si 288.158","95.399","mg/L"
    118835,"Y 377.433","0","mg/L"
    118835,"Zn 213.857","0.0140007","mg/L"
    

    I can import the file (Import-Csv), but I am stuck on how I can loop through the file checking that all 13 elements exist for a given code (SolutionLabel column). 

    This is the link to the file : (link here)

    Thank you all in advance



    Wednesday, December 30, 2015 11:28 AM

Answers

  • Two pipelines.  Adjust behavior to suit required output:

    $grouped=Import-Csv .\sourceFile.csv|%{$_|Add-Member NoteProperty 'El' $_.Element.Split(' ')[0] -PassThru}| Group SolutionLabel
    $grouped | %{
          $items=($_.Group.El | sort ) -join ';'
          if($items -eq 'Al;Ca;Co;Cr;Cu;Fe;Mg;Mn;Ni;S;Si;Y;Zn'){
             Write-Host 'group matches template' -fore green
          }else{
             Write-Host 'group does not match template' -fore green
          }
    }
    


    \_(ツ)_/


    thanks alot jrv for your reply, i will modify and test. 
    • Marked as answer by Fiyo Sunday, January 3, 2016 2:25 AM
    Thursday, December 31, 2015 12:04 PM

All replies

  • One line:

    import-csv .\sourceFile.csv| group solutionlabel | ?{$_.Count -ne 13}


    \_(ツ)_/

    Wednesday, December 30, 2015 1:54 PM
  • Hi jrv

    thank you for you reply. 

    Just a count of 13 per code is not enough, I need to verify that the element Al, Ca, Co etc exist for that code. A code may contain two "Al", for example. If that is the case, the file is invalid 

    Wednesday, December 30, 2015 8:07 PM
  • Then you will have to write a script to validate each  group.

    \_(ツ)_/

    Wednesday, December 30, 2015 8:11 PM
  • hi jrv

    thats exactly my problem, I dont know "how" to do that in powershell - maybe use a Do..While, a ForEach etc...

    Thursday, December 31, 2015 4:44 AM
  • Hi Fiyo,

    You need to use, sort , foreach and switchcase.

    Here goes the rough sketch, hope you get the idea. You need to work on the logic.

    $File = Import-Csv file.csv | sort SolutionLabel $prevSolutionlabel = "999999" $count = 0

    Foreach ($data in $File) { if $data.solutionlabel -ne $prevSolutionlabel

    {

    if count -ne 0 -and count -ne 13 {break}

    else {#reset; $count = 1}

    } Switch $data { Case "A *" -like $data {$count++} Case "B *" -like $data {$count++} } $prev = $data.solutionlabel }



    Regards,

    Satyajit

    Please “Vote As Helpful” if you find my contribution useful or “Mark As Answer” if it does answer your question. That will encourage me - and others - to take time out to help you.

    Thursday, December 31, 2015 9:50 AM
  • Why do it th hard way.  It takes one line t group.  Each group element can just be compared to a template and in one line you will see all non-matches. The whole solution takes two lines of code.


    \_(ツ)_/

    Thursday, December 31, 2015 10:05 AM
  • Hi Fiyo,

    You need to use, sort , foreach and switchcase.

    Here goes the rough sketch, hope you get the idea. You need to work on the logic.

    $File = Import-Csv file.csv | sort SolutionLabel $prevSolutionlabel = "999999" $count = 0

    Foreach ($data in $File) { if $data.solutionlabel -ne $prevSolutionlabel

    {

    if count -ne 0 -and count -ne 13 {break}

    else {#reset; $count = 1}

    } Switch $data { Case "A *" -like $data {$count++} Case "B *" -like $data {$count++} } $prev = $data.solutionlabel }



    Regards,

    Satyajit

    Please“Vote As Helpful” if you find my contribution useful or “MarkAs Answer” if it does answer your question. That will encourage me - and others - to take time out to help you.

    hi Satyajit321

    thank you for your reply, it gives me an another idea of how to approach the problem

    Thursday, December 31, 2015 11:20 AM
  • Two pipelines.  Adjust behavior to suit required output:

    $grouped=Import-Csv .\sourceFile.csv|%{$_|Add-Member NoteProperty 'El' $_.Element.Split(' ')[0] -PassThru}| Group SolutionLabel
    $grouped | %{
          $items=($_.Group.El | sort ) -join ';'
          if($items -eq 'Al;Ca;Co;Cr;Cu;Fe;Mg;Mn;Ni;S;Si;Y;Zn'){
             Write-Host 'group matches template' -fore green
          }else{
             Write-Host 'group does not match template' -fore green
          }
    }
    


    \_(ツ)_/


    • Edited by jrv Thursday, December 31, 2015 11:57 AM
    Thursday, December 31, 2015 11:56 AM
  • Two pipelines.  Adjust behavior to suit required output:

    $grouped=Import-Csv .\sourceFile.csv|%{$_|Add-Member NoteProperty 'El' $_.Element.Split(' ')[0] -PassThru}| Group SolutionLabel
    $grouped | %{
          $items=($_.Group.El | sort ) -join ';'
          if($items -eq 'Al;Ca;Co;Cr;Cu;Fe;Mg;Mn;Ni;S;Si;Y;Zn'){
             Write-Host 'group matches template' -fore green
          }else{
             Write-Host 'group does not match template' -fore green
          }
    }
    


    \_(ツ)_/


    thanks alot jrv for your reply, i will modify and test. 
    • Marked as answer by Fiyo Sunday, January 3, 2016 2:25 AM
    Thursday, December 31, 2015 12:04 PM
  • Here is the simple method to find and output all good groups"

    $grouped=Import-Csv .\sourceFile.csv|%{$_|Add-Member NoteProperty 'El' $_.Element.Split(' ')[0] -PassThru}| Group SolutionLabel
    # find all good groups (change to -ne for bad groups)
    $grouped | ?{(($_.Group.El | sort ) -join ';') -eq 'Al;Ca;Co;Cr;Cu;Fe;Mg;Mn;Ni;S;Si;Y;Zn'}
    

    I am posting this to try and convince more peole to spend time learning how to use PowerShell as an advanced system and not just as a way to build fancy batch files.  These methods and others like them can make processing information much easier. 

    I tend to write fewer formal scripts in favor of just typing in instructions to the PowerShell engine and letting it do all of the work.


    \_(ツ)_/


    • Edited by jrv Thursday, December 31, 2015 12:17 PM
    Thursday, December 31, 2015 12:16 PM
  • Note that the above can be used as a filter on the original file so that we can extract good records into a new CSV file.

    \_(ツ)_/

    Thursday, December 31, 2015 12:19 PM
  • Two pipelines.  Adjust behavior to suit required output:

    $grouped=Import-Csv .\sourceFile.csv|%{$_|Add-Member NoteProperty 'El' $_.Element.Split(' ')[0] -PassThru}| Group SolutionLabel
    $grouped | %{
          $items=($_.Group.El | sort ) -join ';'
          if($items -eq 'Al;Ca;Co;Cr;Cu;Fe;Mg;Mn;Ni;S;Si;Y;Zn'){
             Write-Host 'group matches template' -fore green
          }else{
             Write-Host 'group does not match template' -fore green
          }
    }
    


    \_(ツ)_/


    thanks alot jrv for your reply, i will modify and test. 

    hi jrv

    thank you alot, the script works fine. I just modified the line :

     $items=($_.Group.El | sort ) -join ';'

    to :

    $items = ($_.Group | Select-Object -ExpandProperty El) -join ';'
    as the data in the El column was not passed to the items variable
    Sunday, January 3, 2016 2:28 AM