批量插入 - （0 行受影響）

我正在嘗試使用批量插入將 .CSV 文件導入 mssql 表（儘管我對其他方法持開放態度）。我有一個批量插入 sql 語句，但是當我執行文件時它說（0 行受影響）。但是，我在文件本身中有數據。

CSV 文件範例（標題 + 標題下方的一行）

---

.CSV 標頭

更改，停用，鎖定，noemail，empno，orighire，薪水，salut，fname，mi，lname，使用者名，hstreet1，hstreet2，hcity，hstate，hcountry，hzip，busphone，busext，蜂窩，empemail，職位，職位程式碼，工作組， orglevel1,orglevel2,orglevel3,orglevel4,orglevel5,company,perfdate,supervisor,misc1,misc2,misc3,misc4,active

.CSV 內容

2014-04-03 00:00:00,NO ,NO ,NO ,1133,2014-04-03 00:00:00,1111.1111,,test,test,test,user.test,111 測試方式,,Sacramento, CA,USA,11111,,,,test@test.org,test test,057,02,sss,261,SAC,,test,ttt,1911-11-11 00:00:00,1111,N,,test ,0,空

---

.SQL

BULK
INSERT dbo.Archive
FROM 'C:\scripts\User.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\r\n',
FIRSTROW = 2
)
GO

我試圖通過 ps 來實現這一點，這就是結果和程式碼：

# Database variables
$sqlserver = "dbserver"
$database = "db"
$table = "tb"

# CSV variables
$csvfile = "C:\scripts\User.csv"
$csvdelimiter = ","
$firstRowColumnNames = $true

################### No need to modify anything below ###################
Write-Host "Script started..."
$elapsed = [System.Diagnostics.Stopwatch]::StartNew() 
[void][Reflection.Assembly]::LoadWithPartialName("System.Data")
[void][Reflection.Assembly]::LoadWithPartialName("System.Data.SqlClient")

# 50k worked fastest and kept memory usage to a minimum
$batchsize = 50000

# Build the sqlbulkcopy connection, and set the timeout to infinite
$connectionstring = "Data Source=$sqlserver;Integrated         Security=true;Initial Catalog=$database;"
$bulkcopy = New-Object Data.SqlClient.SqlBulkCopy($connectionstring, [System.Data.SqlClient.SqlBulkCopyOptions]::TableLock)
$bulkcopy.DestinationTableName = $table
$bulkcopy.bulkcopyTimeout = 0
$bulkcopy.batchsize = $batchsize

# Create the datatable, and autogenerate the columns.
$datatable = New-Object System.Data.DataTable

# Open the text file from disk
$reader = New-Object System.IO.StreamReader($csvfile)
$columns = (Get-Content $csvfile -First 1).Split($csvdelimiter)
if ($firstRowColumnNames -eq $true) { $null = $reader.readLine() }

foreach ($column in $columns) { 
$null = $datatable.Columns.Add()
}

# Read in the data, line by line
while (($line = $reader.ReadLine()) -ne $null)  {
$null = $datatable.Rows.Add($line.Split($csvdelimiter))
$i++; if (($i % $batchsize) -eq 0) { 
   $bulkcopy.WriteToServer($datatable) 
   Write-Host "$i rows have been inserted in $($elapsed.Elapsed.ToString())."
   $datatable.Clear() 
} 
} 

# Add in all the remaining rows since the last clear
if($datatable.Rows.Count -gt 0) {
$bulkcopy.WriteToServer($datatable)
$datatable.Clear()
}

# Clean Up
$reader.Close(); $reader.Dispose()
$bulkcopy.Close(); $bulkcopy.Dispose()
$datatable.Dispose()

Write-Host "Script complete. $i rows have been inserted into the database."
Write-Host "Total Elapsed Time: $($elapsed.Elapsed.ToString())"
# Sometimes the Garbage Collector takes too long to clear the huge datatable.
[System.GC]::Collect()

錯誤

PS C:\scripts> C:\scripts\Import-CSVtoSQL.ps1 腳本已啟動…使用“1”參數呼叫“WriteToServer”異常：“從 bcp 客戶端接收到 colid 38 的無效列長度。” 在 C:\scripts\Import-CSVtoSQL.ps1:51 char:2 + $ bulkcopy.WriteToServer( $ 數據表）+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:)

$$ $$, MethodInvocationException + FullyQualifiedErrorId : SqlException

在我嘗試導入的表中，我有額外的列。即：Datestamp, batch number. 表中的內容比 csv 文件多一定是問題所在。我導入了一個臨時表，該表僅包含 csv 中定義的列並且有效。

原始問題\n\r作為行終止符。在 Windows 上，行終止符通常是\r\n，首先是輸入符，然後是換行符（出於晦澀但有趣的歷史原因）。BULK INSERT在你的命令中試試。

接下來，我將嘗試在十六進制編輯器中載入文件以驗證換行符的確切內容。例如，如果數據是 Unicode，您可能需要\0\r\0\n作為分隔符和/或DATAFILETYPE ='widechar'標誌。注意0x0A是十進制的 10，0x0D是十進制的 13：換行 ( \n) 和輸入 ( \r) 的 ASCII 程式碼，分別。

引用自：https://dba.stackexchange.com/questions/103944