This posting outlines how the names & addresses of the “Catholic Portal” are made available. The purpose of this posting is mostly documentation. Documentation for myself, since I always forget. And documentation so somebody else can do the work after I win the lottery and move to the beach to drink cocktails with umbrellas in them.
- Extract data – Open the spreadsheet. Activate the ACCU tab. Copy all of the data sans the “cool” data entry macros. Create a new spreadsheet. Paste all of the previously cut data into the new spreadsheet. Save the new spreadsheet for future reference with the name catholic_libraries.xls.
- Extract more data – Repeat Step #1 for the tab labeled Tab 2, but save the newly created spreadsheet with the name atla.xls.
- Clean – Open catholic_libraries.xls and delete columns so the only ones remaining are: last name, first name, school, address, city, state, zip code, and email address. Make sure the remaining columns are in the the order listed above. During this process the data may need further cleaning. For example, curly quotes need to be straightened. Carriage returns inside cells need to be removed. Make sure city and state values contain only… city and state values. No countries.
- Sort – Sort catholic_libraries.xls in ascending order by school.
- Save – Save the cleaned and sorted data as a tab-delimited text file with the name catholic_libraries.db. Make sure the resulting text file is Unix-based and not DOS- or Macintosh-based. Additionally, Excel often tries to do you a favor by surrounding fields containing commas with quotes. Remove the quotes.
- Go to Step #3 – Repeat the process for the file named atla.xls, but include only the last name, first name, school, city, state, and email address in the saved data, and call the result atla.db.
- Mount – Mount the saved database files (catholic_libraries.db and atla.db) by saving them to the Portal. They are expected to live in Y:\data\vufind\web\etc.
The result of this work should then be visible under the Directory tab.
This process is a bit tedious, but since the directory does not change very often, and now that I have documented the process, the next time the directory needs updating things will be easier. On the other hand, as the Portal grows, there will be a need for a real database, and it will be able to support additional functions, such as document delivery. Stay tuned.