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

Question

I have the following scenario: Iam using PowerShell and have to import data from a .csv file into a already created table on a SQL Server Database. So i dont 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()

Well, for one you've commented out your Query statement, which will cause a problem. And you removed the brackets on the ForEach-Object. You've also added a LOT of random code and copy/paste from around the web, and any of those lines might be causing these problems. I think this approach you're taking is making this much harder than it needs to be. Please try only the approach I've already given you, and let me know what errors you run into. — FoxDeploy
Hello FoxDeploy, i have done it yesterday, the code above was justing experimenting how your code work. Thank you — Arturka1

FoxDeploy FoxDeploy · Accepted Answer · 2015-04-09T13:44:08

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.

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

1 Answers