如何使用 PowerShell 从 SQL Server 中的 .csv 导入数据? [英] How to import data from .csv in SQL Server using PowerShell?

问题描述

我有以下场景:我正在使用 PowerShell，并且必须将数据从 .csv 文件导入到 SQL Server 数据库上已创建的表中.所以我不需要来自 csv 的标题行，只需写入数据.

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()

推荐答案

我写了一篇关于在 PowerShell 中使用 SQL 的博客文章，因此您可以在此处阅读更多相关信息.

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

如果您有可用的 SQL-PS 模块，我们可以轻松完成此操作.只需为您的数据库名称、服务器名称和表提供值，然后运行以下命令:

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)')"
  }

为了清楚起见，将 Column1、Column2 替换为 CSV 中列的名称.

不过，请确保您的 CSV 具有与您的 SQL DB 格式相同的值，否则您可能会遇到错误.

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.

这篇关于如何使用 PowerShell 从 SQL Server 中的 .csv 导入数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！