MSSQL 如何透過 Bat 上傳 CSV 到資料庫上
能夠上傳CSV的指令微軟內建有兩個 bcp.exe 跟 BULK INSERT，不過後者是寫在sql中的代碼有個很致命的缺點是只能上傳HOST端上的檔案，無法從其他電腦上傳過去。
還有一點要注意的是雖然文章是寫CSV，不過準確地來講是DATA，內容不能包含檔頭與頭尾雙引號。不能包含是因為他就按照那個樣子傳上去，到時候資料庫上會看到全部都帶有雙引號，也因為沒有雙引號保護的關係，是無法上傳逗號上去的，會被當作下一份資料的分割。
BULK INSERT 的上傳方法
雖然已知這方法有個根本上的致命問題，不過還是筆記一下紀錄，或許會在哪裡用到。要解決這個致命問題其實有另一個解法是把CSV上傳到HOST也能讀取的SAMA上就能傳了。
這個方法好處大概就是可以跳過檔頭吧，代碼中的 “FIRSTROW = 2” 指的是從第二行開始上傳。
$serverName = "localhost"
$databaseName = "CHG"
$userName = "kaede"
$password = "1230"
$csvFilePath = "\\ORACLE-SV\Source\csvfile.csv"
$schemaName = "CHG"
$tableName = "Employees"
$Table = "[$databaseName].[$schemaName].[$tableName]"
$query = @"
BULK INSERT $Table
FROM '$csvFilePath'
WITH
(
FORMAT = 'CSV',
FIRSTROW = 2
);
"@
sqlcmd -S $serverName -d $databaseName -U $userName -P $password -Q $query
-
BCP 的上傳方法
因為檔案是要讀取純資料的關係，這邊也寫了一個函式自動處理CSV的轉換。
function Export-CustomCSV {
param (
[Parameter(ValueFromPipeline = $true)]
[psobject[]]$InputObject,
[string]$InputPath,
[Parameter(Mandatory = $true)]
[string]$OutputPath,
[string]$Delimiter = ',',
[System.Text.Encoding]$Encoding = (New-Object System.Text.UTF8Encoding $False),
[switch]$SkipHeader
)
begin {
$writer = New-Object System.IO.StreamWriter -ArgumentList $OutputPath, $false, $Encoding
$headerProcessed = $false
}
process {
if ($InputObject) {
foreach ($obj in $InputObject) {
$line = ""
$properties = $obj | Get-Member -MemberType Properties
foreach ($prop in $properties) {
if (-not [string]::IsNullOrEmpty($line)) {
$line += $Delimiter
}
$value = $obj.$($prop.Name) -replace '"', '""'
$line += $value
}
$writer.WriteLine($line)
}
} elseif ($InputPath) {
$reader = New-Object System.IO.StreamReader -ArgumentList $InputPath, $Encoding
while (-not $reader.EndOfStream) {
$line = $reader.ReadLine()
if ($SkipHeader -and -not $headerProcessed) {
$headerProcessed = $true
continue
}
$fields = $line.Split($Delimiter)
$newLine = ""
for ($i = 0; $i -lt $fields.Length; $i++) {
$cleanField = $fields[$i].Trim('"')
if ($i -gt 0) {
$newLine += $Delimiter
}
$newLine += $cleanField
}
$writer.WriteLine($newLine)
}
$reader.Close()
}
}
end {
$writer.Close()
}
}
接下來是上傳的部分
$sourceCsv = 'input.csv' # 更改為您的源 CSV 文件路徑
$destinationCsv = 'output_existing.csv' # 更改為您的目標 CSV 文件路徑
Export-CustomCSV -InputPath $sourceCsv -OutputPath $destinationCsv -SkipHeader
$serverName = "localhost"
$databaseName = "CHG"
$userName = "kaede"
$password = "1230"
$schemaName = "CHG"
$tableName = "TEST"
$Table = "[$databaseName].[$schemaName].[$tableName]"
$csv = $destinationCsv
$output = & bcp $Table in $csv -c -t ',' -r "\n" -S $serverName -U $userName -P $password
$hasError = $false
$numRowsCopied = 0
$outputString = $output -join "`n"
if ($outputString -match "(\d+) rows copied\.") {
$numRowsCopied = [int]$matches[1]
if ($numRowsCopied -eq 0) {
$hasError = $true
}
}
if ($hasError) {
Write-Host "BCP 命令執行失敗，錯誤信息："
Write-Host $outputString
} else {
Write-Host "BCP 命令執行成功，共複製了 $numRowsCopied 行"
}
-
追加寫完之後發現 PowerShell 還有一個工具 System.Data.SqlClient.SqlConnection 這個應該才是標準解，專門為編程而生的，搞定了再放上來。
