How to import data from .csv in SQL Server using PowerShell?

Question

I'm using PowerShell and have to import data from a .csv file into a already created table on a SQL Server Database. So I don't need the header line from the csv, just write the data.

Here is what I have done so far:

#Setup for SQL Server connection
#SQL Server Name
$SQLServer = "APPLIK02\SQLEXPRESS"
#Database Name
$SQLDBName = "code-test"

#Create the SQL Connection Object
$SQLConn = New-Object System.Data.SQLClient.SQLConnection
#Create the SQL Command Object, to work with the Database
$SQLCmd = New-Object System.Data.SQLClient.SQLCommand

#Set the connection string one the SQL Connection Object
$SQLConn.ConnectionString = "Server=$SQLServer;Database=$SQLDBName; Integrated Security=SSPI"
#Open the connection
$SQLConn.Open()

#Handle the query with SQLCommand Object
$SQLCmd.CommandText = $query
#Provide the open connection to the Command Object as a property
$SQLCmd.Connection = $SQLConn

#Execute 
$SQLReturn=$SQLCmd.ExecuteReader()

Import-module sqlps
$tablename = "dbo."+$name

Import-CSV .\$csvFile | ForEach-Object Invoke-Sqlcmd 
  -Database $SQLDBName -ServerInstance $SQLServer 
  #-Query "insert into $tablename VALUES ('$_.Column1','$_.Column2')"
#Close
$SQLReturn.Close()  
$SQLConn.Close()

Answer 1

I wrote a blog post about using SQL with PowerShell, so you can read more about it here.

We can do this easily if you have the SQL-PS module available. Simply provide values for your database name, server name, and table, then run the following:

$database = 'foxdeploy'
$server = '.'
$table = 'dbo.powershell_test'

Import-CSV .\yourcsv.csv | ForEach-Object {Invoke-Sqlcmd `
  -Database $database -ServerInstance $server `
  -Query "insert into $table VALUES ('$($_.Column1)','$($_.Column2)')"
  }

To be clear, replace Column1, Column2 with the names of the columns in your CSV.

Be sure that your CSV has the values in the same format as your SQL DB though, or you can run into errors.

When this is run, you will not see any output to the console. I would recommend querying afterwards to be certain that your values are accepted.