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

    Thanks.

  • 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!

  • 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
    {
    param($thread,$msg)
    $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 = @(
    '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'
    )
    # Counters Used

    $count = 0

    Try
    {

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


    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
    $Table.Rows.Add($Row)
    $count ++
    }

    #$table

    #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)
    $conn.Open()


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

    catch
    {
    $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 5 (of 5 total)

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