Add Shepherd’s Staff User Data

This page documents the python script that accepts Shepherd’s Staff user data and converts it to a form that is suitable for input to the church web site. Originally, the process to accomplish this involved using both a Postgresql database and Visual Basic. However, I decided that expecting someone who might take over the web administration duties of the church web site to have expertise in both these technologies was not likely. Consequently, I completely rewrote the input processing program in Python.

The Python script documented here expects the input data to be in a Excel spreadsheet with the following header:

Household_name First First_or_preferred Cell Personal_E_mail Work_E_mail Number Received Street_Address City,state,postal_code

These categories are taken from the Shepherd’s Staff database and  Shepherd’s Staff is used to export an Excel spreadsheet containing the data corresponding to these categories. Before presenting this spreadsheet to the Python script, it should be sorted by the Number column (Number is the integer identifier assigned to each household by Shepherd’s Staff). The spreadsheet should be named “Input_worksheet.xlsx” and be located in the same directory as the Python script. In a typical situation, the spreadsheet contains the members who have joined since the oldest date in the “Received” column.

In addition, another spreadsheet containing the user data for all members who are currently contained in the web site user database may be presented to the Python script. This data is contained in a file with the identifier “Current_user_data.csv”. This spreadsheet is generated by the import/export plugin (using the export function) on the church web site, which outputs its data in comma-separated-values (CSV) format. Excel can open csv formate files, automatically converting it to an Excel spreadsheet. Before running the script, the data must be saved by Excel in a file called “Current_user_data.xlsx”. The first column is the “user_login” name used to identify church web site member accounts. This is the only data in the spreadsheet used by the Python script.  As for the first spreadsheet, this file must exist in the same directory as the Python script.

It is assumed both spreadsheets hold the header data in their first row. However, this header data is not read by the script. Additional instructions on how to prepare these two spreadsheets for use by the Python script are contained in the comments section at the beginning of the script.

The Python script may be used in one of two ways. The typical use is to update the user data on the web site by using the two spreadsheets mentioned above as input. However, another way is to populate a freshly created web site configured with the same plugins as the existing church web site. In that case, the “Input_worksheet.xlsx” spreadsheet should contain all member data contained in the Shepherd’s Staff database and no “Current_user_data.xlsx” spreadsheet should exist in the Python script directory.

In both cases, the result of the Python script is an Excel spreadsheet suitable for presentation to the import function of the import/export plugin on the church web site. However, this data is created by the script and placed in a file with the name “Output_worksheet.xlsx”. In order to format this data correctly, it must be opened with Excel and saved in csv format to produce a file with the name “Output_worksheet.csv”. Also, when filling out the table specifying the parameters of the import function, make sure the “directory” and “subscriber” roles (and no others) are checked.

A link to the python script is found in the next text block. It is in HTML format, so it displays properly when the link is clicked. To convert to plaintext, click on the link in the web browser to display the HTML and then save as an HTML file. Then open the HTML file (“python-script.html) with MS Word. Select “SaveAs” plaintext (which will create a .txt file – python-script.txt). The result will have the appropriate python indenting and can be used as the main.py file (note you must change its name from “python-script.txt” to “main.py”.