This is going to be a somewhat high level overview of how we get lunch status and balance information from our food service software to our SIS. I’m not going to cover all the tiny details because if you’re confident in dealing with raw data and ftp servers you’re already advanced enough to know and the process will vary depending on your software stack. In our case we use Nutrikids which exports a file through what they call a ‘Call Agent Export’ that is uploaded to our communication vendor for lunch balance notifications. It’s setup as a CSV file with only a few columns/fields:
Since this data was already being created in a file each day I figured we could import this into our SIS and we would have the latest lunch status and also the current balance so it would show in the app for parents. The output file looks something like this:
In order to import this via an AutoComm into our system we clean a few things up and only use the data we absolutely need, which in this case means just student number, lunch status, and balance. First off, our SIS already knows the name and school attached to a student number so we don’t need or want to update those. Also, if a student is on free lunch status we will import a balance of zero. The easiest way I found to do this is with Python, Pandas, and NumPy. I won’t walk through the steps to install it on your system because it’s pretty simple and I’m not a Python expert by any means. Once it’s installed we can create a little script that will run each day and format things like we need:
from pandas import DataFrame, read_csv
import pandas as pd
import numpy as np
file = r’C:\NutrikidsExport\POSCallAgent.txt’
headerNames = [‘student_number’, ‘status’, ‘balance1’]
df = pd.read_csv(file, header=None, usecols=[0,4,5], names=headerNames)
df[‘status’].replace(to_replace=[‘Paid’], value=’P’, inplace=True)
df[‘status’].replace(to_replace=[‘Reduced’], value=’R’, inplace=True)
df[‘status’].replace(to_replace=[‘Free’], value=’F’, inplace=True)
df[‘balance1’] = np.where(df[‘status’]==’F’,0.00, df[‘balance1’])
df.to_csv(‘nk_ps_out.csv’, encoding=’utf-8′, index=False)
cnopts = pysftp.CnOpts()
cnopts.hostkeys = None
srv = pysftp.Connection(host=”sftp.xxxx.com”, username=”xxxx”, password=”xxxx”, cnopts=cnopts)
We start off by importing our libraries to read a csv and parse it with Pandas and NumPy. Next, we set the variable named ‘file’ to be the path to the file exported from NutriKids. After that we will create an array containing the column header names for our output file. Finally, we read the file into a data frame by passing in the filename and the columns we want to import. In this case we are only importing the (zero indexed) 1st, 5th, and 6th columns: student number, lunch status, and balance and putting the header names we specified on them.
Once that’s done, we have the data loaded and labeled but our SIS really just wants the lunch status to be 1 character so let’s go through the status column and replace the instances of ‘Paid’, ‘Reduced’, and ‘Free’ with just ‘P’, ‘R’, or ‘F‘.
After that, let’s search the balance1 column and find anyone with a ‘F’ status and just zero out their balance1 value. This is where Pandas and NumPy really save the day, we tell it to search the data frame for any ‘F’ in the ‘status’ column and replace the ‘balance1’ with ‘0.00’.
Now we are ready to write the formatted data out to a CSV file. It’s worth noting that in NutriKids we could specify this export to only pull the needed fields but you might be working with a program that doesn’t always give you that option so this is a pretty gentle exercise in reformatting data. Taking a peek at the new file should look something like this now:
At this point it depends on where you’re running this script, if you’re already on your SFTP server and have the file written to the spot you’re going to pick it up from you can stop. If however, this is running on a separate server and you need to upload it to an SFTP server for your SIS to pick up you can use this little bit of code.
The virtual machine this script runs on is used exclusively for these types of processes so I just dropped the script on the desktop and used Windows Task Scheduler to schedule the task to run after the export from the lunch line each day:
Now, back in our SIS we setup an AutoComm which grabs a file from an SFTP server and updates the corresponding student records:
While this post wasn’t a total step by step I hope it serves as a gentle introduction to using Python and Pandas for moving and formatting data between systems.