Compare 2 files

Tagged: 

This topic contains 3 replies, has 3 voices, and was last updated by Profile photo of Alan Willis Alan Willis 3 months, 3 weeks ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #40927
    Profile photo of Alan Willis
    Alan Willis
    Participant

    Good Afternoon
    I start my day with 2 slices of humble pie with my coffee which is a nice way to say I know my limitations and when to ask for help and advice.
    I work with a small school system and have run into a minor challenge
    I download an error report from our Dept. of Education in a csv format, we have an alternative school program which has a separate school ID. I am able to export the active students from our Student Information System for the alternative school our extract for the DOE from our SIS does not include the Alternative School Program instead we use a school override code to report the student in the school they would be attending.
    my goal is to use the Get-Content function for the csv list of the active students in the Alternative Program compare the student SSAN from the Alternative School List to the Student SSAN in the error report downloaded from our DOE and output the record to a separate file.
    there are numerous errors that can be present for each student from attendance, discipline, course, etc. I need to pass the value from the Get-content and compare it to the entire error list and output each record found. the column heading for the DOE file are:
    SYSTEM_ID SCHOOL_ID STUDENT_ID STUDENT_NAME GRADE_LEVEL COURSE_NUMBER ERROR_CODE DESCRIPTION FIELD_NAME FIELD_CONTENT RECORD_TYPE MODIFIED_DATE

    I can adjust my SIS export to include school number and student SSAN which is our Student ID
    I went back and reviewed the MVA Jumpstart for PowerShell 3.0 presented by Jason Helmick and Jeffery Snover
    I am sure this is one of the real world opportunities they were talking about 🙂
    any and all help appreciated and thanks for taking the time to look this problem over.

    Sincerely
    Alan

    #40942
    Profile photo of random commandline
    random commandline
    Participant

    What are the headers for both csv files? Are you looking for student ids not located in both files?
    If both have a similar header, something like this should work. I created some sample data here.

    $errorreport = "SYSTEM_ID SCHOOL_ID STUDENT_ID
    123 345 45678
    123 345 45679
    123 345 45680
    123 345 45681
    123 345 45682" -split "`n"
    
    $altschoolist = "SYSTEM_ID SCHOOL_ID STUDENT_ID
    123 345 45686
    123 345 45681
    123 345 45678
    123 345 45683
    123 345 45689" -split "`n"
    
    $errorreport = $errorreport | ConvertFrom-Csv -Delimiter ' '
    $altschoolist = $altschoolist | ConvertFrom-Csv -Delimiter ' '
    
    foreach ($line in $altschoolist){
        If ($errorreport.student_id -notcontains $line.student_id){$line}}
    
    # Results:
    # SYSTEM_ID SCHOOL_ID STUDENT_ID
    # --------- --------- ----------
    # 123       345       45686
    # 123       345       45683
    # 123       345       45689
    
    #40944
    Profile photo of Jonathan Warnken
    Jonathan Warnken
    Participant

    Having two csv files makes your job a little easier. The process is to import each file and then select the record from each and do your comparison. Then send your results to the report file.

    For example
    doe.csv
    SYSTEM_ID,SCHOOL_ID,STUDENT_ID,STUDENT_NAME,GRADE_LEVEL,COURSE_NUMBER,ERROR_CODE,DESCRIPTION,FIELD_NAME,FIELD_CONTENT,RECORD_TYPE,MODIFIED_DATE
    1,1,1,Some Student1,1,1,0,Some Decription,Some Field name, some field content,1,01/01/2016
    2,1,2,Some Student2,1,1,0,Some Decription,Some Field name, some field content,1,01/01/2016

    sis.csv
    SYSTEM_ID,SCHOOL_ID,STUDENT_ID,STUDENT_NAME,GRADE_LEVEL,COURSE_NUMBER,ERROR_CODE,DESCRIPTION,FIELD_NAME,FIELD_CONTENT,RECORD_TYPE,MODIFIED_DATE
    1,1,1,Some Student1,1,1,0,Some Decription,Some Field name, some field content,1,01/01/2016
    2,1,2,Some Student2,1,1,0,Some Decription,Some Field name, some field content,1,01/01/2016

    $doe = import-csv .\doe.cvs
    $sis = import-csv .\sis.cvs
    if(test-path .\out.csv){remove-item .\out.csv}
    $sis|foreach{
           $doerecord = $doe|where-object Student_ID -eq $_.Student_ID
           # your code here 
          
           #build the record for output 
           $studentreport = @{
               Studend_ID = $_.student_id
               SCHOOL_ID = $doe.SCHOOL_ID
            }
           $Object = New-Object PSObject -Property $studentreport
           $Object|export-csv .\out.csv -Append
    }
    

    This shows how to load the two files and build the output on elements from each. Hopefully this will get you started.

    #41120
    Profile photo of Alan Willis
    Alan Willis
    Participant

    Thanks to both of you for the suggestions
    I will give them a try and let you know how it goes.

    thanks again for the help
    AW

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.