csv导入空值powershell脚本

Question

我创建了一个名为KS_Invoicing的表，但是下面的powershell脚本不允许我导入NULL值。有什么想法吗？

$database = 'Database'
$server = '192.168.1.1'
$table = 'KS_Invoicing'
$path = '\hmks01c$temp'
$CSVs = get-childitem $path -Filter *.csv
foreach ($c in $CSVs){
Import-CSV $path$c | ForEach-Object { 
$_.PSObject.Properties | Foreach-Object {$_.Value = $_.Value.Trim()} 
$column1= $_."Column One" 
if ($column1) {
$query= "insert into "+$table+" VALUES ('"+$column1+"')"  
Invoke-Sqlcmd -Database $database -ServerInstance $server -Query $query -U ks  -Password ks2020
}
}
Move-Item $path$c "\hmks01c$tempArchive"
}

Answer 1

根据我的第一条评论。导入空值可以正常工作。

'Field1,Field2,Field3
Row1Field1Data,,Row1Field3Data
Row2Field1Data,,Row2Field3Data
Row3Field1Data,,Row3Field3Data' | 
Out-FIle -FilePath 'D:TempEmptyFields.csv'
psEdit 'D:TempEmptyFields.csv'
# Results
<#
Field1,Field2,Field3
Row1Field1Data,,Row1Field3Data
Row2Field1Data,,Row2Field3Data
Row3Field1Data,,Row3Field3Data
#>
Import-Csv -Path 'D:TempEmptyFields.csv'
# Results
<#
Field1         Field2 Field3        
------         ------ ------        
Row1Field1Data        Row1Field3Data
Row2Field1Data        Row2Field3Data
Row3Field1Data        Row3Field3Data
#>

Answer 2

除了之前的回复。如果要导入NULL值，则必须遵循以下语法：https://www.w3resource.com/sql/insert-statement/insert-null.php

因此，您必须检测csv中的空值，并将其替换为"；NULL"；。

$csv = 'Column One,Column Two,Column Three
Row1Field1Data,,Row1Field3Data
,Row1Field2Data,Row2Field3Data
Row3Field1Data,,Row3Field3Data'  | ConvertFrom-Csv
$table = 'KS_Invoicing'
foreach($line in $csv){
$values = $line.PSObject.Properties.Value 
$values | Foreach-Object {$_ = $_.Trim()} 
$tab = foreach($v in $values)
{
if($v -eq ""){'NULL'}
else{"`"$v`""}
}
"insert into $table VALUES ($($tab -join ','))"
}