Import Excel Into SQL Server

Copied from here.
http://blogs.technet.com/b/sqlthoughts/archive/2008/10/03/out-sql-powershell-function-export-pipeline-to-a-new-sql-server-table.aspx
http://tools.perceptus.ca/text-wiz.php
http://dnhlmssql.blogspot.com/2013/05/from-excel-to-insert-into-another.html

##############################################################################
##
## out-sql.ps1
##
## by Alexey Yeltsov, Microsoft Corp.
## Raju Venkataraman Added some fixes for Table creation and Inserts
## Export pipeline contents into a new SQL table
##
## Parameters:
##    $SqlServer     - SQL Server
##    $Database      - Database name
##    $Table         - Table name
##    $DropExisting  - Drop $Table if it already exists and recreate it
##                    (default $false)
##    $RowId         - Add identity column named $RowId and make it a primary key.
##                    (default "RowID". Can pass $null if identity is not needed)
##
##
## Examples:
##
##    #First, load the function
##    . .\out-sql.ps1
##
##    #Export processes to table Process in database Scratch on local sql server
##    get-process | out-sql -SqlServer . -database Scratch -table Process -dropexisting $true
##
##    #Export volume details from 4 servers into a table
##    @("Server1","Server2","Server3","Server4") `
##    | % {$Server = $_ ; Get-WMIObject Win32_Volume -computer $Server } `
##    | Select-Object `
##        SystemName, `
##        Name, `
##        @{Name="CapacityGb";Expression={[math]::truncate($_.Capacity / 1Gb)}}, `
##        @{Name="FreeGb";Expression={[math]::truncate($_.FreeSpace / 1Gb)}} `
##    | out-sql -sqlserver . -database Scratch -table DiskVolume -dropexisting $true
##
##
##
##############################################################################

 $DebugPreference = "Continue"

