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

by

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.

More Related Contents:

  1. Unexpected variable type returned by Receive-Job
  2. incorrect syntax near keyword where [closed]
  3. nvarchar concatenation / index / nvarchar(max) inexplicable behavior
  4. How do I create a unique constraint that also allows nulls?
  5. How do I get list of all tables in a database using TSQL?
  6. How to convert SQL Server’s timestamp column to datetime format
  7. Multiple rows to one comma-separated value in Sql Server [duplicate]
  8. Escape a string in SQL Server so that it is safe to use in LIKE expression
  9. SQL Server: Difference between PARTITION BY and GROUP BY
  10. How do you truncate all tables in a database using TSQL?
  11. Import ‘xml’ into Sql Server
  12. Query times out when executed from web, but super-fast when executed from SSMS
  13. Efficient ISNUMERIC() replacements on SQL Server?
  14. Login to Microsoft SQL Server Error: 18456
  15. SQL Server datetime LIKE select?
  16. Adding a query hint when calling Table-Valued Function
  17. Find non-ASCII characters in varchar columns using SQL Server
  18. How can I import an Excel file into SQL Server? [closed]
  19. Violation of UNIQUE KEY constraint on INSERT WHERE COUNT(*) = 0 on SQL Server 2005
  20. How can I determine in SQL Server if a dateTime range overlaps another
  21. Intermittent ODBC connection failures
  22. SQL Server Query – groupwise multiplication
  23. SQL Server recursive query
  24. Getting week number off a date in MS SQL Server 2005?
  25. Set database collation in Entity Framework Code-First Initializer
  26. What does the SQL # symbol mean and how is it used?
  27. Restore a SQL Server 2000 backup on SQL Server 2012
  28. time format in SQL Server
  29. Why would using a temp table be faster than a nested query?
  30. Convert float into varchar in SQL Server without scientific notation

Leave a Comment