PowerShell | Kaizen - Continuous Improvement

Import Excel Into SQL Server

Posted on October 1, 2014 by

Raju Venkataraman Posted in PowerShell, SQL Server, Technology — No Comments ↓

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"

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

##############################################################################

## 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"

Category: PowerShell

Change SQL Server Authentication login password

Backup-Zip-Copy-Unzip-Restore-Notify SQL Server Databases

Import Excel Into SQL Server

Run SQL Scripts using PowerShell

Recent Posts

Categories

Recent Posts

Archives