MSSQL 如何透過 PowerShell 上傳 CSV 到資料庫上

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 這個應該才是標準解，專門為編程而生的，搞定了再放上來。