Powershell -验证csv中的列名

Question

对于通过PS从csv文件导入的SQL，我需要检查csv中的列是否为正确的名称。下面的函数告诉我，我丢失了csv文件中的所有列。

Powershell Function CheckCSVColumnsExist
{
Param(
[Object]$CSVImportFile,
[Array]$ColumnsToCheck = ''
)
$c = (get-content $CSVImportFile | Select-Object -First 2) | ConvertFrom-CSV
$ColumnHeaders = $c.psobject.properties.name
foreach ($ctc in $ColumnsToCheck){
if ($ColumnHeaders -notcontains $ctc){
[array]$MissingColumnName += [PSCustomObject]@{Column_Name = $ctc}
}
}
$MissingColumnName
}

执行Powershell命令:

CheckCSVColumnsExist $WorkspaceCSV "column1","column2","column3","column4","column5","column6","column7","column8","column9"

CSV内容:

column1,column,column3,column4,column5,column6,column7,column8,column9
A11111 A111,A111/11,A111,Test Partner,11,A111,DAA,D Test,01/01/1970

Answer 1

问题是，你试图从ConvertFrom-Csv(和Import-Csv)返回的第一个数据行读取列名，但他们不返回任何如果有只是一个标题行在源csv文本(这就是你所拥有的)。

你可以看到:

$tmp = "column1,column2,column3,column4,column5,column6,column7,column8,column9"
Set-Content -Path "c:tempmy.csv" -Value $tmp
$headers = Get-Content -Path "c:tempmy.csv" | Select-Object -First 1
$data = $headers | ConvertFrom-Csv
$null -eq $data
# True

您可以做的是取csv文件的第一行并附加一个虚拟数据行:

$tmp = "column1,column2,column3,column4,column5,column6,column7,column8,column9"
Set-Content -Path "c:tempmy.csv" -Value $tmp
$headers = Get-Content -Path "c:tempmy.csv" | Select-Object -First 1
$data = $headers + "`r`naaa" | ConvertFrom-Csv
#                  ^^^^^^^^^ append a dummy data row
$null -eq data
# False
$data
# column1 : aaa
# column2 :
# column3 :
# column4 :
# column5 :
# column6 :
# column7 :
# column8 :
# column9 :

，然后函数的其余部分将能够读取对象属性。

但是要小心!因为CSV列名可以包含换行符，然后一切都开始出错:

