Embedded Files
A school tracks class enrollment in a database and receives a spreadsheet every day with a snapshot of the data. They want to produce a table of the daily changes to the database to determine when classes were added, removed, or changed.
The daily spreadsheet has about 4,000 rows and 46 columns, and there are at least 150 files to compare each year. There were no simple solutions using only Excel. Manually processing the spreadsheets would have taken hundreds of hours each year.
Input
Input
Multiple files can be uploaded at once. Files can be CSV (comma separated value) or XLS* (Excel) files.
Each file had the same column names. The columns "subject", "catalog", "section", "class number", and "PAT number" uniquely identify each class. The "run date" column indicated when the data were exported from the database. The first row contains a title of the export, which was skipped.
Desired Output
Desired Output
Each uploaded file was compared to the previous file, based on the run date. Changes to the following columns were ignored: "percent", "enrollment", and "waiting". Files with no run date were identified with a date of 1/1/1900.
The output table included only records that had changed from the previous run date. The changes to each record were listed in a "changed from" column. New rows were identified with "NEW ROW". Removed rows were identified with "ALL REMOVED". Each change had the name of the column that changed and the previous value, separated by a colon. Multiple changes to a single record were separated by semi-colons.
Solution
Solution
Data Dot Plus developed a simple web application (LINK) to upload multiple daily spreadsheets, compare changes, and download a table of daily changes. The school is able to easily track changes to their database.
The web app was developed with no up-front cost and is available for a low annual fee of $200 / year.
Contact Data Dot Plus today for a free consultation: Contact
Page updated
Google Sites
Report abuse