如何将“真实”类型添加到数据表？ [英] How to add 'Real' type to a DataTable?
问题描述
我正在将记录从csv文件批量复制到sql表。 sql表中的列为varchar，列为实际数据类型（基于给定的csv属性）
I am bulkcopying records from a csv file to a sql table. The sql table has columns that are varchar, and columns that are real datatype (based on the csv attributes we are given)
让我们假设前7列是外部列varchar（100）的键以及其余80+列的键均为Real数据类型。
Lets suppose that the first 7 columns are the Foreign Keys of varchar(100), and the rest of the 80+ columns are Real datatype.
在批量复制期间，我使用了Out-DataTable函数，因为这显然是批量复制的最有效方法（尤其是对于包含1000条记录的文件）。
During the bulk copy, I used Out-DataTable function because apparently thats the most efficient way to bulk copy (especially with our files containing 1000's of records).
但是，出现以下错误：
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."
现在我希望该错误可以准确指定哪一列，但根据我的研究，我发现可能与假定为所有列的字符串类型的数据类型有关。
Now i wish the error could specify which column exactly, but based on my research, ive found that this could be related to the Datatype being presumed to be string type for all columns.
使用以下内容进行验证： $ column.DataType
Verifying with the following: $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列为字符串，而其余部分为真实数据类型？
So the question is: how do i tell the Datatable to allow the first 7 columns to be string, but the rest of them real datatype?
代码如下：
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 正在检查第一个输入对象的属性...
Out-DataTable is inspecting the properties of the first input object...
foreach($property in $object.PsObject.get_properties())
{
if ($first)
{
...以确定对应的 DataColumn DataType c $ c> ...
...to determine the DataType of the corresponding DataColumn...
if ($property.value -isnot [System.DBNull]) {
$Col.DataType = [System.Type]::GetType("$(Get-Type $property.TypeNameOfValue)")
}
问题是，输入对象由 Import-Csv ...
The problem is, the input objects are produced by Import-Csv...
$CSVDataTable = Import-Csv $csvFile | Out-DataTable
...这不会对CSV字段进行任何转换；每个属性的类型均为 [String] ，因此，每个 DataColumn 也将为
...which doesn't do any conversion of the CSV fields; every property will be of type [String], therefore, every DataColumn will be, too.
.NET等价于 real 的单 ，因此您要么需要硬编码哪些列（按名称或顺序）应为 [Single] ...
The .NET equivalent of real is Single, so you either need to hard-code which columns (by name or ordinal) should be of type [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
}
...或增强检测逻辑...
...or augment your detection logic...
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] 属性。
To comply with the column DataType, this code substitutes the [Single] value for the original property [String] value when parsing succeeds. Note that I've removed the checks for [DBNull] and IsArray because they would never evaluate to $true since, again, Import-Csv will only produce [String] properties.
上面假设，如果可以将第一个输入对象的属性值解析为 [Single] ，那么每个输入对象也是如此。如果不能保证，那么可以一次遍历所有输入对象以确定适当的列类型，然后再次遍历以加载数据...
The above assumes that if a property's value from the first input object can be parsed as a [Single] then the same is true for every input object. If that's not guaranteed, then you can do one pass through all input objects to determine the appropriate column types and a second pass to load the data...
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
这篇关于如何将“真实”类型添加到数据表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
