bulk_csv_to_sql_import.ps1

#################################################### 
# 
# PowerShell CSV to SQL Import Script 
# 
#################################################### 
# Writes contents of a csv file to a table
# https://gallery.technet.microsoft.com/scriptcenter/Import-Large-CSVs-into-SQL-216223d9

# Ticket: SDSK 1130300760
# Author: Kino Mondesir

[CmdletBinding()]
param 
(
	[Parameter(HelpMessage="SQL Server", Position = 0, Mandatory=$false, ValueFromPipelineByPropertyName=$true)]
	[ValidateNotNullOrEmpty()] 
	[string]$sqlserver,

	[Parameter(HelpMessage="Database", Position = 1, Mandatory=$true, ValueFromPipelineByPropertyName=$true)]
	[ValidateNotNullOrEmpty()] 
	[string]$database,

	[Parameter(HelpMessage="Table", Position = 2, Mandatory=$true, ValueFromPipelineByPropertyName=$true)]
	[ValidateNotNullOrEmpty()] 
	[string]$table,

	[Parameter(HelpMessage="File path", Position = 3, Mandatory=$true, ValueFromPipelineByPropertyName=$true)]
	[ValidateNotNullOrEmpty()] 
    [string]$csvfile,
  
    [Parameter(HelpMessage="Column delimiter", Position = 4, Mandatory=$false, ValueFromPipelineByPropertyName=$true)]
	[ValidateNotNullOrEmpty()] 
    [string]$csvdelimiter = ';',
  
    [Parameter(HelpMessage="File path", Position = 5, Mandatory=$false, ValueFromPipelineByPropertyName=$true)]
	[ValidateNotNullOrEmpty()] 
	[switch]$FirstRowColumnNames
)
begin 
{
    ################### No need to modify anything below ################### 
    Write-verbose -Message "Bulk insert started..." 
    $elapsed = [System.Diagnostics.Stopwatch]::StartNew()  
    [void][Reflection.Assembly]::LoadWithPartialName("System.Data") 
    [void][Reflection.Assembly]::LoadWithPartialName("System.Data.SqlClient") 
    [int]$err = 0
    # 50k worked fastest and kept memory usage to a minimum 
    $batchsize = 50000 
    
    # Build the sqlbulkcopy connection, and set the timeout to infinite 
    $connectionstring = "Data Source=$sqlserver;Integrated Security=true;Initial Catalog=$database;"
    try 
    {
        $bulkcopy = New-Object Data.SqlClient.SqlBulkCopy($connectionstring, [System.Data.SqlClient.SqlBulkCopyOptions]::TableLock) 
        $bulkcopy.DestinationTableName = $table 
        $bulkcopy.bulkcopyTimeout = 0 
        $bulkcopy.batchsize = $batchsize 
        
        # Create the datatable, and autogenerate the columns. 
        $datatable = New-Object System.Data.DataTable 
        
        # Open the text file from disk 
        $reader = New-Object System.IO.StreamReader($csvfile) 
        $columns = (Get-Content $csvfile -First 1).Split($csvdelimiter)
    }
    catch 
    {
        $err = -1
		$exception = $_.Exception.Message
		Write-Host $exception 
		return $err
    } 
}

process
{
    try 
    {
        if ($FirstRowColumnNames -eq $true) 
        { 
            $null = $reader.readLine() 
        } 
        
        foreach ($column in $columns) 
        {  
            $null = $datatable.Columns.Add() 
        } 
        
        # Read in the data, line by line, not column by column 
        while ($null -ne ($line = $reader.ReadLine()))  
        { 
            
            $null = $datatable.Rows.Add($line.Split($csvdelimiter)) 
        
            # Import and empty the datatable before it starts taking up too much RAM, but  
            # after it has enough rows to make the import efficient. 
            $i++; if (($i % $batchsize) -eq 0) 
            {  
                $bulkcopy.WriteToServer($datatable)  
                Write-Verbose -Message "$i rows have been inserted in $($elapsed.Elapsed.ToString())." 
                $datatable.Clear()  
            }  
        }  
        
        # Add in all the remaining rows since the last clear 
        if($datatable.Rows.Count -gt 0) 
        { 
            $bulkcopy.WriteToServer($datatable) 
            $datatable.Clear() 
        } 
        
        Write-Verbose -Message "Script complete. $i rows have been inserted into the database." 
        Write-Verbose -Message "Total Elapsed Time: $($elapsed.Elapsed.ToString())" 
        # Sometimes the Garbage Collector takes too long to clear the huge datatable. 
        [System.GC]::Collect()
        return $err
    }
    catch 
    {
        $err = -1
		$exception = $_.Exception.Message
		Write-Host $exception 
		return $err
    }
    finally 
    {
        # Clean Up 
        $reader.Close(); $reader.Dispose() 
        $bulkcopy.Close(); $bulkcopy.Dispose() 
        $datatable.Dispose() 
    }
}