The Magical Adventures of Excel Imports

I was given a project by my manager for a client that seemed simple on its face.  Our client wanted to be able to create an Excel sheet, fill it with data about their investors in a partnership, upload all the data, and then fill the partnership up with all of the financial transactions and data from the import. We would provide the end user with a glorious template.  Just copy and paste the info into the template! The client also wanted the computer to look through our database to see if the investor data was already present, and to just pull that data in case.  So all I would have to do is upload the Excel sheet, store the data into a table, and then sort the data.  Easy stuff, right?

What my manager said the project would be like.

What it was actually like.

I developed a strategy to deal with the import.  First, I used SQL Bulk Copy to take all the data and dump it into it's own special table.  Each upload had a "Batch ID," that way a user could recall a batch if needed.  After the batch was uploaded to the SQL table, I had a stored procedure that checked the other tables for investor data and pulled it in.  The user would be presented with a web page that was divided into two parts: records where the investor and all relevant information was found, and records where that wasn't  true.  For records it couldn't find, the user could manually input the information into a text box, or use a drop down list.  All the user had to do was push a button that said "Import Data" and off to the races!

The best laid plans of mice and men.

I learned the hard way that whenever you program something, you need to have your problem defined very clearly from the outset.  Just because an end user says they want the program to work in a certain way, that's not necessarily what they mean.  You need to have a "success" condition, some way to know that your program is working as intended, and to figure out a way to predict situations and curve balls before they hit you hard and you have to refactor. I didn't take the time to ask the right questions, so the project took an excessive amount of time, and I had to do a lot of last minute refactoring to get it to work the way the client actually wanted it to work.

Also, communication, communication, communication.  

First off, even though we gave the client a template, they ignored it.  We could not get the end user to understand that he/she could not just put in any ol' Excel file and expect the software to work.  This added extra work on my end.  I had to prepare the files for import myself.  I felt like the tax specialist who has someone come in with an old shoe box filled with various family keepsakes, random papers, and dog hair, looking expectantly for you to "make it work."  

It's difficult to deduct the expense of this broken coffee mug sir...

Secondly, I was given a test sheet filled with data.  That's all well and good, but it made me too optimistic about the data I would receive.  The test set I received had all of the fields filled with data.  When they gave me the real Excel sheets they wanted to import, half the data was missing!  How could I find investor data with no ID or client data?  One sheet had financial transactions, but the other sheet had them in an entirely different format.  

Also, I was given the sheets two days before the big client meeting.  I had a chit chat with my boss about timing.

Third, scouring the SQL tables took eons.  I'm working with legacy code, and all the data I needed was in four tables.  Sounds simple, right?  Well the foreign keys got so confusing, I thought I was seeing double. I found myself asking "What the heck is the PartnershipPaymentTypeID?" and trying to make sure if I had to write a new investor that all of the foreign keys matched where they should.  I felt like Indiana Jones raiding a dark temple trying to learn the secrets of the SQL tables.

Fourth, and most importantly, QA took forever.  I hadn't consider that instead of an Excel import, the end user might want to upload pictures of his/her safari to Africa.  That would break my code.  What if instead of writing how much was invested, they instead wrote "Bob was here."  That would break my code.  What if they just enjoy clicking the upload button because they think it's a soothing sound?  That would break my code.

I had to do more prep work in making sure the user input was funneled into acceptable parameters and let them down gently when they stepped out of line.  

All in all I learned a ton from this project.  Sure, I got a lot of flak for the amount of time it took (by flak I mean a chain of expletives).  I also learned that I should always have the mindset that anything I write must be ready for an end user.  I also learned to push back.  Deadlines should be communicated WAY in advance, and I should have someone sit down and help me develop the scope of the project.  I'm getting better, but my stress level will go down as I learn to handle these projects with more finesse.