none
CSV Maniputation RRS feed

  • Question

  • I need to take an exported CSV file with student information and put it in a new csv file with only the information from students grade 3-12 while replacing the school code with a name and adding their email domain at the end of their username.  I can get all of the formatting right, but no matter what I try, I can't get it to filter out grade -1 to 2.

    My exported file would be in the format that follows

    8,Jane,Doe,janed,123,,janed
    2,Joe,Boxer,joeb,246,,joeb
    11,John,Doe,johnd,8087,,johnd

    What I need it to look like is

    First Name,Last Name,Username,School,Type,Email
    Jane,Doe,janed,Mighty Middle School,s,janed@school.org
    John,Doe,johnd,Happy High School,s,johnd@school.org

    I have tried this to just get the students in the grades I need

    $d = import-csv C:\file.csv -Header "Grade","First Name","Last Name","Username","School","Type","Email" | where {($_.Grade -ne -1) -or ($_.Grade -ne 0) -or ($_.Grade -ne 1) -or ($_.Grade -ne 2)} | export-csv C:\file2.csv

    I have also tried piping it in with the rest of the commands that I can make work.

    $d = Import-Csv C:\roster.csv -Header "Grade","First Name","Last Name","Username","School","Type","Email"
    d | where {($_.Grade -ne -1) -or ($_.Grade -ne 0) -or ($_.Grade -ne 1) -or ($_.Grade -ne 2)} | select "First Name","Last Name","External ID", @{Name="School";Expression={$_.School -replace("8087", "Happy High School") -replace("123","Mighty Middle School") -replace("246","Early Elementary School")}}, @{Name="Type" ;Expression={$_."Type" -replace("","s")}}, email | Foreach {$_.email = "$($_.email)@school.org";$_} | Export-csv C:\final.csv

    I'm not sure where what I'm missing.

    Tuesday, October 16, 2018 5:41 PM

Answers

  • Import-Csv C:\file.csv -Header Grade,FirstName,LastName,Username,School,Type,Email |
        where {$_.Grade -notmatch '-1|0|1|2' } |
        Export-Csv C:\file2.csv


    \_(ツ)_/

    • Marked as answer by NetAdminRob Tuesday, October 16, 2018 7:27 PM
    Tuesday, October 16, 2018 6:34 PM

All replies

  • Import-Csv C:\file.csv -Header Grade,FirstName,LastName,Username,School,Type,Email |
        where {$_.Grade -notmatch '-1|0|1|2' } |
        Export-Csv C:\file2.csv


    \_(ツ)_/

    • Marked as answer by NetAdminRob Tuesday, October 16, 2018 7:27 PM
    Tuesday, October 16, 2018 6:34 PM
  • Thanks, that did the trick.
    Tuesday, October 16, 2018 7:27 PM