The script below assumes that your PDF files are named after the Student ID. They also need to be sorted by Year, School, and Term. It can also be helpful to know the Report Card Type for display purposes.


Some options must be set near the top to define the report cards being imported.


As always, please test this in a Sandbox copy of your database first.


#Written by James Lancaster
#c. 2020 Aeries Software
#Last updated: 4/24/2020

$FolderPath = "D:\ReportCards\2019 - 2020\994\t3\" #Complete path to report cards

$SQLServer = "SERVERNAME" #SQL Server name
$SQLlogin = "" #SQL Login (not needed for Windows Auth)
$SQLpass = "" #SQL password
$SQLDB = "DST19000AeriesDemo" #Database

$SCL = 994 #School Code
$YR = 2019 #Academic Year
$TM = 3 #Term
$TY = "SBG" #Report Card Type (Options are SBG or GRD)
$CL = "00" #Report Card Language (00 is English)
$TMD = "3rd Trimester" #Term Description

#Uncomment one of these lines below. One is to use your current Active Directory (Windows Auth) credentials,
#and the other is standard SQL authentication.

$DB = "server=$SQLServer;database=$SQLDB;Trusted_Connection=True;" #Use Windows auth
#$DB = "server=$SQLServer;database=$SQLDB;user id=$SQLlogin;password=$SQLpass;" #Use SQL Auth

#Insert these report cards into RCH table
$conn = new-object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = $DB
$conn.Open()
$cmd = new-object System.Data.SqlClient.SqlCommand
$cmd.Connection=$conn
$cmd.Parameters.Add("@FileData", $file)
${RCHfiles} = Get-ChildItem -Recurse $FolderPath | Where { ! $_.PSIsContainer } 
 foreach (${RCHfile} in $RCHfiles) {
 [byte[]]$file = Get-Content $RCHfile.FullName -Encoding byte -Raw
 ${filepath} = $RCHfile.FullName -replace $RCHfile,"";
 ${filename} = [System.IO.Path]::GetFileNameWithoutExtension(${RCHFile})
 $cmd.CommandText="insert into rch (ID, SCL, YR, TM, TY, REN, CL, TMD, DT, GR)
select ${filename}, '$SCL', '$YR', '$TM', '$TY', @FileData, '$CL', '$TMD', getdate(), (select distinct top 1 ISNULL(GR, 0) from enr where id = ${filename} and yr = '$YR' and del = 0);"
 $cmd.Parameters["@FileData"].Value = $file
 $cmd.ExecuteNonQuery()
}
Generic