function Out-Sql($SqlServer=$null,$Database=$null,$Table=$null,$DropExisting=$false,$RowId="RowID") {
  begin
  {
       $Line = 0
       [string]$CreateTable = ""
       if(-not $SqlServer) { throw 'Out-Sql expects $SqlServer parameter' }
       if(-not $Database) { throw 'Out-Sql expects $Database parameter' }
       if(-not $Table) { throw 'Out-Sql expects $Table parameter' }
       if($DropExisting) { write-debug "Note: If the table exists, it WILL be dropped and re-created."}

       $SqlConnectionString = "  Provider=sqloledb;" +
                           "  Data Source=$SqlServer;" +
                    "  Initial Catalog=$Database;" +
                    "  Integrated Security=SSPI;"

       write-debug "Will open connection to SQL server ""$SqlServer"" and will populate table ""$Table."""
       write-debug "Connection string: `n$SqlConnectionString"
       $SqlConnection = New-Object System.Data.OleDb.OleDbConnection $SqlConnectionString
       $SqlCommand = New-Object System.Data.OleDb.OleDbCommand "",$SqlConnection
       $SqlConnection.Open()
  }
  process
  {
       $Line ++

       $Properties = $_.PSObject.Properties
       if (-not $Properties)
       {
         throw "Out-Sql expects object to be passed on the pipeline. The object must have .PSObject.Properties collection."
       }

       #if we're at the first line, initialize the table
       if ($Line -eq 1)
       {
              #initialize SQL connection and create table

              if($DropExisting) { $CreateTable += "IF OBJECT_ID('$Table') IS NOT NULL DROP TABLE $Table;`n"}

              $CreateTable +="CREATE TABLE $Table ( `n"

              $col = 0
              if ($RowId)
              {
                     $col++;
                     $CreateTable +="$RowId INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED `n"
              }
              foreach($Property in $Properties)
              {
                     $col++;
                     if ($col -gt 1) { $CreateTable +="," }

                     # In below, why not use "if ($Property.Value -is [datetime])"?
                     # Because access can be denied to the value, but Property.TypeNameOfValue would still be accessible!
                     if ($Property.TypeNameOfValue -eq "System.DateTime")
                     {
                           $CreateTable +="["+"$($Property.Name)"+"]"+" DATETIME NULL `n"
                     }
                     else
                     {
                     $CreateTable +="["+"$($Property.Name)"+"]"+" NVARCHAR(MAX) NULL `n"

                     }
              }

              $CreateTable +=")"

              write-debug "Will execute SQL to create table: `n$CreateTable"

              $SqlCommand.CommandText = $CreateTable

              $rows = $SqlCommand.ExecuteNonQuery()

       }

       #Prepare SQL insert statement and execute it
       $InsertStatement = "INSERT $Table VALUES("
       $col = 0
       foreach($Property in $Properties)
       {
              $col++;
              if ($col -gt 1) { $InsertStatement += "," }

              #In the INSERT statement, do speacial tratment for Nulls, Dates and XML. Other special cases can be added as needed.
              if (-not $Property.Value)
              {
                     $InsertStatement += "null `n"
              }
              elseif ($Property.Value -is [datetime])
              {
                     $InsertStatement += "'" + $Property.Value.ToString("yyyy-MM-dd HH:mm:ss.fff") + "'`n"
              }
              elseif ($Property.Value -is [System.Xml.XmlNode] -or $Property.Value -is [System.Xml.XmlElement])
              {
                     $InsertStatement += "'" + ([string]$($Property.Value.Get_OuterXml())).Replace("'","''") + "'`n"
              }
              else
              {
                     $InsertStatement += "'" + ([string]$($Property.Value)).Replace("'","''") + "'`n"
              }
       }
       $InsertStatement +=")"

       write-debug "Running insert statement: `n $InsertStatement"

       $SqlCommand.CommandText = $InsertStatement
       $rows = $SqlCommand.ExecuteNonQuery()
  }
  end
  {
       write-debug "closing SQL connection..."
       $SqlConnection.Close()
  }
}



 function Import-Excel([string]$FilePath, [string]$SheetName = "")
{
    $csvFile = Join-Path $env:temp ("{0}.csv" -f (Get-Item -path $FilePath).BaseName)
    if (Test-Path -path $csvFile) { Remove-Item -path $csvFile }

    # convert Excel file to CSV file
    $xlCSVType = 6 # SEE: http://msdn.microsoft.com/en-us/library/bb241279.aspx
    $excelObject = New-Object -ComObject Excel.Application
    $excelObject.Visible = $false
    $workbookObject = $excelObject.Workbooks.Open($FilePath)
    SetActiveSheet $workbookObject $SheetName | Out-Null
    $workbookObject.SaveAs($csvFile,$xlCSVType)
    $workbookObject.Saved = $true
    $workbookObject.Close()

     # cleanup
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbookObject) |
        Out-Null
    $excelObject.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelObject) |
        Out-Null
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers()

    # now import and return the data
    Import-Csv -path $csvFile
}

function FindSheet([Object]$workbook, [string]$name)
{
    $sheetNumber = 0
    for ($i=1; $i -le $workbook.Sheets.Count; $i++) {
        if ($name -eq $workbook.Sheets.Item($i).Name) { $sheetNumber = $i; break }
    }
    return $sheetNumber
}

function SetActiveSheet([Object]$workbook, [string]$name)
{
    if (!$name) { return }
    $sheetNumber = FindSheet $workbook $name
    if ($sheetNumber -gt 0) { $workbook.Worksheets.Item($sheetNumber).Activate() }
    return ($sheetNumber -gt 0)
}

$ExcelSheetContent = Import-Excel -FilePath "C:\Dev\svn\database\Projects\NewMexico\DBSetup\Data\Excel.xlsx" -SheetName "Sheet1"
Write-Host $ExcelSheetContent

$ExcelSheetContent |Select-Object |out-sql -SqlServer "DEV\INS1" -Database "ETLSTage" -Table "TargetTable" -DropExisting "True"