BulkCopy error

  • I'm trying to load a csv to SQL using the BULKCOPY, but running into a issue load the string value into a Float within SQL I build a Data Table then load from there.

    The data from the csv has values like .247888


  • I could be mistaken, but a value of ".247888" is going to be interpreted as a string as it doesn't start with a numeric value.  What I mean is in your CSV, I think your data would need to be "0.247888" to be interpreted as a float (or any numeric data type).

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • This is what my script looks like.


    THis is where the error is coming from:

    $Row.StdMargin_Pct = $_.StdMargin_Pct

    $global:currenttime= Set-PSBreakpoint -Variable currenttime -Mode Read -Action { $global:currenttime= Get-Date }

    Write-host "Starting File Load to SQL"
    write-host $currenttime

    $sqlsvr = 'xxxx'
    $database = 'xxxxx'
    $tablename = 'cost_import'

    $scriptRoot = "\\sv1\saleshist"

    $CsvDir = "$scriptRoot\"
    $arcDir = "$scriptRoot\Archive\"

    function Write-ScriptLog
    $outfile = 'smocsvtodb.log'
    Add-Content -Path "$CsvDir$outfile" -Value "$((Get-Date).ToString("yyyy-MM-dd HH:mm")) $thread $msg"

    }# Write-ScriptLog

    #Initialize Data Table
    $Table = New-Object System.Data.DataTable $tablename

    $TableColNames = @(
    # Counters Used

    $count = 0


    foreach ($TableColName in $TableColNames)
    $col_ = New-Object system.Data.DataColumn $TableColName,([string])

    Import-Csv -Delimiter '|' -Path $scriptRoot\dbgcostcube.csv -header Invoicenbr,InvoiceDate,SxbGroup,MwAccountType,LineNbr,CeLbs,ActualCost,CostSource,Margin,MetalMargin,MetalType,Rebate,
    StdMargin,StdMargin_Pct,Idh_Std_Cost,Idh_Price,MtlCost,Lbrbrd_Cost,Acconv_Matcst,IhNbr,IhUserID,Ih_OrdDate,IhFrtTerms,Ih_Channel,Idh_site |

    ForEach {
    $Row = $Table.NewRow()
    $Row.Invoicenbr = $_.Invoicenbr
    $Row.Invoicedate = $_.Invoicedate
    $Row.SXBGroup = $_.SXBGroup
    $Row.MwAccountType = $_.MwAccountType
    $Row.linenbr = $_.linenbr
    $Row.CeLbs = $_.CeLbs
    $Row.ActualCost = $_.ActualCost
    $Row.CostSource = $_.CostSource
    $Row.MArgin = $_.MArgin
    $Row.MetalMargin = $_.MetalMargin
    $Row.MetalType = $_.MetalType
    $Row.Rebate = $_.Rebate
    $Row.StdMargin = $_.StdMargin
    $Row.StdMargin_Pct = $_.StdMargin_Pct
    $Row.Idh_Std_Cost = $_.Idh_Std_Cost
    $Row.Idh_Price = $_.Idh_Price
    $Row.MtlCost = $_.MtlCost
    $Row.Lbrbrd_Cost = $_.Lbrbrd_Cost
    $Row.Acconv_Matcst = $_.Acconv_Matcst
    $Row.IhNbr = $_.IhNbr
    $Row.IhUserID = $_.IhUserID
    $Row.Ih_OrdDate = $_.Ih_OrdDate
    $Row.IhFrtTerms = $_.IhFrtTerms
    $Row.Ih_Channel = $_.Ih_Channel
    $Row.Idh_site = $_.Idh_site
    $count ++


    #Create SQL Connection
    Write-host "Creating SQL Connection"
    $ConnectionString = "Data Source=$sqlsvr;Initial Catalog=$database; Integrated Security=SSPI"
    $conn = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)

    $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $ConnectionString
    $bulkCopy.DestinationTableName = $tablename
    $bulkCopy.BatchSize = 25000
    $bulkCopy.BulkCopyTimeout = 0

    $ex = $_.Exception
    Write-Error "$ex.Message"
    exit 1


    Write-host "Ending File Load to SQL"
    write-host $currenttime
  • and what is the error you getting?

  • error converting string to float.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply