One of the teams I support was pulling data from a series of Excel files from various external sources with varying column sequences. These files were already being loaded into a database table but the team needed to pull out a subset of the data, and append it to a new Excel file, to be loaded into a different database table. This was taking about 4 hours each week.

I took the initiative to pull the same subset of data out of the database table that the original files were loaded to. This cut the 4 hours down to 2. There was still a series of manual steps to load this file and perform the required processing on the data.

I worked with our developers to write a stored procedure to pull the data from the first table, load it into the second table, and perform the necessary processing. This cut the process down significantly further but we still had to keep track of what was being processed. Remember, these were externally provided files, that were arriving at different times, and we did not want to have to wait for all files to be received before loading.

The process created by the devs required a manual table insert to ‘tell’ it what to load & process. So the last step in this project was to write an additional stored procedure (and create tracking tables) to facilitate this table insert, and keep track of what files were loaded and processed. An automated email message keeps the team abreast of the progress.

This task, that started out as a 4+ hour job, now takes less that 1 minute and can be repeated as needed as files flow in.