importacao_csv.ps1

#################################################### 
# 
# PowerShell CSV to SQL Import Script 
# 
#################################################### 
 
# Database variables 
$database = "SIRAT"
#$server = "localhost\SQLEXPRESS";
$server = "localhost";
$username = "sa";
$password = "yourStrong(!)Password";
$connectionString = "Server=$server;Database=$database;User ID=$username;Password=$password;"
$table = "_deposito_import" 

# CSV variables 
$csvfile = "inf_diario_fi_201909.csv" 
$csvdelimiter = ";" 
$FirstRowColumnNames = $false
$skipRows = 1

################### No need to modify anything below ################### 
Write-Host "Script started..." 
$elapsed = [System.Diagnostics.Stopwatch]::StartNew()  
[void][Reflection.Assembly]::LoadWithPartialName("System.Data") 
[void][Reflection.Assembly]::LoadWithPartialName("System.Data.SqlClient") 

# removing data from table
Write-Host "Cleaning table $table"
# Build the SQL Server Connection
$sqlconn = New-Object System.Data.SqlClient.SqlConnection
$sqlconn.ConnectionString = $connectionString
$sqlconn.Open()
$dbcmd = New-Object System.Data.SqlClient.SqlCommand
$dbcmd.CommandTimeout = 60
$dbcmd.connection = $sqlconn
$dbsql = "TRUNCATE table $table;"
$dbcmd.commandtext = $dbsql
$dbcmd.executenonquery() > $null
$sqlconn.Close()
$sqlconn.Dispose()
Write-Host "Ok"

# 50k worked fastest and kept memory usage to a minimum 
$batchsize = 50000 
  
# Build the sqlbulkcopy connection, and set the timeout to infinite 
$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) 
if ($FirstRowColumnNames -eq $true) { $null = $reader.readLine() }

# Skip the lines to skip
for ($i=1; $i -le $skipRows; $i=$i+1 ) {
    Write-Host "Skipping line $i";
    $reader.readLine();
}

foreach ($column in $columns) {  
    $null = $datatable.Columns.Add() 
} 

Write-Host "Starting import to table $table";

# Read in the data, line by line 
while (($line = $reader.ReadLine()) -ne $null)  { 
    
    $null = $datatable.Rows.Add($line.Split($csvdelimiter)) 
 
    $i++; if (($i % $batchsize) -eq 0) {  
        $bulkcopy.WriteToServer($datatable)  
        Write-Host "$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() 
} 
  
# Clean Up 
$reader.Close(); $reader.Dispose() 
$bulkcopy.Close(); $bulkcopy.Dispose() 
$datatable.Dispose() 
  
$qtyImported = $i - $skipRows
Write-Host "Script complete. $qtyImported rows have been inserted into the database." 
Write-Host "Total Elapsed Time: $($elapsed.Elapsed.ToString())"

# Sometimes the Garbage Collector takes too long to clear the huge datatable. 
[System.GC]::Collect()