Compare 2 files

PowerShell Help
#1

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 :slight_smile:
any and all help appreciated and thanks for taking the time to look this problem over.

Sincerely
Alan

#2

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
#3

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.

#4

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