CSA Automated Raw File Processing
Overview
This article explains how the CSA processes raw files and any formatting changes that occur to the raw file. It also explains the logic of why rows will be removed from the raw file prior to import.
Processing Raw Files
The CSA will always attempt to process valid customer roster files if there is an import configuration in FileMage and a matching HubSpot ID attached to that customer in the CSA.
Customers who are paused or who do not yet have their files validated will still have their files processed into the CSA so that the Data Team can manipulate their configuration.
Formatting Changes
The import process will apply the following formatting changes to files prior to sending them into Aperture:
-
ZipCode: take the first 5 characters if it is a number and at least 5 characters long, else empty string
-
Gender: if not M, F, X, N (new), or T then set to U
- GradeLevel: Strip all spaces, double quotes and square brackets. If the resulting value is an integer, then use it, else use “K”.
-
We default to “K” because it comes in many formats, but the numbers are pretty consistent.
-
-
Academic & Racial Demographics: Convert “YES”/ “NO” and “TRUE”/ “FALSE” to “Y”/“N” respectively (case-insensitive). If not “Y” or “N” then use “U”.
Removing Rows
The import process will completely remove rows of data from the files under these circumstances:
- If GradeLevel is less than 0 or greater than 12 skip that row
- If GradeLevel starts with “P” then skip that row
- If Staff email doesn't contain an '@' then skip the row
- If Site, StaffLastName, StaffFirstName, StudentID, FirstName, or LastName is blank skip the row
-
The site name, staff name, student name, and student ID must not be null, ‘NULL’, or ''.
-
-
Date must be a valid date of the form
m-d-yorm/d/y