PowerShell: Deluxe Bulk Insert CSV!
Author: jason
Date: 2019-05-04 09:29:11
Category: Technical

Use PowerShell to read the list Column Names from a CSV, Create a Table in SQL, and then Bulk Insert that CSV. Yikes. That's a lotta do...

Code

## Insert a CSV into a SQL Table, after we make the SQL Table
$sqlServer = ".\SQL2017"
$sqlDatabase = "Usage"
$table = "ExampleTable"
$fullFileName = "C:\TempImportMe.csv"

## Get the first Row out of the CSV
$columnNames = (Get-Content $fullfilename -First 1)

## Remove the Quotes, if there are any
$columnNames = $columnNames.Replace("""","")

## Split the columns apart to an array
$split = $columnNames.Split(",")

## Wrap the column name in Brackets and add a safe default Type
$split = $split | ForEach-Object {"[$_] VARCHAR(512)"}

## Special Field Separator, magic variable!
$OFS = ","

## Go back to a string from an array
$split = [string]$split

## Build the Create Table Statement
$query = "CREATE TABLE $table ( $split );"

## Create the Table in the Database, note if it's already there you'll get an error, but we'll be able to keep going to the next step
Invoke-Sqlcmd -Query $query `
-ServerInstance $sqlServer `
-Database $sqlDatabase `
-ConnectionTimeout 60 `
-QueryTimeout 300 `
-ErrorAction continue

## Build the Bulk Insert Statement, back ticks let us pretend this is one line
$query =
@"
BULK INSERT $table FROM '$fullFileName' WITH
(FIRSTROW = 2
, FIELDTERMINATOR = ','
, ROWTERMINATOR = 'n'
, FORMAT = 'CSV'
, FIELDQUOTE = '"'
, KEEPIDENTITY, MAXERRORS = 0
, TABLOCK);
"@

## Insert the CSV into the new Table
Invoke-Sqlcmd -Query $query `
-ServerInstance $sqlServer `
-Database $sqlDatabase `
-ConnectionTimeout 60 `
-QueryTimeout 300 `
-ErrorAction continue







jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009