Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save puttyq/564c46cfe5c6a3a9e5df533251f37fa8 to your computer and use it in GitHub Desktop.
Save puttyq/564c46cfe5c6a3a9e5df533251f37fa8 to your computer and use it in GitHub Desktop.
Dynamic CSV file importer (many CSV's) to SQL Server
cls
# database Details
$dbServer = "xxxxxxx"
$dbDatabaseName = "xxxxxxxx"
$dbTable = "xxxxxxxx"
# loading modules
Import-Module SQLPs
# If not loaded, load SQL assemblies
try {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
catch {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo"}
try {add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=13.0.1601.5, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
catch {add-type -AssemblyName "Microsoft.SqlServer.Smo"}
#######################
function Get-Type
{
param($type)
$types = @(
'System.Boolean',
'System.Byte[]',
'System.Byte',
'System.Char',
'System.Datetime',
'System.Decimal',
'System.Double',
'System.Guid',
'System.Int16',
'System.Int32',
'System.Int64',
'System.Single',
'System.UInt16',
'System.UInt32',
'System.UInt64')
if ( $types -contains $type ) {
Write-Output "$type"
}
else {
Write-Output 'System.String'
}
} #Get-Type
#######################
function Out-DataTable
{
[CmdletBinding()]
param([Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [PSObject[]]$InputObject)
Begin
{
$dt = new-object Data.datatable
$First = $true
}
Process
{
foreach ($object in $InputObject)
{
$DR = $DT.NewRow()
foreach($property in $object.PsObject.get_properties())
{
if ($first)
{
$Col = new-object Data.DataColumn
$Col.ColumnName = $property.Name.ToString()
if ($property.value)
{
if ($property.value -isnot [System.DBNull]) {
$Col.DataType = [System.Type]::GetType("$(Get-Type $property.TypeNameOfValue)")
}
}
$DT.Columns.Add($Col)
}
if ($property.Gettype().IsArray) {
$DR.Item($property.Name) =$property.value | ConvertTo-XML -AS String -NoTypeInformation -Depth 1
}
else {
$DR.Item($property.Name) = $property.value
}
}
$DT.Rows.Add($DR)
$First = $false
}
}
End
{
Write-Output @(,($dt))
}
} #Out-DataTable
#######################
function Get-SqlType
{
param([string]$TypeName)
switch ($TypeName)
{
'Boolean' {[Data.SqlDbType]::Bit}
'Byte[]' {[Data.SqlDbType]::VarBinary}
'Byte' {[Data.SQLDbType]::VarBinary}
'Datetime' {[Data.SQLDbType]::DateTime}
'Decimal' {[Data.SqlDbType]::Decimal}
'Double' {[Data.SqlDbType]::Float}
'Guid' {[Data.SqlDbType]::UniqueIdentifier}
'Int16' {[Data.SQLDbType]::SmallInt}
'Int32' {[Data.SQLDbType]::Int}
'Int64' {[Data.SqlDbType]::BigInt}
'UInt16' {[Data.SQLDbType]::SmallInt}
'UInt32' {[Data.SQLDbType]::Int}
'UInt64' {[Data.SqlDbType]::BigInt}
'Single' {[Data.SqlDbType]::Decimal}
default {[Data.SqlDbType]::VarChar}
}
} #Get-SqlType
#######################
function Add-SqlTable
{
[CmdletBinding()]
param(
[Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance,
[Parameter(Position=1, Mandatory=$true)] [string]$Database,
[Parameter(Position=2, Mandatory=$true)] [String]$TableName,
[Parameter(Position=3, Mandatory=$true)] [System.Data.DataTable]$DataTable,
[Parameter(Position=4, Mandatory=$false)] [string]$Username,
[Parameter(Position=5, Mandatory=$false)] [string]$Password,
[ValidateRange(0,8000)]
[Parameter(Position=6, Mandatory=$false)] [Int32]$MaxLength=1000,
[Parameter(Position=7, Mandatory=$false)] [switch]$AsScript
)
try {
if($Username)
{ $con = new-object ("Microsoft.SqlServer.Management.Common.ServerConnection") $ServerInstance,$Username,$Password }
else
{ $con = new-object ("Microsoft.SqlServer.Management.Common.ServerConnection") $ServerInstance }
$con.Connect()
$server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $con
$db = $server.Databases[$Database]
$table = new-object ("Microsoft.SqlServer.Management.Smo.Table") $db, $TableName
foreach ($column in $DataTable.Columns)
{
$sqlDbType = [Microsoft.SqlServer.Management.Smo.SqlDataType]"$(Get-SqlType $column.DataType.Name)"
if ($sqlDbType -eq 'VarBinary' -or $sqlDbType -eq 'VarChar')
{
if ($MaxLength -gt 0)
{$dataType = new-object ("Microsoft.SqlServer.Management.Smo.DataType") $sqlDbType, $MaxLength}
else
{ $sqlDbType = [Microsoft.SqlServer.Management.Smo.SqlDataType]"$(Get-SqlType $column.DataType.Name)Max"
$dataType = new-object ("Microsoft.SqlServer.Management.Smo.DataType") $sqlDbType
}
}
else
{ $dataType = new-object ("Microsoft.SqlServer.Management.Smo.DataType") $sqlDbType }
$col = new-object ("Microsoft.SqlServer.Management.Smo.Column") $table, $column.ColumnName, $dataType
$col.Nullable = $column.AllowDBNull
$table.Columns.Add($col)
}
if ($AsScript) {
$table.Script()
}
else {
$table.Create()
}
}
catch {
$message = $_.Exception.GetBaseException().Message
Write-Error $message
}
} #Add-SqlTable
#######################
function Write-DataTable
{
[CmdletBinding()]
param(
[Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance,
[Parameter(Position=1, Mandatory=$true)] [string]$Database,
[Parameter(Position=2, Mandatory=$true)] [string]$TableName,
[Parameter(Position=3, Mandatory=$true)] $Data,
[Parameter(Position=4, Mandatory=$false)] [string]$Username,
[Parameter(Position=5, Mandatory=$false)] [string]$Password,
[Parameter(Position=6, Mandatory=$false)] [Int32]$BatchSize=50000,
[Parameter(Position=7, Mandatory=$false)] [Int32]$QueryTimeout=0,
[Parameter(Position=8, Mandatory=$false)] [Int32]$ConnectionTimeout=15
)
$conn=new-object System.Data.SqlClient.SQLConnection
if ($Username)
{ $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout }
else
{ $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout }
$conn.ConnectionString=$ConnectionString
try
{
$conn.Open()
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bulkCopy.DestinationTableName = "[$TableName]"
$bulkCopy.BatchSize = $BatchSize
$bulkCopy.BulkCopyTimeout = $QueryTimeOut
$bulkCopy.WriteToServer($Data)
$conn.Close()
}
catch
{
$ex = $_.Exception
Write-Error "$ex.Message"
continue
}
} #Write-DataTable
#######################
# listing stats files
write-host ("Reading files in Stats folder") -ForegroundColor Green
$files = GCI -Path C:\Hyper-VStatistics
# clear stats dataset
$dataset = $null
#######################
# itterate through all files and get the context from each CSV file
foreach ($file in $files)
{
$data = Import-csv $file.FullName
write-host ("Importing file: " + $file.name + " - " + $data.count + " records") -ForegroundColor Yellow
#write-host ("Importing records " + $data.count) -ForegroundColor Yellow
#$data
$dataset = $dataset + $data
}
####################### - SQL Population
write-host ("Total records: " + $dataset.count) -ForegroundColor Green
$dtDataSet = $dataset | Out-DataTable
write-host ("`nConnecting to SQL Server") -ForegroundColor Green
write-host ("`nDropping SQL table $dbTable if present") -ForegroundColor Green
$dbQuery1 = "IF OBJECT_ID('dbo.$dbTable', 'U') IS NOT NULL DROP TABLE dbo.[$dbTable]"
Invoke-Sqlcmd -Query $dbQuery1 -Database $dbDatabaseName -ServerInstance $dbServer
# Add data to database table
write-host ("`nAdding data to table $dbTable") -ForegroundColor Green
Add-SqlTable -ServerInstance $dbServer -Database $dbDatabaseName -TableName $dbTable -DataTable $dtDataSet
# Write table to database
write-host ("`nWriting data to table $dbTable") -ForegroundColor Green
Write-DataTable -ServerInstance $dbServer -Database $dbDatabaseName -TableName $dbTable -Data $dtDataSet
# validate all data in table
write-host ("`nCounting SQL table $dbTable rows") -ForegroundColor Green
$dbQuery2 = "SELECT COUNT(*) as Count from dbo.[$dbTable]"
$dbCount = Invoke-Sqlcmd -Query $dbQuery2 -Database $dbDatabaseName -ServerInstance $dbServer
If ($dbCount.Count -eq $dataset.Count)
{
write-host ("`nSTATUS - SQL table rows matches FILE import count") -ForegroundColor Green
}
else
{
write-host ("`nSTATUS - SQL table rows DOES NOT matches FILE import count") -ForegroundColor Red
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment