如何将"真实"类型添加到数据表中？

我正在将CSV文件中的记录批量复制到SQL表中。SQL表包含varchar列和真实数据类型的列(基于给定的CSV属性(。

假设前7列是varchar(100(的外键，其余80多列是Real数据类型。

在大容量复制过程中，我使用了Out DataTable函数，因为这显然是大容量复制最有效的方法(尤其是对于包含1000条记录的文件(。

然而，我得到了以下错误：

Exception calling "WriteToServer" with "1" argument(s): "The given value of type String from the data source cannot be converted to type real of the specified target column."

现在我希望这个错误能准确地指定哪一列，但根据我的研究，我发现这可能与所有列的Datatype都被假定为字符串类型有关。

使用以下内容进行验证：$column.DataType

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object
True     True     String                                   System.Object

所以问题是：我如何告诉Datatable允许前7列是字符串，但其余列是真实的数据类型？

这是代码：

function Get-Type 
{ 
param($type) 

$types = @( 
'System.Boolean', 
'System.Byte[]', 
'System.Byte', 
'System.Char', 
'System.Datetime', 
'System.Decimal', 
'System.Double', 
'System.Guid', 
'System.Int16', 
'System.Int32', 
'System.Int64', 
'System.Single', 
'System.UInt16', 
'System.UInt32', 
'System.UInt64') 

if ( $types -contains $type ) { 
Write-Output "$type" 
} 
else { 
Write-Output 'System.String' 

} 
} #Get-Type
function Out-DataTable 
{ 
[CmdletBinding()] 
param([Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [PSObject[]]$InputObject) 

Begin 
{ 
$dt = new-object Data.datatable   
$First = $true  
} 
Process 
{ 
foreach ($object in $InputObject) 
{ 
$DR = $DT.NewRow()   
foreach($property in $object.PsObject.get_properties()) 
{   
if ($first) 
{   
$Col =  new-object Data.DataColumn   
$Col.ColumnName = $property.Name.ToString()   
if ($property.value) 
{ 
if ($property.value -isnot [System.DBNull]) { 
$Col.DataType = [System.Type]::GetType("$(Get-Type $property.TypeNameOfValue)") 
} 
} 
$DT.Columns.Add($Col) 
}   
if ($property.Gettype().IsArray) { 
$DR.Item($property.Name) =$property.value | ConvertTo-XML -AS String -NoTypeInformation -Depth 1 
}   
else { 
$DR.Item($property.Name) = $property.value 
} 
}   
$DT.Rows.Add($DR)   
$First = $false 
} 
}  

End 
{ 
Write-Output @(,($dt)) 
} 

} #Out-DataTable
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $connectionstring
$SqlConnection.Open()
$CSVDataTable = Import-Csv $csvFile | Out-DataTable
# Build the sqlbulkcopy connection, and set the timeout to infinite
$sqlBulkCopy = New-Object ("Data.SqlClient.SqlBulkCopy") -ArgumentList $SqlConnection
$sqlBulkCopy.DestinationTableName = "$schemaName.[$csvFileBaseName]"
$sqlBulkCopy.bulkcopyTimeout = 0
$sqlBulkCopy.batchsize = 50000
$sqlBulkCopy.DestinationTableName = "$schemaName.[$csvFileBaseName]"
#This mapping helps to make sure that the columns match exactly because BulkCopy depends on indexes not column names by default. 
#However, with the DataTable, the correct mappings seems to be already taken care of, but putting this here regardless, because why not?
#Better safe than sorry, right? ;)
#https://stackoverflow.com/a/50995201/8397835
foreach ($column in $CSVDataTable.Columns) { $sqlBulkCopy.ColumnMappings.Add($column.ColumnName, $column.ColumnName) > $null }
$sqlBulkCopy.WriteToServer($CSVDataTable)
# Clean Up
$sqlBulkCopy.Close(); $sqlBulkCopy.Dispose()
$CSVDataTable.Dispose()
# Sometimes the Garbage Collector takes too long to clear the huge datatable.
[System.GC]::Collect()

也许是这样的？

伪代码：

foreach ($column in $CSVDataTable.Columns) { 
$sqlBulkCopy.ColumnMappings.Add(
if($DestinationTableName.Column.type -eq 'Real') {
$column.type() = 'Real'
}
$column.ColumnName, $column.ColumnName
) > $null 
}

Out-DataTable正在检查第一个输入对象的属性。。。

foreach($property in $object.PsObject.get_properties())
{
if ($first) 
{

以确定对应的CCD_ 4的DataType。。。

if ($property.value -isnot [System.DBNull]) { 
$Col.DataType = [System.Type]::GetType("$(Get-Type $property.TypeNameOfValue)") 
}

问题是，输入对象是由Import-Csv生成的。。。

$CSVDataTable = Import-Csv $csvFile | Out-DataTable

它不进行CSV字段的任何转换；每个属性都将是[String]类型，因此，每个DataColumn也将是。

real的.NET等价物是Single，因此您需要硬编码哪些列(按名称或序号(应为[Single]类型。。。

$objectProperties = @($object.PSObject.Properties)
for ($propertyIndex = 0; $propertyIndex -lt $objectProperties.Length)
{
$property = $objectProperties[$propertyIndex]
if ($propertyIndex -lt 7) {
$columnDataType = [String]
$itemValue = $property.Value
}
else {
$columnDataType = [Single]
$itemValue = if ($property.Value -match '^s*-s*$') {
[Single] 0
} else {
[Single]::Parse($property.Value, 'Float, AllowThousands, AllowParentheses')
}
} 
if ($first) 
{   
$Col =  new-object Data.DataColumn   
$Col.ColumnName = $property.Name
$Col.DataType = $columnDataType
$DT.Columns.Add($Col) 
}
$DR.Item($property.Name) = $itemValue
}

或者增强你的检测逻辑。。。

foreach($property in $object.PSObject.Properties)
{
$singleValue = $null
$isSingle = [Single]::TryParse($property.Value, [ref] $singleValue)
if ($first) 
{   
$Col =  new-object Data.DataColumn   
$Col.ColumnName = $property.Name
$Col.DataType = if ($isSingle) {
[Single]
} else {
[String]
}
$DT.Columns.Add($Col) 
}
$DR.Item($property.Name) = if ($isSingle) {
$singleValue
} else {
$property.value
}
}

为了遵守列DataType，当解析成功时，此代码用[Single]值替换原始属性[String]值。注意，我已经删除了对[DBNull]和IsArray的检查，因为它们永远不会计算为$true，因为Import-Csv只会产生[String]属性。

以上假设，如果第一个输入对象的属性值可以解析为[Single]，则每个输入对象也是如此。如果不能保证这一点，那么您可以对所有输入对象进行一次遍历以确定适当的列类型，并进行第二次遍历以加载数据。。。

function Out-DataTable
{ 
End 
{
$InputObject = @($input)
$numberStyle = [System.Globalization.NumberStyles] 'Float, AllowThousands, AllowParentheses'
$dt = new-object Data.datatable 
foreach ($propertyName in $InputObject[0].PSObject.Properties.Name)
{
$columnDataType = [Single]
foreach ($object in $InputObject)
{
$singleValue = $null
$propertyValue = $object.$propertyName
if ($propertyValue -notmatch '^s*-?s*$' `
-and -not [Single]::TryParse($propertyValue, $numberStyle, $null, [ref] $singleValue))
{
# Default to [String] if not all values can be parsed as [Single]
$columnDataType = [String]
break
}
}
$Col =  new-object Data.DataColumn   
$Col.ColumnName = $propertyName
$Col.DataType = $columnDataType
$DT.Columns.Add($Col) 
}
foreach ($object in $InputObject)
{ 
$DR = $DT.NewRow()   
foreach($property in $object.PSObject.Properties) 
{   
$DR.Item($property.Name) = if ($DT.Columns[$property.Name].DataType -eq [Single]) {
if ($property.Value -match '^s*-?s*$') {
[Single] 0
} else {
[Single]::Parse($property.Value, $numberStyle)
}
} else {
$property.value
}
}   
$DT.Rows.Add($DR)   
} 
Write-Output @(,($dt)) 
}  
} #Out-DataTable

在通过.readXml导入XML数据后，我遇到了类似的挑战，因为XML包含空字符串而不是dbnull。我做了很多测试，以尽快将其转换，对我来说效果最好：

创建一个dataTable，其中所有列都作为数据导入的插入
在同一个表中用正确的目标类型创建名称略有不同的相同列，并创建对字符串列的引用(例如，如果类型为"string"的第一列命名为"c1">
在步骤2中创建每一列的过程中，还创建一个类似于"IIF(LEN([c1]＝0(，NULL，[c1]("的表达式；空字符串"-困境
现在进行批量导入，最后通过dataTableReader将引用列导出到自己的dataTable中

相关内容

最新更新

热门标签：