5

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()
TylerH
  • 20,799
  • 66
  • 75
  • 101
Arturka1
  • 65
  • 1
  • 2
  • 11
  • 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 Apr 09 '15 at 15:30
  • Hello FoxDeploy, i have done it yesterday, the code above was justing experimenting how your code work. Thank you – Arturka1 Apr 10 '15 at 07:46

1 Answers1

13

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.

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
FoxDeploy
  • 12,569
  • 2
  • 33
  • 48
  • Hi, thanks for answer but i have problem here with the SQL_PS Module, when i put this command directly in the console the module works, but starting it from my ps script doesnt work, an error tells me that their is no module found – Arturka1 Apr 09 '15 at 14:09
  • Try adding `Import-module sqlps` to your script. If you're running PowerShell v3.0 and newer, you don't need to import-module, but on earlier versions you do have to import first. – FoxDeploy Apr 09 '15 at 14:15
  • so importing is working now but there are some other error, do know an solution without using this module? – Arturka1 Apr 09 '15 at 14:27
  • This module is the best way to work with SQL Server data from PowerShell. Your next step is to update your post with the code you're using right now. – FoxDeploy Apr 09 '15 at 14:28
  • 5
    This can't possibly work without putting each `$_.colname` in a subexpression (`$($_.colname)`). Please fix this. Also, beware that putting variables in SQL statements like this makes you vulnerable to [SQL injection](https://en.wikipedia.org/wiki/SQL_injection). You may want to consider using a [prepared statement](http://stackoverflow.com/a/43308134/1630171) instead. – Ansgar Wiechers Apr 17 '17 at 22:15