Neat Trick...

If you are a developer, chances are at some point you’ve been handed an Excel worksheet and told to update the database of a given application with it. Now depending on what db you are working with, and what kind of access you have to it, there are many easy ways to do this. However, none of my normal methods could help me in the situation I found myself it this week. The db is Oracle and the access is extremely limited so I prepared myself for the horrible task of manually creating a bunch of update statements via cut-n-paste, putting all of that in a ColdFusion page and running it

Thankfully a co-worker stopped me and suggested that I use formulas in Excel. "Huh?" I said to him. After giving me a look that said very clearly said "how could you not know this," he explained that if the data in the spreadsheet is correctly separated in to cell, you can use a little formula magic to write all of your SQL statements for you. For example if you have your primary key in A and data to update in M your formula might look like this:

="UPDATE tableName SET fieldName = '" & M2 &"' WHERE primaryKey = " & A2

Then copy the formula and paste way to create an update statement pre row, which can be copied in to your db tool of choice or a CF file. Now I may, in fact, be the last developer to learn about this, but I had to share, in case there were other lost souls like me out there. ;-)