A problem we encounter too often is extracting data the business stores in an excel spreadsheet. The way some people deal with it is way too often to copy and paste all this in T-SQL code and have long integration scripts.

This causes two major problems. The first one is that you rely on your database to integrate data in your application from other systems. The vast majority of products have validation logic and application integrity checks into your application code, and rarely in the database schema. Not only that, but you suddenly expose as a public and exposed system what should remain an implementation detail of your service. In the end, you just made changes to your database structures an order of magnitude more difficult, which now decreases the maintainability of your code.

And don't even get me started on testability of T-SQL integration scripts, even with the new VS for database professionals unit testing. It just doesn't add up.

That said, the not to do is often the was done and conflicts with the needs to be done by tomorrow. Often enough a project cannot (or doesn't want to) afford a proper integration between their systems, as these projects come in expensive. Often, good enough is what the business wants, and the business controls the budget.

And in these cases, you often have to deal with data from Excel and Access. Few people actually know that you can query these files from within your T-SQL code, by using the OPENROWSET command, which gives you whichever table you want to select, through the table name or through a query.

In this instance I've just spent a good hour trying to understand why my excel spreadsheet opened through the OpenRecordset would return me a column full of null values when the spreadsheet itself had the correct values.

As usual, the answer is hidden in the knowledge base, KB194124. Excel tries to guess the datatype of a column, and gives you null if it fails. Rule of thumb, never trust anything that tries being smart.

Solution is to switch excel to import mode, by changing the connection string you use to add IMEX=1:

SELECT * INTO #xl FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=file.xls;IMEX=1;', ['Festival List$'])

And I'm back in business!

[UPDATE: Added some commentary. Also changed the text around a bit, sometimes I realize my English gets fuzzy when I don't proof read before posting. Sorry chaps.]

Realizing I still have 6 Tips'n'Tricks articles nearly ready to go to follow up on the series, but none of them are finished, I thought I'd give you another powershell trick.

One of the main issue on windows when interop has to be achieved between powershell and cmd is the issue of CreateProcess trying sometimes to be too smart about quotes. That's why powershell supports being called with a base64 encoded parameter as command line parameters, and why attempts to use variables when calling sqlcmd.exe fail miserably.

So I wrote a quick hack script that concatenates data in a nice batch file and then executes the batch file. I've used it on several projects by now. Anyone wanting to make it more fancy don't hesitate.

function Execute-SqlFile($file, [string]$Server, [string]$dbName, [hashtable]$variables, [switch]$WindowsAuthentication=$true, [string]$Username, [string]$Password) {
        $batch = (join-Path (cat env:TEMP) "exec_sql.bat")
        write-Host Connecting to $Server

        $output = (join-Path $env:TEMP "output_sql.txt")
        if (test-Path $batch) {
           Remove-Item $batch -force
        }
        $data = ""
        if (test-Path $batch) { Remove-Item $batch }
        $data += "sqlcmd -S $Server"

        if ($WindowsAuthentication) {
            $data += ' -E'
        } else {
            $data += " -U $Username -P $Password"
        }
        if ($dbname) {
            $data += " -d $dbName"
        }

        if ($variables -and $variables.Count -gt 0) {
            $data += ' -v '
            $isFirst = $true
            foreach($key in $variables.keys) {

                if (! $isFirst) { $data += ' ' } else { $isFirst=$false }

                $val = $variables[$key]
                $data += "$key="
                $data += "`"$val`""

            }
        }
        $data += " -i `"$file`""


        $data += " -o `"$output`""

        $data | Add-Content $batch -force

        cmd /c (Resolve-Path $batch)

        gc $output

    }

Which lets you call it like so:

PS C:\WINDOWS\> Execute-SqlScript "local.sql" "dbserver" @{key="value"; key2="value2"}

Enjoy!