代码之家  ›  专栏  ›  技术社区  ›  b w

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

  •  0
  • b w  · 技术社区  · 14 年前

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

    1. a、 我应该用引号将中间CSV文件中的值包装起来吗?

    2. 在我的测试代码中,它将列标题和换行符压缩到Col1中,第一行的实际值为Col1。

    3. 在我实际的未初始化代码中 String data, right truncation

    无论如何,我从其他地方获得的初始CSV数据如下所示(注意:没有标题行)

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

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

    ABC123型 进入之内 Col1 , Col2 出口1 进入之内 Col3 苹果 Col4 . 是静态字符串。我在Powershell 1.0中执行此操作。

    编辑:Chad的import csv | export csv看起来很有前途,但它不喜欢缺少标题,而且ps1.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
    
    2 回复  |  直到 14 年前
        1
  •  2
  •   vaso    14 年前

    您将拥有完全的控制权:逐行读取、转换数据并根据需要填充DataTable,以及成批调用WriteToServer。 看到了吗 http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

        2
  •  1
  •   Chad Miller    14 年前

    导入csv\论坛.csv-标题“Col1”,“Col2”,“Col4”|选择Col1,Col2,@{n='Col3';e={'Export1'},Col4 | Export Csv./forum2.Csv-NoTypeInfo

    推荐文章