如何使用Powershell将CSV文件的数据插入SQL Server数据库表 [英] How to insert data of a CSV file into SQL Server db table using powershell
问题描述
我想通过在我的本地计算机上执行PS Script来做到这一点.DB服务器是远程计算机.
I want to do this by executing PS Script from my local machine.. where DB server is remote machine.
在CSV文件中,我没有指定任何列名.(仅逗号分隔且对齐的o/p).
And in the CSV file I don't have any column names specified.(only comma separated and aligned o/p).
我不想使用BULK INSERT
推荐答案
我有两种方法可以解决这个问题:
There are two ways I would approach this:
BCP.exe
SQL Server提供了命令行实用程序 bcp 来批量导入数据.您可以简单地将bcp执行合并到Powershell脚本或窗口中以加载csv数据. 示例:
SQL Server provides the command line utility bcp to bulk import data. You could simply incorporate the bcp execution into your Powershell script or window to load the csv data. Example:
$loadfile = "C:\datafile\loadthis.csv"
bcp pity.dbo.foo in $loadfile -T -c -t","
使用.NET
您也可以在Powershell中使用.NET库,但这是一个比较棘手的建议.首先,获取 Out-DataTable 和 Write-DataTable 脚本,这将使您的生活更加充实,容易得多.然后,您可以执行以下操作:
You could also use the .NET libraries in Powershell, but this is a much trickier proposition. First, get the Out-DataTable and Write-DataTable scripts by Chad Miller, which will make your life much, much easier. Then you could do the following:
$dt = Import-Csv -Path "C:\datafile\loadthis.csv" | Out-DataTable
Write-DataTable -ServerInstance "localhost" -Database "pity" -TableName "foo" -Data $dt
These and other solutions can be found in detail in this blog post.
这篇关于如何使用Powershell将CSV文件的数据插入SQL Server数据库表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!