The following will work directly with a CSV:
# ==============================================================================================
#
# Microsoft PowerShell Source File -- Created with SAPIEN Technologies PrimalScript 2012
#
# NAME: Copy-CsvToSQLServer.ps1
#
# AUTHOR: jvierra , Designed Systems & Services
# DATE : 8/16/2013
#
# COMMENT: This is a template only
# 2015-05-22 Fixed some issues
#
# ==============================================================================================
Param (
[parameter(Mandatory = $true)]
[string]$CsvPath,
[parameter(Mandatory = $true)]
[string]$CsvFile,
[parameter(Mandatory = $true)]
[string]$TargetServer,
[parameter(Mandatory = $true)]
[string]$TargetDatabase,
[parameter(Mandatory = $true)]
[string]$TargetTable,
[switch]$Truncate
)
Begin {
Function Get-CsvReader {
Param (
$csvPath,
$csvFile
)
$csvFile = $csvFile.Replace('.', '#')
$connStr = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties="Text;HDR=Yes;FORMAT=Delimited"' -f $csvPath
$csvConnection = New-Object System.Data.OleDb.OleDbConnection($connStr)
$csvConnection.Open()
$csvCommand = $csvConnection.CreateCommand()
$csvCommand.CommandText = "select * from [$csvFile]"
$csvCommand.ExecuteReader()
}
}
Process {
Try {
$csvReader = Get-CsvReader -csvPath $CsvPAth -csvFile $csvFile
$targetConnStr = "Data Source=$ServerName;Initial Catalog=$DbName;Integrated Security=True;"
$bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($targetConnStr, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity)
$bulkCopy.DestinationTableName = $TargetTable
$bulkCopy.WriteToServer($csvReader)
Write-Host "Table $SrcTable in $SrcDatabase database on $SrcServer has been copied to table $DestTable in $DestDatabase database on $DestServer"
} Catch {
throw $_
} Finally {
$csvReader.Close()
$bulkCopy.Close()
}
}
I have used both of these scripts many times.
\_(ツ)_/
