帮助我使用powershell和bcp将CSV加载到SQL Server中 [英] Help me use powershell and bcp to load CSV into SQL Server
问题描述
我正在使用bcp从其他人从另一个表导出的CSV加载一个表，但是遇到了一些问题.我最初遇到的两个问题是:一个导出字段是一个int，需要以varchar字段结尾，而另一个字段则需要用静态字符串填充.好吧，第一个没什么大不了的，Chad的回答使我想到了@{n='Col3';e={'ABC'}}语法.但是我在围绕正确获取数据的几个问题上步履蹒跚.
I'm using bcp to load a table from a CSV exported from another table by someone else far far away, and have run into some issues. My original two problems: one exported field is an int that needs to end up in a varchar field, and another field needs to be populated with a static string. Well, the first is no big deal, and Chad's answer led me to the @{n='Col3';e={'ABC'}} syntax. But i'm stumbling around several issues getting the data loaded correctly.
-
有时，一个值可能没有空格，有时可能没有空格.如何在bcp格式文件中为此指定分隔符?我问，因为大概我需要用引号将它们括起来.
Sometimes a value might have no spaces, sometimes it might. How do i specify delimiters for that in a bcp format file? I ask because presumably i need to surround these with quotes.
a.我应该在带引号的中间CSV文件中包装值吗?
a. Should i be wrapping values in the intermediate CSV file with quotes?
在我的测试代码中，它将列标题和换行符与第一行的实际值Col1一起推入Col1中.
In my test code, it's shoving the column headers and a linefeed into Col1 with the actual value of Col1 for the first row.
在我实际的未经消毒的代码中，尽管第二行已插入，但第一行却显示String data, right truncation.
In my actual, unsanitized code, i'm getting String data, right truncation on the first row, though the second row is inserted okay.
无论如何，我从其他地方获取的初始CSV数据看起来像这样(注意:没有标题行)
Anyhow, the initial CSV data that i get from elsewhere looks like this (note: no header row)
"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中做到这一点.
I need to load ABC123 into Col1, 123456 into Col2, Export1 into Col3, and APPLE into Col4. Export1 is the static string. I'm doing this in Powershell 1.0.
乍得的import-csv | export-csv看起来很有希望，但是它不喜欢缺少标头，并且PS 1.0不支持-Header选项.
Chad's import-csv | export-csv looks promising, but it doesn't like missing headers, and PS 1.0 doesn't support the -Header option.
上面编辑的描述以反映我如何徘徊到这一点.四列表和三列CSV显然是简化的.一个真实的列是一个城市，因此它可以包含简单的字符串或需要引用的字符串.此时，我的Powershell 1.0代码如下.
Edited description above to reflect how i've wandered to this point. The four-column table and three-column CSV are obviously simplifications. One real column is a city, so it could contain simple strings or those that require quoting. My Powershell 1.0 code at this point is as follows.
$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
推荐答案
为什么不在PowerShell中以编程方式使用ADO.NET的SqlBulkCopy? 您将拥有完全的控制权:逐行读取，转换数据并根据需要填充DataTable，然后分批调用WriteToServer. 请参阅 http://msdn.microsoft.com/en -us/library/system.data.sqlclient.sqlbulkcopy.aspx
Why don't you use ADO.NET's SqlBulkCopy programmatically in PowerShell? You will have full control: read line by line, convert data and populate a DataTable as you wish and invoke WriteToServer in batches. See http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx
这篇关于帮助我使用powershell和bcp将CSV加载到SQL Server中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
