Help me use powershell and bcp to load CSV into SQL Server

Question

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.

1. 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. Should i be wrapping values in the intermediate CSV file with quotes?

2. 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.

3. In my actual, unsanitized code, i'm getting String data, right truncation on the first row, though the second row is inserted okay.

Anyhow, the initial CSV data that i get from elsewhere looks like this (note: no header row)

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

The target SQL table is like this

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

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.

EDIT: 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.

EDIT: 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"&gt;
 <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

You could do something like this to add the column

import-csv .\forum.csv -Header "Col1","Col2","Col4" | Select Col1, Col2, @{n='Col3';e={'Export1'}}, Col4 | Export-Csv ./forum2.csv -NoTypeInfo

Answer 2

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