PowerShell: Import CSV to SQL via SQLBulkCopy

This script is made possible by the Out-DataTable module provided by Chad Miller. In order to use this script, copy the code from the link into a script editor. Save it as a .psm1. Use Import-Module to import the script and use Out-DataTable as a cmdlet.

First you’ll need the usual ConnectionString to your server. It should look a little like
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = “Server={yourServerPath};Database={yourDatabase};User ID={yourUsername};Password={yourPassword}”

PowerShell provides a built-in way to import CSV files into the Shell via the Import-CSV cmdlet. The result is a nice Object in the shell. The problem is, you generally had to do a lot of modifications to that object to mold it into a DataTable to actually make any use of it. That was, before Out-DataTable turned it into a simple pipe cmdlet.

$CSVDataTable = Import-Csv ‘C:\Path\To\Your\CSV.csv’ | Out-DataTable

Depending on how the destination table and the CSV match up, your BulkCopy command could be as easy as

$sqlBulkCopy = New-Object (“Data.SqlClient.SqlBulkCopy”) -ArgumentList $SqlConnection
$sqlBulkCopy.DestinationTableName = “dbo.yourTable”

The SqlBulkCopy Class requires use of one of it’s multiple constructors, the simplest being SqlBulkCopy(SqlConnection). SqlBulkCopy also requires you to define the table you intend update via it’s DestinationTableName Property. This example  so far assumes that your destinations table’s columns match up with your csv’s columns. However, if your table has say, a unique ID in the first column that isn’t in your CSV, you’d have to some how tell BulkCopy which columns go where. This can be done through ColumnMapping.

$ColumnMap1 = New-Object System.Data.SqlClient.SqlBulkCopyColumnMapping(x,y) Allows you to reference the source’s(x) column to that of the destination’s(y) column. In order to add this column reference to BulkCopy $sqlBulkCopy.ColumnMappings.Add($ColumnMap1)

Here’s how you could add multiple column mappings

$ColumnMap1 = New-Object System.Data.SqlClient.SqlBulkCopyColumnMapping(0, 1)
$ColumnMap2 = New-Object System.Data.SqlClient.SqlBulkCopyColumnMapping(1, 2)
$ColumnMap3 = New-Object System.Data.SqlClient.SqlBulkCopyColumnMapping(2, 3)
$sqlBulkCopy.ColumnMappings.Add($ColumnMap1)
$sqlBulkCopy.ColumnMappings.Add($ColumnMap2)
$sqlBulkCopy.ColumnMappings.Add($ColumnMap3)

Finally, Once you’ve got your columns mapped correctly, open the connection and WriteToServer().

$SqlConnection.Open()
$sqlBulkCopy.WriteToServer($CSVDataTable)
$SqlConnection.Close()

Here’s the script in all total

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = “Server={yourServerPath};Database={yourDatabase};User vID={yourUsername};Password={yourPassword}”

$CSVDataTable = Import-Csv ‘C:\Path\To\Your\CSV.csv’ | Out-DataTable

$sqlBulkCopy = New-Object (“Data.SqlClient.SqlBulkCopy”) -ArgumentList $SqlConnection
$sqlBulkCopy.DestinationTableName = “dbo.yourTable”

$ColumnMap1 = New-Object System.Data.SqlClient.SqlBulkCopyColumnMapping(0, 1)
$ColumnMap2 = New-Object System.Data.SqlClient.SqlBulkCopyColumnMapping(1, 2)
$ColumnMap3 = New-Object System.Data.SqlClient.SqlBulkCopyColumnMapping(2, 3)
$sqlBulkCopy.ColumnMappings.Add($ColumnMap1)
$sqlBulkCopy.ColumnMappings.Add($ColumnMap2)
$sqlBulkCopy.ColumnMappings.Add($ColumnMap3)

$SqlConnection.Open()
$sqlBulkCopy.WriteToServer($CSVDataTable)
$SqlConnection.Close()

Advertisements

2 thoughts on “PowerShell: Import CSV to SQL via SQLBulkCopy”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s