如何使用Powershell将CSV文件的数据插入SQL Server数据库表 [英] How to insert data of a CSV file into SQL Server db table using powershell

查看:249
本文介绍了如何使用Powershell将CSV文件的数据插入SQL Server数据库表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我想通过在我的本地计算机上执行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数据库表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