Avoid “Excel Hell” with T-SQL
Overview
One of the most used computer tools in the world is some form of a spreadsheet yet nothing will send people into a panic more quickly than telling them that you want to import spreadsheet data into SQL Server. Spreadsheets are usually not in any format that would come close to being easily importable. There are tools like SSIS that can do some of the basic parts of importing complex spreadsheets but the complex parts, such as departitioning groups of monthly columns, requires a script of some sort. Without such a script, the import package would need to be modified each and every month to accommodate the additional month’s columns. Then, there’s the problem of people adding new “categories” to the monthly partitions in the form of yet more columns causing the monthly partitions to have a different number of columns along with new column header names. There’s also the problems with associated with merged cells and having the dates of each monthly partition spread across at least 2 rows for year and month because to do otherwise would make an ugly spreadsheet.
There is a way to make a lot of these spreadsheets in a self-correcting “set it and forget it” mode and you don’t need to know how to program in C#, Java, Perl, VB Script, PowerShell, or any of the other languages to solve these problems. In fact, you don’t need SSIS to do this, either, although the methods explained in this session could be used in conjunction with SSIS.
This “Black Arts” session will show you how to do it all in T-SQL with some initial help of a now old friend, the ACE drivers.
Speaker Bio
Jeff Moden calls SQLServerCentral.com his “home away from home” where you’ll find 36,000 of his posts (as he says, “Some are actually useful” ;-) ) and 33 mostly 5 star articles on multiple T-SQL “Black Arts” subjects. Jeff has the talent of being able to explain and demonstrate complex methods and techniques in a fashion where even beginners will understand and, yet, still keep the interest of advanced T-SQL programmers. Jeff has been working with SQL Server since 1996, has been an SQL Server MVP since 2008, won the RedGate Exceptional DBA award in 2011, and coined the acronym of “RBAR”, which is pronounced “Ree-Bar” and is a Modenism for “Row By Agonizing Row” for anything tht loops in T-SQL. Jeff also presented two sessions at PASS 2010, has spoken at many SQL Saturdays, several local and remote PASS Chapter meetings, and is a proud member of “SPID”, which is this PASS Chapter right here in good ol’ Detroit, Michigan.
Zeal Recruiting Services is this month's sponsor 248-885-8282.
Demo Scripts (.zip 982 KB)