July 28, 2021 at 1:29 pm
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.
July 28, 2021 at 5:33 pm
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.
July 30, 2021 at 12:03 am
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
July 30, 2021 at 7:17 am
and what is the error you getting?
July 30, 2021 at 10:26 am
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