问题描述
我想通过在我的本地计算机上执行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 和脚本，这将使您的生活更加充实，容易得多.然后，您可以执行以下操作:
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数据库表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！