Now that was easy!

This is a pretty common scenario, so I thought I would write it up and post it so everyone could see how to do this. Here’s what happened – my manager wanted a report from me about participation in the MSDN and TechNet Forums, the exact details are unimportant, but suffice it to say, this is a pretty major piece of data analysis.

I was able to pull a report to a spreadsheet, and I started looking through it, but was soon thwarted by two major factors. First, the way it was set up in the spreadsheet, there were headers every few lines in the spreadsheet that made Filtering impossible, I would have had to go into each section (several hundred sections in total) and set a Filter on each one, which would have taken a long time. Second, when I hit Ctrl + End to get to the bottom of the spreadsheet, there were over 35000 rows! That is certainly too much to be playing with in a spreadsheet, that is a job for a database, and luckily I have one on my machine.

SQL Server to the rescue! I obviously needed to get all of this data into a table for some much easier handling of a large amount of data, so I fired up our old friend SQL Server Management Studio (SSMS). From there, it was easy – right-click on the Databases label, and click “New Database” and I named it “ForumStats”. Then, right-click on the ForumStats label in the treeview to the left, and go to Tasks/Import Data, which starts the SQL Server Import and Export Wizard. This will walk you through connecting to the Data Source, which is the spreadsheet. For the Data Source field, I chose Microsoft Excel, then used Browse to point to the spreadsheet itself. Be sure to tell it what version of Excel you are using in the Version field, and hit Next.

The next screen will let you pick the destination database from the dropdown, for the rest of it you should be able to use the defaults for the client and server name. If you have not created a database for this yet, you can also do that from this screen. The next screen allows you to copy all of the data or to write a query to get just selected data into the new table – for this exercise, I used “Copy data from one or more tables or views”. The next screen asked me what sheet from the workbook I want to import, then we’re on to the last screen of the Wizard, which is the one that actually imports the data.

Now I have all of my data in a single table, and I can write my own query to get my results, or I can also use the Query Editor, which was discussed in an earlier post. Start to finish, this took me about 5 minutes, which was a much shorter time that trying to manipulate an enormous spreadsheet, and it kept me much more sane in the process. There’s an old saying about using the right tool for the job, and this is definitely an application of that adage. My manager was happy with the info I was able to give her as well, which is exactly what we were trying to do in the first place, right? J