帮助我使用 powershell 和 bcp 将 CSV 加载到 SQL Server

Question

我正在使用 bcp 从远方其他人从另一个表导出的 CSV 加载表，但遇到了一些问题。我原来的两个问题：一个导出的字段是一个 int，需要以 varchar 字段结束，另一个字段需要用静态字符串填充。嗯，第一个问题没什么大不了的，Chad 的回答让我想到了 @{n='Col3';e={'ABC'}} 语法。但我在正确加载数据方面遇到了几个问题。

1. 有时一个值可能没有空格，有时可能有。如何在 bcp 格式文件中指定分隔符？我问这个问题是因为我可能需要用引号将它们引起来。

   a.我应该用引号将中间 CSV 文件中的值括起来吗？

2. 在我的测试代码中，它将列标题和换行符推送到 Col1 中，并使用第一行的 Col1 的实际值。

3. 在我实际的、未经消毒的代码中，我在第一行得到字符串数据，右截断，尽管第二行插入正常。

无论如何，我从其他地方获得的初始 CSV 数据看起来像这样（注意：没有标题行）

"ABC123",123456,"APPLE"
"XYZ789",456789,"ORANGE"

目标 SQL 表是这样的

Col1 varchar(50) (Primary Key)
Col2 varchar(50)
Col3 varchar(50)
Col4 varchar(50)

我需要将 ABC123 加载到 Col1 中， 123456 导入 Col2，Export1 导入 Col3，APPLE 导入 Col4。 Export1 是静态字符串。我在 Powershell 1.0 中执行此操作。

编辑：乍得的导入-csv | export-csv 看起来很有前途，但它不喜欢缺少标头，而且 PS 1.0 不支持 -Header 选项。

编辑：编辑上面的描述以反映我是如何走到这一点的。四列的表格和三列的 CSV 显然是简化的。一个真实的列是一座城市，因此它可以包含简单的字符串或需要引用的字符串。此时我的Powershell 1.0代码如下。

$SQLSERVER="svr"
$SQLTABLE="test"
$SQLUSER="u"
$SQLPASS="p"

$TESTFILE = "testdata.csv"
$TESTFILEHDR = "testdata-wHeaders.csv"
$TESTFILEFIX = "testdata-fixed.csv"
$OrigHeaders = "`"Col1`",`"Col2`",`"Col3`"`n"

function Create-BcpFormat($fileName)
{
@"
<?xml version='1.0'?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID='1' xsi:type='CharTerm' TERMINATOR=',"' COLLATION='SQL_AltDiction_CP850_CI_AS'/>
  <FIELD ID='2' xsi:type='CharTerm' TERMINATOR='",' COLLATION='SQL_AltDiction_CP850_CI_AS'/>
  <FIELD ID='3' xsi:type='CharTerm' TERMINATOR=',' COLLATION='SQL_AltDiction_CP850_CI_AS'/>
  <FIELD ID='4' xsi:type='CharTerm' TERMINATOR='\r\n' COLLATION='SQL_AltDiction_CP850_CI_AS'/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>
"@ | Set-Content $filename
}

## GHI456 is already in the table, clean out previous attempts
"`nclean test table:"
osql @("-S","$SQLSERVER","-U","$SQLUSER","-P","$SQLPASS","-Q",
    """delete from $SQLTABLE where col1 <> 'GHI456' or col1 is null""")

## Prepend
$body = [string]::join([environment]::NewLine, (gc $TESTFILE))
$OrigHeaders + $body > $TESTFILEHDR

"`nTESTFILEHDR:"
type $TESTFILEHDR

$accts = Import-csv $TESTFILEHDR | select 'Col1', 'Col2', @{n='Col3';e={'ABC'}}, @{n='Col4';e={$_.Col3}}
$accts
$accts | Export-Csv $TESTFILEFIX -NoTypeInfo

"`nTESTFILEFIX:"
type $TESTFILEFIX

$BCPFMTFILE = "bcp.fmt"
$BCPERRFILE = "bcp.err"
Create-BcpFormat $BCPFMTFILE
bcp @("$SQLTABLE","in","$TESTFILEFIX","-S","$SQLSERVER","-U","$SQLUSER","-P","$SQLPASS","-f",$BCPFMTFILE,"-e",$BCPERRFILE)
Remove-Item $BCPFMTFILE

"`ntest table:"
osql @("-S","$SQLSERVER","-U","$SQLUSER","-P","$SQLPASS","-Q",
    """select left(Col1,20) 'Col1', left(Col2,8) 'Col2', left(Col3,8) 'Col3', left(Col4,8) 'Col4' from $SQLTABLE""")

"`nBCPERRFILE:"
type $BCPERRFILE

Answer 1

为什么不在 PowerShell 中以编程方式使用 ADO.NET 的 SqlBulkCopy？
您将拥有完全的控制权：逐行读取、转换数据并根据需要填充 DataTable 并批量调用 WriteToServer。
请参阅 http://msdn.microsoft.com/en -us/library/system.data.sqlclient.sqlbulkcopy.aspx

Answer 2

您可以执行类似的操作来添加列

import-csv .\forum.csv -Header "Col1","Col2","Col4" |选择 Col1、Col2、@{n='Col3';e={'Export1'}}、Col4 |导出-CSV ./forum2.csv -NoTypeInfo