$tmp = "column1,column2,column3,`"column`r`n4`",column5,column6,column7,column8,column9"
#                               ^^^^^^^^^^^^^^^ quoted column name with line breaks
Set-Content -Path "c:tempmy.csv" -Value $tmp
$headers = Get-Content -Path "c:tempmy.csv" | Select-Object -First 1
$headers
# column1,column2,column3,"column
$data = $headers + "`r`naaa" | ConvertFrom-Csv
$null -eq $data
# True

但也许这对你的数据来说不是问题，这可能"足够好"。

如果没有，有各种第三方库，你可以用Add-Type，可以处理csv文件，你可以看看，让你读取列名，即使只有一个标题行在数据…

Answer 2

我想我已经找到解决办法了:

Function CheckCSVColumnsExist
{
Param(
[Object]$CSVImportFile,
[Array]$ColumnsToCheck = ''     
)
$ColumnHeaders = (Import-Csv $CSVImportFile | Get-Member -MemberType NoteProperty).Name
$MissingColumnHeaders = @()
ForEach( $ColumnToCheck in $ColumnsToCheck)
{
$MissingColumnName = New-Object PSObject
If ($ColumnHeaders.Contains($ColumnToCheck) ) 
{
# Nothing to do.
} 
Else 
{
$MissingColumnName | Add-Member -type NoteProperty -Name 'Column_Name' -Value $ColumnToCheck
}
$MissingColumnHeaders += $MissingColumnName
}
Return $MissingColumnHeaders
}

将CSV导入SQL的完整代码

$LogFile="C:CSVImportLogsCSVImports.log";
$WorkspaceCSV  = "C:CSVImporttest.csv";
$sqlServer = "";
$sqlDb = "";
$sqlTable = "";
$SQLUsername = "";
$SQLPassword = "";

function WrDLine {
param([string]$CH);
$($CH*$LR) | Out-File -FilePath $LogFile -Append;
}
function WrDText {
param([string]$STR);
$STR | Out-File -FilePath $LogFile -Append;
}
#logwrite function to write output to log file
Function LogWrite([string]$logstring,[string]$fileLog)
{
#Param ([string]$logstring,[string]$fileLog)

Add-content $WorkspaceCSV -value $logstring
}

#Execute SQL query (Uses .NET, No SQL Server installs required!)
function Invoke-SQL 
{
param(
[string] $sqlCommand = $(throw "Please specify a query.")
)
if ($SQLUsername -eq "") 
{
$connectionString = "Data Source=$sqlServer; " + "Integrated Security=SSPI; " +"Initial Catalog=$sqlDb"
}
else {
$connectionString = "Data Source=$sqlServer;Initial Catalog=$sqlDb;User Id=$SQLUsername; Password=$SQLPassword;Connection Timeout=600;"
}
$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
$connection.Open()
$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet) | Out-Null
$connection.Close()
return $dataSet.Tables
WrDText("$(Get-Date -Format G) Inserted $CSVRowCount rows from CSV into SQL Table $sqlDb.$sqlTable");
}
#check missing columns in CSV before removing data from Staging table
Function CheckCSVColumnsExist
{
Param(
[Object]$CSVImportFile,
[Array]$ColumnsToCheck = ''     
)
$ColumnHeaders = (Import-Csv $CSVImportFile | Get-Member -MemberType NoteProperty).Name
$MissingColumnHeaders = @()
ForEach( $ColumnToCheck in $ColumnsToCheck)
{
$MissingColumnName = New-Object PSObject
If ($ColumnHeaders.Contains($ColumnToCheck) ) 
{
# Nothing to do.
} 
Else 
{
$MissingColumnName | Add-Member -type NoteProperty -Name 'Column_Name' -Value $ColumnToCheck
}
$MissingColumnHeaders += $MissingColumnName
}
Return $MissingColumnHeaders
}


##############################################
# Importing workspace CSV data
##############################################
$CSVImport = @(Import-CSV $WorkspaceCSV -encoding UTF7);
$CSVRowCount = $CSVImport.Count
if (($CSVRowCount -gt 0) -and (CheckCSVColumnsExist $WorkspaceCSV "column1","column2","column3","column4","column5","column6","column7","column8","column9"))
{
# ForEach CSV Line Inserting a row into the staging SQL table
write-host "Inserting $CSVRowCount rows from CSV into SQL Table $sqlDb.$sqlTable";
WrDText("$(Get-Date -Format G) Inserting $CSVRowCount rows from CSV into SQL Table $sqlDb.$sqlTable");
# Clear SQL Table
$Clearsql = "Delete FROM $sqlDb.$sqlTable";
Invoke-SQL $Clearsql

ForEach ($CSVLine in $CSVImport)
{
# Setting variables for the CSV line
$workspaceid = $CSVLine.column2
$library = "Test"
$wsname = $CSVLine.column1
$custom1 = $CSVLine.column3
$custom2 = $CSVLine.column5
$custom3 = $CSVLine.column7
$custom4 = "UK"
$c1desc = $CSVLine.column4
$c2desc = $CSVLine.column6
$c3desc = $CSVLine.column8
$c4desc = "United Kingdom"
$CDate1 = $CSVLine.column9
$insertdate = Get-Date -Format G
$tries  = "0"

##############################################
# SQL INSERT of CSV Line/Row
##############################################
$SQLInsert = "INSERT INTO $sqlDb.$sqlTable ([WorkspaceID],[Library],[Name],[C1Alias],[C2Alias],[C3Alias],[C4Alias],[C1Desc],[C2Desc],[C3Desc],[C4Desc],[CDate1],[InsertDate],[Tries])
VALUES('$workspaceid', '$library', '$wsname', '$custom1', '$custom2', '$custom3', '$custom4', '$c1desc', '$c2desc', '$c3desc', '$c4desc', '$CDate1', '$insertdate', '$tries')";
#$SQLInsert
# Running the INSERT Query
Invoke-SQL $SQLInsert

} #End of ForEach CSV line
}
else 
{
write-host "The CSV has 0 rows or the column names have been changed check $WorkspaceCSV";
WrDText("$(Get-Date -Format G) The CSV has 0 rows or the column names have been changed check $WorkspaceCSV");
}

Answer 3

如果您的csv确实有数据，您可以将函数更改为如下内容:

function CheckCSVColumnsExist {
[CmdletBinding()]
param (
[Parameter(Mandatory = $true, Position = 0)]
[string]$CSVImportFile,
[string[]]$ColumnsToCheck = $null
)
$firstRow = (Import-Csv -Path $CSVImportFile)[0]
$ColumnHeaders = $firstRow.PsObject.Properties.Name
# return an array of missing column headers
,@($ColumnHeaders | Where-Object { $ColumnsToCheck -notcontains $_ } )
}
$WorkspaceCSV = 'D:Testdata.csv'
$missing = CheckCSVColumnsExist $WorkspaceCSV "column1","column","column3","column4","column5","column6","column7","column8","column9"
if ($missing.Count) {
Write-Host ('Missing headers: {0}' -f ($missing -join ';'))
}
else {
Write-Host 'All columns correct' -ForegroundColor Green
}

^{在上面，我故意添加了"column"错误，其中在csv文件中称为"column2"以显示输出:}

Missing headers: column2